Fragmentation Stats on an Index

I’m working with very large data sets and I accidentally clicked on the “Fragmentation” tab on the properties page for the clustered index of a table.  Management Studio did not handle this very gracefully.  Everything locked up and Management Studio becomes un-usable.

What Managment Studio is doing is issuing a DBCC on the index to see what the fragmentation stats are.  What makes this bad is that this table has 100,000,000 rows in it.  Needless to say, this takes a while to complete.

I decided that I wasn’t willing to wait for the DBCC to finish so I used a KILL statement to end the process.  Everything came back to life and I was able to dismiss the index properties dialog.

I wonder if it would be nice for SQL Server to have some idea if the DBCC will take a while and if it will, maybe it can be treaded off so that it doesn’t hang SSMS.  Just a thought.


2 Responses to Fragmentation Stats on an Index

  1. Simon Worth says:

    If you have a look at profiler when you click on the fragmentation tab you’ll see it’s not actually running a DBCC command on the index (like you would in 2000 by running DBCC SHOWCONTIG), it’s running a query that uses sys.tables, sys.indexes, and the DMV called sys.dm_db_index_physical_stats.
    There may be a DBCC command buried under the covers to get some results, but I haven’t read anything about it. Doing an sp_Helptext on sys.dm_db_index_physical_stats gives the following results

    CREATE VIEW sys.dm_db_index_usage_stats AS
    SELECT database_id, object_id, index_id,
    user_seeks, user_scans, user_lookups, user_updates,
    last_user_seek, last_user_scan, last_user_lookup, last_user_update,
    system_seeks, system_scans, system_lookups, system_updates,
    last_system_seek, last_system_scan, last_system_lookup, last_system_update
    WHERE status = 0

  2. Mark Garner says:

    Hmmm. I’ll try this again next time I’m working with this table I’m talking about. I was lookin at the dmv dm_exec_requests and using CROSS APPLY to see sql text and noticed that there was only one query running and it was a DBCC. I’ll let you know what I find out.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: