101 Performance Tuning Tips and Tricks DMF sys.dm_db_database_page_allocations Dynamic Management Function sys.dm_db_database_page_allocations Games How to get all the pages of a Table? How to Get all the Pages of an Index? Pages Associated with a Table Pages Associated with an Index 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 find the list of all Pages that belongs to a Table and Index? Tip 4: Sql Server 101 Performance Tuning Tips and Tricks

Performance Issue Function on Index Column Customers Demo Table

I used to be writing an article on when and why we now have to use included columns and needed to clarify it by displaying how key column and included column values are saved in the Index Pages. That point, I acquired realized that first I want to clarify how we will look into the Sql inner storage. As a result of of this I’ve determined to clarify: How to find all the pages allotted to a Table and Index? How to determine Root, Intermediate and Leaf Pages of the Index? and How to examine the Web page knowledge?. As a outcome of this realization I’ve written this and the subsequent two articles. These articles might be referred in most of the articles on this collection of 101 Performance Tuning Tips and Tricks.

From Sql Server 2012 onwards we’ve got an un-documented (i.e. function that might change or eliminated with none discover or might produce un-expected end result) dynamic administration perform sys.dm_db_database_page_allocations to get the list of pages related to a number of Tables and Indexes. This perform returns one row for every web page related to the Table/Index.

This dynamic Administration perform takes following 5 parameters/arguments

  • @DatabaseId: Database ID of the Database whose tables or indexes assocaited pages info is required. That is a obligatory required parameter. We will use the DB_ID() perform to get the Database ID of the Database by Database Identify
  • @TableId: Table Id of the desk whose assocaited pages info is required. We will use the OBJECT_ID() functio to get the desk id by desk identify. It’s an non-compulsory parameter, whether it is Handed as NULL then it returns the pages related to all tables in the Database and when it’s NULL then subsequent two parameters (i.e. @IndexId and @PartionId) values are ignored
  • @IndexId: Index Id of the Index whose related pages info we’re on the lookout for. We will use the sys.indexes catalog view to get the Index Id. It’s an elective parameter, whether it is handed as NULL then it returns the pages related to all the indexes.
  • @PartitionId: Id of the partition whose related pages info we’re in search of. It’s an elective parameter, whether it is handed as NULL then it returns the pages related to all the partitions.
  • @Mode: That is obligatory Parameter, it’s worth may be both ‘LIMITED’ or ‘DETAILED’. ‘LIMITED’ returns much less info. Nevertheless, ‘DETAILED’ returns detailed/extra info. Obivously the ‘DETAILED’ mode makes use of extra assets.

Allow us to perceive the utilization of this Dynamic Administration Perform sys.dm_db_database_page_allocations with examples

To know how we will use this DMF to determine the pages belonging to the Table and Index, allow us to create a Buyer desk as proven in the under picture with 35Okay data. Execute the following script to Create the Buyer Table with Clustered Index on the CustomerId column and Composite Non-Clustered Index on the FirstName and LastName Column with pattern 35Okay data.


–Create Demo Database
CREATE DATABASE SqlHints101PerfTips4
GO
USE SqlHints101PerfTips4
GO
–Create Demo Table 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)
GO
–Create a Non-Clustered Composite Index on the FirstName and LastName column
CREATE NONCLUSTERED INDEX IX_Customers_FirstName_LastName
ON dbo.Clients (FirstName, LastName)

Instance 1: Get all the Pages Related to the Table Clients

To get all the Pages related to the desk Clients we’d like to cross solely the @DatabaseId, @TableId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations. We will write a question like under to get this info:


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(‘SqlHints101PerfTips4’),
OBJECT_ID(‘Clients’),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:
How to get all the Pages Associated with a Table in Sql Server

From the above end result we will see that, this DMV has returned all the pages related to all the indexes of the Table when we’ve not specified (i.e. NULL is handed right here) the @IndexId parameter worth. In the end result we will see the pages related to the non-clustered index IX_Customers_FirstName_LastName and in addition to the clustered index PK_Customers_CustomerId.

The data with page_level as zero 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 worth might be the most worth for that index.

Clustered Index leaf pages retailer the precise desk knowledge, so the web page sort of the leaf pages of the Clustered Index is a DATA_PAGE. The Web page Sort of the non-leaf web page (i.e. Root Web page and Intermediate Web page) of the Clustered Index is an INDEX_PAGE. The place as for the Non-Clustered index all the pages together with leaf pages are of Sort INDEX_PAGE, as the leaf web page of the Non-Clustered index once more factors to the Clustered Index the place precise Table Knowledge is saved.

allocated_page_file_id (i.e. file_id) and allocated_page_page_id (i.e. page_id) are the ceaselessly used columns from the end result set of this DMV.

Instance 2: Get all the Pages Related to an Index

To get all the Pages related to a particular Index of the desk, we’d like to cross the @DatabaseId, @TableId, @IndexId and @Mode Parameter values of the DMF sys.dm_db_database_page_allocations.

Assume that we would like to find all the pages related to the Non-Clustered Index IX_Customers_FirstName_LastName. Then first query is available in the thoughts is how to get the Index Id for the Non-Clustered Index IX_Customers_FirstName_LastName. Reply to this query could be very easy we will use the sys.indexes catalog view to get the Index Id. We will write a question like under to get Clients tables all the Indexes and their Index Id


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’

RESULT:
How to get Index Id in Sql Server

From the above end result we will see that Index Id for the Non_Clustered
Index IX_Customers_FirstName_LastName is 2.

Notice:

  • 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 Table is zero.

We will write a question like under to get all the pages related to the Non-Clustered Index IX_Customers_FirstName_LastName


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(‘SqlHints101PerfTips4’),
OBJECT_ID(‘Clients’), 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:
How to get all the Pages Associated with an Index

Following are the list of columns returned by this DMF

database_id object_id index_id partition_id rowset_id allocation_unit_id allocation_unit_type allocation_unit_type_desc data_clone_id clone_state clone_state_desc extent_file_id extent_page_id allocated_page_iam_file_id allocated_page_iam_page_id allocated_page_file_id allocated_page_page_id is_allocated is_iam_page is_mixed_page_allocation page_free_space_percent page_type page_type_desc page_level next_page_file_id next_page_page_id previous_page_file_id previous_page_page_id is_page_compressed has_ghost_records

(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