24-SQLServer数据页的分配和使用情况

一、总结

1、SQLServer中的数据库有的时候会有多个数据文件组或者多个数据文件的情况，该博客就是讨论当有多个数据文件时，表的数据会怎么存储，存储在哪些数据文件中。

2、首先SQLServer中的数据文件主要是用来存储表和索引的数据的。

3、SQLServer的最小存储单位是页，每页8KB，8个页组成一个区。

4、区分为混合区和统一区2类，表或索引的前8个数据页都在混合区中分配，当空间超过8个数据页时，就开始给其分配统一区。

5、新创建的空表不会对其分配区，当有数据插入时才会分配区，可以使用dbcc extentinfo命令来查看表被分配区的情况。

6、SQLServer的表数据会在多个数据文件上分布，官方说法：文件组对组内的所有文件都使用按比例填充策略。所以数据写入文件时，数据库引擎会根据文件中的可用空间量按一定比例将数据写入每个文件中，而不是将所以数据先写满第一个文件，再写下一个。比如当第一个文件当前的空间写满时，不会先扩展这个文件，而是去写下一个文件（或者扩展），直到所有的文件都写一遍之后，才会轮到扩展第一个文件。

7、对表做delete操作时，表分配的空间不会释放，但是数据文件的可用百分比会增加，下次再次插入数据时，会优先把这些空间写满。

8、对表做truncate操作时，会释放表占用的空间。

二、操作步骤

1、查看所有的文件

select * from run.sys.sysfiles

2、查看对象分配的区

dbcc extentinfo(run,T3)  

dbcc extentinfo(payment,person)

dbcc extentinfo(run,test,NonClu_index)    --查询索引对应的存储区

参数1：对应的库名

参数2：对应的表名

参数3：对应的索引名称

参数4：对应的分区number（如果表做分区的话）

字段含义：

　　file_id：数据库的数据文件的编号

　　page_id：在某个盘区中的第一个页面的页面号

　　pg_alloc：该盘区分配的页面的数量(大于等于1，小于等于8)

　　ext_size：盘区的大小，以页面为单位

　　object_id：数据库对象的ID

　　index_id：表示数据对象的类型

注：可以根据上面查出的file_id定位到具体的数据文件

3、查看某张表分配的页

dbcc ind(run,1525580473,1)   

dbcc ind(payment,person,-1)

参数1：数据库名或数据库ID

参数2：对象名或对象id（表名）

参数3：非聚集索引ID或1,0,1，2 partition_number(可以根据上一个查询得出)

  0: 只显示对象的in-row data页和 in-row IAM 页。
  1: 显示对象的全部页, 包含IAM 页, in-row数据页, LOB 数据页row-overflow 数据页 . 如果请求的对象含有聚集所以则索引页也包括。
 -1: 显示全部IAM页,数据页, 索引页 也包括 LOB 和row-overflow 数据页。
 -2: 显示全部IAM页。

 注：根据上面查出的分区数量449个，前面有8个分区是只分配了1个page，所以总的page数是441*8+8*1=3536

        下面查看的page页是3537，但是前面有一个页4426的IAMPID是NULL(该页是IAM页，用于检索堆中的数据行)，所以除去这一页，结果是相同的。

　　 索引分配映射页(IAM):Index Allocation Map

结果含义：

　　PageFID：数据文件的ID

　　PagePID：数据页的ID

　　IAMFID：IAM页所在的文件ID

　　IAMPID：IAM页所在的PageID

　　ObjectID：对象ID

　　IndexID：索引ID

　　PartitionNumber：表或索引的分区号

　　PartitionID：数据库范围内唯一的表或索引的分区ID

　　iam_chain_type：页面所属的分配单元类型：IN_ROW_DATA、ROW_OVERFLOW_DATA、LOB_DATA

　　PageType：页类型：1=data page，2=index page，3=LOB_MIXED_PAGE，4=LOB_TREE_PAGE，10=IAM page

　　IndexLevel：索引的级别，0表示叶级

　　NextPageFID：当前级别的后一页的文件ID

　　NextPagePID：当前级别的后一页的PageID

　　PrevPageFID：当前级别的前一页的文件ID

　　PrevPagePID：当前级别的前一页的PageID

4、查询一个页面的内部存储信息（必须开启跟踪3604的标志才能显示结果）

   dbcc page(run,1,22132)

  dbcc ind(payment,person,-1)   ----显示整页数据

参数：

　第一个参数：数据库名或数据库ID

　第二个参数：指定数据文件的号filenum

　 第三个参数：指定的page页号

    第四个参数（可选）：

　　0：默认值，输出buffer header 和page header信息

       1：输出buffer header，page header，分别输出每行信息，行偏移量

　　2：输出buffer  header，page header，整页数据，行偏移量

　　3：输出buffer  header，page header，分别输出每行信息，行偏移量；分别列出每列的值

 

 

 

 

参考网址：

http://blog.sina.com.cn/s/blog_6556b2cc0101fibv.html

https://www.cnblogs.com/gaiyang/archive/2011/07/25/2115909.html