dmf nmf_SQL Server 2019 –新的DMF sys.dm_db_page_info

dmf nmf

Microsoft released preview of SQL Server 2019 recently in Ignite 2018. With every release of SQL Server is enriched with new dynamic management view and functions along with enhancements to existing features.

微软最近在Ignite 2018中发布了SQL Server 2019预览版。每个SQL Server版本都增加了新的动态管理视图和功能以及对现有功能的增强。

In this article, we will view the newly introduced dynamic management function (DMF) sys.dm_db_page_info and explore the different scenarios around it.

在本文中，我们将查看新引入的动态管理功能（DMF）sys.dm_db_page_info并探索围绕它的不同方案。

Overview of sys.dm_db_page_info

sys.dm_db_page_info概述

SQL Server 2019 provides new database management function ‘sys. dm_db_page_info’ to get information about the pages in SQL Server. Prior to SQL Server 2019, we use undocumented dynamic management function sys.dm_db_database_page_allocations and DBCC Page to get details information about the page in SQL Server for the tables, indexes.

SQL Server 2019提供了新的数据库管理功能'sys。 dm_db_page_info”以获取有关SQL Server中页面的信息。 在SQL Server 2019之前，我们使用未记录的动态管理功能sys.dm_db_database_page_allocations和DBCC页面来获取有关SQL Server中用于表，索引的页面的详细信息。

Dynamic Management function sys.dm_db_page_info DMF is introduced to replace DBCC Page function but this DMF returns only page header information while DBCC page shows complete details of the page. We will see this later in the demo section.

引入了动态管理功能sys.dm_db_page_info DMF来代替DBCC页面功能，但是该DMF仅返回页面标题信息，而DBCC页面显示了页面的完整详细信息。 我们将在后面的演示部分中看到这一点。

Before we move further, let us take a look of new dynamic management function (DMF) syntax and parameters.

在继续之前，让我们看一下新的动态管理功能（DMF）语法和参数。

Syntax for sys.dm_db_page_info

sys.dm_db_page_info的语法

sys.dm_db_page_info ( DatabaseId, FileId, PageId, Mode )

sys.dm_db_page_info（DatabaseId，FileId，PageId，Mode）

Below are the parameters to pass into sys.dm_db_page_info.

以下是传递给sys.dm_db_page_info的参数。

Argument	Description	Null allowed	Allowed Values
DatabaseId	It is unique ID of the database	No	DBID, or we can use DB_ID() for the current database
FileId	It is ID of the database file	No	DB file id
PageId	It is ID of the page we want to examine	No	PageID
Mode	It shows the level of detail in the output of the function	No	LIMITED– No information about description columns DETAILED– it gives information about detailed columns as well

论据	描述	允许为空	允许值
数据库编号	它是数据库的唯一ID	没有	DBID，或者我们可以对当前数据库使用DB_ID（）
FileId	它是数据库文件的ID	没有	数据库文件ID
PageId	这是我们要检查的页面的ID	没有	页面ID
模式	它显示了函数输出中的详细程度	没有	LIMITED –没有有关描述列的信息 详细 -它也提供有关详细列的信息

We require the VIEW DATABASE STATE permission in the database. We can provide permissions to run with below command.

我们需要数据库中的VIEW DATABASE STATE权限。 我们可以使用以下命令提供运行权限。

GRANT VIEW DATABASE STATE TO [login]

To move further, let us create the sample database and data.

为了更进一步，让我们创建示例数据库和数据。

Create Database SQL2019
Go
Use SQL2019
Go
Create table DemoSQL2019
( 
  ID int identity(1,1),
  Name varchar(10)
)
Go
Insert into DemoSQL2019 Values ('SqlShack')

In this article, we will take an overview of new DMF sys.dm_db_page_info along and compare it with the previously used DMF sys.dm_db_database_page_allocations.

在本文中，我们将对新的DMF sys.dm_db_page_info进行概述，并将其与以前使用的DMF sys.dm_db_database_page_allocations进行比较。

Now let us view the information about all the pages and extents allocated for the table using the DMF sys.dm_db_database_page_allocations. This is undocumented DMF introduced in SQL Server 2012.

现在，让我们使用DMF sys.dm_db_database_page_allocations查看有关为表分配的所有页面和扩展数据块的信息。 这是SQL Server 2012中引入的未记录的DMF。

Syntax for sys.dm_db_database_page_allocations:

sys.dm_db_database_page_allocations的 语法 ：

sys.dm_db_database_page_allocations

sys.dm_db_database_page_allocations

(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)

（@ DatabaseId，@ TableId，@ IndexId，@ PartionID，@ Mode）

Argument	Description	Null allowed	Allowed Values
DatabaseId	It is unique ID of the database.	No	DB ID
TableID	It is ID of the database file.	Yes	Table ID or Null
IndexID	It is ID of the page we want to examine	Yes	Index ID or Null
PartionID	We can pass PatitionID if we require.	Yes	partitionID or Null
Mode	It shows the level of detail in the output of the function	No	LIMITED– If we use this parameter, it does not show any information about description columns. DETAILED– it gives information about detailed columns as well. We cannot provide Null value in this parameter.

论据	描述	允许为空	允许值
数据库编号	它是数据库的唯一ID。	没有	数据库ID
表格ID	它是数据库文件的ID。	是	表ID或空
索引ID	这是我们要检查的页面的ID	是	索引ID或空
PartionID	如果需要，我们可以传递PatitionID。	是	partitionID或Null
模式	它显示了函数输出中的详细程度	没有	LIMITED –如果使用此参数，它将不显示有关描述列的任何信息。 详细 -它也提供有关详细列的信息。 我们无法在此参数中提供Null值。

Now run the below query to get information about allocated page id and page type description.

现在运行以下查询以获取有关分配的页面ID和页面类型描述的信息。

Select database_id,DB_name(database_id) as [Database],
 allocated_page_page_id , page_type_desc
from sys.dm_db_database_page_allocations(DB_ID(),null,null,null,'Detailed')

In the above query, we can directly pass the DB ID as well. We can get the DB ID from the sp_helpdb or sys.sysdatabases.

在上面的查询中，我们也可以直接传递数据库ID。 我们可以从sp_helpdb或sys.sysdatabases获取数据库ID。

Now we can view the information about any particular page from DBCC Page.

现在，我们可以从DBCC页面查看有关任何特定页面的信息。

Below is the syntax for DBCC Page:

以下是DBCC页面的语法：

dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Printout values can be as:

打印输出值可以是：

• 0 – Prints only page header related information
  0 –仅打印与页眉相关的信息
• 1 – Prints page header and page slot array dump with hex dump for each row
  1 –打印每行的页眉和页槽阵列转储以及十六进制转储
• 2 – Prints page header and whole page hex dump
  2 –打印页眉和整页十六进制转储
• 3 – Prints detailed information of per row along with page header
  3 –打印每行的详细信息以及页面标题

Note: We need to turn on trace flag 3604 for DBCC Page to show output in SQL Server Management Studio.

注意：我们需要为DBCC页面打开跟踪标志3604 ，以在SQL Server Management Studio中显示输出。

DBCC TRACEON(3604)
DBCC page (6,1,157,3) WITH TABLERESULTS

We can see that DBCC Page gives detailed information about the page.

我们可以看到DBCC页面提供了有关该页面的详细信息。

Now if we view the same thing from the newly introduced sys.dm_db_page_info. We will use the same page id as used before for a comparison purpose.

现在，如果我们从新引入的sys.dm_db_page_info中查看相同的内容。 我们将使用与以前相同的页面ID进行比较。

We can see here that sys.dm_db_page_info gives one row with all the page information for a given parameter in the detailed mode as specified by us.

我们可以在这里看到sys.dm_db_page_info在我们指定的详细模式下为一行给出了给定参数的所有页面信息。

Note here, we are using the mode as limited so we can see there are some columns showing null values (highlighted by blue arrow)

请注意，此处我们使用的是受限模式，因此我们可以看到有些列显示空值（蓝色箭头突出显示）

Select * from sys.dm_db_page_info(6,1,157,’limited’)

Important columns in Sys.dm_db_page_info are:

Sys.dm_db_page_info中的重要列是：

Column	Description
database_id	Database ID
page_type_desc	Page type description such as data page, index page, IAM page
page_level	It shows the Level of the page in the index. For a leaf level, its value is 0
slot_count	It shows the total slot counts. It is number of rows for a data page,
ghost_rec_count	A number of ghost records that are marked for deletion.
is_iam_pg	If that particular page is IAM page, its value is 1
is_mixed_ext	This indicates whether the extent is mixed extent or not
free_bytes	Free bytes on the page
Lsn	Log sequence number
diff_map_page_id	It shows the Page ID of the corresponding differential bitmap page
header_version	Page header version

柱	描述
database_id	数据库ID
page_type_desc	页面类型描述，例如数据页面，索引页面，IAM页面
page_level	它显示索引中页面的级别。 对于叶级别，其值为0
slot_count	它显示了插槽总数。 它是数据页的行数，
ghost_rec_count	许多标记为删除的幻像记录。
is_iam_pg	如果该特定页面是IAM页面，则其值为1
is_mixed_ext	这表明范围是否是混合范围
free_bytes	页面上的可用字节
n	日志序号
diff_map_page_id	它显示相应的差分位图页面的页面ID
header_version	页面标题版本

Now let us run the same query with mode ‘Detailed’. We can see here that the description columns also showing the data. Therefore, we can use the mode as per our requirement.

现在，让我们以“详细”模式运行相同的查询。 我们在这里可以看到，描述列也显示了数据。 因此，我们可以根据需要使用该模式。

Select * from sys.dm_db_page_info(6,1,157,’Detailed’)

If it is a data page, it will show the contents of the page along with the values for the columns.

如果它是数据页面，它将显示页面的内容以及列的值。

Join sys.dm_db_page_info with DMVs

将sys.dm_db_page_info与DMV连接

We can join the sys.dm_db_page_info with other dynamic management views to get information about the page. In SQL Server 2019, sys.dm_exec_processes and sys.sysprocesses contains a new column page_resource and we can use function sys.fn_PageResCracker to get information about the database id, file id along with the page id.

我们可以将sys.dm_db_page_info与其他动态管理视图结合起来以获取有关页面的信息。 在SQL Server 2019中，sys.dm_exec_processes和sys.sysprocesses包含新列page_resource，我们可以使用函数sys.fn_PageResCracker来获取有关数据库ID，文件ID和页面ID的信息。

Let us create another table and index on our table in order to explore the join sys.dm_db_page_info.

让我们创建另一个表并在我们的表上建立索引，以探索联接sys.dm_db_page_info。

CREATE TABLE dbo.SQLShackDemo(
    ID int IDENTITY(1,1) NOT NULL
    , Name VARCHAR(100) NOT NULL)
GO
 
CREATE UNIQUE CLUSTERED INDEX CIX_SQLShackDemo
ON dbo.SQLShackDemo (ID)
GO

Now we will use SqlQueryStress simulator created by Adam Machanic. We will run the query through this tool with multiple threads to put a load into our database. You can download this tool from the link.

现在，我们将使用由Adam Machanic创建的SqlQueryStress模拟器。 我们将通过具有多个线程的该工具运行查询，以将负载加载到数据库中。 您可以从链接下载此工具。

This downloads the SqlQueryStress.exe as shown below.

如下所示，这将下载SqlQueryStress.exe。

Double click on the SqlQueryStress.exe. this launches the tool window as shown below.

双击SqlQueryStress.exe。 这将启动工具窗口，如下所示。

Next, click on Database to enter the connection details like instance name, authentication mode, database name etc.

接下来，单击数据库以输入连接详细信息，例如实例名称，身份验证模式，数据库名称等。

Let us run the below query in multiple numbers of threads and interactions. This will create the wait resources in the database.

让我们在多个线程和交互中运行以下查询。 这将在数​​据库中创建等待资源。

SET NOCOUNT ON;
DECLARE @i int = 1
WHILE @i < 100000
BEGIN
    INSERT INTO dbo.SQLShackDemo 
    (Name) VALUES ('Rajendra Gupta')
    SET @i += 1
END;

Now, we will join the sys.dm_db_page_info with the DMV sys.dm_exec_requests and the function sys.fn_PageResCracker. The function sys.fn_ PageResCracker takes input of page_resource from the sys.dm_exec_requests. page_resource is 8-byte hexadecimal representation of a database page.

现在，我们将sys.dm_db_page_info与DMV sys.dm_exec_requests和函数sys.fn_PageResCracker结合在一起。 函数sys.fn_ PageResCracker从sys.dm_exec_requests中获取page_resource的输入。 page_resource是数据库页面的8字节十六进制表示形式。

We require the VIEW Server STATE permissions to run the sys.fn_PageResCracker. We can grant permission using below script.

我们需要VIEW Server STATE权限才能运行sys.fn_PageResCracker。 我们可以使用以下脚本授予权限。

GRANT VIEW Server STATE to [Username]

Now let us run the below query in SQL Server Management Studio.

现在，让我们在SQL Server Management Studio中运行以下查询。

SELECT DMF.*
FROM sys.dm_exec_requests AS DM  
CROSS APPLY sys.fn_PageResCracker (DM.page_resource) AS  fn 
CROSS APPLY sys.dm_db_page_info(fn.db_id, fn.file_id, fn.page_id, 'Detailed') AS DMF

We can see below the output showing the one row per wait_resource from the sys.dm_exec_requests when the row contains a non-null page_resource.

我们可以在下面的输出中看到sys.dm_exec_requests中的每个wait_resource一行，当该行包含非null的page_resource时。

Conclusion

结论

SQL Server 2019 provides many enhancements and a new feature to troubleshoot the issues. We can use this DMF to get information about a specific page or to get information after joining from multiple DMV’s. Explore this dynamic management function to get a hands-on experience.

SQL Server 2019提供许多增强功能和新功能以解决问题。 我们可以使用该DMF来获取有关特定页面的信息，或者在从多个DMV加入后获取信息。 探索此动态管理功能以获得实践经验。

翻译自: https://www.sqlshack.com/sql-server-2019-new-dmf-sys-dm_db_page_info/

dmf nmf