It would be more useful to be able to filter indexes based on the number of pages rather than the size.
voted UP accidentally ...
Why do you think it will be more useful? This is the same thing, just different angles
Check this out:
http://thwack.solarwinds.com/ideas/2794#comment-178376
Results of sizing an index are also relative to the method used to do so, especially when dealing with clustered. If you are estimating index size simply by multiplying 8 * (# of pages) vs using a query joining sys.indexes with sys.partitions or sys.sys.dm_db_partition_stats, you will get wildly differing results (which may well be how the Appinsight template works).
For example, I have a clustered index that is continuously flagged by SW that has 46 pages even though I had the "Minimum Size (KB) of Indexes to Retrieve:" set to 8000 (I've even set it to 10000 and 15000 and still being flagged).
If I view it in Idera's "Quick Index", it shows 46 pages (which is consistent) and an estimated size of 368KB. Right on, right? So, this should NOT be getting flagged by SolarWinds if it were just calculating based on the number of pages... but it is.
If I use the following query to determine the index size on a clustered index, it will return the size of the table for the index size:
SELECT i.[name] AS IndexName
,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
GROUP BY i.[name]
ORDER BY i.[name]
GO
Filtering using the number of pages will bring the desired consistent results "I" am looking for that eliminates much of the "depends" that the index size stat obviously brings. I'm a big fan of simplicity.
