SQL doctor Development Blog

Earlier this week I was researching a performance issue on a SQL Server instance using the ‘sys.dm_os_waiting_tasks’ and ‘sys.dm_exec_requests’ DMV’s and noticed a ton of page latch and page io latch waits.  This is nothing unusual when dealing with performance issues and is normally just one of the signs of poorly written queries, missing indexes, poorly designed indexes, or out of data statistics.

View executing requests with Page waits

Since the problem was so prevalent and consistently occurring on the machine I wanted see if I could query the DMV’s to quickly find out if this was due to one or more poorly written queries.  This lead me to whip together the following query:

select
        r.command,
        r.wait_resource,
        r.wait_type,
        qp.query_plan,
        [statement_text]=SUBSTRING(st.text, (r.statement_start_offset/2)+1,
                        ((CASE r.statement_end_offset
                            WHEN -1 THEN DATALENGTH(st.text)
                                ELSE r.statement_end_offset
                            END - r.statement_start_offset)/2) + 1),
        st.text
from sys.dm_exec_requests r
    outer apply sys.dm_exec_sql_text (r.sql_handle) st
    cross apply sys.dm_exec_query_plan(r.plan_handle) qp
    where wait_type like 'PAGE%'

The above query will produce output similar to the following:

Find performance issues in the query plan

With this output I can easily click on the ‘query_plan’ and see if there are any missing indexes, missing join predicates, or any other warnings.  None of these were found so the next step was to determine if the indexes are properly being used.  This is easy enough to verify by reviewing the use of Index Seeks, Clustered Index Seeks, Index Scans, Table Scans, and verify that a scan is not occurring due to a function or implicit conversion occurring on a column used in a search predicate.

Everything seems easy enough for the requests that have a ‘query_plan’ but as you can see, those are not always shown.  This compelled me to make use of the wait_resource to try and identify the object being accessed.  With a little research you will find out that the ‘wait_resource’ for ‘PAGE%’ waits will identify the actual database and page being access.  It is in the format A:B:C where A = database id, B = file id, and C = page number.  It is easy enough to find the database name from the database id, but how do you find out specific object information using the file id and page number?

Finding the object name using the wait resource

Using SQL Server 2008 R2, the only way that I have found to convert a database id, file id, and page number to a specific object is to make use of the undocumented DBCC PAGE command.  The following is an example:

declare @db sysname;
set @db = DB_NAME(4)
dbcc page (@db , 1, 152764, 0) with tableresults,no_infomsgs

Running the above with database id = 4, file id = 1, and page number = 152764 on my SQL Server instance produced the following output:

Notice the red rectangle showing the ‘Metadata: IndexId’ and ‘Metadata: ObjectId’ fields and values.  The value for ‘Metadata: ObjectId’ can be used to find the object name using the following:

select object_name(149575571, 4)

Stored procedure to convert page id to object

To make things even easier I put together a stored procedure that accepts the wait resource in the format A:B:C where A = database id, B = file id, and C = page number.  Since this is the format of the resource description for ‘PAGE%’ wait types, this string can be given to the stored procedure and return the Database Name, Object Name, Object Id, and Index Id.  The following is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF (OBJECT_ID('p_GetObjectFromPageId') IS NOT NULL)
BEGIN
    DROP PROCEDURE p_GetObjectFromPageId
END
GO
-- =============================================
-- Author:        George Bailey - Idera Software
-- Create date: 2/11/2011
-- Description:    Return object information based on
--              a resource string in the following
--              format:
--                    1:2:3
--              Where:
--                    1 = Database id
--                    2 = File id
--                    3 = Page id
-- =============================================
CREATE PROCEDURE p_GetObjectFromPageId(@PageId varchar(128))
as
begin
    set nocount on;
    declare @pageinfo table ([ParentObject] sysname,
                                [Object] sysname,
                                [Field] sysname,
                                [Value] sysname);
    declare @db sysname;
    declare @dbid int;
    declare @fileid int;
    declare @pageid int;
    declare @objid int;
    declare @indid int;
    declare @parms nvarchar(1024);
    declare @xml xml;

    set @xml = N'<root><r>' + replace(@PageId,':','</r><r>') + '</r></root>';

    select @dbid = @xml.value('(/root/r) [1]', 'int');
    select @fileid = @xml.value('(/root/r) [2]', 'int')
    select @pageid = @xml.value('(/root/r) [3]', 'int')  

    set @db = DB_NAME(@dbid);
    set @parms = N'@db sysname, @fileid int, @pageid int';

    insert into @pageinfo
        exec sp_executesql
        N'dbcc page (@db , @fileid, @pageid, 0) with tableresults,no_infomsgs',
        @parms, @db = @db, @fileid = @fileid, @pageid = @pageid;

    select @objid=[Value] from @pageinfo where [Field] = 'Metadata: ObjectId';
    select @indid=[Value] from @pageinfo where [Field] = 'Metadata: IndexId';

    select
        [Database]=@db,
        [ObjectName]=object_name(@objid, @dbid),
        [ObjectId]=@objid,
        [IndexId]=@indid
    return
end
go

The following is an example execution of the stored procedure:

exec p_GetObjectFromPageId '6:1:411'

The result of the stored procedure will be similar to the following:

Please feel free to leave a comment on any suggested improvements or requests for additional information.