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.
Sql Server's locks are described in good detail in the product documentation. A lock might or might not be escalated to page/table level, the behavior is documented, again, in Technet.
A fairly common tactic for deleting large amount of rows is to split up the delete in batches. Instead of deleting all the 5M rows at once like so,
DELETE FROM MyTable WHERE foo='bar'
rather try and delete data in, say, 100 * 50k row batches like so,
WHILE (@rows > 0)
BEGIN
DELETE TOP (50000) FROM MyTable
WHERE foo='bar'
SET @rows = @@ROWCOUNT
END
By splitting the delete in batches, you should lock way less data than with a all-encompassing single-pass delete. YMMV, depending on the selectivity and other activity in your table.
In addition, keep in mind that delete is logged and you might get a nasty surprise with your transaction log size.
This article has nice an overview about how to batch delete data and mind the logs too.
Best Answer
Yes it does take a shared lock on the rows that it reads by default (it also takes an Intent Shared lock on all the pages of the clustered index that it will read), this is done to prevent dirty reads. However there are ways to bypass this (SQL Server has the nolock hint). If the statement is not in a BEGIN TRAN the lock is released after the SELECT statement has run.
More info can be found here:
http://msdn.microsoft.com/en-us/library/ms184286(v=sql.105).aspx http://www.sqlteam.com/article/introduction-to-locking-in-sql-server