SQL Server’s Undocumented Changes

When an undocumented feature changes or is replaced by a new undocumented feature, should that change be documented? I'm not really asking a philosophical question here, of course, but talking about some of the most interesting features of SQL Server that I love to write about.

One of my favorite undocumented features is the ability to see every page that belongs to a table or index by using the DBCC IND command. I have described this command in detail in my books and in less detail in various blog posts. Other people have also written about this command and its usefulness in getting information about your data structure organization, but it's not listed in the official SQL Server documentation. However, some people think that when Microsoft engineers blog, it's pretty close to being official, so read what Paul Randal said about DBCC IND when he was still working on SQL Server Team at Microsoft.

Dynamic Management Object in SQL Server 2012

The DBCC IND command is available in SQL Server 2012 and is still undocumented, but in SQL Server 2012 there's a replacement to DBCC IND that’s even more useful. We now have the dynamic management object (DMV) called sys.dm_db_database_page_allocations, which is a function that takes the following five parameters:

The sys.dm_db_database_page_allocations function returns one row for every page belonging the objects, indexes, or partitions specified by the parameters.  For all but the last parameter, NULL means ALL, so you can see information for all databases, all objects, all indexes, or all partitions. For example, the following function call will return page information for all partitions and all indexes for the Sales.SalesOrderHeader table in the AdventureWorks2012 database:

SELECT allocated_page_file_id, allocated_page_page_id, page_type_desc     FROM sys.dm_db_database_page_allocations        (db_id('AdventureWorks2012'), object_id('Sales.SalesOrderHeader'),                NULL, NULL, 'DETAILED');

My query selected only a small subset of the columns available from this function; basically, it gave me the file ID and the page ID for all the pages, as well as a description of the type of page, so I could tell if it was a data page, an index page, or something else. Feel free to execute the function on a table of your choosing in a SQL Server 2012 database to see the rest of the columns returned. As mentioned, this function is undocumented, and this short commentary isn’t going to be the place where it becomes documented. But this function returns a lot more information than DBCC IND does. In addition to the file and page number, type page, and previous and next page number, which are also available with DBCC IND, sys.dm_db_database_page_allocations returns partition_id, allocation_unit_id, extent information, page fullness, compression information, and whether the page has any ghost records. We need several DMVs and other undocumented DBCC commands to get all that information in SQL Server 2008, so I have been spending a lot of time rewriting my class demos to use this great new function.

Another benefit of the sys.dm_db_database_page_allocations function over the old DBCC IND command is that this function can also return details about pages that have been deallocated but are still marked as belonging to a table or index to help you investigate problems when database space doesn’t seem to be reusable.

Column Reporting Transaction Nesting Level of Session

There’s another undocumented change in SQL Server 2012 that I would like to point out, but this time I'm guessing that the lack of documentation is just an oversight.  In "Metadata for Troubleshooting," I mentioned a problem with the sys.dm_exec_sessions DMV. I pointed out that if it really is going to let us have a replacement for the old pseudo-table sysprocesses, this new DMV needed to have a column that could report the transaction nesting level of a session. I wrote that commentary right before the SQL Server 2008 was released to manufacturing, and I was still desperately hoping that the powers-that-be would add this one column to that view. But it didn't happen. And then, for SQL Server 2012, I thought they still hadn’t included this column, because when I looked at the documentation for sys.dm_exec_sessions, there was no mention of this new column. I knew the documents had been updated because they mention a new column called authenticating_database_id, which was added to support changes due to Contained Databases, which is definitely a SQL Server 2012 feature. So imagine my surprise when I finally had access to a SQL Server 2012 instance and queried the sys.dm_exec_sessions view, and there as the very last column was open_transaction_count. My testing does seem to indicate that this column is everything I wanted it to be, but it just isn't documented.

I'm assuming that sys.dm_db_database_page_allocations is undocumented because its predecessor DBCC IND, was not. There might also be issues that some of the columns refer to features that aren't implemented or not documented. But the information it returns is so useful, people will soon be discussing it as if it were documented. And I’m also assuming that leaving out a mention of the new open_transaction_count column in sys.dm_exec_sessions was just a human error, and I'm grateful that column is in the view. So, the fact that the docs don't mentionsys.dm_db_database_page_allocations won’t be considered a problem at all.

Who knows what other new undocumented features are still out there waiting to be discovered. I can’t wait to find out!

Related: Cool New SQL Server 2012 Metadata