101 Performance Tuning Tips and Tricks Clustered Index B Tree Structure Games How to get B Tree Index Pages Information How to Get Index Intermediate Pages How to Get Index Leaf Pages How to Get Index Root Page Index B+ Tree Structure Performance Performance Optimization Performance Tuning Performance Tuning Tips and Tricks Sql Sql 101 Performance Tuning Sql Server Sql Server Performance Tips and Tricks Sql Server Performance Tuning Tip sys.dm_db_database_page_allocations

How to get an Index’s Root Page, Intermediate Pages and Leaf Pages Information? Tip 5: Sql Server 101 Performance Tuning Tips and Tricks

Structure of Clustered Index

On this article, I’ll clarify you on How to get an Index’s Root Web page, Intermediate Pages and Leaf Pages Info. Prior to that permit us get some insights into the Index construction.

In Sql Server an index is made up of a set of pages (index nodes) which are organized in a B+ tree construction. This construction is hierarchical in nature. The highest node is known as the ROOT node (Root Web page). The underside degree of nodes within the index are referred to as the leaf nodes (leaf Pages). Any index ranges between the basis and the leaf nodes are collectively referred to as intermediate ranges. In a clustered index, the leaf nodes include the info pages of the underlying desk. The basis and intermediate degree nodes include index pages holding index rows. Every index row incorporates a key worth and a pointer to both an intermediate degree web page within the B-tree, or a knowledge row within the leaf degree of the index. The pages in every degree of the index are linked in a doubly-linked listing. In case of Clustered Index, knowledge in a desk is saved in a sorted order by the Clustered Index key Column. In consequence, there could be just one clustered index on a desk or view. As well as, knowledge in a desk is sorted provided that a clustered index has been outlined on a desk.

Non-Clustered Index construction is analogous to that of a B+ Tree Construction of Clustered Index, however the leaf nodes of a Non-Clustered index include solely the values from the listed columns and row locators that time to the precise knowledge rows, fairly than include the info rows themselves. Due to this in case of Non-Clustered Index, Sql Server takes further steps to find the precise knowledge. The row locators construction is determined by whether or not underlying desk is a clustered desk (Desk with Clustered Index) or a heap desk (Desk with out Clustered Index). In case the underlying desk is a Clustered desk the row locator factors to the Clustered Index key worth corresponding to the Non-Clustered Key and it’s used to look-up into the Clustered Index to navigate to the precise knowledge row. If underlying desk is a heap desk, the row locator factors to the precise knowledge row tackle. In case of Non-Clustered Index, the info rows of the underlying desk will not be sorted and saved so as based mostly on their Non-Clustered keys.

B+ Tree Construction of a Clustered Index

Structure of Clustered Index

Allow us to clarify how to get an Index’s Root Web page, Intermediate Pages and Leaf Pages Info with an instance

To elucidate how to get an Index’s Root Web page, Intermediate Pages and Leaf Pages Info, allow us to create a Buyer desk as proven within the under picture with 35Okay data. Execute the next script to Create the Buyer Desk with Clustered Index on the CustomerId column with pattern 35Okay data.


–Create Demo Database
CREATE DATABASE SqlHints101PerfTips4
GO
USE SqlHints101PerfTips4
GO
–Create Demo Desk Clients
CREATE TABLE dbo.Clients (
CustomerId INT IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50), LastName VARCHAR(50),
PhoneNumber VARCHAR(10), EmailAddress VARCHAR(50),
CreationDate DATETIME
)
GO
–Populate 35Okay dummy buyer data
INSERT INTO dbo.Clients (FirstName, LastName, PhoneNumber, EmailAddress, CreationDate)
SELECT TOP 35000 REPLACE(NEWID(),’-‘,”), REPLACE(NEWID(),’-‘,”),
CAST( CAST(ROUND(RAND(CHECKSUM(NEWID()))*1000000000+4000000000,zero) 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.Clients
ADD CONSTRAINT PK_Customers_CustomerId
PRIMARY KEY CLUSTERED (CustomerId)

As defined within the earlier article we will use Dynamic Administration Perform sys.dm_db_database_page_allocations to get the listing of all of the pages related to an Index. Notice sys.dm_db_database_page_allocations is an un-documented (i.e. function which will change or eliminated with none discover or might produce un-expected outcome) DMF. So keep away from utilizing it within the Manufacturing setting.

Get all of the Pages Related to the Clustered Index PK_Customers_CustomerId

To get all of the Pages related to the Index PK_Customers_CustomerId of the Clients desk, we’d like to move the @DatabaseId, @TableId, @IndexId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations. We will use sys.indexes catalog view as proven under to get the Index Id for a Index.


SELECT OBJECT_NAME(object_id) table_name, object_id,
identify index_name, index_id, sort, type_desc
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = ‘Clients’
AND identify = ‘PK_Customers_CustomerId’

RESULT:
Use sys.indexes to get the indexid for the index

As defined within the earlier article:

  • Clustered Index will all the time could have the Index Id as 1.
  • Index Id for the Non-Clustered Index will probably be >=2.
  • Index Id for the Heap Desk is zero.

We will execute the under question to get all of the pages related to the Clustered Index PK_Customers_CustomerId


SELECT DB_NAME(PA.database_id) [DataBase],
OBJECT_NAME(PA.object_id) [Table], SI.Identify [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(‘SqlHints101PerfTips5’),
OBJECT_ID(‘Clients’), 1, NULL, ‘DETAILED’) PA
LEFT OUTER JOIN sys.indexes SI
ON SI.object_id = PA.object_id
AND SI.index_id = PA.index_id
WHERE is_allocated = 1
and page_type in (1,2) — INDEX_PAGE and DATA_PAGE Solely
ORDER BY page_level DESC, is_allocated DESC,
previous_page_page_id

RESULT:
Clustered Index All Pages

As proven within the above picture the Root Web page File Id is 1 and Web page Id is 968. Root Web page is the one with most Page_level (right here on this instance it’s 2) having previous_page_id and next_page_id as NULL. Intermediate Pages File Id is 1 and Web page Id’s are: 2016 and 2032. Intermediate pages are the one whose Page_Level is lower than the basis web page degree and higher than zero (i.e. Leaf Web page Degree (i.e. zero)

We will use the under perform to get the pages of the totally different ranges of a B+ Tree Index:

We will use the under perform to get the B+ Tree Clustered/Non-Clustered Index Root Web page, Intermediate Degree Pages and Leaf Degree Pages. Word I’m utilizing sys.dm_db_database_page_allocations un-documented (i.e. function which will change or eliminated with none discover or might produce un-expected outcome) DMF. So keep away from utilizing it within the Manufacturing surroundings.


CREATE FUNCTION dbo.GetPagesOfBPlusTreeLevel(
@DBName VARCHAR(100), @TableName VARCHAR(100) = NULL, @IndexName VARCHAR(100) = NULL,
@PartionId INT = NULL, @MODE VARCHAR(20), @BPlusTreeLevel VARCHAR(20)
)
RETURNS
@IndexPageInformation TABLE (
[DataBase] VARCHAR(100), [Table] VARCHAR(100), [Index] VARCHAR(100),
[partition_id] INT, [file_id] INT, [page_id] INT, page_type_desc VARCHAR(100),
page_level INT, [previous_page_id] INT, [next_page_id] INT)
AS
BEGIN

DECLARE @MinPageLevelId INT = zero , @MaxPageLevelId INT = zero, @IndexId INT = NULL

SELECT @IndexId = index_id
FROM sys.indexes
WHERE OBJECT_NAME(object_id) = @TableName AND identify = @IndexName

IF @IndexId IS NULL
RETURN

IF @BPlusTreeLevel IN (‘Root’, ‘Intermediate’)
BEGIN
SELECT @MaxPageLevelId = (CASE WHEN @BPlusTreeLevel =’Intermediate’ THEN MAX(page_level) – 1 ELSE MAX(page_level) END),
@MinPageLevelId = (CASE WHEN @BPlusTreeLevel =’Intermediate’ THEN 1 ELSE MAX(page_level) END)
FROM sys.dm_db_database_page_allocations
(DB_ID(@DBName), OBJECT_ID(@TableName), @IndexId, @PartionId, ‘DETAILED’) PA
LEFT OUTER JOIN sys.indexes SI
ON SI.object_id = PA.object_id AND SI.index_id = PA.index_id
WHERE is_allocated = 1 AND page_type in (1,2) — INDEX_PAGE and DATA_PAGE Solely

IF @MaxPageLevelId IS NULL OR @MaxPageLevelId = zero
RETURN
END

INSERT INTO @IndexPageInformation
SELECT DB_NAME(PA.database_id) [DataBase], OBJECT_NAME(PA.object_id) [Table], SI.Identify [Index],
[partition_id], 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(@DBName), OBJECT_ID(@TableName), @IndexId, @PartionId, ‘DETAILED’) PA
LEFT OUTER JOIN sys.indexes SI
ON SI.object_id = PA.object_id AND SI.index_id = PA.index_id
WHERE is_allocated = 1 AND page_type in (1,2) — INDEX_PAGE and DATA_PAGE Solely
AND page_level between @MinPageLevelId AND @MaxPageLevelId
ORDER BY page_level DESC, previous_page_page_id

RETURN
END

How to get an Index’s Root Web page Info?

We will use the above perform like under to get the basis web page of the Clustered Index PK_Customers_CustomerId on the Clients Desk within the SqlHints101PerfTips5 Database.


SELECT *
FROM dbo.GetPagesOfBPlusTreeLevel
(‘SqlHints101PerfTips5’, ‘Clients’,
‘PK_Customers_CustomerId’, NULL, ‘DETAILED’, ‘Root’)

RESULT:
How to Get Root Page of B Tree Index

There can be all the time on Root Web page. Root Web page is the one with most Page_level having previous_page_id and next_page_id as NULL. If the variety of data within the desk are very much less and only one web page is sufficient to retailer it. Then Sql Server doesn’t create the B+ Tree construction. So, in such state of affairs, the above perform won’t return the Root Web page info.

How to get an Index’s Intermediate Pages Info?

We will use the above perform like under to get the Index’s Intermediate pages of the Clustered Index PK_Customers_CustomerId on the Clients Desk within the SqlHints101PerfTips5 Database.


SELECT *
FROM dbo.GetPagesOfBPlusTreeLevel
(‘SqlHints101PerfTips5’, ‘Clients’,
‘PK_Customers_CustomerId’, NULL, ‘DETAILED’, ‘Intermediate’)

RESULT:
How to Get Intermediate Pages of a B Tree Index

Intermediate pages are the one whose Page_Level is lower than the basis web page degree and larger than Leaf Web page Degree (i.e. zero) (i.e. Leaf Web page Degree

How to get an Index’s Leaf Web page Info?

We will use the above perform like under to get the Index’s Leaf pages of the Clustered Index PK_Customers_CustomerId on the Clients Desk within the SqlHints101PerfTips5 Database.


SELECT *
FROM dbo.GetPagesOfBPlusTreeLevel
(‘SqlHints101PerfTips5’, ‘Clients’,
‘PK_Customers_CustomerId’, NULL, ‘DETAILED’, ‘Leaf’)

RESULT:
How to Get Leaf Pages of a B Tree Index

In case of Clustered Index the Page_Type_Desc of Leaf Pages is DATA_PAGE the place as in case of Non-Clustered Index will probably be INDEX_PAGE.

Within the Subsequent Article I’ll clarify how we will use the DBCC PAGE command to look into what truly Sql Server shops within the Web page.

(perform(d, s, id) var js, fjs = d.getElementsByTagName(s)[0]; if (d.getElementById(id)) return; js = d.createElement(s); js.id = id; js.src = ‘//join.fb.internet/en_US/sdk.js#xfbml=1&appId=249643311490&model=v2.three’; fjs.parentNode.insertBefore(js, fjs); (doc, ‘script’, ‘facebook-jssdk’));

Categories