Check Index Fragmentation on SQL Server Audit Queues I've read this excellent article, http://rusanu.com/2010/03/09/dealing-with-large-queues/, and seem to be having the same issue, due to a low space issue, the service broker was turned off on a database, and the sys.transmission_queue table grew to around 40 million records.  Turning the broker back on significantly increased the size of tempdb, and now I'm wondering whether my service queues are suffering index fragmentation.

I would normally use dm_db_index_physical_stats to check for index stats, but this doesn't work on queues, so how do I found out about index stats on internal tables, or can I even do so in SQL Server 2005?
I dug into this and it looks like the SQL Server team went out of their way to prevent internal table stats from being exposed through DMFs and DMVs.

I can't say I blame them, as the implementation of things like queues is always subject to change, but it doesn't help you out with your problem. I can see why in this case it's really difficult to balance between exposing metadata and exposing implementation details.

Anyway, let's play around with a few things and see what happens.



If I grab an object_id from sys.internal_tables and plug it into sys.dm_db_index_physical_stats (the obvious thing to do), something interesting happens -- on 2005, you get an empty result set and a level 16 error (msg 2561, state 10); on 2008 R2, you get just an empty result set with no error. In contrast, if you put in some random number on 2005 you get the same error, while on 2008 R2 you now get a different level 16 error (msg 2573, state 40).

This tells me there is some internal checking going on specifically to handle the case of calling this function (and sys.dm_db_index_operational_stats -- probably all of them) with a valid, yet non-user-table object_id. (Note: using the OBJECT_ID function on an internal table name prefixed with sys. does return a result.)



Since DBCC DBREINDEX "solves" the fragmentation problem, I figured I'd try out something else from the same era -- DBCC SHOWCONTIG, the precursor to sys.dm_db_index_physical_stats.

If I feed it a table name from sys.internal_tables (again, prefixed with sys.), it returns nothing -- no error, just a dissatisfying DBCC execution completed. If DBCC printed error messages, contact your system administrator. -- while giving it a random object_id results in a level 16 error (msg 2501, state 45). This is a pretty clear indication there's an explicit check for this case in the code.



I tried all this using a normal connection and the DAC with the same result. For the sake of curiosity, I even tried started the server in single-user mode and used the DAC, but that didn't change anything.

Hacking into the resource database to dump the definitions of the DMFs yielded nothing except internal-only OPENROWSET syntax, which isn't particularly helpful (kind of interesting, though).

While I was hacking into the resource database, I also dumped out the internal assemblies and used Reflector to look at what's inside, but that didn't yield anything (but was likewise interesting).



In any event, I think the only reasonable recourse is doing as the article suggests -- running the deprecated DBCC DBREINDEX from a job as frequently as you need. There just isn't any visibility into these kinds of stats, at least not that I can find, and to me at least, it appears that these stats have been intentionally hidden.

Sadly, even if you were okay writing your own algorithm, the undocumented DBCC IND fails the same way as DBCC SHOWCONTIG, which is really too bad, because this probably would have given you a decent shot. As an academic exercise you could do it with output from DBCC PAGE, but obviously that's nowhere close to a practical (or efficient) solution.