SQL SERVER – Identifying Page Types

I try to keep the understanding and concepts for learning simple. Because for a strong building one needs to have a solid foundation. The structure’s stability is a function of how well the foundation holds them. Just having a beautiful exterior will not be sufficient. So getting into complex design issues or internals sometimes is not of any use if you didn’t get the basics right. Let us learn about Identifying Page Types in SQL.

You all know how I love challenges and how I take such challenges by the horn. This is something that keeps me motivated and makes me learn. So in a recent customer consulting exercise, I was going through the basics of SQL Server building blocks, one of the DBAs who was pretty seasoned and was working in SQL Server for more than a decade came back and asked: “Pinal, I am trying to read the pages of SQL Server using a DBCC command. I love doing it, but I would like to know what page am I working on?” Is there a simpler mechanism to know about the page that I am reading?

Though the question was getting me uncomfortable, I wanted to still help this friend who is just trying to check on what I know. So I said, let me sit with you to see what script you are doing. Post the scheduled sessions and topics covered, I asked him to come up to stage and show what he has. I was surprised that he was using randomly DBCC PAGE() to read data from SQL Server. This was very strange.

I said, let me help you in a much more structured manner and I went about changing the code to show what needs to be done.

I started to show how one can get the pages associated with a particular table:

USE AdventureWorks2016
GO
-- Examine data page allocations in the Person.Address table using the 
-- new DMF sys.dm_db_database_page_allocations
SELECT *
FROM sys.dm_db_database_page_allocations(db_id('AdventureWorks2016'), 
object_id('Person.Address'), 1, null, 'DETAILED')
WHERE page_type_desc = 'DATA_PAGE'

As you can see, now we have a list of data pages that have been allocated to a given data from a table. Now we can easily look at the table’s data using the DBCC PAGE() command. There is no rocket science about this as a number of people have written a blog on this command before.

-- enable TF 3604
DBCC TRACEON (3604)
GO
-- view first GAM page in file 1, the first GAM page is page 2 of the data file,
--  verify this by looking at the page type field, m_type.
DBCC PAGE (11, 1, 2, 1)
GO

Now it is not evident to what this page type is actually. So here is a good start for folks who would like to know, common page types:

• 1 – data page
• 2 – index page
• 3 and 4 – text pages
• 8 – GAM page
• 9 – SGAM page
• 10 – IAM page
• 11 – PFS page

Now you can see that we have the 2^nd Page (GAM) showing with a type value of 8. We can easily query the data page like:

-- using data from sys.dm_db_database_page_allocations - view data page and review rows
DBCC PAGE (11, 1, 10624, 1)
GO

Now in this case, the m_type will be 1 as it would be a data page. When I took this as part of the session as a stretch, I felt really good that I was able to answer some of these fundamentals though the content was looking deeply internal to how things work inside SQL Server.

-- disable TF 3604
DBCC TRACEOFF (3604)
GO

The above command is to clean up the trace flag because we used this to show the data from a DBCC PAGE (). Now they would not be required as is for future requirements.

Let me ask you a much more philosophical question, how many of you used such DBCC commands to see data internally? What are the use cases when you had to do something like this? I am curious to learn them from you. Please let me know via comments below.

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