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:
- We have compaction figure at an aggregate level - potentially could be different compaction ratio for different internal volumes.
- 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
Post a Comment