When it comes to learning SQL Server, it is important to start with a solid foundation. Just like a building needs a strong foundation for stability, understanding the basics of SQL Server is crucial for building a strong knowledge base. In this article, we will explore the concept of identifying page types in SQL Server.
Recently, during a customer consulting exercise, I came across a seasoned DBA who had been working with SQL Server for over a decade. He was trying to read the pages of SQL Server using the DBCC command, specifically the DBCC PAGE() command. However, he was unsure about how to identify the page he was working on.
Curious to help him out, I asked him to show me the script he was using. To my surprise, he was randomly using the DBCC PAGE() command without any structured approach. I decided to guide him on a more structured method to identify the pages associated with a particular table.
Here is an example of how we can get the pages associated with a specific 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'
By executing this query, we can obtain a list of data pages that have been allocated to a given table. Once we have this information, we can easily view the table’s data using the DBCC PAGE() command.
For example, to view the first GAM (Global Allocation Map) page in file 1, we can use the following command:
-- 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
After executing this command, we can see the page type for the GAM page is 8. In SQL Server, each page type has a specific value:
- 1 – Data page
- 2 – Index page
- 3 and 4 – Text pages
- 8 – GAM page
- 9 – SGAM page
- 10 – IAM page
- 11 – PFS page
By understanding these page types, we can easily query the data page using the DBCC PAGE() command. For example:
-- using data from sys.dm_db_database_page_allocations - view data page and review rows
DBCC PAGE (11, 1, 10624, 1)
GO
In this case, the m_type (page type) will be 1, indicating a data page.
While this topic may seem deeply internal to how SQL Server works, it is important to have a fundamental understanding of these concepts. As a SQL Server professional, there may be scenarios where you need to use DBCC commands to view data internally.
Now, I would like to hear from you. Have you ever used DBCC commands to view data internally? What were the use cases where you found them helpful? Please share your experiences in the comments below.