金沙澳门官网网址-澳门金沙官网登录

金沙澳门官网网址拥有大量的原创内容,澳门金沙官网登录提供最权威、快速、全面、专业的报道,所以金沙澳门官网网址注册送彩金就相当于直接送现金了,享受最尊贵的贵宾服务。

则返回与数据库中所有表的关联页面,SQL Server把

2020-03-15 17:13栏目:金沙澳门官网
TAG:

总结

SQL Server会给我们包含4个部分的输出。第1部分是BUFFER,里面是一些内存分配信息,对此我们没多少兴趣。下一部分是固定96 bytes大小的页头(page header),页头(page header)会类似如下显示:

@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”模式会占用更多资源。

在页头我们看到当前页面还有7644 bytes可以用,我们一起来验证下。

对于大表而言,如果选择“DETAILED”参数,则消耗的资源和时间非常长,这个时候非常有必要选择“LIMITED”参数。

 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 2012提供了一个无文档的DMF可以实现我们的需求,sys.dm_db_database_page_allocations有下面几个参数:

剩下的 36 bytes (8192-96-8060)保留给槽数组(Slot array)或者任何转发行返回指针(forwarding row back pointer)(每条10 bytes)。这就意味一个页不一定就能保存18(36/2)条记录。槽数组(Slot array)根据你的记录数从下往上增长。如果记录长度小,页里就可以存储更多的记录,偏移表也会自下而上占用更多的空间。

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

为了更好的理解sys.dm_db_database_page_allocations输出的数据,其实我们有必要简单了解、回顾一下SQL Server中数据存储的相关知识点。 这就涉及到页和区的概念了。SQL Server中数据存储的基本单位是页,磁盘I/O操作在页级执行。也就是说,SQL Server读取或写入数据的最小单位就是以8 KB为单位的页。

页的最后一部分是行偏移数组表,我们可以用参数为1的DBCC PAGE命令来,在输出信息的底部获得。

另外,关于sys.dm_db_database_page_allocations的输出字段信息如下所示:

 

参考资料:

图片 2

字段 中文字段描述 英文描述 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

  • Page @0x08F84000            同BUFFER中的bpage地址
  • m_pageId = (1:79)              数据页号     
  • 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                         页的校验位或者被由数据库页面保护形式决定分页保护位取代

区是管理空间的基本单位。 一个区是8个物理上连续的页的集合,所有页都存储在区中。区用来有效地管理页所有页都存储在区中。 SQL Server中有两种类型的区:

这个行偏移表,应该从下往上读。每条槽条目是一个2 bytes长的指针指向页里槽偏移量。这里我们插入了2条记录,所以表里有2个槽条目。第1条记录指向第96 bytes,刚好在页头后。这个行偏移表可以帮助我们管理页面的记录。在页里的行偏移表里,每条记录需要2 bytes的大小来存储。于此类似,在堆表上建立的非聚集索引,每个非聚集索引行里都包含一个物理指针映射回堆表里的行记录。这个物理指针是[文件号:页号:槽号](file:page:solt)的结构,因此在读取页的时候,可以找到堆表里的对应行,再通过行偏移表里槽号里的偏移量,就可以在页里读取到对应的行记录。如果我们要修改页中间的记录,我们并不一定需要重组整个页,我们只要修改偏移表里偏移量即可。

统一区: 由单个对象所有。区中的所有8页只能有一个对象使用。 混合区: 最多可由8个对象共享。区中8页中每一页都可由不同的对象所有。但是一页总是只能属于一个对象。

 

前言

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

简单了解了上面知识点后,我们在使用这个DMF找出表或索引相关的页面,基本上可以读懂这些输出信息了。

SQL Server会给我们如下的输出结果:
图片 3

SQL Server中页也有很多类型,具体参考下面表格。

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

注意事项:有些Page Type比较少见,暂时有些资料没有补充完善

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

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 FILEHEADER_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 INDEXDEFRAG) 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

页头相关字段的含义:

SQL Server在输出信息的底部,给我们如下的信息:

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 2000,第四个参数是可选的,该参数用于指定一个分区号.如果不给定值或者给定0, 则显示全部分区数据。
和DBCC PAGE不同的是, SQL Server运行DBCC IND不需要开启3604跟踪标志.

图片 4

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

     7 * 2 = 每条记录的系统行开销 * 记录数

  • 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 语句之后的大容量操作所修改的区的信息

图片 5

版权声明:本文由金沙澳门官网网址发布于金沙澳门官网,转载请注明出处:则返回与数据库中所有表的关联页面,SQL Server把