DBCC SHOWCONTIG- introduced in SQL Server 2000
sys.dm_db_index_physical_stats- introduced in SQL Server 2005
Question: Is it wrong to consider scan density as a performance metric in higher versions of SQL Server apart from SQL Server 2000? I am looking for an answer whether SCAN density was or was not something we should be concerned about in higher versions of SQL Server (> SQL Server 2000), as this metric was rolled out in DMVs and functions.
Background: I have a requirement to maintain scan density of more than 90% in on of my SQL Server database (SQL Server 2016) as part of vendor's requirement.
However, I can see in the Microsoft site,
DBCC SHOWCONTIG as a deprecated feature which will be removed in future version of SQL Server and I don't see the SCAN DENSITY column in function
sys.dm_db_index_physical_stats (which is the replacement of
For one of my table with 12954223 rows, below are the stats for clustered index (page count - 830943) with different queries:
Scan density - 76.11 %[103868:106468] Logical scan fragmentation - 4.68%
avg_page_space_used_in_precent - 92.84 % agg_fragmentation_in_precent - 4.68%