New Page Functions in SQL Server 2019

I will be the first to admit that I am what one might call a “SQL Head”.  In other words, I’m a nerd that likes to dig into the internal workings of SQL Server, sometimes down to the bits and bytes. The tools to facilitate this archaeology have slowly been evolving and with the release of SQL Server 2019 later this year (2nd half of 2019), this continues to hold true.  Two new tools, a dynamic management function (DMF) and the other a function, will be making an appearance, namely sys.dm_db_page_info and sys.fn_pagerescracker respectively.

Note: For this post I am using Azure Data Studio to get more familiar with the product.  

sys.dm_db_page_info

This new function will return the page header information of a specified page in a single row.  This information will contain vital information such as object_id, index_id, and partition_id, along with many others.  In the past, the only real tool available to retrieve this information was to utilize DBCC PAGE.  With the use of trace flag 3604, DBCC PAGE would output the contents of a page into the results pane of SSMS.   By having this information now exposed through a DMF, we can easily and quickly get information about a given page if needed.  We can even see that Azure Data Studio (shown below) already has intellisense available for the DMF.

In this example, I am connected to a SQL Server 2019 instance that is residing on Linux.

If I were to look at page ID 336 in the PagesOnLinux database on this instance, we can see information from the page header, such as the status of the differential map. We can see below that the status is set to “CHANGED” as well as the DIFF_MAP page ID is 6.

One thing to note about this particular is that all of the parameters except for the MODE (the last one) has NULL as the default value, however, if you attempt to pass a NULL value into the function when executing it will result in an error.  I find this to be confusing as other DMF’s allow you to pass in NULL values to return a larger sub-set of data.  It’s possible that functionality will change by the time SQL Server 2019 is released since the product is still under development.

sys.fn_pagerescracker

Another new addition to SQL Server 2019 is the function sys.fn_pagerescracker.   This function will consume a page resource binary value from sys.dm_exec_requests.  This binary value is a representation of the database ID, file ID, and page ID.  The function will convert it into these values which can then be cross applied to the sys.dm_db_page_info dynamic management function.  Note that the page resource value from sys.dm_exec_request will only be not NULL in the event that the WAIT_RESOURCE column has a valid value.  If the column has a NULL value, this indicates that there is not any type of wait resource, so the row can’t be used for this purpose.  The intent of this is to help diagnose active requests that are waiting on some type of page resource.  A good example of this would be page latches.

Watch for a future blog from me on how this function works internally.  Coming soon!

In our database, let’s create a table, dbo.Table1 and insert a single row into Table1.

CREATE TABLE dbo.table1 (id int, fname varchar(20))
GO
INSERT dbo.table1 (id, fname) select 1,'John'
GO

BEGIN TRANSACTION
UPDATE dbo.Table1
SET fname = 'John'  --I'm just setting it back to the same value; it doesn't matter what I'm setting it to
WHERE id = 1
GO

-- Note the Pagelock: not recommends for production usage
SELECT * FROM dbo.Table1 WITH (PAGLOCK)

Now that we have a transaction that is blocking, we examine sys.dm_exec_request to obtain the page_resource value that our second query is waiting on.  Remember that the page_resource column will only have a non-NULL value if the request is waiting for another resource.

Now we can finish up and look at the entire query which would allow us to quickly identify what a request is waiting on.  Borrowed from Books Online:

SELECT page_info.* 
FROM sys.dm_exec_requests AS d 
CROSS APPLY sys.fn_PageResCracker (d.page_resource) AS r 
CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id, 1) AS page_info

Here we can see that the new function gave us the database id, file id, and page id of the corresponding page that we are waiting on.

Summary

Microsoft continues to make advancements in a number of areas with SQL Server 2019.  These two new tools will make analysis of various scenarios easier to decipher and resolve.  Make sure to add these two new tools to your tool belt so that you can have them ready at your disposal.

© 2019, John Morehouse. All rights reserved.