In a previous post, we looked at how to take the the binary page values in sysindexes and convert the values into file id and page id for the first and root index page and first IAM page for each index.  This information can be useful when you need to find the base pages for indexes and don’t want to run DBCC IND on each index to retrieve those pages.

While this information is readily available in sysindexes, it doesn’t happen to be a part of sys.indexes. And since sysindexes is deprecated and potentially on it’s last hurrah in SQL Server 2012 – a new solution is needed.

If the information isn’t a part of sys.indexes, is there another place this information is available?  Fortunately the answer is yes.  There is a new undocumented dynamic management object (DMO) in SQL Server 2012 called sys.dm_db_database_page_allocations.  This DMO generally provides the same output that DBCC IND does but in a query-able format.

Two Data Elements into a Binary Value

The purpose of this post is to demonstrate how to take the file id and page id from designated pages and reconstruct the binary value that is being returned from sysindexes.  While I’m not certain on the advantages and uses for the values in the native sysindexes format, that discussion is really for another day.  The aim is just to reconstruct with no questions asked.  Ok, maybe some questions – that’s what the comment section is for.

Start With the Pages

Building the binary values for the root and first index pages and the first IAM pages requires a few steps.  The first is to identify the proper page types from the output of sys.dm_db_database_page_allocations that are needed.  There are a number of different page types returned by the DMO.  For the purpose here, the types that map to the intended outputs are  INDEX_PAGE, IAM_PAGE, and DATA_PAGE.  The query in Listing 1 returns a list of these pages for object id 245575913.

--Listing 1 – Page types from sys.dm_db_database_page_allocations
SELECT page_type_desc
,object_id
,index_id
,page_level
,allocated_page_file_id
,allocated_page_page_id
,next_page_page_id
,previous_page_page_id
FROM sys.dm_db_database_page_allocations(db_id(), NULL, NULL, NULL, 'DETAILED')
WHERE page_type_desc IN ('INDEX_PAGE','IAM_PAGE','DATA_PAGE')

Reviewing the results from the query, shown in Figure 1, show a couple interesting items that need to be considered for the final results.  First, as with the DBCC IND output, the information returned is hierarchical in nature.  Each of the pages in the index trees relate to one another (shown with red arrows) and have multiple levels (highlighted in yellow).  In order to discern the first and root pages, the pages will need to be laid out in the proper order.

Figure 1 – Results from sys.dm_db_database_page_allocations

Ordering the Pages

The next step with the data is three-fold.  First, the pages need to be ordered following the hierarchy of the index.  Then the pages need to be ranked according to their order between page levels and the hierarchy level.  And finally, the binary representation of the values needs to be created.

To start the ordering of pages, the previous and next page ids need to be used to walk down the page order for the indexes.  This is a fairly basic recursive operation that creates a page order value as each page is traversed.  This is needed since the order of pages in a database isn’t necessarily the order of the pages in the index.

The next step is to assign an order to the pages. The easiest way to order the page is to use the ROW_NUMBER() function.  Each ordering needs to be partitioned by page type, object id, and index id.  For the actual order, the rows are ordered by index level in a descending order and then by page order (the value created during recursion) in ascending.  Additionally, for heaps and clustered indexes, a page order is needed for the data pages to identify the first data page.  Of course, we won’t need more than the first two or three order pages, so the recursion can be existed after those pages have been identified.

For the last step, the binary values are generated in much the same way as was required to decode the binary value, but in reverse.  The page id and file id values are converted to binary(4) and binary(2), respectively.  Then the resulting value is split by each of the values in the binary and reassembled in reverse.

The query that accomplishes both of the tasks laid out in this section is accomplished with query in Listing 2.  Before this query will run, though, the results from Listing 1 need to be inserted into a temporary table named #page.  Otherwise, an error will be generated when running the query.

--Listing 2 – Query to order pages
WITH pages_rec AS (
SELECT *, 1 AS page_order
FROM #pages
WHERE previous_page_page_id IS NULL
UNION ALL
SELECT p.*, page_order + 1
FROM #pages p
INNER JOIN pages_rec pr ON p.page_type_desc = pr.page_type_desc
AND p.object_id = pr.object_id
AND p.index_id = pr.index_id
AND p.page_level = pr.page_level
AND p.allocated_page_page_id = pr.next_page_page_id
WHERE page_order < 3
)
SELECT pr.page_type_desc
,pr.object_id
,pr.index_id
,ROW_NUMBER() OVER (PARTITION BY pr.page_type_desc, pr.object_id, pr.index_id
ORDER BY pr.page_level DESC, page_order) page_sequence
,ROW_NUMBER() OVER (PARTITION BY pr.page_type_desc, pr.object_id, pr.index_id
ORDER BY pr.allocated_page_page_id, page_order) primary_page_sequence
,page_level
,allocated_page_page_id
,allocated_page_file_id
,SUBSTRING(CAST(allocated_page_page_id AS VARBINARY(6)),4,1)
+ SUBSTRING(CAST(allocated_page_page_id AS VARBINARY(6)),3,1)
+ SUBSTRING(CAST(allocated_page_page_id AS VARBINARY(6)),2,1)
+ SUBSTRING(CAST(allocated_page_page_id AS VARBINARY(6)),1,1)
+ SUBSTRING(CAST(allocated_page_file_id AS VARBINARY(6)),2,1)
+ SUBSTRING(CAST(allocated_page_file_id AS VARBINARY(6)),1,1) page_binary
FROM pages_rec pr

The results from the query, shown in Figure 2, show how the page_sequence and heap_page_sequence have ordered the pages appropriately for each index and page type.  Also, the page_binary value for each page has been calculated and ready to be used when the proper pages are identified for each of the needed values.

Figure 2 – Results from sys.dm_db_database_page_allocations

Pivoting the Results

At this point, the pages are ordered and the data is ready to pick through to for the proper values for each of the required columns.  For the root column, if the index is a heap then there is no root to the index, since a heap isn’t an index.  Otherwise, the root will be the first INDEX_PAGE in the page sequence.  The first column for heaps and clustered indexes will be the first DATA_PAGE in the primary_page_sequence.  For non-clustered indexes it is the first page in the page_sequence if the first page has a level of 0.  Otherwise, it is the second page in the page_sequence.  And for the first IAM column, regardless of the index type or heap, the value is always the first IAM_PAGE in the database.  This logic is all included in the query in Listing 3.

--Listing 3 – Pivot page binary values
SELECT ps.OBJECT_ID
,ps.index_id
,MAX(CASE WHEN index_id = 0 THEN CAST(0 AS BINARY(6))
WHEN page_type_desc = 'INDEX_PAGE' AND page_sequence = 1 THEN page_binary
END) AS root
,MAX(CASE WHEN index_id IN (0,1) AND page_type_desc = 'DATA_PAGE' AND primary_page_sequence = 1 THEN page_binary
WHEN page_type_desc = 'INDEX_PAGE' AND page_sequence = 1 AND page_level = 0 THEN page_binary
WHEN page_type_desc = 'INDEX_PAGE' AND page_sequence = 2 THEN page_binary
END) AS first
,MAX(CASE WHEN page_type_desc = 'IAM_PAGE' AND page_sequence = 1 THEN page_binary END) AS first_iam_page
FROM #page_sequence ps
GROUP BY ps.OBJECT_ID
,ps.index_id

Reviewing these results, shown in FIgure 3, provides the same values as querying sysindexes does.  WIth these results, it is now possible to use DMOs in SQL Server 2012 and forward to continue to retrieve the columns root, first, and FirstIAM which are missing from sys.indexes

Figure 3 – Results from pivot page binary values

Wrapping Up

Digging into all of this has been an interesting exercise.  I found out a bit about how the SQL Server team identifies different page types and how to access them without the need for DBCC commands.  One thing that might be tempting, with these queries, is to join them all together into a single CTE.  You might want to avoid that – with my machine, the CPU fan got a good workout when trying to get the values for every index in AdventureWorks2012.  Instead, just change the object id in the first query to NULL and run each individually – the results should return pretty quickly.

After reading through this post, if you have any comments, please leave them below.  Also, if you are interested in how to map compatibility views to the their replacement objects, see my blog series Lost in Translation – Deprecated System Tables,

Related posts:

1. Decoding first, root, and FirstIAM in Sysindexes
2. XQuery for the Non-Expert – Binary Values
3. What Happened to rowmodctr from Sysindexes?