SQL Internal Series- Viewing PFS, GAM and SGAM Pages Using DBCC Page
SQL Internal Series- Viewing PFS, GAM and SGAM Pages Using DBCC Page #jbswiki
-- Query 1
dbcc traceon (3604) -- Print the output in query window.
-- DBCC PAGE is an undocumented command. Never run this on a Customer environment. I am doing this for learning purpose and IMPORTANTLY on a LAB machine.
-- DBCC PAGE (DATABASENAME,FILE_NUMBER, PAGE_NUMBER [, PRINT_OUTPUT (0|1|2|3)])
-- PFS PAGE
DBCC PAGE (JB_Common_Query_Tuning_DB,1,1,3)
-- GAM PAGE
DBCC PAGE (JB_Common_Query_Tuning_DB,1,2,3)
-- SGAM PAGE
DBCC PAGE (JB_Common_Query_Tuning_DB,1,3,3)
-- Query 2
create database JBPAGE
go
ALTER DATABASE JBPAGE set MIXED_PAGE_ALLOCATION on
go
use JBPAGE
go
create table testtable(col1 int, Col2 varchar(8000))
insert into testtable values (1,replicate('a',1000))
select ht1.Col1,ht1.Col2, p.file_id,p.page_id, is_mixed_page_allocation
From testtable as ht1 CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as p inner join
sys.dm_db_database_page_allocations(db_id(),object_id('dbo.testtable'),null,null,'detailed') as dddpa
on p.file_id=dddpa.allocated_page_file_id and
p.page_id=dddpa.allocated_page_page_id
dbcc traceon (3604)
DBCC PAGE (JBPAGE,1,1,3)
DBCC PAGE (JBPAGE,1,2,3)
DBCC PAGE (JBPAGE,1,3,3) Disclaimer:
The views expressed on this Video are mine alone and do not reflect the views of my company or anyone else. All postings on this Video are provided “AS IS” with no warranties, and confers no rights.
