THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Fragmentation on the System Tables

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!


Published Thursday, January 17, 2008 6:05 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Paul Randal said:

Interesting what your reader reported - it's not possible to run DBCC INDEXDEFRAG on system tables - I filtered out tables with IDs under 100 when I wrote it. And I've yet to hear of a case of DBCC INDEXDEFRAG causing corruption :-)

January 31, 2008 4:51 AM

SQL_Girl said:

My two main questions regarding the above-mentioned DMV are the following:

1)       Using the DETAILED option includes the non-zero index-levels as well (non-leaf pages).  According to many source, this should also be actioned, seeing that this indicates "logical" fragmentation (meaning that the leaf pages are not in the order of the non-leaf keys any more).  I cannot find anything conclusive on this.  Where as the index level=0 occurrences, indicates extent fragmentation.  We are using this this dmv, to check fragmentation-levels first, and based on that, do the necessary re-index/reorg.  Currently we are using the LIMITED option (which only shows leaf-level), but we seem to be missing indexes that needs to be re-indexed/re-orged.

2)       Secondly, the stats that this function return, does not seem to be reliable.  I ran the function prior to an ALTER…. REBUILD, and then again thereafter.  There were hardly any differences in the stats.  Is it now a case of the stats being unreliable, or the REBUILD not being effective?

We do not want to be in the situation where we miss indexes because of unreliable stats.  We already suspect that this could be the case.


February 27, 2008 6:40 AM

Kalen Delaney said:

A couple of questions arrived regarding my earlier post about fragmentation on the system tables , and

February 28, 2008 8:02 PM

Aaron M said:

And most system tables will spend 99.999999% of their time in memory anyway...

June 30, 2009 12:48 PM

king said:



January 16, 2018 11:31 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement