In a post from two weeks ago, I told you that this week I would show you how to look inside pages to see the NEXTPAGE and PREVPAGE pointers. When I started to write this, I realized that before I can show you how to look inside the pages, I need to show you how to find out what pages to look at. So I’m going to juggle this blog schedule a bit, and this week I’ll show you how to find out what pages belong to a table and next time we’ll actually look inside the pages. However, the methods I’m going to show you to find page numbers will also provide the NEXTPAGE and the PREVPAGE values, so you don’t actually have to look inside the pages to get that information.
Starting in SQL Server 2014, Microsoft included the dynamic management object sys.dm_db_database_page_allocations. This function is technically undocumented but other people have blogged about it elsewhere, and it is a very useful function to be aware of. The function has five parameters: database_id, object_id, index_id, partition_number, and mode. For most of the parameters, a NULL means ALL. So a NULL for object_id would mean you want all objects. Once an ALL is specified, the rest of the parameters, except mode, are ignored. So if you specify NULL for object_id, the value for index_id and partition_number are basically ignored.
I’m not going to go into all the details, but I’ll show a few examples of the use of this function.
The most important columns returned for use when you want to look inside the pages are the allocated_page_page_id and allocated_page_file_id columns, for the file ID and the page ID, respectively, of a page. The function returns one row for each page in the table, index and partition specified in the function’s parameters. Here’s an example using a very small table that I create. I am using a database called test, but you can use any database you like, as long as you include the correct database name in the function call.
I’m adding a counter to the GO command to tell SQL Server to run the batch 400 times. I want to add enough rows to end up with more than one data page in this table. I’m returning the page ID and file id for each page, as well as the page ID and file ID for the next and previous pages for each page.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | USE test; GO DROP TABLE IF EXISTS smallrows; GO CREATE TABLE smallrows ( a INT IDENTITY, b datetime DEFAULT getdate() ); GO SET NOCOUNT ON; GO INSERT INTO smallrows DEFAULT VALUES; GO 400 SELECT COUNT(*) AS ROW_COUNT FROM smallrows; GO SELECT allocated_page_file_id AS file_id, allocated_page_page_id AS page_id, page_type_desc, next_page_file_id, next_page_page_id, previous_page_file_id, previous_page_file_id FROM sys.dm_db_database_page_allocations (db_id('test'), object_id('smallrows'), NULL, NULL, 'DETAILED'); GO CREATE CLUSTERED INDEX small_id ON smallrows(a); GO SELECT allocated_page_file_id AS file_id, allocated_page_page_id AS page_id, page_type_desc, next_page_file_id, next_page_page_id, previous_page_file_id, previous_page_file_id FROM sys.dm_db_database_page_allocations (db_id('test'), object_id('smallrows'), NULL, NULL, 'DETAILED'); GO |
Here is the output I got:
Keep in mind that your page numbers will most likely be different. Notice that the first time I return the page numbers, no values are shown for the next and previous pages. This is because there is no clustered index on the table and thus, there is no implied ordering to the pages. When I create a clustered index, the data moves to new pages, and there is now one index page. One of the data pages has values for the next page, and that next page has values for the previous page.
If you want to try this on a bigger table, you can use the dbo.Person table I created in the post I mentioned at the start of this one. This query will just show you the pages from the clustered index, which are the data pages, and the IAM pages (which I’ll tell you about in another post.) If you replace the 1 in the third parameter with a NULL, you’ll get all the index pages.
1 2 3 4 5 6 7 8 | USE AdventureWorks2014; GO SELECT allocated_page_file_id AS PageFID, allocated_page_page_id AS PagePID, object_id AS ObjectID, index_id, partition_id AS PartitionID, allocation_unit_type_desc AS AU_type, page_type AS PageType FROM sys.dm_db_database_page_allocations(db_id('AdventureWorks2014'), object_id('dbo.Person'), 1, NULL, 'DETAILED'); GO |
If you’re running an older version of SQL Server, earlier than SQL Server 2012, you won’t be able to use the sys.dm_db_database_pages_allocations function, but you’re not completely out of luck. We have always had a DBCC command to return page numbers, called DBCC IND. The problem is that the DBCC command cannot have a filter added, and cannot be joined to other metadata. So here’s what I do if I’m working on an older version. I use the EXEC command to make the output of DBCC IND available to be inserted into a table. I create a table with the sp_ prefix in the master database, which means it can be accessed from anywhere. And then I can save the output from DBCC IND into a table for further filtering, or joining, or whatever else I want to do.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | -- Create a table to hold the output of DBCC IND USE master; GO IF object_id('sp_pages') IS NOT NULL DROP TABLE sp_pages; GO CREATE TABLE sp_pages (PageFID tinyint, PagePID INT, IAMFID tinyint, IAMPID INT, ObjectID INT, IndexID tinyint, PartitionNumber tinyint, PartitionID BIGINT, iam_chain_type VARCHAR(30), PageType tinyint, IndexLevel tinyint, NextPageFID tinyint, NextPagePID INT, PrevPageFID tinyint, PrevPagePID INT, PRIMARY KEY (PageFID, PagePID)); GO |
And here’s some examples using this special sp_pages table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | USE AdventureWorks2014 GO TRUNCATE TABLE sp_pages; GO INSERT INTO sp_pages EXEC ('DBCC IND (AdventureWorks2014, [dbo.Person], -1) ') GO -- Find first data page SELECT * FROM sp_pages WHERE pagetype = 1 AND prevpagePID = 0 AND prevpageFID = 0; GO -- how many pages of each type are there? SELECT PageType, NUMBER = COUNT(*) FROM sp_pages GROUP BY PageType; GO --how many pages for each index are there? -- You must be in the AdventureWorks2014 database to get the index name USE AdventureWorks2014; GO SELECT Index_ID, name, NUMBER = COUNT(*) FROM sp_pages JOIN sys.indexes ON IndexID = Index_ID AND ObjectID = Object_ID GROUP BY Index_ID, name ORDER BY Index_ID; GO |
Examining the output of sys.dm_db_database_page_allocations or looking at the rows in sp_pages will show you the next and previous pages for all pages in an index, for which ordering is implied. You could even find the page where the previous page number is 0, which means it’s the first page, and following the next page IDs to get the entire list of pages for a table or index, in order. In the next post, I’ll show you how to look inside a page and find your own answers to some frequently asked (or misunderstood) questions about clustered indexes.
