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:
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:
- INF: Understanding and resolving SQL Server blocking problems
And, here is the well presented guide:
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.
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.
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 …
dbcc traceon (3604) dbcc pagee (<database-id>, <file-id>, <page-id>) DBCC traceoff (3604)
dbcc traceon (3604) dbcc page (12,1,6000) DBCC traceoff (3604)
DBCC page – Output
DBCC page – Output – Explanation
DBCC Page provides a full plate of information. Here is the data that is pertinent to our current discussion:
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.
/* 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) )
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.
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
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
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.
So if you have data, that needs to kept away from DBA, etc, learn to play with encryption and compression.
References – SQL Server – DBCC Page
- SQL Server Storage Engine Blog – How to use DBCC PAGE – Paul Randal MSFT
- INFO: Description of DBCC PAGE Command
References – SQL Server – DMV – sys.dm_db_database_page_allocations
- SQL Server 2012: sys.dm_db_database_page_allocations
- Page Count in sys.dm_db_database_page_allocations doesn’t match page count in sys.dm_db_index_physical_stats by jdanton1
References – SQL Server – Storage Engine
- Sql Server Storage Engine Allocation Maps
References – SQL Server – TroubleShooting Blocking Problems
- INF: Understanding and resolving SQL Server blocking problems
- Troubleshooting Blocking issues in SQL Server by cuko
- How To Find locked tables and Kill Session IN Sql Server by naga raju
References – SQL Server – Latch Waits
- Latch waits on 2:1:103? You are probably creating too many temp tables in Sql Server
by Matt Wrock
References – SQL Server – Documentation
- Better Documentation for Tasks waiting on resource
- Connect Item -Document sys.dm_os_waiting_tasks