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    

How to View the Lock Timeouts (timeout > 0)/sec counter

Method 1
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'

How to Fix Locking Issues

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:

Query tuning

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

Row versioning

Related Posts

SQL Server – How to troubleshoot query timeouts

Sys.dm_os_memory_clerks and AWE memory allocation

Performance Monitor Counters for SQL Server performance testing