SQL Server Page Types

There are lots of different page types and Paul Randall list them in his article. However as I tend to have a rather poor memory for remembering them first off here are the ones that can be anywhere within the database.

Now the other ones should be in predictable locations assuming I have the script coded correctly. I have added in a few example page locations, as well as looking in to the suspect pages table as in a restoration scenario only certain pages can be restored using page restore.

SELECT *,
CASE
WHEN [page_id] = 0 THEN 'File Header Page m_type 15'
WHEN [page_id] = 1 OR [page_id] % 8088 = 0 THEN 'PFS m_type 11'
WHEN [page_id] = 2 OR [page_id] % 511232 = 0 THEN 'GAM m_type 8'
WHEN [page_id] = 3 OR ([page_id] - 1) % 511232 = 0 THEN 'SGAM m_type 9'
WHEN [page_id] = 6 OR ([page_id] - 6) % 511232 = 0 THEN 'DCM m_type 16'
WHEN [page_id] = 7 OR ([page_id] - 7) % 511232 = 0 THEN 'BCM m_type 17'
WHEN [page_id] = 9 AND [file_id] = 1 THEN 'Boot Page m_type 13' --DBCC DBINFO WITH TABLERESULTS
WHEN [page_id] = 10 AND DB_ID() = 1 THEN 'config page -> sp_configure settings only present in master m_type 14'
ELSE 'Other'
END [Description],
'DBCC PAGE('''+DB_NAME()+''','+LTRIM(STR(x.[file_id]))+','+LTRIM(STR(x.[page_id]))+',3) WITH TABLERESULTS' [Page]
FROM
(
SELECT 0 [page_id],1 [file_id] UNION ALL -- A File Header Page
SELECT 1 [page_id],1 [file_id] UNION ALL -- A PFS Page
 --SELECT 8088 [page_id],1 [file_id] UNION ALL -- A PFS Page
 --SELECT 16176 [page_id],1 [file_id] UNION ALL -- A PFS Page
SELECT 2 [page_id],1 [file_id] UNION ALL -- A GAM Page
 --SELECT 511232 [page_id],1 [file_id] UNION ALL -- A GAM Page
SELECT 3 [page_id],1 [file_id] UNION ALL -- A SGAM page
 --SELECT 511233 [page_id],1 [file_id] UNION ALL -- A SGAM page
SELECT 6 [page_id],1 [file_id] UNION ALL -- A DCM page
 --SELECT 511238 [page_id],1 [file_id] UNION ALL -- A DCM page
SELECT 7 [page_id],1 [file_id] UNION ALL -- A BCM page
 --SELECT 511239 [page_id],1 [file_id] UNION ALL -- A BCM page
SELECT 9 [page_id],1 [file_id] UNION ALL -- The Boot Page
SELECT [page_id],[file_id] FROM msdb.dbo.suspect_pages
) x

Acknowledgement

This m_type values were gathered from Paul Randall’s blog, and the script works along the same lines as Robert L Davis blog post on tempdb contention.

http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontentionpart2

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/