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

前言

在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比较少见,暂时有些资料没有补充完善


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/

总结

以上就是我在处理客户端真实IP的方法,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。

(0)

相关推荐

  • SQL Server Page结构深入分析

    SQL Server存储数据的基本单元是Page,每一个Page的大小是8KB,数据文件是由Page构成的.在同一个数据库上,每一个Page都有一个唯一的资源标识,标识符由三部分组成:db_id,file_id,page_id,例如,15:1:8733,15是数据库的ID,1是数据文件的ID,8733是Page的编号,Page的编号从0依次递增.8个连续的Page组成一个区(Extent),数据文件中已分配(Allocated)的空间被分割成区的整数倍.一次磁盘IO操作作用于Page级别,而空间

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

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

  • Sql Server中清空所有数据表中的记录

    Sql Server中清空所有数据表中的记录 清空所有数据表中的记录: 复制代码 代码如下: exec sp_msforeachtable  @Command1 ='truncate table ?' 删除所有数据表: 复制代码 代码如下: exec sp_msforeachtable 'delete   N''?''' 清空SQL Server数据库中所有表数据的方法(有约束的情况) 其实删除数据库中数据的方法并不复杂,为什么我还要多此一举呢,一是我这里介绍的是删除数据库的所有数据,因为数据之

  • SQL Server 2008中的数据表压缩功能详细介绍

    SQL Server 2005 SP2为我们带来了vardecimal功能,当时针对decimail和numeric数据类型推出了新的存储格式--vardecimal.vardecimal存储格式允许 decimal和numeric数据类型的存储作为一个可变长度列. 这项功能使得原来定长的decimal数据在数据文件中以可变长的格式存储,据称这项功能可以为典型的数据仓库节省30%的空间,而SQL Server 2008在这一基础上又进一步增强了数据压缩功能.SQL Server 2008现在支持

  • SQL Server创建数据库和数据表的相关约束实现方法

    本文分析了SQL Server创建数据库和数据表的相关约束实现方法.分享给大家供大家参考,具体如下: 创建约束语法如下: CREATE DATABASE [test] ON (NAME=N'test',FILENAME=N'd:\SQL2kt_Data\test.mdf',SIZE=3mb,MAXSIZE=UNLIMITED,FILEGROWTH=1MB) LOG ON (NAME=N'test_log',FILENAME=N'd:\SQL2kt_Data\test_log.ldf',SIZE=

  • C++通过自定义函数找出一个整数数组中第二大数的方法

    本文实例讲述了C++通过自定义函数找出一个整数数组中第二大数的方法.分享给大家供大家参考.具体实现方法如下: const int MINNUMBER = -32767 ; //2字节的Int 0x8000-1, //4字节的Int 0x80000000-1 -2147483647 int find_sec_max( int data[] , int count) { int maxnumber = data[0] ; int sec_max = MINNUMBER ; for ( int i =

  • sql server建库、建表、建约束技巧

    下面给大家分享下sql server建库.建表.建约束技巧,下文介绍有文字有代码. --创建School数据库之前:首先判断数据库是否存在,若存在则删除后再创建,若不存在则创建-- --exists关键字:括号里边能查询到数据则返回'true' 否则返回'false' if exists(select * from sysdatabases where name = 'School') --exists返回'true'则执行删除数据库操作-- drop database School --exi

  • python找出一个列表中相同元素的多个索引实例

    定义:X=[1,2,3,1,4] 任务:找出元素为1的索引 Solution: # 如果直接用X.index(1),只能得到0这一个索引,而我们需要所有索引. l = len(X) zip_list = zip(*(range(l),X)) id1 = [z[0] for i,z in enumerate(zip_list) if z[1]==1] # 或者更简单的 id1 = [i for i,x in enumerate(X) if x==1] 以上这篇python找出一个列表中相同元素的多

  • SQL Server实现用触发器捕获DML操作的会话信息【实例】

    需求背景 上周遇到了这样一个需求,维护人员发现一个表的数据经常被修改,由于历史原因:文档缺少:以及维护人员的经常变更,导致他们对系统也业务也不完全熟悉,他们也不完全清楚哪些系统和应用程序会对这个表的数据进行操作.现在他们想找出有哪些服务器,哪些应用程序会对这个表进行INSERT.UPDATE操作.那么问题来了,怎么去解决这个问题呢? 解决方案 由于数据库版本是标准版,我们选择了使用触发器来捕获进行DML操作的会话的相关信息,例如,Host_Name.Program_Name等 ,选择触发器是因为

  • SQL Server 聚焦存储过程性能优化、数据压缩和页压缩提高IO性能方法(一)

    前言 关于SQL Server基础系列尚未结束,还剩下最后一点内容未写,后面会继续.有园友询问我什么时候开始写SQL Server性能系列,估计还得等一段时间,最近工作也比较忙,但是会陆陆续续的更新SQL Server性能系列,本篇作为性能系列的基本引导,让大家尝尝鲜.在涉及到SQL Server性能优化时,我看到的有些文章就是一上来列出SQL Server的性能优化条例,根本没有弄清楚为什么这么做,当然也有可能是自己弄懂了,只是作为备忘录,但是到了我这里,我会遵循不仅仅是备忘录,还要让各位园友

  • SQL Server简单模式下误删除堆表记录恢复方法(绕过页眉校验)

    首先,我需要强调下,这篇主旨是揭示堆表的删除记录找回的原理,我所考虑的方面并不适用于每个人的每种情况,望大家见谅~ 很多朋友认为数据库在简单模式下,堆表误删除一条记录,是无法找回的,因为没有日志记录.其实不然,某种意义上是可以找回的,因为堆表在删除记录时,只更改了行偏移,实际数据没有被物理删除,所以利用这点,测试了下恢复数据,果然成功了,但是还有点问题没有研究出结果:如果不关闭页面校验,除了更改偏移量,删除数据时还需要更改页眉,这点还没时间去琢磨,所以恢复数据时还要能推断出页眉的16进制对应关系

随机推荐