September 3, 2015 at 4:38 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2015 at 1:08 pm
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!
September 4, 2015 at 1:23 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 4, 2015 at 1:56 pm
The number of rows in the table and whether the query is parallelized are both completely irrelevant to lock partitioning. Microsoft's example to illustrate lock partitioning is on a table with one row. See here: https://technet.microsoft.com/en-US/library/ms187504(v=SQL.105).aspx
That's easy enough to confirm. Just run the SELECT within a transaction with a HOLDLOCK hint, and then look at the locks held for that request_session_id in sys.dm_tran_locks before committing/rolling back. You'll see a bunch of object-level S locks with resource_partition_id ranging from 0 to 1 less than the number of cores.
Also, how can it be clear that SQL Server is taking out row locks? That output shows only locks on objects (and those had a lock partition specified; that's the last number in that lock resource number).
If row locks were being taken out, you'd see row locks as well as IS locks on pages. Neither of those things are being taken out, just the object locks.
Again, that's easy enough to test by creating a permanent table with the same data and running the same SELECT against it. You'll start to see S locks or IS locks on pages (depending on whether it chooses to take S locks at page or row level). The output of 1200 isn't always complete, though, so if you want to be ultra sure, then just run the query in a transaction with HOLDLOCK and query sys.dm_tran_locks before committing/rolling back, or capture lock acquired events with a server-side trace.
As always, it might be that I'm just really confused, but so far I'm not seeing it 🙂
Cheers!
September 8, 2015 at 9:09 am
Munabhai (9/1/2015)
Thanks all. Special thanks to Luis C.I got solution with the help of Luis C.
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN CAST('' AS VARCHAR(6))
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN ''
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN CAST( DATEDIFF(DAY, date1,GETDATE()) AS varchar(6))
ELSE
CAST(DATEDIFF(DAY, date1, date2) AS VARCHAR(6))
END AS PaymentDays
FROM #temp;
This can be further simplified:
SELECT date1, date2,
CASE
WHEN date1 IS NULL THEN CAST('' AS VARCHAR(6))
ELSE CAST(DATEDIFF(DAY, date1, ISNULL(date2, GETDATE()) AS VARCHAR(6))
END AS PaymentDays
FROM #temp;
It makes one assumption that seems reasonable.... Your data from the date1 and date2 fields does NOT contain the value '1900-01-01'. If that's true, then this simplification should work just fine, and should be easier to understand.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply