Jacob Wilkins (9/1/2015)
ScottPletcher (9/1/2015)
Jacob Wilkins (9/1/2015)
ScottPletcher (9/1/2015)
Jacob Wilkins (9/1/2015)
I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.SELECT queries against temporary tables typically only take a shared object lock anyway, so it's really just the difference between taking an S lock on the object and a Sch-S lock on the object. There's really no additional overhead in terms of the number of locks taken, as that is quite low in both cases.
Aaron Bertrand took a quick look at that here: http://blogs.sqlsentry.com/aaronbertrand/nolock-temp-tables/.
The first time you run them you'll see a bunch of locks on metadata and such for the query to compile (you see that in Aaron's piece above), but afterwards on a local temporary table that is a heap you'll see an S lock an an IS lock for the object without the NOLOCK hint (or directive, if you prefer), and with NOLOCK you'll see a Sch-S lock on the object and an S lock on the HOBT. With a clustered index, the results are the same except that you won't see the S lock on the HOBT for the NOLOCK case. I'm not sure that the difference between two locks and one lock when you have a clustered index on the temp table justifies the use of NOLOCK here 🙂
Basically, it just doesn't really make a difference with local temporary tables, so while you're technically not doing much other than wasting a few keystrokes, I can see how it might have some negative effects in terms of perpetuating some bad practices and misconceptions.
Cheers!
SQL does take many fewer locks in tempdb for SQL 2012 forward. AFAIK, SQL did not do that in SQL 2008 and before. (Not sure about 2008 R2). I thought this forum was for SQL 2008 (or perhaps before). In older versions, SQL acquires shared locks row by row for SELECTs, in tempdb as with any other db. Naturally it must also release those locks: admittedly, that is so little overhead it can probably be ignored. I.e., a 100K rows SELECTed = a 100K locks. AFAIK, if you run the code in the article yourself on a SQL 2008 box, you should be able to see the row locks.
For local temporary tables, I always remember seeing object level S locks for SELECTs, even in 2008. I'll have to track down a 2008 instance (fortunately getting harder and harder to do!) and test to make sure I'm not suffering from a poor memory 🙂
Yes, you will always see object level locks, even today. But you also saw the row-level locks in SQL for #tables back then. SQL hadn't figured out the (seemingly obvious!) fact that it could forgo row-level shared locks on a temp table.
I understand that you will always see object level locks because of intent locks. I did mistype and left out a word, but I would think it might be clear from context anyway; apparently it was not. Even in 2008, I remember seeing only object level locks for SELECTs against local temporary tables. With that bit of clarification out of the way, obviously it just needs be tested. I should be able to track down a 2008 server later and post some results.
Cheers!
EDIT: Technically, I just realized, there was nothing wrong with my initial statement of what I remembered, because I did specify object level S locks, which you wouldn't see if the S locks were being taken out at the row level; you'd see an IS lock at the object level instead. That aside, at least now it should be ultra-clear what I was saying. I'll post those test results later today, hopefully. 🙂
I got to a SQL 2008 box (not R2). Here are the results:
--just from #table
Process 126 acquiring S lock on DATABASE: 465 [PLANGUIDE] (class bit0 ref1) result: OK
Process 126 acquiring Sch-S lock on OBJECT: 2:275153863:11 (class bit0 ref1) result: OK
Process 126 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = 275153863, index_id or stats_id = 0) (class bit0 ref1) result: OK
Process 126 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = 275153863, index_id or stats_id = 0) (class bit0 ref1) result: OK
Process 126 releasing lock reference on METADATA: database_id = 2 INDEXSTATS(object_id = 275153863, index_id or stats_id = 0)
Process 126 releasing lock on METADATA: database_id = 2 INDEXSTATS(object_id = 275153863, index_id or stats_id = 0)
Process 126 releasing lock on OBJECT: 2:275153863:11
Process 126 releasing lock on DATABASE: 465 [PLANGUIDE]
Process 126 acquiring IS lock on OBJECT: 2:275153863:11 (class bit0 ref1) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:0 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:1 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:2 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:3 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:4 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:5 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:6 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:7 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:8 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:9 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:10 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:11 (class bit0 ref1) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:12 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:13 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:14 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:15 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:16 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:17 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:18 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:19 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:20 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:21 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:22 (class bit20000000 ref0) result: OK
Process 126 acquiring S lock on OBJECT: 2:275153863:23 (class bit20000000 ref0) result: OK
Process 126 releasing lock on OBJECT: 2:275153863:11
Process 126 releasing lock on OBJECT: 2:275153863:0
Process 126 releasing lock on OBJECT: 2:275153863:1
Process 126 releasing lock on OBJECT: 2:275153863:2
Process 126 releasing lock on OBJECT: 2:275153863:3
Process 126 releasing lock on OBJECT: 2:275153863:4
Process 126 releasing lock on OBJECT: 2:275153863:5
Process 126 releasing lock on OBJECT: 2:275153863:6
Process 126 releasing lock on OBJECT: 2:275153863:7
Process 126 releasing lock on OBJECT: 2:275153863:8
Process 126 releasing lock on OBJECT: 2:275153863:9
Process 126 releasing lock on OBJECT: 2:275153863:10
Process 126 releasing lock on OBJECT: 2:275153863:12
Process 126 releasing lock on OBJECT: 2:275153863:13
Process 126 releasing lock on OBJECT: 2:275153863:14
Process 126 releasing lock on OBJECT: 2:275153863:15
Process 126 releasing lock on OBJECT: 2:275153863:16
Process 126 releasing lock on OBJECT: 2:275153863:17
Process 126 releasing lock on OBJECT: 2:275153863:18
Process 126 releasing lock on OBJECT: 2:275153863:19
Process 126 releasing lock on OBJECT: 2:275153863:20
Process 126 releasing lock on OBJECT: 2:275153863:21
Process 126 releasing lock on OBJECT: 2:275153863:22
Process 126 releasing lock on OBJECT: 2:275153863:23
--from #table WITH (NOLOCK)
Process 126 acquiring S lock on DATABASE: 465 [PLANGUIDE] (class bit0 ref1) result: OK
Process 126 acquiring Sch-S lock on OBJECT: 2:803155744:14 (class bit0 ref1) result: OK
Process 126 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = 803155744, index_id or stats_id = 0) (class bit0 ref1) result: OK
Process 126 acquiring Sch-S lock on METADATA: database_id = 2 INDEXSTATS(object_id = 803155744, index_id or stats_id = 0) (class bit0 ref1) result: OK
Process 126 releasing lock reference on METADATA: database_id = 2 INDEXSTATS(object_id = 803155744, index_id or stats_id = 0)
Process 126 releasing lock on METADATA: database_id = 2 INDEXSTATS(object_id = 803155744, index_id or stats_id = 0)
Process 126 releasing lock on OBJECT: 2:803155744:14
Process 126 releasing lock on DATABASE: 465 [PLANGUIDE]
Process 126 acquiring Sch-S lock on OBJECT: 2:803155744:14 (class bit0 ref1) result: OK
Process 126 acquiring S lock on HOBT: 2:1585274278655492096 [BULK_OPERATION] (class bit0 ref1) result: OK
Process 126 releasing lock on OBJECT: 2:803155744:14
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.