As a SQL Server enthusiast, I always keep an eye out for new features and updates in each new version of SQL Server. In this blog post, I want to introduce you to a new function introduced in SQL Server 2019 that serves as a partial replacement for DBCC PAGE. If you’re not familiar with DBCC PAGE, you can refer to my earlier blog post on the same topic.

Let’s dive into the comparison between the information provided by the old method and the new function:

SET NOCOUNT ON
GO
CREATE DATABASE DBCCPage
go
use DBCCPage
go
CREATE TABLE MyPageDemo (iId int, cName Char(8000))
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 7.0')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2000')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2005')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2008')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2008 R2')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2012')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2014')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2016')
GO
INSERT INTO MyPageDemo VALUES (1, 'SQL Server 2019')
GO

Once we have populated the data, we can use the dm_db_database_page_allocations function to find the pages allocated to the MyPageDemo table:

USE DBCCPage
GO
SELECT *
FROM sys.dm_db_database_page_allocations(db_id('DBCCPage'), 
object_id('MyPageDemo'), 0, null, 'DETAILED')
WHERE page_type_desc = 'DATA_PAGE'

Now, with the new DMF (Dynamic Management Function), we can obtain more detailed information about each page:

USE DBCCPage
GO
SELECT p_info.*
FROM sys.dm_db_database_page_allocations(db_id('DBCCPage'), 
object_id('MyPageDemo'), 0, null, 'DETAILED') p_alloc
CROSS APPLY sys.dm_db_page_info(p_alloc.database_id, 
p_alloc.allocated_page_file_id, p_alloc.allocated_page_page_id, 'DETAILED') AS p_info
WHERE p_alloc.page_type_desc = 'DATA_PAGE'

As you can see, this new function provides us with information available in the page header, offering a wealth of interesting details. I encourage you to run it and explore the results yourself. It’s a great way to delve into the internals of SQL Server.

I will continue to explore this function further and share any new findings with you in future blog posts. In the meantime, if you’re interested, feel free to check out my SQL Server Performance Tuning Practical Workshop on this page.