SQL Server Performance – Is SCAN DENSITY a Valid Metric?

sql server

  • 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 DBCC SHOWCONTIG).

Example:

For one of my table with 12954223 rows, below are the stats for clustered index (page count – 830943) with different queries:

With DBCC SHOWCONTIG:

Scan density - 76.11 %[103868:106468]
Logical scan fragmentation - 4.68%

With sys.dm_db_index_physical_stats

avg_page_space_used_in_precent - 92.84 %
agg_fragmentation_in_precent - 4.68%

Best Answer

Scan Density is how many times you jump from one extent to another (dividing the "optimal" to the current state). Thing is that this is a side effect of logical fragmentation (jump back and forth between pages, and you are likely to jump back and forth between extents). When the DBCC was redone into a DMV, Paul cleaned up lots of stuff in there.

I suggest you work with logical frag instead (avg frag in percent), if you care about how contiguous things are in the first place. Perhaps more interesting is how full pages are, since random vs seqential I/O is probably more or less irrelevant with modern hardware (read-ahead set aside).

Paul vocals pretty strongly how we should stop using the old DBCC and use the DMV instead. Do some searches and you will find lots of good nuggets out there. Here is one thread that can give you some insights, for instance: https://social.microsoft.com/Forums/partner/en-US/8d01a374-1493-43c3-8cea-5bf5de723b4d/dmdbindexphysicalstats-and-dbcc-showcontig-disparity.

Related Question