SQL Server Query Optimization

SQL Server Query Optimization ,Internal Storage, Indexes

Internale Storage  - Data Pages

Create table tab1 in msdb

create table tab1
(id int,
 item varchar(20))

 insert into tab1 values(1,'AAA')
 insert into tab1 values(1,'BBB')

 select id,item from tab1
 select * from tab1

 DBCC IND ('msdb',tab1,-1);
 DBCC TRACEON(3604)
 DBCC PAGE('msdb',1,1510,1)

The Internal Page Structure is shown below

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:1510)


BUFFER:


BUF @0x07DA7290

bpage = 0x1228E000                  bhash = 0x00000000                  bpageno = (1:1510)
bdbid = 4                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 4547                        bstat = 0x10b
blog = 0x5adb21cc                   bnext = 0x00000000                 

PAGE HEADER:


Page @0x1228E000

m_pageId = (1:1510)                 m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xa000
m_objId (AllocUnitId.idObj) = 412   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594064928768                               
Metadata: PartitionId = 72057594055491584                                Metadata: IndexId = 0
Metadata: ObjectId = 1305771709     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 2                       m_freeCnt = 8028
m_freeData = 160                    m_reservedCnt = 0                   m_lsn = (97:256:2)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                     

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED             
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL                        DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED          

DATA:


Slot 0, Offset 0x60, Length 32, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 32                   
Memory Dump @0x0F79A060

00000000:   70000800 01000000 02000001 00120041 41410000  p..............AAA..
00000014:   00000000 00002f15 00000000                    ....../.....  

Slot 1, Offset 0x80, Length 32, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 32                   
Memory Dump @0x0F79A080

00000000:   70000800 01000000 02000001 00120042 42420000  p..............BBB..
00000014:   00000000 00003215 00000000                    ......2.....  

OFFSET TABLE:

Row - Offset                       
1 (0x1) - 128 (0x80)               
0 (0x0) - 96 (0x60)                


DBCC execution completed. If DBCC printed error messages, contact your system administrator.