Geek City: Fragmentation on the System Tables

Kalen Delaney — Fri, 18 Jan 2008 01:05:00 GMT

It's my first post of the new year. I hope it's starting out well for all of you! New year, but sometimes the same old questions. I got another email asking about defragging the system tables. It seems to be in the Hit Parade of FAQs.

First of all, WHY do you think you would need to defrag a system table? Fragmentation is only a problem when you are performing an in-order scan on a table of more than 100 pages or so, and how often do you do that to a system table that is that big? Rarely, I would assume, but let me know if you have seen an actual need for defragging a system table.

In SQL Server 2000, you can actually use a system table as a parameter to DBCC SHOWCONTIG to see the fragmentation values. One of my readers reported that he tried running a DBCC INDEXDEFRAG on SQL 2000 system tables and while sometimes it worked, sometimes it corrupted the table! It doesn't sound worth it to me.

And now, with SQL Server 2005 there is no way to see the fragmentation on the system tables. What about using the new Dynamic Management Object sys.dm_db_index_physical_stats? It requires an object_id as a parameter, and if you select from the metadata view sys.all_objects you can see the object_id for the real system tables, even if you're not using the DAC. Try this:

select * from sys.all_objects
where type = 'S'

You should see all the system tables in your current database, and their object IDs. So you could try passing the id to the management view. Here I'll try it for syshobts, which has an ID of 15:

select * from
sys.dm_db_index_physical_stats(1, 15, null, null, 'detailed')

No results are returned. If I replace the 15 with null, to indicate I want all objects, I do not see any objects with IDs less than 100, which means there are no system objects reported.

I did notice one interesting behavior while playing around with this concept. If I use the old DBCC SHOWCONTIG and try to get a report for a system table, this command gives me an error:

dbcc showcontig ('syshobts')

Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name "syshobts". Check the system catalog.

However, if I qualify the table name with the schema name, something different happens.

dbcc showcontig ('sys.syshobts')

This time, I don't get an error. I don't get a fragmentation report, but I don't get an error. I just get the message that DBCC execution completed. So it's obvious that there is a difference here. If I then intentionally misspell the object name

dbcc showcontig ('sys.syshobbits')

now I get the error that SQL Server cannot find the object. So it seems that in SQL Server 2005, DBCC SHOWCONTIG is deliberately filtering out the real system tables, and it was by design to not return fragmentation information. So even if you could run defrag on your system tables, you could never know what impact you had!

I think there's better ways to spend our time.

Have fun!

~Kalen

Search results matching tags 'Internals', 'fragmentation', and 'DMVs'

Geek City: Fragmentation on the System Tables