October 1, 2003 at 10:22 am
How can I identify the cause of a long wait period? I know how long my querys run, I know how much CPu time I'm eating up, but I don't know where the difference is comming from. I've looked at the locking wait time, but that doesn't appear to be it either? HELP!
Patrick
Hi Joe.. thought you might like this
Hi Joe.. thought you might like this
October 1, 2003 at 10:56 am
Are you trying to determine what eats up the time for a query to return results to the client?
You've got IO, bandwidth on the network, rendering time on the client.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
October 3, 2003 at 4:24 am
You can see where a process is waiting for in the columns lastwaittype and waitresource from master..sysprocesses.
If it is waiting for a lock you can get more info from master..syslockinfo
I wrote a script that looks into it, but if the process is waiting for a latch and not for another process it will show nothing. You will have to adapt the script for those that case. It was written for non-DBA's to send the output back to me, so don't be insulted by the remarks.
-- blocking and waiting processes
-- Blocked-proces.sql 5.1, Joachim Verhagen 28-2-2003, 19-3-2003 (Just some comment changes)
-- Recipe:
-- 1 Start isqlw
-- 2 Connect to server
-- 3 Select in the upper window (with the yellow cylinder) the database with the blocking problems
-- 4 Go to ASCII output with control-t
-- 4 Run the script by clicking the green arrow
set nocount on
DECLARE @CursorVar CURSOR
DECLARE @waiter integer
DECLARE @blocker integer
SET @CursorVar = CURSOR FORWARD_ONLY STATIC
FOR
select spid,blocked
from master..sysprocesses
where blocked>0
OPEN @CursorVar
FETCH NEXT FROM @CursorVar into @waiter, @blocker
WHILE @@FETCH_STATUS = 0
BEGIN
set nocount on
select '============================================================================================' as 'NEXT BLOCKER AND WAITER SET'
select @blocker, ' blocks ', @waiter
select 'blocker', @blocker
-- statement blocker
DBCC INPUTBUFFER(@blocker)
-- who and what is blocking
select
p.spid,
cast(p.status as varchar(10)) as 'status',
p.waittime,
cast(p.loginame as varchar(30)) as 'loginname',
cast(p.hostname as varchar(20)) as 'hostname',
cast(p.program_name as varchar(25)) as 'ProgramName',
p.cmd,
WaitStatus=case
when waittype=0 then 'Previously Waiting for: '
else 'Now waiting for:'
end,
TypeWait=case Lastwaittype
when 'LCK_M_SCH_S' then 'Schema stability'
when 'LCK_M_SCH_M' then 'Schema modification'
when 'LCK_M_IS' then 'Intent-Share'
when 'LCK_M_SIU' then 'Shared intent to update'
when 'LCK_M_IS_S' then 'Intent-Share-Share (Key-Range Lock)'
when 'LCK_M_IX' then 'Intent-Exclusive'
when 'LCK_M_SIX' then 'Share-Intent-Exclusive'
when 'LCK_M_S' then 'Share'
when 'LCK_M_U' then 'Update'
when 'LCK_M_II_NL' then 'Intent-Insert-NULL (Key-Range Lock)'
when 'LCK_M_II_X' then 'Intent-Insert-Exclusive (Key-Range Lock)'
when 'LCK_M_IU' then 'Intent-Update lock'
when 'LCK_M_IS_U' then 'Intent-Share Update (Key-Range Lock)'
when 'LCK_M_X' then 'Exclusive'
when 'LCK_M_BU' then 'Bulk Update'
else LastWaittype
end,
p.waitresource
from master..sysprocesses p
where p.spid=@blocker
--locksblocker
selectdistinct
cast(o.name as varchar(20)) as 'object name',
cast(i.name as varchar(20)) as 'index name',
Object_type=
case
when rsc_type=1 then 'NULL Resource'
when rsc_type=2 then 'Database'
when rsc_type=3 then 'File'
when rsc_type=4 then 'Index'
when rsc_type=5 then 'Table'
when rsc_type=6 then 'Page'
when rsc_type=7 then 'Key'
when rsc_type=8 then 'Extent'
when rsc_type=9 then 'RID (Row id)'
when rsc_type=10 then 'Application'
else 'Unknown'
end,
cast(rsc_text as varchar(15)) as 'lockresource',
Lock_mode=
case
when req_mode=0 then 'No Access'
when req_mode=1 then 'Sch-S (Schema Stability)'
when req_mode=2 then 'Sch-M (Schema modification)'
when req_mode=3 then 'S (Shared)'
when req_mode=4 then 'U (Update)'
when req_mode=5 then 'X (Exclusive)'
when req_mode=6 then 'IS (Intent Shared)'
when req_mode=7 then 'IU (Intent Update)'
when req_mode=8 then 'IX (Intent Exclusive)'
when req_mode=9 then 'SIU (Shared Intent Update)'
when req_mode=10 then 'SIX (Shared Intent Exclusive)'
when req_mode=11 then 'UIX (Update Intent Exclusive)'
when req_mode=12 then 'BU (Bulk)'
when req_mode=13 then 'RangeS_S'
when req_mode=14 then 'RangeS_U'
when req_mode=15 then 'RangeI_N'
when req_mode=16 then 'RangeI_S'
when req_mode=17 then 'RangeI_U'
when req_mode=18 then 'RangeI_X'
when req_mode=19 then 'RangeX_S'
when req_mode=20 then 'RangeX_U'
when req_mode=21 then 'RangeX_X'
else 'Unknown'
end,
lock_status=
case
when l.req_status=1 then 'Granted'
when l.req_status=2 then 'Converting'
when l.req_status=3 then 'Waiting'
else 'Unknown'
end,
l.req_refcnt as 'lock count',
cast(d.name as varchar(20)) as 'database'
from master..syslockinfo l, sysobjects o, sysindexes i, master..sysdatabases d
where l.req_spid=@blocker
and l.rsc_objid=i.id
and l.rsc_indid=i.indid
and l.rsc_objid=o.id
and o.id=i.id
and l.rsc_dbid=d.dbid
and l.rsc_dbid=d.dbid
union
selectdistinct
cast(o.name as varchar(20)) as 'object name',
cast(o.name as varchar(20)),
Object_type=
case
when rsc_type=1 then 'NULL Resource'
when rsc_type=2 then 'Database'
when rsc_type=3 then 'File'
when rsc_type=4 then 'Index'
when rsc_type=5 then 'Table'
when rsc_type=6 then 'Page'
when rsc_type=7 then 'Key'
when rsc_type=8 then 'Extent'
when rsc_type=9 then 'RID (Row id)'
when rsc_type=10 then 'Application'
else 'Unknown'
end,
cast(rsc_text as varchar(15)) as 'lockresource',
Lock_mode=
case
when req_mode=0 then 'No Access'
when req_mode=1 then 'Sch-S (Schema Stability)'
when req_mode=2 then 'Sch-M (Schema modification)'
when req_mode=3 then 'S (Shared)'
when req_mode=4 then 'U (Update)'
when req_mode=5 then 'X (Exclusive)'
when req_mode=6 then 'IS (Intent Shared)'
when req_mode=7 then 'IU (Intent Update)'
when req_mode=8 then 'IX (Intent Exclusive)'
when req_mode=9 then 'SIU (Shared Intent Update)'
when req_mode=10 then 'SIX (Shared Intent Exclusive)'
when req_mode=11 then 'UIX (Update Intent Exclusive)'
when req_mode=12 then 'BU (Bulk)'
when req_mode=13 then 'RangeS_S'
when req_mode=14 then 'RangeS_U'
when req_mode=15 then 'RangeI_N'
when req_mode=16 then 'RangeI_S'
when req_mode=17 then 'RangeI_U'
when req_mode=18 then 'RangeI_X'
when req_mode=19 then 'RangeX_S'
when req_mode=20 then 'RangeX_U'
when req_mode=21 then 'RangeX_X'
else 'Unknown'
end,
lock_status=
case
when l.req_status=1 then 'Granted'
when l.req_status=2 then 'Converting'
when l.req_status=3 then 'Waiting'
else 'Unknown'
end,
l.req_refcnt as 'lock count',
cast(d.name as varchar(20)) as 'database'
from master..syslockinfo l, sysobjects o, master..sysdatabases d
where l.req_spid=@blocker
and l.rsc_indid=0
and l.rsc_objid=o.id
and l.rsc_dbid=d.dbid
union
selectdistinct
'-',
'-',
Object_type=
case
when rsc_type=1 then 'NULL Resource'
when rsc_type=2 then 'Database'
when rsc_type=3 then 'File'
when rsc_type=4 then 'Index'
when rsc_type=5 then 'Table'
when rsc_type=6 then 'Page'
when rsc_type=7 then 'Key'
when rsc_type=8 then 'Extent'
when rsc_type=9 then 'RID (Row id)'
when rsc_type=10 then 'Application'
else 'Unknown'
end,
cast(rsc_text as varchar(15)) as 'lockresource',
Lock_mode=
case
when req_mode=0 then 'No Access'
when req_mode=1 then 'Sch-S (Schema Stability)'
when req_mode=2 then 'Sch-M (Schema modification)'
when req_mode=3 then 'S (Shared)'
when req_mode=4 then 'U (Update)'
when req_mode=5 then 'X (Exclusive)'
when req_mode=6 then 'IS (Intent Shared)'
when req_mode=7 then 'IU (Intent Update)'
when req_mode=8 then 'IX (Intent Exclusive)'
when req_mode=9 then 'SIU (Shared Intent Update)'
when req_mode=10 then 'SIX (Shared Intent Exclusive)'
when req_mode=11 then 'UIX (Update Intent Exclusive)'
when req_mode=12 then 'BU (Bulk)'
when req_mode=13 then 'RangeS_S'
when req_mode=14 then 'RangeS_U'
when req_mode=15 then 'RangeI_N'
when req_mode=16 then 'RangeI_S'
when req_mode=17 then 'RangeI_U'
when req_mode=18 then 'RangeI_X'
when req_mode=19 then 'RangeX_S'
when req_mode=20 then 'RangeX_U'
when req_mode=21 then 'RangeX_X'
else 'Unknown'
end,
lock_status=
case
when l.req_status=1 then 'Granted'
when l.req_status=2 then 'Converting'
when l.req_status=3 then 'Waiting'
else 'Unknown'
end,
l.req_refcnt as 'lock count',
cast(d.name as varchar(20)) as 'database'
from master..syslockinfo l, master..sysdatabases d
where l.req_spid=@blocker
and l.rsc_indid=0
and l.rsc_objid=0
and l.rsc_dbid=d.dbid
select 'waiter', @waiter
-- statement waiter
DBCC INPUTBUFFER (@waiter)
-- who and what is waiting
select
p.spid,
cast(p.status as varchar(10)) as 'status',
p.waittime,
cast(p.loginame as varchar(30)) as 'loginname',
cast(p.hostname as varchar(20)) as 'hostname',
cast(p.program_name as varchar(25)) as 'ProgramName',
p.cmd,
WaitStatus=case
when waittype=0 then 'Previously Waiting for: '
else 'Now waiting for:'
end,
TypeWait=case Lastwaittype
when 'LCK_M_SCH_S' then 'Schema stability'
when 'LCK_M_SCH_M' then 'Schema modification'
when 'LCK_M_IS' then 'Intent-Share'
when 'LCK_M_SIU' then 'Shared intent to update'
when 'LCK_M_IS_S' then 'Intent-Share-Share (Key-Range Lock)'
when 'LCK_M_IX' then 'Intent-Exclusive'
when 'LCK_M_SIX' then 'Share-Intent-Exclusive'
when 'LCK_M_S' then 'Share'
when 'LCK_M_U' then 'Update'
when 'LCK_M_II_NL' then 'Intent-Insert-NULL (Key-Range Lock)'
when 'LCK_M_II_X' then 'Intent-Insert-Exclusive (Key-Range Lock)'
when 'LCK_M_IU' then 'Intent-Update lock'
when 'LCK_M_IS_U' then 'Intent-Share Update (Key-Range Lock)'
when 'LCK_M_X' then 'Exclusive'
when 'LCK_M_BU' then 'Bulk Update'
else LastWaittype
end,
p.waitresource
from master..sysprocesses p
where p.spid=@waiter
-- locks waiter
selectdistinct
cast(o.name as varchar(20)) as 'object name',
cast(i.name as varchar(20)) as 'index name',
Object_type=
case
when rsc_type=1 then 'NULL Resource'
when rsc_type=2 then 'Database'
when rsc_type=3 then 'File'
when rsc_type=4 then 'Index'
when rsc_type=5 then 'Table'
when rsc_type=6 then 'Page'
when rsc_type=7 then 'Key'
when rsc_type=8 then 'Extent'
when rsc_type=9 then 'RID (Row id)'
when rsc_type=10 then 'Application'
else 'Unknown'
end,
cast(rsc_text as varchar(15)) as 'lockresource',
Lock_mode=
case
when req_mode=0 then 'No Access'
when req_mode=1 then 'Sch-S (Schema Stability)'
when req_mode=2 then 'Sch-M (Schema modification)'
when req_mode=3 then 'S (Shared)'
when req_mode=4 then 'U (Update)'
when req_mode=5 then 'X (Exclusive)'
when req_mode=6 then 'IS (Intent Shared)'
when req_mode=7 then 'IU (Intent Update)'
when req_mode=8 then 'IX (Intent Exclusive)'
when req_mode=9 then 'SIU (Shared Intent Update)'
when req_mode=10 then 'SIX (Shared Intent Exclusive)'
when req_mode=11 then 'UIX (Update Intent Exclusive)'
when req_mode=12 then 'BU (Bulk)'
when req_mode=13 then 'RangeS_S'
when req_mode=14 then 'RangeS_U'
when req_mode=15 then 'RangeI_N'
when req_mode=16 then 'RangeI_S'
when req_mode=17 then 'RangeI_U'
when req_mode=18 then 'RangeI_X'
when req_mode=19 then 'RangeX_S'
when req_mode=20 then 'RangeX_U'
when req_mode=21 then 'RangeX_X'
else 'Unknown'
end,
lock_status=
case
when l.req_status=1 then 'Granted'
when l.req_status=2 then 'Converting'
when l.req_status=3 then 'Waiting'
else 'Unknown'
end,
l.req_refcnt as 'lock count',
cast(d.name as varchar(20)) as 'database'
from master..syslockinfo l, sysobjects o, sysindexes i, master..sysdatabases d
where l.req_spid=@waiter
and l.rsc_objid=i.id
and l.rsc_indid=i.indid
and l.rsc_objid=o.id
and o.id=i.id
and l.rsc_dbid=d.dbid
and l.rsc_dbid=d.dbid
union
selectdistinct
cast(o.name as varchar(20)) as 'object name',
cast(o.name as varchar(20)),
Object_type=
case
when rsc_type=1 then 'NULL Resource'
when rsc_type=2 then 'Database'
when rsc_type=3 then 'File'
when rsc_type=4 then 'Index'
when rsc_type=5 then 'Table'
when rsc_type=6 then 'Page'
when rsc_type=7 then 'Key'
when rsc_type=8 then 'Extent'
when rsc_type=9 then 'RID (Row id)'
when rsc_type=10 then 'Application'
else 'Unknown'
end,
cast(rsc_text as varchar(15)) as 'lockresource',
Lock_mode=
case
when req_mode=0 then 'No Access'
when req_mode=1 then 'Sch-S (Schema Stability)'
when req_mode=2 then 'Sch-M (Schema modification)'
when req_mode=3 then 'S (Shared)'
when req_mode=4 then 'U (Update)'
when req_mode=5 then 'X (Exclusive)'
when req_mode=6 then 'IS (Intent Shared)'
when req_mode=7 then 'IU (Intent Update)'
when req_mode=8 then 'IX (Intent Exclusive)'
when req_mode=9 then 'SIU (Shared Intent Update)'
when req_mode=10 then 'SIX (Shared Intent Exclusive)'
when req_mode=11 then 'UIX (Update Intent Exclusive)'
when req_mode=12 then 'BU (Bulk)'
when req_mode=13 then 'RangeS_S'
when req_mode=14 then 'RangeS_U'
when req_mode=15 then 'RangeI_N'
when req_mode=16 then 'RangeI_S'
when req_mode=17 then 'RangeI_U'
when req_mode=18 then 'RangeI_X'
when req_mode=19 then 'RangeX_S'
when req_mode=20 then 'RangeX_U'
when req_mode=21 then 'RangeX_X'
else 'Unknown'
end,
lock_status=
case
when l.req_status=1 then 'Granted'
when l.req_status=2 then 'Converting'
when l.req_status=3 then 'Waiting'
else 'Unknown'
end,
l.req_refcnt as 'lock count' ,
cast(d.name as varchar(20)) as 'database'
from master..syslockinfo l, sysobjects o, master..sysdatabases d
where l.req_spid=@waiter
and l.rsc_indid=0
and l.rsc_objid=o.id
and l.rsc_dbid=d.dbid
union
selectdistinct
'-',
'-',
Object_type=
case
when rsc_type=1 then 'NULL Resource'
when rsc_type=2 then 'Database'
when rsc_type=3 then 'File'
when rsc_type=4 then 'Index'
when rsc_type=5 then 'Table'
when rsc_type=6 then 'Page'
when rsc_type=7 then 'Key'
when rsc_type=8 then 'Extent'
when rsc_type=9 then 'RID (Row id)'
when rsc_type=10 then 'Application'
else 'Unknown'
end,
cast(rsc_text as varchar(15)) as 'lockresource',
Lock_mode=
case
when req_mode=0 then 'No Access'
when req_mode=1 then 'Sch-S (Schema Stability)'
when req_mode=2 then 'Sch-M (Schema modification)'
when req_mode=3 then 'S (Shared)'
when req_mode=4 then 'U (Update)'
when req_mode=5 then 'X (Exclusive)'
when req_mode=6 then 'IS (Intent Shared)'
when req_mode=7 then 'IU (Intent Update)'
when req_mode=8 then 'IX (Intent Exclusive)'
when req_mode=9 then 'SIU (Shared Intent Update)'
when req_mode=10 then 'SIX (Shared Intent Exclusive)'
when req_mode=11 then 'UIX (Update Intent Exclusive)'
when req_mode=12 then 'BU (Bulk)'
when req_mode=13 then 'RangeS_S'
when req_mode=14 then 'RangeS_U'
when req_mode=15 then 'RangeI_N'
when req_mode=16 then 'RangeI_S'
when req_mode=17 then 'RangeI_U'
when req_mode=18 then 'RangeI_X'
when req_mode=19 then 'RangeX_S'
when req_mode=20 then 'RangeX_U'
when req_mode=21 then 'RangeX_X'
else 'Unknown'
end,
lock_status=
case
when l.req_status=1 then 'Granted'
when l.req_status=2 then 'Converting'
when l.req_status=3 then 'Waiting'
else 'Unknown'
end,
l.req_refcnt as 'lock count',
cast(d.name as varchar(20)) as 'database'
from master..syslockinfo l, master..sysdatabases d
where l.req_spid=@waiter
and l.rsc_indid=0
and l.rsc_objid=0
and l.rsc_dbid=d.dbid
FETCH NEXT FROM @CursorVar into @waiter, @blocker
END
CLOSE @CursorVar
DEALLOCATE @CursorVar
-- Explanation Proces Status
-- Background = SPID is performing a background task.
-- Sleeping = SPID is not currently executing. This usually indicates that the SPID is awaiting a command from the application.
-- Runnable = SPID is currently executing.
-- Dormant = Same as Sleeping, except Dormant also indicates that the SPID has been reset after completing an RPC event. The reset cleans up resources used during the RPC event. This is a normal state and the SPID is available and waiting to execute further commands.
-- Rollback = The SPID is in rollback of a transaction.
-- Defwakeup = Indicates that a SPID is waiting on a resource that is in the process of being freed. The waitresource field should indicate the resource in question.
-- Spinloop = Process is waiting while attempting to acquire a spinlock used for concurrency control on SMP systems.
-- Explanation lock mode
-- 0 = NULL. No access is granted to the resource. Serves as a placeholder.
-- 1 = Sch-S (Schema stability). Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
-- 2 = Sch-M (Schema modification). Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
-- 3 = S (Shared). The holding session is granted shared access to the resource.
-- 4 = U (Update). Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
-- 5 = X (Exclusive). The holding session is granted exclusive access to the resource.
-- 6 = IS (Intent Shared). Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
-- 7 = IU (Intent Update). Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
-- 8 = IX (Intent Exclusive). Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
-- 9 = SIU (Shared Intent Update). Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
-- 10 = SIX (Shared Intent Exclusive). Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
-- 11 = UIX (Update Intent Exclusive). Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
-- 12 = BU. Used by bulk operations.
-- 13 = RangeS_S (Shared Key-Range and Shared Resource lock). Indicates serializable range scan.
-- 14 = RangeS_U (Shared Key-Range and Update Resource lock). Indicates serializable update scan.
-- 15 = RangeI_N (Insert Key-Range and Null Resource lock). Used to test ranges before inserting a new key into an index.
-- 16 = RangeI_S. Key-Range Conversion lock, created by an overlap of RangeI_N and S locks.
-- 17 = RangeI_U. Key-Range Conversion lock, created by an overlap of RangeI_N and U locks.
-- 18 = RangeI_X. Key-Range Conversion lock, created by an overlap of RangeI_N and X locks.
-- 19 = RangeX_S. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_S. locks.
-- 20 = RangeX_U. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_U locks.
-- 21 = RangeX_X (Exclusive Key-Range and Exclusive Resource lock). This is a conversion lock used when updating a key in a range.
-- Last waittype
-- Last waittype (text) and waittype (hex)
-- Last Waittype Locks
-- LCK_M_SCH_S 0x01 Schema stability
-- LCK_M_SCH_M 0x02 Schema modification
-- LCK_M_IS 0x03 Intent-Share
-- LCK_M_SIU 0x04 Shared intent to update
-- LCK_M_IS_S 0x05 Intent-Share-Share (Key-Range Lock)
-- LCK_M_IX 0x06 Intent-Exclusive
-- LCK_M_SIX 0x07 Share-Intent-Exclusive
-- LCK_M_S 0x08 Share
-- LCK_M_U 0x09 Update
-- LCK_M_II_NL 0x0A Intent-Insert-NULL (Key-Range Lock)
-- LCK_M_II_X 0x0B Intent-Insert-Exclusive (Key-Range Lock)
-- LCK_M_IU 0x0C Intent-Update lock
-- LCK_M_IS_U 0x0D Intent-Share Update (Key-Range Lock)
-- LCK_M_X 0x0E Exclusive
-- LCK_M_BU 0x0F Bulk Update
-- Last Waittype Latches
-- PWait_LATCH_NL 0x400
-- PWait_LATCH_EX 0x401
-- PWait_LATCH_SH 0x402
-- PWait_LATCH_UP 0x403
-- PWait_PAGELATCH_NL 0x410
-- WAIT_PAGELATCH_EX 0x411
-- WAIT_PAGELATCH_SH 0x412
-- PWait_PAGELATCH_UP 0x413
-- PWait_PAGEIOLATCH_NL 0x420
-- PWait_PAGEIOLATCH_EX 0x421
-- PWait_PAGEIOLATCH_SH 0x422
-- PWait_PAGEIOLATCH_UP 0x423
-- Last Waittype Others
-- PWait_RESOURCE_SEMAPHORE 0x40 Waiting to a acquire a resource semaphore. Used for synchronization.
-- PWait_DTC 0x41 Waiting on Distributed Transaction Coordinator (DTC).
-- PWait_OLEDB 0x42 Waiting on an OLE DB provider.
-- PWait_WRITELOG 0x81 Waiting for log records for a transaction to be flushed to disk.
-- PWait_PSS_CHILD 0x101 Waiting on a child thread in asynchronous cursor operations.
-- PWait_EXCHANGE 0x200 Exchange synchronization up for parallel query threads.
-- PWait_XCB 0x201 Acquiring access to a transaction control block.
-- Transaction control blocks (XCBs) are usually private to a session, but can be shared between sessions when using the bound session feature or having multiple sessions enlist in the same DTC transaction. Only a single session can have access to the XCB at a time. This waittype likely indicates one session waiting for the XCB while the other session which is holding the XCB resource is waiting on a separate resource.
-- PWait_DBTABLE 0x202 Only used by Checkpoint process.
-- PWait_EC 0x203 Killing a connection subthread or Execution Context.
-- PWait_TEMPOBJ 0x204 Dropping a Temporary Object.
-- PWait_XACTLOCKINFO 0x205 Waiting on Bulk Operation when releasing\escalating\transferring locks.
-- PWait_LOGMGR 0x206 Waiting on log writer.
-- PWait_CMEMTHREAD 0x207 Waiting on access to memory object.
-- PWait_CXPACKET 0x208 Waiting on packet synchronize up for exchange operator (parallel query).
-- PWait_PAGESUPP 0x209 Release Spinlock in parallel query thread.
-- PWait_SHUTDOWN 0x20A Wait for SPID to finish completion before shutdown.
-- PWait_WAITFOR 0x20B Wait initiated by a WAITFOR command.
-- PWait_CURSOR 0x20C Waiting for thread synchronization with asynchronous cursors.
-- Waitresource
-- TAB: DatabaseID:ObjectID (Table)
-- PAGE: DatabaseID:FileID:PageID
-- KEY: DatabaseID:ObjectID:IndexID (Hash value for index key)
-- RID: DatabaseID:FileID:PageID:Slot (row)
-- Lock Count: Each time a transaction asks for a lock on a particular resource, a reference count is incremented. The lock cannot be released until the reference count equals 0.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply