12 October,2012 by Jack Vamvas
Lock Timeouts (timeout > 0)/sec is part Locks counter part of the SQLServer:Locks object.Accrding to BOL : “Number of lock requests per second that timed out, but excluding requests for NOWAIT locks”
It is not uncommon to monitor on lock timeouts . The can cause bottlenecks and it is important to identify queries which cause these timeouts. The overall objective is to decrease the amount of time a lock is held. The goal is to have 0 during normal Production time.
One of the resources SQL Server can lock out on is Extent. A lock on a contiguous group of 8 pages.
Other Lock Timeout Types include include .: AllocUnit,HoBT,Metadata,Application,RID , Key , Page ,Object,File,Database
To view the counter use Perfmon – check Window Perfmon scripting, SQL Server perfmon and how to perfmon
Add the counter \\MYINST1:Locks(Extent)\Lock Timeouts(timeout > 0)/sec
Method 2 Note: these are cumulative values
Select * from sys.dm_os_performance_counters where counter_name = 'Lock Timeouts/sec' and instance_name = 'Extent'
1) Locate the queries with excessive locking delays. One method is to trace the event “Blocked Process report” using SQL Server Profiler
It’s a useful method as it allows an event to fire only when a certain threshold is met. For example , if you wanted the event to fire after 10 seconds
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'blocked process threshold', 10 RECONFIGURE
2) Start SQL Server Profiler , set up a trace with the Blocked Process report, start the trace
3) You’ve now identified queries with excessive locking delays. The objective is to decrease the duration of time locks are held. Some command methods are:
Stored procedure versus adhoc queries
Analyse indexes – for example , if you have a heavily updated column don’t include it in a Clustered Index as it will will also lock the non clustered index via the row locator