Troubleshooting Blocking in
SQL2000
Why are we waiting ?
Jasper Smith
Agenda
Overview of Isolation Levels and what we mean by
blocking
Common blocking scenarios
Locking Hints
SQL Server wait types dbcc sqlperf(waitstats)
Identifying and diagnosing blocking
Further Reading and KB Articles
Transaction Isolation Levels
ISOLATION LEVEL
DIRTY READ
NONREPEATABLE
READ
PHANTOM
READ UNCOMMITTED
YES
YES
YES
READ COMMITTED*
NO
YES
YES
REPEATABLE READ
NO
NO
YES
SERIALIZABLE
NO
NO
NO
What do we mean by Blocking ?
Attempting to acquire a lock that conflicts
with a lock held by another connection,
the attempt is blocked (SPID waits) until:
The conflicting lock is freed
The time-out interval for the connection
expires (default is no timeout)
Locks are granted on a first-come, firstserved basis as the preceding connections
free their locks
Some (transient) blocking is normal
Only blocks with long durations should be
considered a problem
Mechanics of a Blocking Scenario
t1
t2 t3
t4
t5
t6
t7
Obj1
Obj2
Exponential Increase in Blocked SPIDs
Obj3
Rapid Drop off in CPU Activity
Obj4
Obj5
Obj6
1 2
Critical
Object
14
30
Head Blocker (Resolve him and everything else
will move ahead)
Common Blocking Scenarios
No usable indexes - poorly optimised query
Long Running Transactions
Slow client consumption of results
Client Disconnect/Query Cancel
Lock Escalation
Inappropriate Isolation Level
Stored Procedure Compile Locks
Controlling Locking
SET TRANSACTION ISOLATION LEVEL
SET LOCK_TIMEOUT timeout_period
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
Get current value from @@lock_timeout
Setting to -1 reverts to indefinite timeout (default)
Error 1222
RTM behaviour was to rollback transaction, reverted in
SP1 to SQL 7 behaviour no automatic rollback
TABLE HINTS
SELECT au_lname FROM authors WITH (NOLOCK)
Controlling Locking
Locking hint
Description
HOLDLOCK
Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table,
row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK
Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an
uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible.
Only applies to the SELECT statement.
PAGLOCK
Use page locks where a single table lock would usually be taken.
READCOMMITTED
Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation
level. By default, SQL Server 2000 operates at this isolation level.
READPAST
Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily
appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on
these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and
will read only past row-level locks. Applies only to the SELECT statement.
READUNCOMMITTED
Equivalent to NOLOCK.
REPEATABLEREAD
Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation
level.
ROWLOCK
Use row-level locks instead of the coarser-grained page- and table-level locks.
SERIALIZABLE
Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level.
Equivalent to HOLDLOCK.
TABLOCK
Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of
the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
TABLOCKX
Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the
end of the statement or transaction.
UPDLOCK
Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or
transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update
it later with the assurance that the data has not changed since you last read it.
XLOCK
Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This
lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the
appropriate level of granularity.
Toolset
sp_who2
sp_lock
aba_lockinfo
pss_blocker80
dbcc sqlperf(waitstats) - sp_top_waits
Profiler
System Monitor
Whats missing ?
Enterprise Manager Current Activity
Starts well
set transaction isolation level read uncommitted
set quoted_identifier on
set nocount on
set lock_timeout 5000
Goes mad in the middle
set transaction isolation level read committed
Can block reading tempdb sysobjects
select @stmt ='update ' + @locktab + ' set [Table] = name, [ObjOwner] = user_name(uid)
from ' + quotename(@lckdb, '[') + '.[dbo].[sysobjects] where id = ' + convert(nvarchar(10),
@lckobjid) + ' and [Database] = ''' + @lckdb + ''' and [ObjID] = ' + convert(nvarchar(10),
@lckobjid)
sp_who2
Spid 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
Spinloop
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.
Process is waiting while attempting to acquire a spinlock used for
concurrency control on SMP systems.
sp_lock
DBCC SQLPERF(WAITSTATS)
sp_top_waits
create procedure sp_top_waits
(
@interval char(8) = '00:00:30',
-- time between snapshots
@showall int = 1
-- show all waits longer than this value
) as
-Uses snapshot of waits to determine what's waiting longest
-Best reference found to date for wait types at http://sqldev.net/misc/WaitTypes.htm
set nocount on
create table #waits (
runid
wait_type
requests
wait_time
signal_wait_time
CONSTRAINT PK_waits
(runid,wait_type) )
int identity(1,1) NOT NULL,
sysname
NOT NULL,
float(53)
NOT NULL,
float(53)
NOT NULL,
float(53)
NOT NULL,
PRIMARY KEY CLUSTERED
insert #waits
exec('dbcc sqlperf(waitstats)')
waitfor delay @interval
insert #waits
exec('dbcc sqlperf(waitstats)')
select a.wait_type,(b.requests-a.requests) as 'requests',
(b.wait_time-a.wait_time) as 'wait_time',
(b.signal_wait_time-a.signal_wait_time) as 'signal_wait_time'
from #waits a
join #waits b
on
a.wait_type = b.wait_type and b.runid>a.runid
where (b.wait_time-a.wait_time) >= CAST(@showall as float(53))
and a.wait_type not in ('WAITFOR', 'SLEEP', 'RESOURCE_QUEUE', 'Total')
order by wait_time desc
option(KEEPFIXED PLAN)
drop table #waits
return
Monitoring
create procedure sp_blockcnt2
as
return(select blockedusers=count(*)
from master.dbo.sysprocesses where blocked <> 0)
-- in a job step
declare @b int
exec @b = sp_blockcnt2
exec sp_user_counter1 @b
Set up an alert on the counter to run another job calling
diagnostic script(s) of choice to output to a file or store in a table
DEMO
Identifying blocking and Waits
using Query Analyzer
Summary
Keep transactions short
Appropriate (useful) indexes
Use appropriate Isolation level for
business requirements e.g. do you really
need SERIALIZABLE for a simple select
Client application needs to handle query
cancellation and clean up any transactions
Further investigation
Monitor blocking to pick up areas of
contention
Use Profiler to capture statements running
during periods of blocking
Waitstats is a view on system performance
Used to identify areas to concentrate
tuning efforts
Leads to further investigation by
Profiler
Perfmon
Further Reading
Hands on SQL Server 2000
Troubleshooting : Locking and Blocking
Kalen Delaney
http://tinyurl.com/puwn
224453 - INF Understanding and Resolving SQL Server 7 or
2000 Blocking Problems
224587 - HOW TO Troubleshoot Application Performance with
SQL Server
243586 - INF Troubleshooting Stored Procedure Recompilation
271509 - INF How to Monitor SQL Server 2000 Blocking
244455 - INF Definition of Sysprocesses Waittype and
Lastwaittype Fields for SQL Server
http://sqldev.net/misc/WaitTypes.htm
http://www.sql-server-performance.com/blocking.asp
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html
References
Hands on SQL Server 2000 Troubleshooting : Locking and
Blocking - Kalen Delaney
http://sqldev.net/misc/WaitTypes.htm
SQL 2000 Performance : Waits & Queues Tom Davidson
Microsoft Corp PASS 2002
