Cool New SQL Server 2012 Metadata

Last month I told you about one of the scripts I use to explore internal storage, so I'll continue on that theme and describe another command I use to look at internal storage, as well as a new SQL Server 2012 metadata function that removes the need to use the command I describe this month. I told you last month about IAMs and the fact that SQL Server uses IAMs to keep track of space used for objects, including space for large object data, as well as row-overflow data. There's an undocumented DBCC command that will show you some of the information that the IAMs are keeping track of. Specifically, the undocumented DBCC IND command will report all the page numbers occupied by a table or a specific index on a table, and I'll explain each page type. The following is a sample of what the information returned from DBCC IND might look like:

Figure 1: Sample of information returned from DBCC IND

The DBCC IND command is a useful command in SQL Server 2008 and earlier versions, but the biggest problem with it is that although the information that comes back looks tabular, it can’t be queried like tabular data. You can't select the columns you want returned, change the column header, filter the rows, or group by a column such as the page type. There are more columns returned than those I've shown, but I was limited to the width of the page, so it sure would be nice to have a way to just list the columns I'm interested in. Well, SQL Server 2012 provides a way. There's a new undocumented DMV, which is actually a function, called sys.dm_db_database_page_allocations. This function returns one row for each page, just like DBCC IND. However, because it's a DMV, it returns tabular data, and I can query it just like a table. For example, the query below returns all the large object (LOB) pages for all partitions (the 4 ^th parameter) of index 1 (the 3 ^rd parameter) for the dbo.bigrows table (the 2 ^nd parameter) in the AdventureWorks2012 database (the 1 ^st parameter). The 5 ^th parameter is either LIMITED or DETAILED, and specifies how much information you want returned. The command below shows all the large object pages for all partition of index 1 for the dbo.bigrows table in the AdventureWorks2012 database.

SELECT allocated_page_file_id as PageFID,        allocated_page_page_id as PagePID,FROM sys.dm_db_database_page_allocations(db_id('AdventureWorks2012'),      object_id('dbo.bigrows'), 1, null, 'DETAILED')WHERE allocation_unit_type_desc = 'LOB_DATA'; 

It's beyond the scope of this article to give all the details about the input to and output from this function, but if you have a SQL Server 2012 instance, the above example should give you enough information to get started. I'll mention the five different values for page_type and page_type_desc that might be reported:

 Table 1: Five different values for page_type and page_type_desc that might be reported

Some of the information will be very meaningful to you if you have worked with the DBCC IND command in SQL Server 2008 and earlier, so you might want to search around the Internet (or read my SQL Server 2008 Internals book) to get details about that command.

A frequent question I get when I demonstrate new features is, "How do I get the same information if I have an older version?" Usually, I need to explain that new features are added to encourage you to upgrade, and many, if not most, new features don't have equivalent functionality in older versions. However, for this new DMV, I can tell you that there was something sort of similar, but not exactly the same, in previous versions. As I mentioned, the DBCC IND command is limited in that it doesn't produce tabular output that can be filtered and summarized. But because I always found that output so useful, I wrote a script that creates a view called sp_index_info to hold the output of DBCC IND, which can be populated using an INSERT/EXEC command. (Giving the view a name starting with sp_ allows the view to be accessed from any database.)  You can find the script, as well as a description of a scenario in which it can be useful, in my blog post, Geek City: Nonclustered Index Keys. DBCC IND, and the script to use it, doesn't give you all the same information as the new SQL Server 2012 sys.dm_db_database_page_allocations function, but it can give you a good start.  Getting access to the new DMV might be as good a reason as any for upgrading to SQL Server 2012!

Related: Synchronize Metadata Across SQL Server Database Copies