Troubleshooting page-related performance issues in Azure SQL

diegoc · Oct 16, 2024

Introduction

Azure SQL is a family of managed, secure, and intelligent products that use the SQL Server database engine in the Azure cloud. Though Azure SQL is built upon the familiar SQL Server engine, there are some differences between SQL Server and Azure SQL, such as availability of certain diagnostic commands like DBCC PAGE. DBCC PAGE is a very useful command in SQL Server for troubleshooting and inspecting the internal structure of data pages, but it is not available in Azure SQL due to differences in the underlying infrastructure and management approaches. This limitation can present some challenges for database administrators and developers who depend on DBCC PAGE for troubleshooting. Nevertheless, Azure SQL provides alternative methods and tools for database troubleshooting, ensuring that DBAs can still achieve effective results, even without the use of DBCC PAGE. This article explores these alternatives, though they do not fully replace DBCC PAGE.

Understanding sys.dm_db_page_info()

The sys.dm_db_page_info() dynamic management function (DMF) was introduced to help DBAs obtain critical metadata about database pages. With DBCC PAGE being unavailable in Azure SQL, sys.dm_db_page_info() serves as a supported, fully documented alternative to view most of the important page-level details.

The primary use of sys.dm_db_page_info() is to identify page details that are essential for diagnosing page-related issues, such as waits, blocking scenarios, and contention. Common performance problems that involve specific pages include:

TempDB contention: Contention on system databases often requires identifying the exact page where bottlenecks occur.
Last page insert contention: Scenarios where multiple transactions are trying to insert rows into the last page of an index, causing bottlenecks.
Page-level blocking: Scenarios where requests for pages are blocked due to contention.

For these issues, understanding whether the page is a data page, index page, or is of another type is critical. sys.dm_db_page_info() makes this possible by providing header information about the page, such as: object_id, index_id, partition_id.

Using sys.fn_PageResCracker along with sys.dm_db_page_info()

While sys.dm_db_page_info() provides crucial page metadata, the challenge arises when working with wait-related information provided by DMVs like sys.dm_exec_requests. The wait resource column, wait_resource, stores page details in a hexadecimal format that’s difficult to parse directly. The sys.fn_PageResCracker function was designed to translate the hexadecimal format inside wait_resource into individual components (namely database ID, file ID, and page ID). This function is vital when you need to crack the hexadecimal string and obtain values for further troubleshooting.

Syntax and Examples

The sys.dm_db_page_info() function accepts four parameters:

Database ID: The ID of the database containing the page.
File ID: The ID of the file in which the page resides.
Page ID: The ID of the page.
Mode: 'LIMITED' and 'DETAILED' are the two modes supported today, depending on the level of information required.

This simple query example returns some metadata about a page. Please enter a valid value on each of the three “id” parameters:

Code:

SELECT page_header_version, page_type, page_type_desc, page_lsn
FROM sys.dm_db_page_info(<database_id>, <file_id>, <page_id>, 'DETAILED');

As mentioned earlier, sys.dm_db_page_info() can be joined with other DMVs like sys.dm_exec_requests to correlate the page data with real-time query execution details.

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

Code:

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 waits and blocking information along with participating page details related to wait resource information:

Code:

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%'

Troubleshooting page-related performance issues in Azure SQL

diegoc