Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Find queries causing Lock Timeouts per second

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    

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

Blocked_processes

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer