Jacob Wilkins (9/4/2015)
Ah, thanks for posting that. I'd been having more trouble finding a 2008 instance than I'd expected.So it seems 2008 (not R2) is the same as the others I've tested, in that only the object level locks get taken. It's good to know that my memory hasn't gone completely yet 🙂
Of course, the visual impression here is misleading, since this was apparently run on a very nice machine with enough cores for lock partitioning to kick in, 24 cores from the looks of the locks.
The NOLOCK query doesn't seem so cluttered because Sch-S locks are one of the lock types for which only one will be acquired, even with lock partitioning.
Either way, it's just an object level S lock without NOLOCK vs an object level Sch-S lock with NOLOCK (with an additional lock on that HOBT, indicating your temp table didn't have a clustered index), so no reduction in locking overhead. Now to look for a 2005 instance...
Cheers!
It's pretty clear to me that SQL is acquiring normal shared row locks and then releasing them. The table is not partitioned. And I can't imagine that SQL would use parallelism to process less than 50 rows.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.