SQL SERVER – New Way to Use DBCC Page in SQL Server 2019

October 19, 2018

As soon as a new version of SQL Server is announced, I always try to find new things and update my old post with new information or write a new post. In this blog, we would learn about new function introduced in SQL Server 2019 which is a partial replacement of DBCC PAGE.
If you have not heard about the capability of DBCC PAGE then you can look at my earlier blog on the same topic. SQL SERVER – Identifying Page Types

Here is the comparison of the information shown my old and new method

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 the data is populated, we can use dm_db_database_page_allocations to find the pages which are allocated to this 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, using new DMF, we can get more details 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 we can see, this function can show information which is available in the page header. It has a lot of interesting details. Run it and see. I am sure this would help us in looking into more internals of SQL Server. I am going to explore it more and share with you in the future.

Meanwhile, if you are interested, please go ahead and look at my SQL Server Performance Tuning Practical Workshop on this page.

Reference: Pinal Dave (https://blog.SQLAuthority.com)