July 18, 2015 at 10:50 am
Jacob Wilkins (7/18/2015)
So, just to double check, when you run the second INSERT after the TRUNCATE (all within the same transaction) in 2012 SP2, do you see the 6200ish page locks taken out before committing/rolling back, as I do in my test?
No. 25 locks total, database, metadata, Sch-S, Sch-M, locks on some system tables and page locks for pages that are listed as deallocated after the commit completes, pages that the truncate deallocated but the insert didn't reuse.
And, :blush:, I've been connected to my 2014 instance, not my 2012 instance for all of these. Whoops.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2015 at 11:03 am
Interesting...I hadn't even bothered to try 2014 yet. There were so many variables to control for just with 2008 R2 and 2012 🙂
I guess at some point I'll extend my test to 2014 as well, just to be thorough.
So even when you try 2012 SP2 and not 2014 you get different results? I'll have to figure out why 2012 SP2 behaves the same as 2008 R2 SP3 for me and not for you. That one's strange.
When I get back near a computer I'll post the exact script I'm using, along with any specs/configurations that might be relevant. I'm quite interested to see what the difference is.
Cheers!
July 18, 2015 at 11:03 am
GilaMonster (7/18/2015)
I think this needs writing up in more detail. JT, do you have a blog? Do you want to write this up or may I?
I've been meaning to start one, but I'm not sure I could do this topic justice. Feel free to write it up on yours!
July 18, 2015 at 11:37 am
As I said, I was connected to the 2014 instance for all of these tests.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2015 at 12:07 pm
Understood. I just wasn't sure how much of the post was from after your edit, so I just wanted to double-check whether that lock info in it was for 2014 only or for your 2012 SP2 instance as well.
Cheers!
July 18, 2015 at 12:30 pm
I'm not spending another couple hours redoing this all on 2012. Not on a Saturday evening.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2015 at 12:34 pm
That's perfectly understandable! Testing fairly esoteric SQL Server behavior is not high on the list of Saturday evening-appropriate activities 🙂
I'll tackle it from the other end and run the tests on 2014 to see if I see the same locking info as you did. I imagine I will, which would mean additional improvements (improvements probably, changes definitely) were made in 2014.
I'll post those here shortly.
Cheers!
July 18, 2015 at 1:21 pm
Confirmed. My 2014 instance behaves the same as yours. I end up with 25 locks held after the second INSERT.
Following what you posted regarding the Lock Acquired events, I also confirmed that in all three versions the process does successfully take out an X lock on the table, and holds it for the duration of the second INSERT. It just seems to be released as soon as the INSERT is completed, as you pointed out.
That disconfirms both my hypotheses about why it takes the page locks. It looks like it might just be another "feature" (read, bug) that survived to 2012 but ended up getting fixed in 2014.
I too am losing some motivation for Saturday-related reasons, so I'm going to revisit this with more rigor tomorrow.
Cheers!
July 18, 2015 at 1:36 pm
Check the headers of the pages which have the page locks at the end of the second insert. See if they're pages which are no longer allocated. Pages deallocated by the truncate have to be locked until the end of the transaction (or otherwise flagged as not reusable, eg still allocated) in case the truncate is rolled back.
If they are, then the difference is in how SQL protects the deallocated pages, not in the locking behaviour of the insert.
Edit: Thinking about it some more, that's probably what the 6000 page locks are in both 2008 and 2012. It's not that the second insert is not escalating, (it is, that's been proven). It's the locks on the deallocated pages from the truncate. 2014's either deferring the deallocation to a background spid (which holds the locks, not the original session), or is taking locks at a different physical level. I think I remember seeing an allocation unit lock. Those page locks can't escalate to table, as the pages are no longer part of a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2015 at 10:04 am
Well, the plot thickens. At least in 2008 R2, the page locks held at the end of the second INSERT are for the new pages allocated by the second INSERT.
I grabbed the file id and page id for each of the pages locked at the end of the second insert, and checked the headers using DBCC PAGE. They showed as allocated.
At first I didn't think that was all that strange, since I thought SQL Server didn't actually perform the deallocation until the transaction committed anyway (seems confirmed here https://msdn.microsoft.com/en-us/library/ms177495.aspx and here http://sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth).
The TRUNCATE just seemed to take an X lock on the existing allocation unit and mark it for deferred drop. That I confirmed by checking the status of that allocation unit in sys.allocation_units before and after the truncate.
After committing the transaction, the pages associated with the original allocation unit were indeed marked as deallocated. The catch is that those were not the pages that had been locked after the second INSERT. The pages locked at the end of the second INSERT were associated with the non-marked-for-deferred-drop allocation unit, and still showed as allocated after the deferred drop had run.
It seems for some reason that despite successfully obtaining an X lock on the table, SQL Server is still taking page locks for the second INSERT. I think it has something to do with the initial allocations, since the difference between the first and the second is that the with the first INSERT, the initial allocation was done outside the transaction (the CREATE TABLE). If you include the CREATE TABLE in the transaction, then the first INSERT behaves the same as the second.
At lunch I'll check in 2012 to see if that specific behavior is the same.
Cheers!
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply