《Microsoft Sql server 2008 Internals》读书笔记--第七章Special Storage(1)

 

《Microsoft Sql server 2008 Internals》读书笔记订阅地址:

http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Internals》索引目录:

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

 

第五章《Table》和第六章《Indexes:Internals and Management》我们主要讨论了“规则行”在表和索引中的存储机制,第五章中我们了解到规则行是以“固定变量”的格式存储,SQL Server提供了另外一种格式存储数据,即“列描述”(Column Descriptor)。它能存储一些特殊的值(包括固定变量和不能适应常规8-KB大小页面的CD格式的值),在这章中。我们将讨论一些超过正常数据行 大小限制的数据,它们被存储作为"行溢出"或Large Object(LOB)数据。此外,SQL Server 2008还提供了一种常规列的复杂存储列,即稀疏列(Sparse Columns),和新的CD格式(compressed data)。另外,还将讨论文件流访问,即SQL Server访问操作系统的文件。最后将讨论SQL Server分离数据到分区的操作。

 ■Large Object Storage

根据第五章的知识,我们了解到8k=8192字节中,只有8060字节是用于存储数据,如果一个表的行长度超过8060,将会收到一条提示:

 USE testdb;
go

CREATE TABLE dbo.bigrows_fixed  
(   a 
char(3000),  
    b 
char(3000), 
    c 
char(2000), 
    d 
char(60) ) ;
--Msg 1701, Level 16, State 1, Line 2
--
Creating or altering table 'bigrows_fixed' failed because the minimum row size would be 8067, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
--
-- The following code creates a table with rows that have a maximum defined length that is much longe

 

 

你可以看到8067中有7个字节用于存储行自身的开销。其实还有两个字节用于存储行偏移,但没有包含在这7个字节,详见前文第五章部分。

  ■限制长度的大对象数据(行溢出数据Row-Overflow Data)

 超过8060字节的行限制的一个方法是使用可变长度列。因为SQL Server 2005/2008会使用“行溢出”页面来存储这些列,只要固定长度列不超过限制即可。可变长度列包括 varbonary,varchar,nvarchar,sqlvariant等,还可以是在CLR中用户定义的数据类型。

如下语句:

CREATE TABLE dbo.bigrows  
  (a 
varchar(3000),  
   b 
varchar(3000), 
   c 
varchar(3000), 
   d 
varchar(3000) ); 

 

事实上。这个语句在SQL Server 7.0出错而不会被执行,SQL Server 2000中会执行,但会收到一个警告,超过8060的行可能会被截断。而在SQL Server 2005/2008中,你不但可以创建表,而且可以插入超过8060字节的数据。如下:

INSERT INTO dbo.bigrows
     
SELECT REPLICATE('e'2100), REPLICATE('f'2100),
      
REPLICATE('g'2100),  REPLICATE('h'2100);
--我们来确认一下SQL Server是如何存储行溢出数据的:
 SELECT object_name(object_idAS name,  
    partition_id, partition_number 
AS pnum,  rows,  
    allocation_unit_id 
AS au_id, type_desc as page_type_desc,
    total_pages 
AS pages
FROM sys.partitions p  JOIN sys.allocation_units a
   
ON p.partition_id = a.container_id
WHERE object_id=object_id('dbo.bigrows');
--name    partition_id    pnum    rows    au_id    page_type_desc    pages
--
bigrows    72057594043432960    1    1    72057594045005824    IN_ROW_DATA    2
--
bigrows    72057594043432960    1    1    72057594045071360    ROW_OVERFLOW_DATA    2

结果中可以看出,这一行中有两个页用来存放规则“行内”数据,两个页来存放“行溢出”数据,此外,我们还可以使用DBCC IND命令来查看更详细的四个页的存储,
注意:当表开始增长时,这个命令将无法应付成千上万的页,第六章提供了一个sp_tablepages脚本,可以很方便捕获DBCC的输出。

INSERT INTO sp_tablepages
    
EXEC ('DBCC IND (testdb, bigrows, -1)')

SELECT PageFID, PagePID, ObjectID, PartitionID, IAM_chain_type, PageType
FROM sp_tablepages;

  邀月工作室

 邀月工作室

 在上图中,e(对应ascii码为65)与g(67)之间对应的部分前16个字节存储了Row-Overflow的指针,如下:

 

字节数 十六进制值 十进制值
Meaning
0 0x02 2 1=LOB;2=Overflow
1-2 0x0000 0 B树等级,对OverFlow始终为0
3 0x00 0 未使用
4-7 0x00000163 ? 序列号,在优化并发时使用
8-11 0x004ae100 ? 时间戳
12-15 0x00000834 2100 长度

 

SQL Server 在一定条件下存储可变长度列在行溢出页。决定因素在于行自身的长度,即只有行自身需要超过8060字节时才会选择。表中的每一行要么全在行中,要么全在行 外。一个行如果包含多个large Variable-length列,那么它可以跨越多个行溢出页。  例如:

CREATE TABLE dbo.hugerows  
  (a 
varchar(3000),  
   b 
varchar(8000),
   c 
varchar(8000),
   d 
varchar(8000));
 
INSERT INTO dbo.hugerows
     
SELECT REPLICATE('a'3000), REPLICATE('b'8000),
         
REPLICATE('c'8000),  REPLICATE('d'8000);
--name    partition_id    pnum    rows    au_id    page_type_desc    pages
--
hugerows    72057594043498496    1    1    72057594045136896    IN_ROW_DATA    2
--
hugerows    72057594043498496    1    1    72057594045202432    ROW_OVERFLOW_DATA    4

 注意:虽然SQL Server提供了大容量数据存储方式,你可以高效的使用特殊数据存储而不必重新设计你的表结构,但是,最佳的使用环境是:表的大部分数据是在行内(即常规数据)而偶尔需要大容量数据处理。

在某些情况下,如果一个大的可变长度列被收缩,可能变成常规列,然而,出于效率考虑,如果仅减少几个字节,SQL Server并不会检查是否需要改变。只有改变长度超过1000字节时,SQL Server才会检查是否需要移动为常规列。

看前 面的例子,我们修改行的长度:

UPDATE bigrows
SET a = replicate('a'1600);

 行总长度减为7900,减少500字节,此时,你仍然可以看到两个row-flow 页

代码
--name    partition_id    pnum    rows    au_id    page_type_desc    pages 
--
bigrows    72057594043432960    1    1    72057594045005824    IN_ROW_DATA    2
--
bigrows    72057594043432960    1    1    72057594045071360    ROW_OVERFLOW_DATA    2

再执行一次:

UPDATE bigrows
SET a = 'aaaaa'

邀月工作室 

 注意:行溢出数据存储仅仅应用于可变长度列,即不超过8000字节的常规列,而且,存储可变长度列在行溢出页,你还必须下列条件:

1、所有固定长度列,包括开销字节(overhaed bytes,还有24字节指针指向数据溢出页),总和加起来不超过8060字节。

2、可变长度列的实际数据必须大于24字节。

3、 该列必须不是聚集索引键的一部分。

如果,单个列长度超过8000字节,则应使用LOB(text,image or ntext) 列或Max数据类型。

本文简单介绍了限制长度的Large Object Data存储形式,下一节将介绍不限长度的Large Object Data存储形式。

posted @ 2010-05-03 12:05  邀月  阅读(1772)  评论(0编辑  收藏  举报