欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-21 08:33:00
前言 在sql server中,如何找到一张表或某个索引拥有那些页面(page)呢? 有时候,我们在分析和研究(例如,死锁分析)的时候还真有这样的需求,那么如何做呢? sql ser...

前言

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

  •  @databaseid:    数据库的id,可以用db_id()函数获取某个数据库或当前数据库的id
  •  @tableid:     表的id。 我们可以使用object_id()函数通过表名获取表id。 这是一个可选参数,如果将其作为null传递,则返回与数据库中所有表的关联页面,当它为null时,将忽略接下来的两个参数(即@indexid和@partionid)值
  • @indexid:     索引的索引id。 我们可以使用sys.indexes目录视图来获取索引id。 它是一个可选参数,如果将其作为null传递,则返回所有索引关联的页面。
  • @partitionid: 分区的id,它是一个可选参数,如果将其作为null传递,则返回与所有分区关联的页面.
  • @mode:       这是必填参数,有“limited”或“detailed”两个参数。 “limited”返回的信息较少。 “detailed”会返回详细/更多信息。显然,“detailed”模式会占用更多资源。

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

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

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

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

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

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

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

简单了解了上面知识点后,我们在使用这个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;

参考资料: 

总结

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