{"id":83480,"date":"2019-02-26T23:12:15","date_gmt":"2019-02-26T23:12:15","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83480"},"modified":"2019-08-15T13:44:28","modified_gmt":"2019-08-15T13:44:28","slug":"identifying-page-information-in-sql-server-2019","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/identifying-page-information-in-sql-server-2019\/","title":{"rendered":"Identifying Page Information in SQL Server 2019"},"content":{"rendered":"

SQL Server 2019 has some new and very interesting functions to identify information about pages. Many operations, such as analyzing current activities, locks or deadlocks, may result in some metadata pointing\/blaming one page\u00a0in the database.<\/p>\n

However, how to translate this information to get a database and object name?<\/p>\n

The image below is an example of information included in an XML_DEADLOCK_REPORT<\/strong>, only as an example. We can face information like this in other administrative tasks as well. How to translate this?<\/p>\n

\"deadlock<\/p>\n

SQL Server 2019 brings the new function sys.dm_db_page_info<\/strong> that allow us to recover page information.<\/p>\n

Take a look on this example:<\/p>\n

select<\/span>\u00a0objecT_name<\/i><\/span>(<\/span>objecT_id<\/span>)<\/span>,<\/span>*<\/span>\u00a0from<\/span>\u00a0sys<\/span>.<\/span>dm_db_page_info<\/b><\/span>(<\/span>9<\/span>,<\/span>1<\/span>,<\/span>392<\/span>,<\/span>‘DETAILED’<\/span>)<\/span> <\/span><\/div>\n

Using sys.dm_db_page_info<\/strong> we can identify even the object which own the page.<\/p>\n

\"PageInfo\"<\/p>\n

SQL Server 2019 goes even beyond: sys.dm_exec_requests<\/strong> has a new field called page_resource<\/strong> with the binary identification of the page when the request is waiting\/blocked by a page.<\/p>\n

However, this identification is not on the format for the sys.dm_db_page_info<\/strong>, we need first to convert the format and we can do this using sys.fn_pagerescracker<\/strong><\/p>\n

Let’s see an example. We can create some locks to block a request and check the result.<\/p>\n

Execute on one query window:<\/p>\n

BEGIN<\/span>\u00a0TRANSACTION<\/span>
\nSELECT<\/span>\u00a0*<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0customers<\/span>\u00a0WITH<\/span>\u00a0(<\/span>updlock<\/span>,<\/span>\u00a0paglock<\/span>)<\/span>\u00a0 <\/span><\/div>\n

Execute on another query window:<\/p>\n

BEGIN<\/span>\u00a0TRANSACTION<\/span>
\nSELECT<\/span>\u00a0*<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0orders<\/span>\u00a0WITH<\/span>\u00a0(<\/span>updlock<\/span>,<\/span>\u00a0paglock<\/span>)<\/span>
\nSELECT<\/span>\u00a0*<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0customers<\/span>\u00a0WITH<\/span>\u00a0(<\/span>updlock<\/span>,<\/span>\u00a0paglock<\/span>)<\/span>\u00a0 <\/span><\/div>\n

\nThe 2nd query window will be blocked by the customers lock on the first query window. The PAGLOCK<\/strong> hint forces the lock to be taken on the entire page only for this example, it’s not something to be used in production.<\/p>\n

Use this query on a 3rd query window to confirm we have a request waiting for a page:<\/p>\n

SELECT<\/span>\u00a0session_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0request_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0page_resource<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>dm_exec_requests<\/span>
\nWHERE<\/span>\u00a0\u00a0page_resource<\/span>\u00a0IS<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>\u00a0 <\/span><\/div>\n

\"Page<\/p>\n

Now we can use the two other functions to identify the information about the page, including the object name:<\/p>\n

SELECT<\/span>\u00a0Object_name<\/span>(<\/span>page_info<\/span>.<\/span>object_id<\/span>)<\/span>\u00a0AS<\/span>\u00a0object_name,page_info.<\/span>*<\/span><\/span>\u00a0
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>dm_exec_requests<\/span>\u00a0AS<\/span>\u00a0d<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CROSS<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>Fn_pagerescracker<\/span>(<\/span>d<\/span>.<\/span>page_resource<\/span>)<\/span>\u00a0AS<\/span>\u00a0r<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CROSS<\/span>\u00a0apply<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0sys<\/span>.<\/span>Dm_db_page_info<\/span>(<\/span>r<\/span>.<\/span>db_id<\/span>,<\/span>\u00a0r<\/span>.<\/span>file_id<\/span>,<\/span>\u00a0r<\/span>.<\/span>page_id<\/span>,<\/span>\u00a0‘DETAILED’<\/span>)<\/span>\u00a0AS<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0page_info<\/span>
\nWHERE<\/span>\u00a0\u00a0d<\/span>.<\/span>page_resource<\/span>\u00a0IS<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>\u00a0<\/span><\/div>\n
\u00a0<\/div>\n
\"dmrequest<\/div>\n
\u00a0<\/div>\n

References<\/h4>\n
sys.fn_PageResCraker<\/a><\/div>\n
sys.dm_db_page_info<\/a><\/div>\n
\u00a0<\/div>\n","protected":false},"excerpt":{"rendered":"

SQL Server 2019 has some new and very interesting functions to identify information about pages. Many operations, such as analyzing current activities, locks or deadlocks, may result in some metadata pointing\/blaming one page\u00a0in the database. However, how to translate this information to get a database and object name? The image below is an example of……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[4170],"coauthors":[6810],"class_list":["post-83480","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-database-administration"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83480","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83480"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83480\/revisions"}],"predecessor-version":[{"id":83485,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83480\/revisions\/83485"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83480"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83480"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83480"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83480"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}