《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_id )  AS  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 Ｓerver 在一定条件下存储可变长度列在行溢出页。决定因素在于行自身的长度，即只有行自身需要超过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字节的常规列，而且，存储可变长度列在行溢出页，你还必须下列条件：

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

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

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

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

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

转载于:https://www.cnblogs.com/downmoon/archive/2010/05/03/1726302.html