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:
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
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:
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
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
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.
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
