• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    SQL Server怎么找出一个表包含的页信息(Page)

    前言

    在SQL Server中,如何找到一张表或某个索引拥有那些页面(page)呢? 有时候,我们在分析和研究(例如,死锁分析)的时候还真有这样的需求,那么如何做呢? SQL Server 2012提供了一个无文档的DMF(sys.dm_db_database_page_allocations)可以实现我们的需求,sys.dm_db_database_page_allocations有下面几个参数: 

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

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

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

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

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

    PAGE_TYPE
    页类型
    页类型码
    描述
    1
    Data Page
    DATA_PAGE
    数据页(Data Page)用来存放数据
    l  堆中的数据页
    l  聚集索引中“叶子“页
    2
    Index Page
    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在数据文件中第三个页上,文件和页的编号为(1:2),它用bit位来标识相应的区(extents)是否已经被分配。它差不多能标识约64000个区(8k pages * 8 bits per byte),也就是4G的空间,如果数据空间超过4G,那么数据库会用另外一个GAM页来标识下一个4G空间
    Bit=1: 标识当前的区是空闲的,可以用来分配
    Bit=0: 标识当前的区已经被数据使用了
    9
    Shared Global Allocation Map Page
    SGAM_PAGE
    SGAM在数据文件的第四个页上,文件和页编号为(1:3),它的结构和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 by during a repair operation
    19
     
     
    the temporary page that  (or DBCC 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

    另外,关于sys.dm_db_database_page_allocations的输出字段信息如下所示(搜索相关资料结合自己的理解,如果错误,敬请指出):

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

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

    USE AdventureWorks2014
    GO
    SELECT 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_id
    ORDER BY page_level DESC ,
     is_allocated DESC ,
     previous_page_page_id;

    参考资料: 

    https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

    您可能感兴趣的文章:
    • SQL Server Page结构深入分析
    上一篇:sql server编写archive通用模板脚本实现自动分批删除数据
    下一篇:SQLyog连接MySQL8.0报2058错误的完美解决方法
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    SQL Server怎么找出一个表包含的页信息(Page) SQL,Server,怎么,找出,一个,