Systems Engineering and RDBMS

DBCC SHOWCONTIG and Extent Scan Fragmentation

Posted by decipherinfosys on June 10, 2008

One of the client DBAs had asked this question some time back so we thought we would post the discussion on the blog since it is likely that other DBAs/Database Developers might have this question as well. What he had noticed was this:

He ran DBCC SHOWCONTIG and saw the numbers in Scan Denisty, Logical Scan Fragmentation and Extent Scan Fragmentation (along with Extents Scanned and Extent Switches). He ran the rebuild to rebuild the indexes and after that noticed that the scan density for some of those indexes was now close to 100% and the logical scan fragmentation was also very less (1-3%) however, the extent fragmentation for many of those indexes was still over 30%.

Two things to know about this output –

1) If your indexes are small, then these values do not matter at all. Why? Because SQL Server has two types of extents: Uniform Extents and Mixed Extents. Uniform extents are extents that are owned by a single object where as mixed extents are used for more than one smaller objects. In the case of a uniform extent, all the 8 pages for the extent (an extent = 8 pages = 64KB) are allocated to one single object. That is why in smaller objects, you will see that the scan density will be low, extent scan fragmentation will be high since the object will be using say a single page in N extents. If the objects are large, then SQL Server will be using the uniform extents and you will see scan density closer to 100% if there is no fragmentation.

2) Another thing to note is that the extent scan fragmentation data that is shown in the output of DBCC SHOWCONTIG does not represent the true value if there are multiple files in the filegroup and the index belongs to that filegroup.  This is actually also documented in BOL.

One Response to “DBCC SHOWCONTIG and Extent Scan Fragmentation”

  1. […] by decipherinfosys on January 30, 2009 We had discussed uniform and mixed extents in one our posts before.   A colleague recently pointed out that there is a trace flag (-T111 which can be used to force […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: