Exploring SQL Server under the hood using undocumented DBCC commands
Have you ever wanted to see the details on how data is organised in a SQL Server database? Exactly how tables, indexes, blogs, etc are stored. How SQL Server can find a specific row in a table. This is possible through two undocumented DBCC commands: DBCC IND and DBCC PAGE.
Here's a description of the DBCC commands.
Lists all of a table's data and index pages. Syntax:
'database_name' | database_id,
where index_id is either a value from sys.indexes or
-1 to show all indexes and IAMs
-2 to show all IAMs.
Displays the content of data and index pages.
'database_name' | database_id,
where detail_level is a number between 0 (least detailed) and 3 (most detailed).
Note that both these DBCC commands require that you set trace flag 3604:
DBCC TRACEON (3604)
Let's start by creating a database with a few files.
CREATE DATABASE [TEST] ON PRIMARY
( NAME = N'TEST_Primary1', FILENAME = N'C:\Your_path_here\TEST_Primary1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'TEST_Primary2', FILENAME = N'C:\Your_path_here\TEST_Primary2.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'TEST_Secondary', FILENAME = N'C:\Your_path_here\TEST_Secondary.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
( NAME = N'TEST_log', FILENAME = N'C:\Your_path_here\TEST_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
Next, let's create a table.
CREATE TABLE [dbo].[TestTable]
[ID] [int] IDENTITY(1,1) NOT NULL,
[SmallText] [varchar](2000) NOT NULL,
[LargeText] [text] NOT NULL,
[SparseText] [varchar](20) SPARSE NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [SECONDARY]
Now let's insert some data into the table.
WITH CTE1 AS
SELECT 1 AS [Ctr]
SELECT [Ctr] + 1
WHERE [Ctr] < 2000
'*** Row ' + CAST([Ctr] AS VARCHAR) + ' ' + REPLICATE('*', 1000) [Str]
INSERT INTO TestTable(SmallText, LargeText, SparseText)
SELECT [Str], [Str], NULL
OPTION (MAXRECURSION 0)
Let's modify one of the rows.
SET SparseText = 'Hello world!'
WHERE ID = 2
Now let's have a look a the table details through a DMV.
SELECT * FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID('TEST'), OBJECT_ID('TestTable'), NULL, NULL, 'DETAILED')
The interesting output here is that the IN_ROW_DATA (ID, SmallText and SparseText) has 2000 records (same as amount of rows) while the LOB_DATA (LargeText) has 4000 records! That is something you could investigate using the DBCC commands.
First let's find the page numbers.
DBCC TRACEON (3604)DBCC IND('TEST', TestTable, -1)
You'll get a long list of rows (one for each data/index page). Some of the interesting columns are: PageFID and PagePID which gives you the file_number and page_number of the page. We'll see three different values for PageFID: 1 for Primary1, 3 for Primary2 and 4 for Secondary if you have used my database creation statement. There is also a PageType field that has indicates type of page: 1 for data page, 2 for index page, 3 and 4 for text pages, 8 for GAM page, 9 for SGAM page, 10 for IAM page and 11 for PFS page.
Now you can display the detailed contents of any page using the DBCC PAGE command. Let's have a look at the first data page of the table - in my example PageFID 1 / PagePID 89.
DBCC PAGE('TEST', 1, 89, 3)
The number 3 says we want to get as much detail as possible. You'd then get very detailed info about the page. Some of the output is shown below (run the command yourself to see all output).
bpage = 0x0000000085C2C000 bhash = 0x0000000000000000 bpageno = (1:89)
bdbid = 9 breferences = 0 bcputicks = 390
bsampleCount = 1 bUse1 = 7672 bstat = 0xc0010b
blog = 0xca2159bb bnext = 0x0000000000000000
m_pageId = (1:89) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039828480
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:94)
pminlen = 8 m_slotCnt = 7 m_freeCnt = 759
m_freeData = 7419 m_reservedCnt = 0 m_lsn = (43:39:2)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 1043
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 1043
Memory Dump @0x000000000DD8A060
0000000000000000: 30000800 01000000 03000002 00030413 †0...............
0000000000000010: 842a2a2a 20526f77 2031202a 2a2a2a2a †*** Row 1 *****
0000000000000020: 2a2a2a2a 2a2a2a2a 2a2a2a2a 2a2a2a2a †****************
0000000000000030: 2a2a2a2a 2a2a2a2a 2a2a2a2a 2a2a2a2a †****************
0000000000000040: 2a2a2a2a 2a2a2a2a 2a2a2a2a 2a2a2a2a †****************
Good luck exploring SQL Server storage!