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.