Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Rationale...
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.

share|improve this question
    
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? –  Martin Smith Feb 21 '12 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 '12 at 14:39

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer

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'
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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