Does SQL Server have any simple way to fetch metrics on how many (and possibly what type) of locks a given query has taken out during the course of it's execution?

I'm in the process of rewriting of a rather nasty stored procedure which makes heavy use of cursors. A set-based solution is possible, and as a result I've got three versions on it: the original, a set-based version with table vars and a set-based version that's purely CTE based. I'm getting good metrics out for the CTE based solution, but because of the size and complexity of the query created by the CTE version I'm wary of releasing it until I've done as much due diligence as I can on it.

Now I realise that the locks SQL take out are those that it thinks it needs to take out at a given point in time, and on a loaded production server it may behave differently, but I'm hoping it would give me a feeling for how it's going to behave.

Currently I'm using profiler and holding transactions open so I can run sp_lock, but was wondering whether I'm missing a trick in SQL Server 2008.

Alternatively, I'm trying to persuade myself that profiler returning a high number of reads when inserting into a table variable is okay (for a given value of okay), but I've not convinced myself. The CTE solution has half the reads of the table-var version, and the high read-count of the table-var version seems purely to be down to the inserts. In terms of duration, both the CTE and table var one are about the same, giving execution times roughly 150-200% faster than the cursor based approach.

  • You can trace locks acquired and released in Profiler filtered for a spid. Then script that out as a SQL Trace and query the trace file with sys.fn_trace_gettable. Have you also evaluated a #temp table? Feb 21, 2012 at 14:13
  • Yep, gone down the #temp route and got consistently inconsistent results; sometimes faster, sometimes slower, evaluating the same dataset (which left me somewhat confused). I haven't completely discounted it yet... also: forgot about fn_trace_gettable; have used it before, but don't use it often enough to remember about it, will look into that, ta.
    – Chris J
    Feb 21, 2012 at 14:39

2 Answers 2


Extended Events with Histogram target, event counter target or a bucketizer target. You can start from the How to: Find the Objects That Have the Most Locks Taken on Them example on MSDN, which offers almost exactly what you want. You can modify the example to get the lock types (the example 'as is' filters out S and U locks) and you can refine it to filter per session etc etc.

That being said, I must point out that approaching a performance investigation from counting the locks acquired is highly unusual. The focus is usually be on wait stats, as described in the Waits and Queues methodology. See for exampleCapturing wait stats for a single operation for how to use, again, Extended Events to capture the relevant info for a specific query.

On the other hand if you are concerned about locks from the point of view of concurrency (will the new CTEs create more contention due to locking?) then I would recommend investigating deploying snapshot isolation.


Grab the sid of the query when it is running and then plug it into sys.dm_tran_locks.

select * from sys.dm_tran_locks where request_session_id='insert sid here'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.