Troubleshooting page-related performance issues in Azure SQL

introduction

Azure SQL is a suite of managed, secure, intelligent products that use the SQL Server database engine in the Azure cloud. Although Azure SQL is built on the familiar SQL Server engine, There are some differences between SQL Server and Azure SQL, such as the availability of certain diagnostic commands such as DBCC PAGE. DBCC PAGE is a very useful command for troubleshooting and examining the internal structure of data pages in SQL Server, but it is not available in Azure SQL due to differences in the underlying infrastructure and management approaches. These limitations can present some challenges for database administrators and developers who rely on DBCC PAGE to troubleshoot problems. Nonetheless, Azure SQL provides alternative methods and tools for database troubleshooting so DBAs can still achieve effective results without using DBCC PAGE. This article explores these alternatives, although they are not a complete replacement for DBCC PAGE.

Understanding sys.dm_db_page_info()

that sys.dm_db_page_info() Dynamic Management Facility (DMF) was introduced to enable DBAs to obtain important metadata about database pages. If DBCC PAGE is not available in Azure SQL sys.dm_db_page_info() It serves as a supported and fully documented alternative for viewing the most important page-level details.

Main uses sys.dm_db_page_info() Identifying page details is essential for diagnosing page-related issues, such as waiting, blocking scenarios, and contention. Common performance issues related to specific pages include:

1. TempDB contention: Contention for system databases often requires identifying the exact page where the bottleneck is occurring.
2. Last page insertion contention: A scenario where multiple transactions are attempting to insert rows into the last page of an index, creating a bottleneck.
3. Page level blocking: This is a scenario where a page request is blocked due to contention.

For these problems, it is important to understand whether the page is a data page, an index page, or another type. sys.dm_db_page_info() We make this possible by providing header information for the page: object_id, index_id, partition_id.

Using sys.fn_PageResCracker with sys.dm_db_page_info()

while sys.dm_db_page_info() Because it provides important page metadata, problems arise when working with wait-related information provided by the DMV, such as: sys.dm_exec_requests. atmospheric resource heat, wait_resourceStore page details in hexadecimal format, which is difficult to parse directly. that sys.fn_PageResCracker The function is designed to convert internal hexadecimal format. wait_resource Separated into individual components (i.e. database ID, file ID, and page ID). This feature is very important when you need to decode a hexadecimal string and obtain the value for further troubleshooting.

Syntax and Examples

that sys.dm_db_page_info() The function accepts 4 parameters:

• database ID: ID of the database containing the page.
• file id: ID of the file where the page is located.
• page id: ID of the page.
• method: ‘LIMITED’ and ‘DETAILED’ are the two currently supported modes depending on the level of information required.

This simple query example returns some metadata about the page. Enter valid values ​​for each of the three “id” parameters.

SELECT page_header_version, page_type, page_type_desc, page_lsn
FROM sys.dm_db_page_info(, , , 'DETAILED');

As mentioned earlier, sys.dm_db_page_info() You can join other DMVs, such as: sys.dm_exec_requests Correlate page data with real-time query execution details.

This other example identifies and provides detailed information about requests that are experiencing contention (PAGELATCH wait) on memory data pages in tempdb.

SELECT er.session_id
	,er.wait_type
	,er.wait_resource
	,[object] = OBJECT_NAME(pi.[object_id], pi.database_id)
	,er.command
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.fn_PageResCracker(er.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.[db_id], prc.[file_id], prc.page_id, 'DETAILED') AS pi
WHERE UPPER(er.wait_type) LIKE '%PAGELATCH%'
	AND pi.database_id = 2

This third example can be used to obtain queue and block information along with participating page details related to queue resource information.

SELECT er.session_id
	,er.wait_type
	,er.wait_resource
	,OBJECT_NAME(page_info.[object_id], page_info.database_id) AS [object_name]
	,er.blocking_session_id
	,er.command
	,SUBSTRING(st.TEXT, (er.statement_start_offset / 2) + 1, (
			(
				CASE er.statement_end_offset
					WHEN - 1
						THEN DATALENGTH(st.TEXT)
					ELSE er.statement_end_offset
					END - er.statement_start_offset
				) / 2
			) + 1) AS statement_text
	,page_info.database_id
	,page_info.[file_id]
	,page_info.page_id
	,page_info.[object_id]
	,page_info.index_id
	,page_info.page_type_desc
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker(er.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info
WHERE er.wait_type LIKE '%page%'

Related documents

Feedback and Suggestions

If you have any feedback or suggestions for improving this data migration asset, please contact our Database SQL Ninja Engineering Team:datasqlninja@microsoft.com). Thank you for your support!

Note: For more information about migrating various source databases to Azure, see: Azure Database Migration Guide.