Fragmentation Stats on an Index (Part 2)

In my last post I was discussing how and why clicking on the Fragmentation tab on the properties page of an index could take a while to return results.  I identified it as a DBCC since sp_who2 as well as being identified as a DBCC command by querying

SELECT * FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

I guess the point I was trying to make was that index fragmentation stats don’t seem to be cached anywhere (why would they be since they are changing all the time) and must be calculated each time you click on the Fragmentation tab – or perform DBCC SHOWCONTIG – or query db_dm_physical_index_stats.  This means that on deep tables, these queries can take some time.  I’m not working with an amazing disk subsystem and the server I’m working on only has two processors.  So for me – for sure – they take a while.

If you don’t mind waiting for SSMS to finish the query to see the results, that’s great.  I had accidentally clicked on it and needed SSMS to release the thread so that that SSMS would operate properly.  I’m hoping that I was showing how to deal with SSMS hanging on this page when your data set is large.

Thanks to Simon Worth for pointing this out.  He is completely correct.  DBCC SHOWCONTIG has been retired for using db_dm_physical_index_stats instead.  SSMS is making a SELECT to db_dm_physical_index_stats and not really calling DBCC SHOWCONTIG.  I wouldn’t be suprised if under the covers that both DBCC SHOWCONTIG and a call to db_dm_physical_index_stats are doing the exact same operations to figure out what the fragmentation stats are.  Microsoft has just put a new front end on to DBCC SHOWCONTIG so that it looks more like a regular T-SQL SELECT statement.

Thanks, Simon.

Advertisements

One Response to Fragmentation Stats on an Index (Part 2)

  1. Richard says:

    (yes, this post is very old, but came up on a google search of the issue, so I figured my input might help others who find this page)

    The main reason that viewing the fragmentation in SSMS can take a very long time, is that it is calling db_dm_physical_index_stats for the *entire database* and then filtering the results based on the table/index name that you are looking for.

    In a very large database, this can take *forever*.

    Example: we have a database with 676 user tables, and it was taking over 5 minutes to view fragmentation of an index. Copy/pasting the command from DBCC inputbuffer, and replacing:

    sys.dm_db_index_physical_stats(@database_id, NULL, NULL, NULL, ‘SAMPLED’)

    with:

    sys.dm_db_index_physical_stats(@database_id, object_id(‘schema_name.table_name’, NULL, NULL, ‘SAMPLED’)

    gave results in the query window in under 30 seconds.

    This is covered by ms, and a fix exists:
    http://support.microsoft.com/kb/972937

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: