I was writing an article on when and why we have to use included columns and wanted to explain it by showing how key column and included column values are stored in the Index Pages. That time, I got realized that first I need to explain how we can look into the Sql internal storage. Because of this I have decided to explain: How to find all the pages allocated to a Table and Index? How to identify Root, Intermediate and Leaf Pages of the Index? and How to inspect the Page data?. As a result of this realization I have written this and the next two articles. These articles will be referred in most of the articles in this series of 101 Performance Tuning Tips and Tricks.
From Sql Server 2012 onwards we have an un-documented (i.e. feature that may change or removed without any notice or may produce un-expected result) dynamic management function sys.dm_db_database_page_allocations to get the list of pages associated with one or more Tables and Indexes. This function returns one row for each page associated with the Table/Index.
This dynamic Management function takes following 5 parameters/arguments
- @DatabaseId: Database ID of the Database whose tables or indexes assocaited pages information is required. This is a mandatory required parameter. We can use the DB_ID() function to get the Database ID of the Database by Database Name
- @TableId: Table Id of the table whose assocaited pages information is required. We can use the OBJECT_ID() functio to get the table id by table name. It is an optional parameter, if it is Passed as NULL then it returns the pages associated with all tables in the Database and when it is NULL then next two parameters (i.e. @IndexId and @PartionId) values are ignored
- @IndexId: Index Id of the Index whose associated pages information we are looking for. We can use the sys.indexes catalog view to get the Index Id. It is an optional parameter, if it is passed as NULL then it returns the pages associated with all the indexes.
- @PartitionId: Id of the partition whose associated pages information we are looking for. It is an optional parameter, if it is passed as NULL then it returns the pages associated with all the partitions.
- @Mode: This is mandatory Parameter, it’s value can be either ‘LIMITED’ or ‘DETAILED’. ‘LIMITED’ returns less information. However, ‘DETAILED’ returns detailed/more information. Obivously the ‘DETAILED’ mode uses more resources.
Let us understand the usage of this Dynamic Management Function sys.dm_db_database_page_allocations with examples
To understand how we can use this DMF to identify the pages belonging to the Table and Index, let us create a Customer table as shown in the below image with 35K records. Execute the following script to Create the Customer Table with Clustered Index on the CustomerId column and Composite Non-Clustered Index on the FirstName and LastName Column with sample 35K records.
--Create Demo Database
CREATE DATABASE SqlHints101PerfTips4
GO
USE SqlHints101PerfTips4
GO
--Create Demo Table Customers
CREATE TABLE dbo.Customers (
CustomerId INT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50), LastName VARCHAR(50),
PhoneNumber VARCHAR(10), EmailAddress VARCHAR(50),
CreationDate DATETIME
)
GO
--Populate 35K dummy customer records
INSERT INTO dbo.Customers (FirstName, LastName, PhoneNumber, EmailAddress, CreationDate)
SELECT TOP 35000 REPLACE(NEWID(),'-',''), REPLACE(NEWID(),'-',''),
CAST( CAST(ROUND(RAND(CHECKSUM(NEWID()))*1000000000+4000000000,0) AS BIGINT) AS VARCHAR(10)),
REPLACE(NEWID(),'-','') + '@gmail.com',
DATEADD(HOUR,CAST(RAND(CHECKSUM(NEWID())) * 19999 as INT) + 1 ,'2006-01-01')
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2
GO
--Create a PK and a Clustered Index on CustomerId column
ALTER TABLE dbo.Customers
ADD CONSTRAINT PK_Customers_CustomerId
PRIMARY KEY CLUSTERED (CustomerId)
GO
--Create a Non-Clustered Composite Index on the FirstName and LastName column
CREATE NONCLUSTERED INDEX IX_Customers_FirstName_LastName
ON dbo.Customers (FirstName, LastName)
Example 1: Get all the Pages Associated with the Table Customers
To get all the Pages associated with the table Customers we need to pass only the @DatabaseId, @TableId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations. We can write a query like below to get this information:
SELECT DB_NAME(PA.database_id) [DataBase],
OBJECT_NAME(PA.object_id) [Table], SI.Name [Index],
is_allocated, allocated_page_file_id [file_id],
allocated_page_page_id [page_id], page_type_desc,
page_level, previous_page_page_id [previous_page_id],
next_page_page_id [next_page_id]
FROM sys.dm_db_database_page_allocations
(DB_ID('SqlHints101PerfTips4'),
OBJECT_ID('Customers'),NULL, NULL, 'DETAILED') PA
LEFT OUTER JOIN sys.indexes SI
ON SI.object_id = PA.object_id
AND SI.index_id = PA.index_id
ORDER BY page_level DESC, is_allocated DESC,
previous_page_page_id
RESULT:
From the above result we can see that, this DMV has returned all the pages associated with all the indexes of the Table when we have not specified (i.e. NULL is passed here) the @IndexId parameter value. In the result we can see the pages associated with the non-clustered index IX_Customers_FirstName_LastName and as well as the clustered index PK_Customers_CustomerId.
The records with page_level as 0 are the leaf pages and INDEX_PAGE with previous_page_id and next_page_id as NULL are the ROOT pages of the Index. And the root pages page_level value will be the maximum value for that index.
Clustered Index leaf pages store the actual table data, so the page type of the leaf pages of the Clustered Index is a DATA_PAGE. The Page Type of the non-leaf page (i.e. Root Page and Intermediate Page) of the Clustered Index is an INDEX_PAGE. Where as for the Non-Clustered index all the pages including leaf pages are of Type INDEX_PAGE, as the leaf page of the Non-Clustered index again points to the Clustered Index where actual Table Data is stored.
allocated_page_file_id (i.e. file_id) and allocated_page_page_id (i.e. page_id) are the frequently used columns from the result set of this DMV.
Example 2: Get all the Pages Associated with an Index
To get all the Pages associated with a specific Index of the table, we need to pass the @DatabaseId, @TableId, @IndexId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations.
Assume that we want to find all the pages associated with the Non-Clustered Index IX_Customers_FirstName_LastName. Then first question comes in the mind is how to get the Index Id for the Non-Clustered Index IX_Customers_FirstName_LastName. Answer to this question is very simple we can use the sys.indexes catalog view to get the Index Id. We can write a query like below to get Customers tables all the Indexes and their Index Id
SELECT OBJECT_NAME(object_id) table_name, object_id,
name index_name, index_id, type, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = 'Customers'
RESULT:
From the above result we can see that Index Id for the Non_Clustered
Index IX_Customers_FirstName_LastName is 2.
Note:
- Clustered Index will always will have the Index Id as 1.
- Index Id for the Non-Clustered Index will be >=2.
- Index Id for the Heap Table is 0.
We can write a query like below to get all the pages associated with the Non-Clustered Index IX_Customers_FirstName_LastName
SELECT DB_NAME(PA.database_id) [DataBase],
OBJECT_NAME(PA.object_id) [Table], SI.Name [Index],
is_allocated, allocated_page_file_id [file_id],
allocated_page_page_id [page_id], page_type_desc,
page_level, previous_page_page_id [previous_page_id],
next_page_page_id [next_page_id]
FROM sys.dm_db_database_page_allocations
(DB_ID('SqlHints101PerfTips4'),
OBJECT_ID('Customers'), 2, NULL, 'DETAILED') PA
LEFT OUTER JOIN sys.indexes SI
ON SI.object_id = PA.object_id
AND SI.index_id = PA.index_id
ORDER BY page_level DESC, is_allocated DESC,
previous_page_page_id
RESULT:
