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.
m_type | |
1 | DATA_PAGE |
2 | INDEX_PAGE |
3 | TEXT_MIX_PAGE |
4 | TEXT_TREE_PAGE |
7 | SORT_PAGE |
10 | IAM_PAGE |
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/
Leave a Reply