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.