Using the OCI DWH to Calculate Storage Efficiency Figures

The following post details an SQL query that you can use to get Storage Efficiency figures per aggregate (storage pool) for your NetApp ONTAP systems (both Clustered ONTAP and 7-Mode), from the NetApp OnCommand Insight Data Warehouse.

These figures should be pretty close to what you see in ActiveIQ - they won't be perfect but close. You need to remember:

  1. We have compaction figure at an aggregate level - potentially could be different compaction ratio for different internal volumes.
  2. We have deduplication and compression ratio for the whole internal volume and not just the dataUsed bit (data used not including snapshot) - potentially could have different ratios for the dataUsed bit and the snapshot bit.
Image: NetApp Active IQ > Capacity and Efficiency > Storage Efficiency > Node > Savings without Snapshot Backups

The SQL Query

SELECT
*,
`Logical Used MB`/`Physical Used MB` AS 'Storage Efficiency'
FROM(
 SELECT
 CASE
  WHEN s.`microcodeVersion` like '%7-Mode' THEN '7-Mode'
  WHEN s.`microcodeVersion` like '%ONTAP' THEN 'ONTAP'
  ELSE 'UNKNOWN'
 END AS 'Mode',
 spd.storageName AS 'Storage',
 spd.name AS 'Aggregate',
 COUNT(*) AS 'Vol Count',
 SUM(i.dataUsedCapacityMB) AS 'DataUsed Minus Snapshots MB',
 FLOOR(SUM(i.dataUsedCapacityMB) * COALESCE(spcf.compactionRatio,1)) AS 'Physical Used MB',
 FLOOR(SUM(
  CASE
   WHEN i.`dedupeRatio` IS NOT NULL AND i.`compressionRatio` IS NOT NULL
    THEN
     CASE
      WHEN s.`microcodeVersion` like '%7-Mode'
       THEN (i.`dataUsedCapacityMB` * 1/(i.`dedupeRatio` * i.`compressionRatio`))
      ELSE (i.`dataUsedCapacityMB` * 1/(i.`dedupeRatio` + i.`compressionRatio` -1))
     END
   WHEN i.`dedupeRatio` IS NOT NULL THEN (i.`dataUsedCapacityMB` * 1/(i.`dedupeRatio`))
   WHEN i.`compressionRatio` IS NOT NULL THEN (i.`dataUsedCapacityMB` * 1/(i.`compressionRatio`))
   ELSE i.`dataUsedCapacityMB`
  END)) AS 'Logical Used MB'
 FROM dwh_capacity.internal_volume_capacity_fact AS i
 JOIN dwh_capacity.date_dimension AS dd ON dd.tk = i.dateTk
 JOIN dwh_capacity.storage_pool_dimension AS spd ON spd.tk = i.storagePoolTk
 JOIN dwh_capacity.storage_and_storage_pool_capacity_fact AS spcf ON i.storagePoolTk = spcf.storagePoolTK AND i.dateTk = spcf.dateTk
 JOIN dwh_capacity.storage_dimension AS s ON s.tk = spcf.storageTk
 WHERE dd.latest = 1
 AND s.manufacturer = 'NetApp'
 GROUP BY i.storagePoolTk
) AS t0
-- TRY TO REMOVE ROOT AGGRS --
WHERE `Vol Count` > 1
AND `Aggregate` NOT LIKE '%_ROOT'

Comments