Summary of locks for a query 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.
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.