行大小计算测试

数据库中每一行到底占用多少空间,在这里,做个小测试~~~这里只做了堆表的计算,如果包含索引,按照文档把索引的列也加上就OK啦~

 

建个测试表,然后用DBCC PAGE打印出Slot的信息

 
 
 
CREATE TABLE TstA
(
 col1 CHAR(5) NULL,
 col2 CHAR(5) NULL,
 col3 VARCHAR(5) NULL,
 col4 VARCHAR(10) NULL,
 col5 VARCHAR(50) NULL
)

INSERT INTO TstA (col1,col2,col3,col4,col5) VALUES ('aa1','2222222222','joan2','aoaonfoen','1111111')

dbcc ind(
TestDB, Object_id('TstA'),-1)
DBCC PAGE(TestDB,1,11957,1)

然后我就直接把slot的消息贴出来分析吧~~

 

-------------------------------------------------------------------------------------------------------------------------------------

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

 

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 51

Memory Dump @0x0A93C060

00000000: 30000e00 61613120 206a6f61 6e320500 †0...aa1 joan2..

00000010: 00030023 002c0033 00323232 32323232 †...#.,.3.2222222

00000020: 32323261 6f616f6e 666f656e 31313131 †222aoaonfoen1111

00000030: 313131†††††††††††††††††††††††††††††††111    

 

        4        8       12      16       20       24       28      32       36      40       44        48      52

30000e00 61613120 206a6f61 6e320500 00030023 002c0033 00323232 3232323 32323261 6f616f6e 666f656e 31313131 313131

    1       2         3         4      5      6            7                    8                    9

1: 固定长度列结束位置

2: 固定长度第一列(col1)

3: 固定长度第二列(col3) --如果之后还有更多的列,继续往后排

4: 行包含的栏位总数,本例为5

5: 空值维护:计算方式为 2+ (可为空列数+7)/8 

6: 每一个可变列的结束位置。长度计算   2 + (可变列列数*2)

7+:变长列的每一个填充列

 

验证了这一句话,每行的开销

  • ix bytes per row for status information and length information.
  • One bit per fixed width column, rounding up to the nearest byte.
  • If there are any variable length columns - four bytes for the first, plus two bytes for each additional variable length column
  • An additional two bytes per row for the offset pointer located at the end of the page.

 

-----------------------------------------------------------------------------------------------------

经过测试,无论列的顺序怎么排,首先会先把定长的字段先排出来,然后再排变长字段~好吧~测试结束

小弟不才~欢迎大家指出不足 

 

参考:

  http://technet.microsoft.com/zh-cn/library/ms189124.aspx

 

PS:上面2个DBCC 的语法

dbcc ind(dbname|dbid, tbname|tbid,-1)
 

dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354.

The printopt parameter has the following meanings:

0 - print just the page header 
1 - page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn't have one, like allocation bitmaps) 
2 - page header plus whole page hex dump 
3 - page header plus detailed per-row interpretation

 

 

posted on 2014-11-22 17:14  神崎橙  阅读(261)  评论(0编辑  收藏  举报

导航