略知皮毛数据页构造,Server怎么寻找三个表包括的页音信

前言

作者们都很精晓SQL Server用8KB 的页来积累数据,并且在SQL Server里磁盘 I/O
操作在页级试行。也便是说,SQL Server
读取或写入全数数据页。页有区别的品种,像数据页,GAM,SGAM等。在此小说里,让大家一齐来明白下多少页构造。

在SQL Server中,怎么样找到一张表或有些索引具备那么些页面吗?
不经常候,大家在剖判和研商的时候还真犹如此的须求,那么如何是好呢? SQL
Server
二〇一二提供了三个无文书档案的DMF能够兑现大家的急需,sys.dm_db_database_page_allocations有下面多少个参数:

SQL Server把数量记录存在数据页(Data
Page)里。数据记录是堆表里、集中索引里叶子节点的行。

@DatabaseId:
数据库的ID,能够用DB_ID(卡塔尔国函数获取某些数据库或当前数据库的ID @TableId:
表的ID。 我们得以使用OBJECT_ID函数通过表名获取表ID。
那是叁个可选参数,要是将其作为NULL传递,则赶回与数据库中全数表的涉嫌页面,当它为NULL时,将忽视接下去的八个参数值
@IndexId: 索引的索引ID。 大家得以行使sys.indexes目录视图来获得索引ID。
它是三个可选参数,要是将其视作NULL传递,则赶回全体索引关联的页面。
@PartitionId:
分区的ID,它是三个可选参数,假使将其作为NULL传递,则赶回与持有分区关联的页面.
@Mode: 那是必填参数,有“LIMITED”或“DETAILED”四个参数。
“LIMITED”再次来到的音信超级少。
“DETAILED”会回去详细/更加多消息。显著,“DETAILED”格局会占用越多能源。

数码页由3个部分构成。页头(标头),数据区(数据行和可用空间)及行偏移数组。

对此大表来讲,即使接纳“DETAILED”参数,则消耗的财富和岁月非常的短,当时特别有须要选取“LIMITED”参数。

图片 1

为了越来越好的精通sys.dm_db_database_page_allocations输出的数据,其实大家有至关重大轻巧驾驭、回想一下SQL
Server中多少存款和储蓄的相干知识点。 那就事关到页和区的定义了。SQL
Server中数量存款和储蓄的着力单位是页,磁盘I/O操作在页级实践。也正是说,SQL
Server读取或写入数据的蝇头单位正是以8 KB为单位的页。

在大家谈谈在SQL
Server里,数据页内部构造具体是如何以前,我们来成立一个表并插入一些笔录。

区是治本空间的宗旨单位。
三个区是8个大要上连年的页的群集,全体页都存款和储蓄在区中。区用来有效地管理页全部页都存款和储蓄在区中。
SQL Server中有二种档期的顺序的区:

 

统一区: 由单个对象具有。区中的持有8页只可以有三个对象使用。 混合区:
最多可由8个指标分享。区中8页中每一页都可由差异的目的具备。不过一页总是不能不归属贰个对象。

 1 USE [InternalStorageFormat] 2 GO 3  4 IF EXISTS ( SELECT * 5       FROM  sysobjects 6       WHERE  id = OBJECT_ID(N'[dbo].[Customers]') 7           AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) 8   DROP TABLE dbo.Customers 9 10 CREATE TABLE Customers11 (12  FirstName CHAR(50) NOT NULL,13  LastName CHAR(50) NOT NULL,14  Address CHAR(100) NOT NULL,15  ZipCode CHAR(5) NOT NULL,16  Rating INT NOT NULL,17  ModifiedDate DATETIME NOT NULL,18 )19 GO20 21 22 INSERT INTO dbo.Customers23     ( FirstName ,24      LastName ,25      Address ,26      ZipCode ,27      Rating ,28      ModifiedDate29     )30 VALUES ( 'Woody' , -- FirstName - char(50)31      'Tu' , -- LastName - char(50)32      'ZUOQIAO YOUXI TOWN LINHAI CITY' , -- Address - char(50)33      '0000' , -- ZipCode - char(5)34      1 , -- Rating - int35      '2015-05-07 10:09:51' -- ModifiedDate - datetime36     )37     go 2

SQL Server中页也许有那多少个门类,具体参谋下边表格。

现行反革命我们要找寻SQL
Server给那个表分配的页有如何,那几个将在接受非文书档案的下令DBCC IND。
它的语法如下:

注意事项:稍加Page Type相当少见,暂且有个别资料还没补偿完备

DBCC IND 命令用于查询叁个囤积对象的中间存款和储蓄布局新闻,该命令有4个参数,
前3个参数必须内定。语法如下:
DBCC IND ( { ‘dbname’ | dbid }, { ‘objname’ | objid },{ nonclustered
indid | 1 | 0 | -1 | -2 } [, partition_number] )
第二个参数是数据库名或数据库ID。
其次个参数是数据库中的对象名或对象ID,对象足以是表恐怕索引视图。
其三个参数是两个非集中索引ID大概 1, 0, 1, or 2. 值的含义:
 0: 只突显对象的in-row data页和 in-row IAM 页。
 1: 展现对象的所有的事页, 满含IAM 页, in-row数据页, LOB 数据页row-overflow
数据页 . 假诺须要的靶子蕴含集中所以则索引页也囊括。
 -1: 展现全数IAM页,数据页, 索引页 也囊括 LOB 和row-overflow 数据页。
 -2: 突显整个IAM页。
 Nonclustered index ID:呈现索引的整套 IAM页, data页和索引页,满含LOB和
row-overflow数据页。
为了协作sql server
二〇〇二,第四个参数是可选的,该参数用于钦赐三个分区号.如若不给定值或然给定0,
则展现全部分区数据。
和DBCC PAGE分歧的是, SQL Server运营DBCC IND无需敞开3604追踪标记.

PAGE_TYPE 页类型 页类型码 描述 1 Data Page DATA_PAGE 数据页(Data
Page卡塔尔国用来存放数据 l堆中的数据页 l聚焦索引中“叶子“页 2 Index Page
INDEX_PAGE 索引页,聚焦索引的非叶子节点和非聚集索引的持有索引记录 3 Text
Mixed Page TEXT_MIX_PAGE 八个文本页面,在那之中蕴藏小块的LOB值以至text
tree的在那之中,这几个能够在目录或堆的相像分区中的LOB值之间分享。 A text page
that holds small chunks of LOB values plus internal parts of text tree.
These can be shared between LOB values in the same partition of an index
or heap. 4 Text Tree Page TEXT_TREE_PAGE A text page that holds large
chunks of LOB values from a single column value 7 Sort Page
在排序操作时期囤积中间结果的页面 8 Global Allocation Map Page GAM_PAGE
GAM在数据文件中第八个页上,文件和页的编号为,它用bit位来标记相应的区是不是已经被分配。它基本上能标记约64000个区,也正是4G的半空中,就算数量空间超越4G,那么数据库会用别的多少个GAM页来标识下一个4G上空
Bit=1:标志当前的区是悠闲的,能够用来分配
Bit=0:标志当前的区已经被数据接受了 9 Shared Global Allocation Map Page
SGAM_PAGE
SGAM在数据文件的第三个页上,文件和页编号为,它的结构和GAM是一模一样的,不一致在于Bit位的意义区别:
Bit=1:区是混合区,且区内至稀少叁个页是足以被用来分配的
Bit=0:区是统一区,大概是混合区不过区内具备的页都以在被应用的 10 Index
Allocation Map Page IAM_PAGE 表或索引所运用的区的音讯。 11 Page Free
Space Page PFS_PAGE 存款和储蓄本数据文件里具备页分配和页的可用空间的音讯 13
Boot Page BOOT_PAGE
蕴涵关于数据库的有关新闻。数据库中有且唯有叁个。它座落文件1中的第9页。
15 File header page FILEHEADEGTC4Lusso_PAGE
文件标题页。富含关于文件的新闻。每种文件三个,文件的第0页。 16
Differential Changed Map DIFF_MAP_PAGE 自最终一条BACKUP
DATABASE语句之后改良的区的音讯 17 Bulk Changed Map 自最后一条BACKUP
LOG语句之后的大体量操作锁改善的区的新闻 18 a page that’s be deallocated
byduring a repair operation 19 the temporary page that(orDBCC
INDEXDEFRAGState of Qatar uses when working on an index 20 a page pre-allocated as
part of a bulk load operation, which will eventually be formatted as a
‘real’ page

我们来进行下列的下令:

另外,关于sys.dm_db_database_page_allocations的出口字段新闻如下所示:

1 DBCC IND('InternalStorageFormat','Customers',-1)

字段 粤语字段描述 菲律宾语描述 database_id 数据库ID ID of the database
object_id 表或视图对象的ID Object ID For the table or view index_id
索引ID ID for the index partition_id 索引的分区号 Partition number for
the index rowset_id 索引的Partition ID Partition ID for the index
allocation_unit_id 分配单元的ID ID of the allocation unit
allocation_unit_type 分配单元的种类 Type of allocation unit
allocation_unit_type_desc 分配单元的类型描述 Description for the
allocation unit data_clone_id clone_state clone_state_desc
extent_file_id 区的文件ID File ID of the extend extent_page_id
区的文本ID Page ID for the extend allocated_page_iam_file_id
与页面关联的目录分配映射页面包车型地铁文本ID File ID for the index allocation
map page associate to the page allocated_page_iam_page_id
与页面关联的目录分配映射页面包车型地铁页面ID Page ID for the index allocation
map page associated to the page allocated_page_file_id 分配页面包车型大巴File
ID File ID of the allocated page allocated_page_page_id
分配页面包车型地铁Page ID Page IDfor the allocated page is_allocated
该页是或不是被分配出去了 Indicates whether a page is allocated is_iam_page
是否为IAM页 Indicates whether a page is the index allocation page
is_mixed_page_allocation 是或不是分配的滥竽充数页面 Indicates whether a page
is allocated page_free_space_percent 页面的空余比例 Percentage of
space free on the page page_type 页面包车型地铁类型 Description of the page
type page_type_desc 页面包车型地铁品种描述 page_level 页的层数
next_page_file_id 下一个页的Fiel ID File ID for the next page
next_page_page_id 下二个页的Page ID Page ID for the next page
previous_page_file_id 前多少个页的File ID File ID for the previous page
previous_page_page_id 前一个页的Page ID Page ID for the previous Page
is_page_compressed 页是还是不是减弱 Indicates whether the page is compressed
has_ghost_records 是还是不是存虚影记录记录 Indicates whether the page have
ghost records

SQL Server会给我们如下的出口结果:
图片 2

简单的说询问了地点知识点后,大家在选拔这几个DMF寻找表或索引相关的页面,基本上可以读懂那些输出音讯了。

可以看见有2条记下,一条记下为页面类型(PageType)为10的页和一条记下为页面类型(PageType)为1的页。页面类型(PageType)10是IAM页,页面类型(PageType)1是数据页,它的页ID是79.

USE AdventureWorks2014GOSELECT DB_NAME(pa.database_id) AS [database_name] , OBJECT_NAME(pa.object_id) AS [table_name] , id.name AS [index_name] , pa.partition_id AS [partition_id], pa.is_allocated AS [is_allocated], pa.allocated_page_file_id AS [file_id] , pa.allocated_page_page_id AS [page_id] , pa.page_type_desc , pa.page_level , pa.previous_page_page_id AS [previous_page_id] , pa.next_page_page_id AS [next_page_id] , pa.is_mixed_page_allocation AS [is_mixed_page_allocation], pa.is_iam_page AS [is_iam_page], pa.allocation_unit_id AS [allocation_unit_id], pa.has_ghost_records AS [has_ghost_records]FROM sys.dm_db_database_page_allocations(DB_ID('AdventureWorks2014'), OBJECT_ID('TestDeadLock'), NULL, NULL, 'DETAILED') pa LEFT OUTER JOIN sys.indexes id ON id.object_id = pa.object_id AND id.index_id = pa.index_idORDER BY page_level DESC , is_allocated DESC , previous_page_page_id;

至于数据库页类型如下所示:

参照他事他说加以考察资料:

  • 1 Data page 堆表和集中索引的卡片节点数据
  • 2 Index page 聚集索引的非叶子节点和非聚焦索引的有着索引记录

  • 3 Text mixed page A text page that holds small chunks of LOB
    values plus internal parts of text tree. These can be shared between
    LOB values in the same partition of an index or heap.

  • 4 Text tree page A text page that holds large chunks of LOB
    values from a single column value.

  • 7 Sort page 排序时所用到的一时页,排序中间操作存款和储蓄数据用的。

  • 8 GAM page 全局分配映射(Global Allocation Map,GAM)页面
    这一个页面记录了什么区已经被分配并用作何种用项。

  • 9 SGAM page 分享全局分配映射(Shared Global Allocation
    Map,GAM)页面
    那一个页面记录了哪些区当前被当做混合类型的区,何况那几个区需包括起码贰个未利用的页面。

  • 10 IAM page  有关每种分配单元中表或索引所利用的区的新闻

  • 11 PFS page  有关页分配和页的可用空间的音信

  • 13 boot page 记录了关于数据库的新闻,仅存于每种数据库的第9页

  • 15 file header
    page 
    笔录了有关数据库文件的消息,存于各个数据库文件的第0页

  • 16 DCM page 记录自从上次全备以来的数码变动的页面,以备差别备份

  • 17 BCM page 有关各种分配单元中自最后一条 BACKUP LOG
    语句之后的大体积操作所改良的区的音讯

总结

不久前大家来拜见79号项目为1的多寡页里寄放的多寡,这一个将要用到DBCC
PAGE命令,它的语法如下:

以上正是那篇文章的全体内容了,希望本文的剧情对大家的求学或许干活有所一定的参阅学习价值,感谢大家对剧本之家的支持。

dbcc page 命令读取数据页构造的吩咐DBCC Page。
该命令为非文书档案化的命令,具体如下:
  DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])
  具体参数描述如下:
  dbid 包含页面包车型客车数据库ID
  dbname 包括页面包车型客车数据库的名称
  filenum 包含页面包车型地铁文书编号
  pagenum 文件内的页面
  printopt 可选的出口选项;选取此中叁个值:
  0:暗中同意值,输出缓冲区的标题和页面标题
  1:输出缓冲区的标题、页面标题(分别出口每一行卡塔尔,以至行偏移量表
  2:输出缓冲区的题目、页面题目(全体出口页面State of Qatar,以致行偏移量表
  3:输出缓冲区的标题、页面标题(分别出口每一行卡塔尔,以至行偏移量表;每一行
  后跟分别列出的它的列值
  要想看见这么些输出的结果,还亟需安装DBCC TRA首席营业官N(3604State of Qatar。

大家来实行下列的指令:

1 DBCC TRACEON(3604)2 DBCC PAGE(InternalStorageFormat,1,79,3)3 GO  

SQL
Server会给大家包蕴4个部分的输出。第1有的是BUFFE中华V,里面是有些内部存款和储蓄器分配消息,对此我们十分的少兴趣。下一部分是稳固96
bytes大小的页头(page header),页头(page header)会周围如下展现:

图片 3

页头相关字段的含义:

  • Page @0x08F84000            同BUFFER中的bpage地址
  • m_pageId = (1:79State of Qatar              数据页号     
  • m_headerVersion = 1         头文件版本号,一直为1          
  • m_type = 1                          页面类型,1为多少页面
  • m_typeFlagBits = 0x4         数据页和索引页为4,别的页为0        
  • m_level = 0                         该页在索引页(B树)中的级数
  • m_flagBits = 0x8000          页面标记
  • m_objId (AllocUnitId.idObj) = 46                       同Metadata:
    ObjectId             
  • m_indexId (AllocUnitId.idInd) = 256                  同Metadata:
    IndexId
  • Metadata: AllocUnitId =
    72057594040942592  存款和储蓄单元的ID,sys.allocation_units.allocation_unit_id                              
  • Metadata: PartitionId =
    72057594039304192   数据页所在的分区号,sys.partitions.partition_id                             
  • Metadata: IndexId = 0                                      
     页面包车型地铁索引号,sys.objects.object_id&sys.indexes.index_id
  • Metadata: ObjectId = 277576027                    
     该页面所属的目的的id,sys.objects.object_id
  • m_prevPage =
    (0:0卡塔尔(قطر‎                  该数据页的前一页面;首要用在数据页、索引页和IAM页
  • m_nextPage = (0:0卡塔尔(قطر‎                
     该数据页的后一页面;主要用在数据页、索引页和IAM页
  • pminlen = 221                          定长数据所占的字节数
  • m_slotCnt = 2                           页面中的数据的行数
  • m_freeCnt = 7644                    页面中剩下的空中
  • m_freeData =
    544                    从第多个字节到结尾三个字节的空中字节数
  • m_reservedCnt = 0                   活动工作释放的字节数
  • m_lsn = (255:8406:2卡塔尔                日志记录号
  • m_xactReserved = 0              
      最新到场到m_reservedCnt领域的字节数
  • m_xdesId = (0:0)                    
      添加到m_reservedCnt的近年来的事情id
  • m_ghostRecCnt = 0                 幻影数据的行数
  • m_tornBits = 0                      
      页的校验位照旧被由数据库页面爱慕格局决定分页爱惜位代表

再来看下页面相关分配情状:

 图片 4

  • GAM (1:2卡塔尔 = ALLOCATED                                              
        在GAM页上的分配情形
  • SGAM (1:3卡塔尔 = ALLOCATED                                              
      在SGAM页上的分配情形
  • PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 
    50_PCT_FULL 在PFS页上的分配意况,该页为百分之五十满,                       
  • DIFF (1:6) = CHANGED
  • ML (1:7) = NOT MIN_LOGGED   

接下去正是用来存放实际数目标槽(slot),每条记下存放叁个槽(slot)里。0号槽在页里具有第1条数据,1号槽具备第2条数据,以此类推。通过上面包车型大巴图形,你能够阅览我们记录大小是224
bytes,217 bytes(50+50+100+5+4+8) 的定长和7 bytes 的种类行成本。

图片 5

页的最终一片段是行偏移数组表,大家得以用参数为1的DBCC
PAGE命令来,在输出音讯的最底层取得。

施行如下的一声令下:

1 DBCC TRACEON(3604)2 DBCC PAGE(InternalStorageFormat,1,79,3)3 GO  

SQL Server在输出音讯的底层,给我们如下的音信:

图片 6

这些行偏移表,应该从下往上读。每条槽条款是叁个2
bytes长的指针指向页里槽偏移量。这里我们插入了2条记下,所以表里有2个槽条目款项。第1条记下指向第96
bytes,正巧在页头后。那么些行偏移表能够帮忙我们管理页面包车型客车笔录。在页里的行偏移表里,每条记下须要2
bytes的高低来囤积。于此相通,在堆表上创制的非集中索引,每一个非集中索引行里都包蕴贰个物理指针映射回堆表里的行记录。这么些物理指针是[文件号:页号:槽号](file:page:solt)的构造,因而在读取页的时候,能够找到堆表里的对应行,再经过行偏移表里槽号里的偏移量,即可在页里读取到相应的行记录。如若大家要校正页中间的记录,大家并不一定必要组合全体页,大家只要校勘偏移表里偏移量就可以。

图片 7

在页头我们看来日前页面还恐怕有7644 bytes能够用,我们一同来注解下。

(8 * 1024) – 96 – (217 * 2)-(7 * 2)-(2 * 2)=7644 bytes

8 * 1024 = 页的总大小,8K

         96 = 页头大小 96 bytes       

 217 * 2 = 每条记下的总参谋长 * 记录数

     7 * 2 = 每条记下的种类行成本 * 记录数

     2 * 2 = 行偏移表里每槽占用字节数 * 记录数

现行反革命大家曾经知晓了页的布局,我们联合来小结下。

页是 8KB 的轻重,即 8192 bytes,固定 96
bytes的高低给页头使用,接下去是具体的数额以槽的点子存款和储蓄。数据记录的最大尺寸是
8060 bytes(富含 7
bytes的系统行花销),因此一条记下中您全部的最大字节数是 8053
bytes。下列的表创设语句会退步。

1 CREATE TABLE Maxsize(2 id     CHAR(8000) NOT NULL,3 id1    CHAR(54) NOT NULL4 )

图片 8

结余的 36 bytes (8192-96-8060)保留给槽数组(Slot
array)恐怕别的转载行重返指针(forwarding row back pointer)(每条10
bytes)。那就表示三个页不肯定就能够保留18(36/2卡塔尔国条记下。槽数组(Slot
array)依据你的记录数从下往上抓好。要是记录长度小,页里就足以积攒越多的笔录,偏移表也会自下而上占用越来越多的长空。

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注