Troubleshooting Blocking in SQL2000

1
Troubleshooting Blocking in SQL2000
Why are we waiting ?

• Jasper Smith

2
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

3
Transaction Isolation Levels
4
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, first-served
  basis as the preceding connections free their
  locks
• Some (transient) blocking is normal
• Only blocks with long durations should be
  considered a problem

5
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
14
30
2
4
6
Critical Object
Head Blocker (Resolve him and everything else
will move ahead)
6
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

7
Controlling Locking

• SET TRANSACTION ISOLATION LEVEL
• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE
• SET LOCK_TIMEOUT timeout_period
• Get current value from _at__at_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)

8
Controlling Locking
9
Toolset

• sp_who2
• sp_lock
• aba_lockinfo
• pss_blocker80
• dbcc sqlperf(waitstats) - sp_top_waits
• Profiler
• System Monitor

10
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 _at_stmt 'update ' _at_locktab ' set
  Table name, ObjOwner user_name(uid) from
  ' quotename(_at_lckdb, '') '.dbo.sysobjects
  where id ' convert(nvarchar(10), _at_lckobjid)
  ' and Database ''' _at_lckdb ''' and ObjID
  ' convert(nvarchar(10), _at_lckobjid)

11
sp_who2
12
Spid Status
13
sp_lock
14
DBCC SQLPERF(WAITSTATS)
15
sp_top_waits

• create procedure sp_top_waits
• (
• _at_interval char(8) '000030', -- time
  between snapshots
• _at_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 int identity(1,1) NOT
  NULL,
• wait_type sysname
  NOT NULL,
• requests float(53)
  NOT NULL,
• wait_time float(53)
  NOT NULL,
• signal_wait_time float(53)
  NOT NULL,
• CONSTRAINT PK_waits PRIMARY KEY CLUSTERED
• (runid,wait_type) )
• insert waits

16
Monitoring

• create procedure sp_blockcnt2
• as
• return(select blockeduserscount()
• from master.dbo.sysprocesses where blocked ltgt 0)
• -- in a job step
• declare _at_b int
• exec _at_b sp_blockcnt2
• exec sp_user_counter1 _at_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

17
DEMO

• Identifying blocking and Waits using Query
  Analyzer

18
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

19
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

20
Further Reading

• Hands on SQL Server 2000Troubleshooting
  Locking and Blocking
• Kalen Delaneyhttp//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

21
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