Microsoft – SQL Server – Wait Resource – Database Object and Page

Introduction

As I was debugging an unresponsive a SQL Server Instance, I ran into the need to review the sessions and determine whether they might be colliding with each other.

Review System Processes

Let us quickly review which processes are being blocked:

select *
from   master.dbo.sysprocesses
where  spid > 50
and    (
	  (blocked != 0)
	)	

Here is our output:

sysprocesses_waitresource

The column we are tracking is the waitresource column.

Wait Resource Format

Thankfully, Microsoft has done a good job documenting and publishing detailed guide as to how data in the waitresource column is arrived at.

The relevant KB Articles are:

And, here is the well presented guide:

Resource Format Example
Table TAB: DatabaseID:ObjectID:IndexID TAB:
<dbid>,
<objectID>,
<indexID>
Page PAGE:
DatabaseID:FileID:PageID
PAGE:
<dbid>,
<DBID>,
<PageID>
Key Key:
DatabaseID:HashValueForIndexKey (HOBT_ID)
KEY:
<dbid>,
<Hobt_ID>
Row RID:
DatabaseID:FileID:PageID:Slot (row)
 RID:
<dbid>,
<DBID>,
<PageID>,
<Slot (Row)>
Compile DatabaseID:ObjectID [[COMPILE]] <dbid>,
<ObjectID>[[COMPILE]]

 

From our query output pasted earlier, we see that spid = 54 is waiting on a specific resource 20:1:56444 and spid is waiting on resource PAG 20:1:168.

Interpretation:

Item Value – SPID – 54 Value – SPID – 65
Database 20 20
File ID 1 1
Page ID 56444 56444

Wait Resource – PAG* – MS SQL Server – v2008/R2 and below

To determine the specific object that is being waited on we can try a couple of things.

dbcc inputbuffer(<spid>)

We can issue dbcc inputbuffer(<spid>) and read the SQL Statement that is the session’s input buffer.

dbcc page(<database-id>, <file-id>, <page-id>)

The truth is that dbcc inputbuffer will return the entire SQL Statement in the batch and so it is often not precise enough when one will like the specific database object that is being waited on.

For example if our SQL statement is:


select tblOrder.*

from   dbo.order  tblOrder

         inner join dbo.orderDetail tblOrderDetail

              on tblOrder.orderNumber = tblOrderDetail.orderNumber

where   tblOrderNumber.orderNumber = @orderNumber            

we will know that dbo.order and dbo.orderDetail are possible culprit, but we will not know which specific database object.

So to get specific detail we should use dbcc page …

Syntax:

dbcc traceon (3604)

    dbcc pagee (<database-id>, <file-id>, <page-id>)

DBCC traceoff (3604)

 

Sample Code:

dbcc traceon (3604)

    dbcc page (12,1,6000)

DBCC traceoff (3604)

 

Command Interpretation:

Command Reason
dbcc traceon(3604) Redirect output of DBCC to the client and not to the log
dbcc page(<db>, <file>, <page>) Actual DBCC Command
dbcc traceoff(3604) Reverse DBCC Output Redirection

DBCC page – Output

DBCCPageOutput

DBCC page – Output – Explanation

DBCC Page provides a full plate of information.  Here is the data that is pertinent to our current discussion:

Item Sample What does it mean?
bdbid 14 Database ID – Use dbname(<dbid>) to get database name
m_pageId 1:145604 Page ID
Metadata: AllocUnitId 72057594044350464 Allocation Unit ID
Metadata: PartitionId 72057594039959552 Partition ID
Metadata: ObjectId 565577053 Object ID – Use object_name(<object-id>, <dbid>) to get actual table name
Metadata: IndexId 0 Index ID – 0 is for heap, 1 for clustered index, and >1 for non-clustered index
m_lsn (6856:580:56) LSN – Log Sequence Number
m_ghostRecCnt 0 Ghost Record Count – 0 means it is not a ghost record; anything other than 0 mean the record is marked for deletion

 

Wait Resource – PAG* – MS SQL Server – v2012 and above

As good and revealing as “DBCC Page” is, it is encumbered for a variety of reasons; The reasons includes:

  • You need to have sysadmin privileges to use it
  • The output is not delivered via tabulated easy to read grid display
  • And, also it is easily consumable for monitoring and other uses

To widen the audience, Microsoft is now offering “DBCC Page” data via dynamic management views (DMV) specifically sys.dm_db_database_page_allocations.

Please note that it was added as part of MS SQL Version v2012.

Sample Code:


/*
    RID: 14:1:145604:0  

    DB Name - 14
    File ID - 1
    Page ID : 145604
*/

declare @dbName sysname
declare @dbid int
declare @tableID int
declare @indexID int
declare @partionId int
declare @pageID int
declare @mode sysname

set  @dbid = db_id()
set  @dbName = db_name(@dbid)
set  @tableID = null
set  @indexID = null
set  @partionId = null

set  @pageID = 145604
/* Mode - LIMITED - DETAILED */
set  @mode = 'LIMITED'
set  @indexID = null

select
	  tblPage.database_id
	, db_name(tblPage.database_id) as databaseName
	, tblPage.object_id
	, object_name(tblPage.object_id, tblPage.database_id) as objectName
	, tblPage.index_id
	, tblPage.partition_id
	, tblPage.allocation_unit_type_desc
	, tblPage.allocated_page_file_id
	, tblPage.allocated_page_page_id
	, tblPage.allocation_unit_type_desc
	, tblPage.page_type_desc
from   sys.dm_db_database_page_allocations
		(@dbid, @tableID, @indexID, @partionId, @mode) tblPage

where (
	 (tblPage.allocated_page_page_id = @pageID)
      )

Image:

sys

Please be sure to pass in ‘LIMITED’ as the @mode option.  Passing in the other available option of ‘DETAILED’ will result in a much longer wait.

Only pass in DETAILED if there are columns that you need, and are missing when you pass in the former.

Lab Environment

It is very easy to set up a Lab environment to simulate resource wait.

Lab Environment – Create Database – DBLab


use [master]
go

if db_id('DBLab') is null
begin

	print 'Creating DB - DBLab .... '

	exec('create database [DBLab]')

	exec('ALTER DATABASE [DBLab] set recovery simple')

	print 'Creating DB - DBLab'

end
go

Lab Environment – Create Table – dbo.whatisonyourmind


use [DBLab]
go

set noexec off
go

if object_id('dbo.whatisonyourmind') is not null
begin

	set noexec on
end
go

create table dbo.whatisonyourmind
(
	  [post] nvarchar(600)

	, [addedBy] sysname not null
		constraint defaultWhatIsOnYourMindAddedBy default SYSTEM_USER

	, [addedOn] datetime not null
		constraint defaultWhatIsOnYourMindAddedOn default getutcdate()

)

go

set noexec off
go

Lab Environment – Populate Table – dbo.whatisonyourmind

Let us populate the table.

The DML statements are familiar and simple.  But, rather than to conform to normal Microsoft SQL Server default mode of auto-commit, let us use explicit transaction mode.

use [DBLab]
go

truncate table [dbo].[whatisonyourmind];

go

begin tran

    insert into [dbo].[whatisonyourmind]
    ([post])
    values ('DEV6834')

    insert into [dbo].[whatisonyourmind]
    ([post])
    values ('DEV6834.2')

    update [dbo].[whatisonyourmind]
    set    [post] = 'DEV6834.3'
    where  [addedBy] = SYSTEM_USER

    delete
    from   [dbo].[whatisonyourmind]
    where  [post] = 'DEV6834.3'

/*
while (@@trancount > 0)
begin
    print 'rolling back';
    rollback tran;
end
*/

Notice that we have commented out the “rollback tran” statement.

Lab Environment – Populate Table – dbo.whatisonyourmind (2nd Connection)

Please initiate a new SQL Server Connection and issue the statement pasted below:


while (@@trancount > 0)
begin
	print 'rolling back';
	rollback tran;
end
go

use [DBLab]
go

delete from
from   [dbo].[whatisonyourmind]

Lab Environment – Inspect Sessions and find blockers

Microsoft SQL Server (ver 2000)

select 

		  tblSysProcess.spid
		, tblSysProcess.cmd
		, tblSysProcess.blocked
		, tblSysProcess.waitresource
		, tblSysProcess.open_tran
		, tblSysProcessBlocker.spid spidBlocker

from   master.dbo.sysprocesses tblSysProcess

			left outer join master.dbo.sysprocesses tblSysProcessBlocker

				on tblSysProcess.blocked = tblSysProcessBlocker.spid

where
	(

		(tblSysProcess.spid > 50)

	)

and
	   (

		 (tblSysProcess.blocked != 0)
	      or (tblSysProcess.spid in
                        (
                            select blocked
                            from master.dbo.sysprocesses tblSysProcess_Inner
                        )
                  )

	)

Image – v2000

waitResource_output_v2000

Microsoft SQL Server (more recent versions)


SELECT
		sysprc.spid,
		sysprc.waittime,
		sysprc.lastwaittype,
		DB_NAME(sysprc.dbid) AS database_name,
--		sysprc.cpu,
--		sysprc.physical_io,
--		sysprc.login_time,
--		sysprc.last_batch,
		sysprc.status,
		sysprc.hostname,
--		sysprc.[program_name],
		sysprc.cmd,
--		sysprc.loginame,
		OBJECT_NAME(sqltxt.objectid) AS [object_name],
		sqltxt.text

FROM master.sys.sysprocesses sysprc

    left outer join master.sys.sysprocesses tblSysProcessBlocker

       on sysprc.blocked = tblSysProcessBlocker.spid

    OUTER APPLY master.sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt

where  sysprc.spid >  50

and

   (

	      (sysprc.blocked != 0)
	 or (
               sysprc.spid in (
                              select blocked
                              from   master.dbo.sysprocesses tblSysProcess_Inner
                             )
            )

 )

Image – v2012

waitResource_output_v2012

Security Implications

DBCC Page came over from the old Sybase days.

You can use it to dig deep into the SQL Server Storage Internals.

This behavior is documented in http://support.microsoft.com/kb/83065.

When you invoke it with a print option greater than 1, it will in fact emit out raw page contents.

DBCCPagePrintOption2

 

So if you have data, that needs to kept away from DBA, etc, learn to play with encryption and compression.

References

References – SQL Server – DBCC Page

References – SQL Server – DMV – sys.dm_db_database_page_allocations

References – SQL Server – Storage Engine

References – SQL Server – TroubleShooting Blocking Problems

References – SQL Server – Latch Waits

 

References – SQL Server – Documentation

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s