/*
01 - Data Structure.sql
Remark: Learned from sqlskills.com
*/
USE master
GO
IF DATABASEPROPERTY('MCM2008','Version') > 0
drop database MCM2008;
GO
CREATE DATABASE MCM2008;
GO
USE MCM2008;
GO
CREATE TABLE DbCCPageTest (
intCol1 INT IDENTITY,
intCol2 INT,
vcharCol VARCHAR(8000),
lobCol VARCHAR(MAX)
);
INSERT INTO DbCCPageTest VALUES (
1, REPLICATE('ROW1',600), REPLICATE('Row1Lobs',1000));
INSERT INTO DbCCPageTest VALUES (
2, REPLICATE('ROW2',600), REPLICATE('Row2Lobs',1000));
INSERT INTO DbCCPageTest VALUES (
3, REPLICATE('ROW3',600), REPLICATE('Row3Lobs',1000));
INSERT INTO DbCCPageTest VALUES (
4, REPLICATE('ROW4',600), REPLICATE('Row4Lobs',1000));
-- Introducing DBCC IND
DBCC IND ('MCM2008','DbccPageTest',-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 147 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10 NULL 0 0 0 0
1 146 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 154 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 145 NULL NULL 2105058535 0 1 72057594038779904 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
(8 row(s) affected)
*/
-- Pick a data page and use DBCC PAGE on it.
-- Trace flag is required to make output
-- go to the console rather than the errorlog.
-- Takes db, file, page, print the option 1-3
DBCC TRACEON (3604);
GO
-- Option 1 is a hex dump of each record
-- plus interpreting the slot arry
--DBCC PAGE ('MCM2008',1,XX,1);
DBCC PAGE ('MCM2008',1,147,1);
GO
-- Data page with data records
-- Option 2 is a hex dump of the page plus
-- interpreting the slot array
DBCC PAGE ('MCM2008',1,154,2);
GO
-- Option 3 interprets each records folly
DBCC PAGE ('MCM2008',1,154,3);
GO
-- Note the off-row link. Let's follow it
DBCC PAGE ('MCM2008',1,153,3);
GO
-- Text page
-- Now let's face a forwarding record by
-- updating a row in the middle of a page
UPDATE DbCCPageTest
SET vcharCol = REPLICATE('LongRow2',1000)
WHERE intCol2 =2;
UPDATE DbCCPageTest
SET vcharCol = REPLICATE('LongRow4',1000)
WHERE intCol2 =4;
GO
-- Introducing DBCC IND
DBCC IND ('MCM2008','DbccPageTest',-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 147 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10 NULL 0 0 0 0
1 146 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 154 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 156 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 157 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 145 NULL NULL 2105058535 0 1 72057594038779904 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
(10 row(s) affected)
*/
-- Look at the first data page again
DBCC PAGE ('MCM2008',1,154,3);
GO
/*
00000000: 049d0000 00010000 00†††††††††††††††††.........
Forwarding to = file 1 page 157 slot 0
*/
-- Now we've got a new record type, let's
-- follow the link
DBCC PAGE ('MCM2008',1,157,3);
GO
-- Another new record type. What's the
-- difference The back-link ...
-- Now we create a clustered index
CREATE CLUSTERED INDEX Dbcc_CL
ON DbccPageTest (intCol1);
GO
-- And look at the pages again, with
-- the new index ID
DBCC IND ('MCM2008','DbccPageTest',-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 168 NULL NULL 2105058535 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0
1 159 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 169 0 0
1 169 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 171 1 159
1 170 1 168 2105058535 1 1 72057594038845440 In-row data 2 1 0 0 0 0
1 171 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 172 1 169
1 172 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 0 0 1 171
1 145 NULL NULL 2105058535 1 1 72057594038845440 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
(11 row(s) affected)
*/
-- Pick an index page, with m_type=2.
DBCC PAGE ('MCM2008',1,170,1);
GO
-- Index page with index records
-- Let's take a closer look.
DBCC PAGE ('MCM2008',1,170,3);
GO
/*
PAGE: (1:170)
BUFFER:
BUF @0x042C3C44
bpage = 0x0D266000 bhash = 0x00000000 bpageno = (1:170)
bdbid = 14 breferences = 0 bUse1 = 14003
bstat = 0x1c0010b blog = 0x1432121b bnext = 0x00000000
PAGE HEADER:
Page @0x0D266000
m_pageId = (1:170) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 1 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 30 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039894016
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 11 m_slotCnt = 4 m_freeCnt = 8044
m_freeData = 140 m_reservedCnt = 0 m_lsn = (19:246:48)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
FileId PageId Row Level ChildFileId ChildPageId intCol1 (key) UNIQUIFIER (key) KeyHashValue
------ ----------- ------ ------ ----------- ----------- ------------- ---------------- ----------------
1 170 0 1 1 159 NULL NULL NULL
1 170 1 1 1 169 2 0 NULL
1 170 2 1 1 171 3 0 NULL
1 170 3 1 1 172 4 0 NULL
(4 row(s) affected)
*/
-- Now add a non-clustered index
CREATE NONCLUSTERED INDEX Dbcc_NCL
ON DbccPageTest (intCol2);
GO
-- And look at the pages again, with
-- the new index ID
DBCC IND ('MCM2008','DbccPageTest',-1);
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 168 NULL NULL 2105058535 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0
1 159 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 169 0 0
1 169 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 171 1 159
1 170 1 168 2105058535 1 1 72057594038845440 In-row data 2 1 0 0 0 0
1 171 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 172 1 169
1 172 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 0 0 1 171
1 145 NULL NULL 2105058535 1 1 72057594038845440 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 174 NULL NULL 2105058535 3 1 72057594038910976 In-row data 10 NULL 0 0 0 0
1 173 1 174 2105058535 3 1 72057594038910976 In-row data 2 0 0 0 0 0
(13 row(s) affected)
*/
-- Pick another index page and explain the
-- key columns
-- %%lockres%% to investigate the key hashes
DBCC PAGE ('MCM2008',1,173,3);
GO
/*
PAGE: (1:173)
BUFFER:
BUF @0x042BF9C4
bpage = 0x0D0A6000 bhash = 0x00000000 bpageno = (1:173)
bdbid = 14 breferences = 0 bUse1 = 14079
bstat = 0x1c0010b blog = 0x1212121b bnext = 0x00000000
PAGE HEADER:
Page @0x0D0A6000
m_pageId = (1:173) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 32 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040025088
Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 3
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 9 m_slotCnt = 4 m_freeCnt = 8040
m_freeData = 144 m_reservedCnt = 0 m_lsn = (19:309:39)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
FileId PageId Row Level intCol2 (key) intCol1 (key) UNIQUIFIER (key) KeyHashValue
------ ----------- ------ ------ ------------- ------------- ---------------- ----------------
1 173 0 0 1 1 0 (05001887e4aa)
1 173 1 0 2 2 0 (07000dc1c9d4)
1 173 2 0 3 3 0 (0900fefcd2fe)
1 173 3 0 4 4 0 (0b00274d9328)
(4 row(s) affected)
*/
-- Now drop the clustered index and look
-- again at the nonclustered index
DROP INDEX Dbcc_CL on DbccPageTest
GO
-- And look at the pages again, with
-- the new index ID
DBCC IND ('MCM2008','DbccPageTest',-1);
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 168 NULL NULL 2105058535 0 1 72057594038976512 In-row data 10 NULL 0 0 0 0
1 159 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 1 169 0 0
1 169 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 1 171 1 159
1 171 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 1 172 1 169
1 172 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 0 0 1 171
1 145 NULL NULL 2105058535 0 1 72057594038976512 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 146 NULL NULL 2105058535 3 1 72057594039042048 In-row data 10 NULL 0 0 0 0
1 175 1 146 2105058535 3 1 72057594039042048 In-row data 2 0 0 0 0 0
(12 row(s) affected)
*/
DBCC PAGE ('MCM2008',1,175,3);
GO
/*
PAGE: (1:175)
BUFFER:
BUF @0x042C33A8
bpage = 0x0D22C000 bhash = 0x00000000 bpageno = (1:175)
bdbid = 14 breferences = 0 bUse1 = 14427
bstat = 0x1c0010b blog = 0x1212121b bnext = 0x00000000
PAGE HEADER:
Page @0x0D22C000
m_pageId = (1:175) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 3
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 13 m_slotCnt = 4 m_freeCnt = 8024
m_freeData = 160 m_reservedCnt = 0 m_lsn = (19:321:94)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
FileId PageId Row Level intCol2 (key) HEAP RID (key) KeyHashValue
------ ----------- ------ ------ ------------- ------------------ ----------------
1 175 0 0 1 0x9F00000001000000 (a100c7b49351)
1 175 1 0 2 0xA900000001000000 (ac00cd3caf65)
1 175 2 0 3 0xAB00000001000000 (af00df772fbc)
1 175 3 0 4 0xAC00000001000000 (b100499772c2)
(4 row(s) affected)
*/
Friday, January 14, 2011
SQL 2008 Core Studies - Data Structure
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment