SQL Server 堆表行存储大小(Record Size)
2013-11-19 15:01 听风吹雨 阅读(4773) 评论(5) 编辑 收藏 举报一.本文所涉及的内容(Contents)
二.背景(Contexts)
有的时候你需要计算堆表的一行记录有多大?又或者想计算一个数据页(8K)能保存多少条记录?字段类型是设计成nchar还是nvarchar?他们有什么区别呢?在做数据库表设计的时候会经常出现这些问题。要计算一行记录的大小,并不是简单把列字段类型大小直接相加就行的,具体原因请看下文。
三.堆表行记录存储格式(Heap)
下面是计算堆表行记录大小的公式,它引自MSDN:估计堆的大小
计算公式:Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定长度列的总字节大小 + 可变长度数据的大小 + Null位图 + 数据行的行标题开销
单看上面的计算公式是比较难理解的,而且MSDN并没有提供相关的说明,比如上面公式最后的4代表什么意思?我参考了一些书籍之后整理出下面更容易理解的堆记录存储格式图,希望能帮助大家理解:
(Figure1:堆表记录存储格式)
四.案例分析(Case)
本文针对堆表数据页的存储做一个测试,测试不同数据类型的存储大小,测试的数据类型包括:int、char、nchar和nvarchar,测试后你会理解什么是定长类型、什么是变长类型,他们在存储上有什么区别;
(一) 首先创建一个测试数据库;
/******* Step1:创建示例数据库*******/ USE master GO IF EXISTS(SELECT name FROM sys.databases WHERE name = 'RecordSize_DB') DROP DATABASE RecordSize_DB GO CREATE DATABASE RecordSize_DB GO
(二) 接着在数据库中创建3个不同的堆表:[HeapPage_char]、[HeapPage_nchar] 和[HeapPage_nvarchar],3个表分别代表:char、nchar和nvarchar不同数据类型的存储;
USE RecordSize_DB GO /******* Step2:创建个堆表*******/ CREATE TABLE [dbo].[HeapPage_char]( [id] [int] IDENTITY(1,1) NOT NULL, [names] [char](10) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[HeapPage_nchar]( [id] [int] IDENTITY(1,1) NOT NULL, [names] [nchar](10) NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[HeapPage_nvarchar]( [id] [int] IDENTITY(1,1) NOT NULL, [names] [nvarchar](10) NULL ) ON [PRIMARY] GO
(三) 再接着就是在3个不同的堆表插入相同的数据;
/******* Step3:分别插入测试数据*******/ INSERT INTO [HeapPage_char](names) values('XX') GO INSERT INTO [HeapPage_char](names) values('XXXX') GO 2 INSERT INTO [HeapPage_nchar](names) values('XX') GO INSERT INTO [HeapPage_nchar](names) values('XXXX') GO 2 INSERT INTO [HeapPage_nvarchar](names) values('XX') GO INSERT INTO [HeapPage_nvarchar](names) values('XXXX') GO 2
(四) 返回3个测试表数据;
-- 返回表数据 SELECT * FROM [HeapPage_char] SELECT * FROM [HeapPage_nchar] SELECT * FROM [HeapPage_nvarchar]
(Figure2:HeapPage_char)
(Figure3:HeapPage_nchar)
(Figure4:HeapPage_nvarchar)
(五) 通过开启3604跟踪和DBCC PAGE命令查询数据的存储信息;
/******* Step4:查看各表数据页大小*******/ --开启跟踪 DBCC TRACEON(3604) --查看表信息 DBCC IND(RecordSize_DB,HeapPage_char,-1)
上面的命令会返回如下图所示的信息:
(Figure5:堆表HeapPage_char)
上图返回结果都会因为每次创建而有所不同,你需要根据每次不同的值来填写下面的DBCC命令中的参数。上图第一行记录为IAM page,第二行为data page(数据页),这里是查看数据页的内容,在参数中填入PageFID=1和PagePID=80
--查看PAGE信息 DBCC PAGE(RecordSize_DB,1,80,1)
执行上面的DBCC PAGE命令将返回下面信息:
DATA:
Slot 0, Offset 0x60, Length 21, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x6523C060
00000000: 10001200 01000000 58582020 20202020 †........XX
00000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 1, Offset 0x75, Length 21, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x6523C075
00000000: 10001200 02000000 58585858 20202020 †........XXXX
00000010: 20200200 00†††††††††††††††††††††††††† ...
Slot 2, Offset 0x8a, Length 21, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 21
Memory Dump @0x6523C08A
00000000: 10001200 03000000 58585858 20202020 †........XXXX
00000010: 20200200 00†††††††††††††††††††††††††† ...
OFFSET TABLE:
Row - Offset
2 (0x2) - 138 (0x8a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
从上面的信息我们可以知道3条记录的长度都是:Record Size = 21,这个值是怎么算出来的呢?公式可以参考:估计堆的大小
计算公式:Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定长度列的总字节大小 + 可变长度数据的大小 + Null位图 + 数据行的行标题开销
计算过程:
Fixed_Data_Size = 所有固定长度列的总字节大小 = 4+10(int大小为4个字节,char(10)固定大小为10个字节)
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
Num_Variable_Cols = 可变长度列数 = 0(没有可变长度列)
Max_Var_Size = 所有可变长度列的最大总字节大小 = 0
Variable_Data_Size = 0(如果没有可变长度列,请将 Variable_Data_Size 设置为 0)
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
Num_Cols = 总列数(固定长度和可变长度)= 2(id、names两个列)
Null_Bitmap = 3(只保留整数部分,放弃所有余数)
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 14 + 0 + 3 +4 = 21(与上面Record Size = 21符合)
(六) 接下来,我们来看看HeapPage_nchar表的存储大小会有什么不同:
--查看表HeapPage_nchar DBCC ind(RecordSize_DB,HeapPage_nchar,-1)
(Figure6:堆表HeapPage_nchar)
--查看PAGE信息 DBCC PAGE(RecordSize_DB,1,90,1)
DATA:
Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 31
Memory Dump @0x61ADC060
00000000: 10001c00 01000000 58005800 20002000 †........X.X. . .
00000010: 20002000 20002000 20002000 020000†††† . . . . . ....
Slot 1, Offset 0x7f, Length 31, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 31
Memory Dump @0x61ADC07F
00000000: 10001c00 02000000 58005800 58005800 †........X.X.X.X.
00000010: 20002000 20002000 20002000 020000†††† . . . . . ....
Slot 2, Offset 0x9e, Length 31, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 31
Memory Dump @0x61ADC09E
00000000: 10001c00 03000000 58005800 58005800 †........X.X.X.X.
00000010: 20002000 20002000 20002000 020000†††† . . . . . ....
OFFSET TABLE:
Row - Offset
2 (0x2) - 158 (0x9e)
1 (0x1) - 127 (0x7f)
0 (0x0) - 96 (0x60)
套用上面的公式:
计算公式:Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定长度列的总字节大小 + 可变长度数据的大小 + Null位图 + 数据行的行标题开销
计算过程:
Fixed_Data_Size = 所有固定长度列的总字节大小 = 4+10*2(int大小为4个字节,nchar(10)固定大小为20个字节)
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
Num_Variable_Cols = 可变长度列数 = 0(没有可变长度列)
Max_Var_Size = 所有可变长度列的最大总字节大小 = 0
Variable_Data_Size = 0(如果没有可变长度列,请将 Variable_Data_Size 设置为 0)
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
Num_Cols = 总列数(固定长度和可变长度)= 2(id、names两个列)
Null_Bitmap = 3(只保留整数部分,放弃所有余数)
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 24 + 0 + 3 +4 = 31(与上面Record Size = 31符合)
总结:从上面测试结果来看:char(10)与nchar(10)是固定长度数据类型,无论你保存多少内容,在数据页中会为记录保留固定长度的空间,而char(10)与nchar(10)的区别是nchar(10)占用的空间是char(10)的两倍;
(七) 接下来,我们来看看HeapPage_nvarchar表的存储大小又有什么不同:
--查看表HeapPage_nvarchar DBCC ind(RecordSize_DB,HeapPage_nvarchar,-1)
(Figure7:堆表HeapPage_nvarchar)
--查看PAGE信息 DBCC PAGE(RecordSize_DB,1,94,1)
DATA:
Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 19
Memory Dump @0x61ADC060
00000000: 30000800 01000000 02000001 00130058 †0..............X
00000010: 005800†††††††††††††††††††††††††††††††.X.
Slot 1, Offset 0x73, Length 23, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x61ADC073
00000000: 30000800 02000000 02000001 00170058 †0..............X
00000010: 00580058 005800††††††††††††††††††††††.X.X.X.
Slot 2, Offset 0x8a, Length 23, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
Memory Dump @0x61ADC08A
00000000: 30000800 03000000 02000001 00170058 †0..............X
00000010: 00580058 005800††††††††††††††††††††††.X.X.X.
OFFSET TABLE:
Row - Offset
2 (0x2) - 138 (0x8a)
1 (0x1) - 115 (0x73)
0 (0x0) - 96 (0x60)
套用上面的公式:
计算公式:Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
= 所有固定长度列的总字节大小 + 可变长度数据的大小 + Null位图 + 数据行的行标题开销
计算过程:
Fixed_Data_Size = 所有固定长度列的总字节大小 = 4(int大小为4个字节)
Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
Num_Variable_Cols = 可变长度列数 = 1(names列的数据类型是nvarchar)
Max_Var_Size = 所有可变长度列的最大总字节大小 = 第一行记录是2*2;第二、三行记录是2*4
第一行记录Variable_Data_Size = 2 + (1 * 2) + (2*2) = 8
第二、三行记录Variable_Data_Size = 2 + (1 * 2) + (2*4) = 12
Null_Bitmap = 2 + ((Num_Cols + 7) / 8)
Num_Cols = 总列数(固定长度和可变长度)= 2(id、names两个列)
Null_Bitmap = 2 + ((2 + 7) / 8) = 3(只保留整数部分,放弃所有余数)
Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4
第一行记录Row_Size = 4 + 8 + 3 + 4 = 19(与上面第一行记录Record Size = 19符合)
第二、三行记录Row_Size = 4 + 12 + 3 + 4 = 23(与上面第二、三行记录Record Size = 23符合)
总结:从上面结果来看:nvarchar(10)是变长度数据类型,你输入字符串有多少就存储多少内容,这就是定长数据类型与变长数据类型的区别,从第一行记录(占用19 Bytes)与第二、三行记录(占用23 Bytes)占用了不同的数据空间可以证实这一点;而nchar(10)与nvarchar(10)一样,都属于Unicode编码,所以占用的空间是非Unicode编码的2倍;
五.参考文献(References)
作者:听风吹雨
出处:
http://www.cnblogs.com/gaizai/
邮箱:gaizai@126.com
版权:本文版权归作者和博客园共有
转载:欢迎转载,必须保留原文链接
格言:不喜欢是因为不会 && 因为会所以喜欢