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
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.)
DBCC DBREINDEX "solves" the fragmentation problem, I figured I'd try out something else from the same era --
DBCC SHOWCONTIG, the precursor to
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.