Shulou(Shulou.com)05/31 Report--

This article is about how SQL Server finds the page information contained in a table. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In SQL Server, how do you find out which pages (page) a table or index has? Sometimes we do have such a need in analysis and research (for example, deadlock analysis), so how do we do it? SQL Server 2012 provides an undocumented DMF (sys.dm_db_database_page_allocations) to implement our needs, and sys.dm_db_database_page_allocations has the following parameters:

@ DatabaseId: the ID of the database. You can use the DB_ID () function to get the ID of a database or the current database.

TableId: the ID of the table. We can use the OBJECT_ID () function to get the table ID by the table name. This is an optional parameter that, if passed as NULL, returns the page associated with all tables in the database, and when it is NULL, the next two parameter values (@ IndexId and @ PartionId) are ignored

IndexId: the index ID of the index. We can use the sys.indexes catalog view to get the index ID. It is an optional parameter that, if passed as a NULL, returns the pages associated with all indexes.

PartitionId: the ID of the partition, which is an optional parameter, and if passed as a NULL, returns the page associated with all partitions.

Mode: this is a required parameter and has two parameters: "LIMITED" or "DETAILED". "LIMITED" returns less information. "DETAILED" returns details / more information. Obviously, the "DETAILED" mode takes up more resources.

For large tables, if you select the "DETAILED" parameter, the resources and time consumed will be very long, so it is necessary to select the "LIMITED" parameter.

In order to better understand the data output from sys.dm_db_database_page_allocations, it is necessary for us to briefly understand and review the relevant knowledge points of data storage in SQL Server. This involves the concepts of Page and Extent. The basic unit of data storage in SQL Server is the page, and the disk Imax O operation is performed at the page level. That is, the smallest unit of data that SQL Server reads or writes is a page in 8 KB units.

Zone is the basic unit of managing space. An extent is a collection of eight physically contiguous pages (64KB), all of which are stored in the zone. An area is used to effectively manage pages. All pages are stored in the zone. There are two types of zones in SQL Server:

Unified area: owned by a single object. Only one object can be used for all 8 pages in the area.

Mixed zone: can be shared by up to 8 objects. Each of the 8 pages in the area can be owned by a different object. But a page can always belong to only one object.

There are also many types of pages in SQL Server, as shown in the table below.

Note: some Page Type are relatively rare, and some materials have not been supplemented and improved for the time being.

PAGE_TYPE

Page type

Page type code

Description

one

Data Page

DATA_PAGE

Data pages (Data Page) are used to store data

L data pages in the heap

L leaves page in clustered index

two

Index Page

INDEX_PAGE

Index pages (Index Page), non-leaf nodes of a clustered index and all index records of a nonclustered index

three

Text Mixed Page

TEXT_MIX_PAGE

A text page that contains small lob values and the interior of the text tree, which can be shared between LOB values in the same partition of the index or heap.

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.

four

Text Tree Page

TEXT_TREE_PAGE

A text page that holds large chunks of LOB values from a single column value

seven

Sort Page

A page that stores intermediate results during a sort operation

eight

Global Allocation Map Page

GAM_PAGE

GAM on the third page in the data file, the file and page are numbered (1:2), and it uses the bit bit to identify whether the corresponding extents has been allocated. It can almost identify about 64000 zones (8k pages * 8 bits per byte), that is, 4G space. If the data space exceeds 4G, then the database will use another GAM page to identify the next 4G space.

Bit=1: indicates that the current zone is free and can be used to allocate

Bit=0: indicates that the current zone has been used by the data

nine

Shared Global Allocation Map Page

SGAM_PAGE

SGAM on the fourth page of the data file, the file and page are numbered (1:3), and its structure is the same as GAM, except that the meaning of the Bit bit is different:

Bit=1: the zone is a mixed zone, and at least one page in the zone can be used for allocation

Bit=0: the zone is a unified zone, or a mixed zone, but all the pages in the zone are in use

ten

Index Allocation Map Page

IAM_PAGE

Information about the extent used by the table or index.

eleven

Page Free Space Page

PFS_PAGE

Stores information about the allocation and free space of all pages in this data file

thirteen

Boot Page

BOOT_PAGE

Contains information about the database. There is one and only one in the database. It is located on page 9 of file 1.

fifteen

File header page

FILEHEADER_PAGE

File title page. Contains information about the file. One for each file, page 0 of the file.

sixteen

Differential Changed Map

DIFF_MAP_PAGE

Information about extents that have changed since the last BACKUP DATABASE statement

seventeen

Bulk Changed Map

Bulk operation lock modified zone information since the last BACKUP LOG statement

eighteen

A page that's be deallocated by during a repair operation

nineteen

The temporary page that (or DBCC INDEXDEFRAG) uses when working on an index

twenty

A page pre-allocated as part of a bulk load operation, which will eventually be formatted as a 'real' page

In addition, the output field information about sys.dm_db_database_page_allocations is shown below (search related materials combined with your own understanding, if there is an error, please point it out):

Field

Chinese field description

English description

Database_id

Database ID

ID of the database

Object_id

ID of a table or view object

Object ID For the table or view

Index_id

Index ID

ID for the index

Partition_id

The partition number of the index

Partition number for the index

Rowset_id

Partition ID of the index

Partition ID for the index

Allocation_unit_id

ID of the allocation unit

ID of the allocation unit

Allocation_unit_type

Type of allocation unit

Type of allocation unit

Allocation_unit_type_desc

Type description of the allocation unit

Description for the allocation unit

Data_clone_id

?

Clone_state

?

Clone_state_desc

?

Extent_file_id

File ID for the zone

File ID of the extend

Extent_page_id

File ID for the zone

Page ID for the extend

Allocated_page_iam_file_id

The index allocation associated with the page maps the file ID of the page

File ID for the index allocation map page associate to the page

Allocated_page_iam_page_id

The index allocation associated with the page maps the page ID of the page

Page ID for the index allocation map page associated to the page

Allocated_page_file_id

Assign the File ID of the page

File ID of the allocated page

Allocated_page_page_id

Assign the Page ID of the page

Page ID for the allocated page

Is_allocated

Is the page assigned?

Indicates whether a page is allocated

Is_iam_page

Whether it is an IAM page

Indicates whether a page is the index allocation page

Is_mixed_page_allocation

Whether the mixed page is assigned or not

Indicates whether a page is allocated

Page_free_space_percent

The proportion of idle pages

Percentage of space free on the page

Page_type

Type of page (numeric description)

Description of the page type

Page_type_desc

Type description of the page

Page_level

The number of layers of the page

Next_page_file_id

The Fiel ID of the next page

File ID for the next page

Next_page_page_id

The Page ID of the next page

Page ID for the next page

Previous_page_file_id

File ID of the previous page

File ID for the previous page

Previous_page_page_id

Page ID of the previous page

Page ID for the previous Page

Is_page_compressed

Whether the page is compressed

Indicates whether the page is compressed

Has_ghost_records

Whether or not to keep false shadow records

Indicates whether the page have ghost records

After a brief understanding of the above knowledge points, we can basically read the output information by using this DMF to find out the pages related to the table or index.

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 Thank you for reading! This is the end of this article on "how to find out the page information contained in a table in SQL Server". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.