All About SQL Server Stored Procedures: Some Notes On Locking Hints
Video Summary
In this video, I delve into the fascinating world of locking hints in SQL Server queries, addressing some common misconceptions and highlighting their practical applications. I start by discussing how even with a good index in place, locking hints are merely suggestions to SQL Server, which can escalate locks based on the situation. This leads us to explore useful scenarios where locking hints are particularly beneficial, such as preventing race conditions under read-committed isolation levels and ensuring fully parallel inserts into temporary tables. I also cover some potential pitfalls, like how clustered indexes or identity columns can interfere with parallelism, and demonstrate practical examples using T-SQL code to illustrate these points.
Full Transcript
Erik Darling here with Darling Data and got, I don’t know, mildly educational video here about some stuff with locking hints and SQL Server queries. This is by no means a deep dive into every single locking hint. Most of them are fairly easy to figure out, but I do want to show you some misconceptions about locking hints. And then some useful locking hints, situationally useful locking hints. And then some potential gutches with a couple other locking hints. Alright, so that’s what we’re going to talk about here. I probably should have progressed that slide before I started yakking about locking hints. But anyway, before we get into all that nonsense, let’s talk a little bit about the health and and and I class, thatare.
support my efforts to bring you these interesting educational SQL Server videos you can sign up for a membership right about here in the video description for as few as four dollars a month pre-tax before before it comes to me it does get taxed YouTube does take 30% out of that so I’m looking at basically like three one dollar scratch tickets and a Lucy from the bodega for every subscribe for every paying member if you can do that great if you have already blown your financial load this month on scratch tickets and Lucy’s at the bodega well you’re a person after my own heart and you would have to do something a bit less expensive in order to show your undying loyalty to being a data darling you can like you can comment you can subscribe and if you would like to ask a question on office on my office hours episodes where I take a question excuse me that you ask semi privately and I answer it very publicly this link is also available down in the video description as well and you can submit a question and I will answer it assuming that it is legible and vaguely understandable if you need help that goes beyond just asking strangers random questions on the internet I am of course available to do consulting for SQL Server all sorts of good stuff health checks performance analysis hands-on tuning responding to performance emergencies and of course making sure your developers smarten up and start not causing performance emergencies so all of that stuff is available on this beautiful face if you would like some recorded versions of this beautiful face teaching you about SQL Server you can get all 24 25 hours of my performance tuning content for about 150 US dollars and that will last you the rest of your life good for you there upcoming events we are still looking forward to SQL Saturday New York City launching May the 10th taking place in Times Square beautiful crime-free clean friendly Times Square where you can come and you can go to the Microsoft offices there and you can you can learn all about all sorts of data stuff so that’s a that’s a happy that’s a happy time for everyone you can get lunch I’m served by me not made by me but I’ll I’ll hand you a bag maybe maybe I’ll just let you take a bag if you’re really nice I’ll let you take two but you got to be nice anyway let’s talk about some locking-in stuff because it is fun and interesting now what I want to show you here is that even with a good index in place and even doing something like this where we are limiting the number of rows that come out of that we are like planning to modify the roll lock hint for a SQL Server is merely a suggestion all right so if we do this and we run the code in here SQL Server does not SQL Server rather SQL Server does maybe start taking roll locks but it does escalate those roll locks up to an object level lock if we were to change this to a much higher number like 90,000 and we were to rerun this SQL Server you’ll see that you know in this case SQL Server does actually take key locks right we do that stuff in here right so here’s all our key locks and well I mean there are some some page locks as well but all of the all of the X locks right we have IX locks on pages and objects and stuff those aren’t like the that those aren’t like the real exclusive locks but the X locks for these we do have those actually happening here right so for the actual X locks those are only taken at the row level but for everything else we have just intent exclusive but when we had like like when we had to sift through more stuff in that that that top 5,000 query when we had that set to 30,000 SQL Server was like well that’s a lot of key locks that’s a lot of roll locks we’re escalating that to a table level lock right remember that locks go from row to object or page to object they do not go from row to page to object so even the row lock hint can be ignored by SQL Server where I end up or rather the where I see people like forgetting that they need lock hints is covered in another great post by my dear friend and Canadian Michael J Swart I look forward to you joining me as an American in the 51st state my friend see you soon so we’ll have passport free friendship we do like I do see people messing this up a lot so when you use merge and that merge has multiple actions right tip cop most commonly update insert right if you just have a single action merge I mean granted there are like some interesting use cases for for that for doing that with inserts but usually like a combo merge thing you do need to either set the transaction isolation level for that merge statement to serializable or hint the serializable isolation level with your your the table you’re merging into okay so important stuff there a place where I love using locking hints is in places where people did not know that they were subject to potential race conditions in their code I’ve talked about this a bunch in some recent videos so I’m not going to spend too too long on it here because we have some other stuff to cover but if you are doing anything that could be negatively affected by concurrency either while your query is running remember read committed not a very strict isolation level allows all sorts of other stuff is not a snapshot of your data and you need to make sure that you are actually getting the current top thing to do something with like this then you need to make sure that nothing else can mess with your data while you are in the process of finding that data right so you need locking hints to prevent race conditions even under read committed the default pessimistic isolation level brace conditions are not alone with often in optimistic isolation level land it can happen to you anytime anywhere another place where I lay I end up using locking hints a lot is when I am putting data into temp tables now table variables without doing some real crazy tricks you can’t get a parallel insert into them but when I’m dumping data especially a large like a fairly large amount of data into a temp table well I usually want to make sure that I get a fully parallel insert while that’s happening there are some things that will prevent that for example if you have a clustered index on the table if you have a well clustered primary key but really any nonclustered index right or if you have an identity column on your table you will mess up SQL Server’s ability to do a fully parallel insert it’s worth noting that if you need something to behave like an identity column in your temp table just replace the I mean you can just like remove the identity attribute from whatever column that is and just use row number in your select list to generate that auto incrementing number row number does not have this limitation so you can get around a lot of problems with that so it’s any of this stuff of course any nonclustered index like I said that’ll also mess you up but we’ll get to that in a minute so let’s run this query and these there is not a tremendously big performance difference for what I’m doing here but there is but the execution plan is a good enough example and there is some benefit that you can see from these so in this query plan note that we do have a parallel a scan of the clustered index right we can have a little parallelism operator here but then we gather streams almost immediately afterwards and the insert into the temp table is done outside of the parallel zone right this marks the end of the parallel zone right here and this happens single threaded this all takes 1.4 seconds we can see that right there now we can get around that by adding a tab lock hint to the insert so again no no clustered index no identity column no primary key no nonclustered indexes it’s got to be a heap for this if we run this we will get a fully parallel insert and this will shave a bit of time off that notice now the gather streams operator is over here the insert is with before that and it has our little racing stripes on it and rather than taking like a second and a half it takes about 800 milliseconds so this is not for a ton of rows obviously the more rows you get involved with here the better this is as a hint to including your queries you can get very very similar behavior by doing select into right so we’re going to do we’re going to have a little select into magic here and we will see just about the same just about the same execution metrics on this one you know still 800 milliseconds with the table insert within the parallel zone there’s our little parallel racing stripes and the parallel zone ends here so you know I do get questions sometimes like hey is it better to create your temp table or just do select into and you know my answer is that it is somewhat situational I am generally okay with select into but you should be careful with the way SQL Server interprets with the way SQL Server interprets expressions with select into remember that like the beauty of creating a table is you fully control the data types the data lengths for strings the nullability of columns you get to pick all that stuff when you create the table you don’t really you don’t really get that with select into when you if you if you start like having like case expressions or you start concatenating strings to together or like you know you’re doing any math in your select list there’s all sorts of stuff where SQL Server might either implicitly infer or convert your expression to some other data types so if you are getting weird inconsistent results with select into in that regard you may want to think about just creating the table with the types you want but if you’re doing select into and you’re getting the parallel insert and then you do the create table insert in your plan slows down at all you most likely can get around that just adding a tab lock hint to the to the to the to the insert so that you get the fully parallel insert which is commensurate with the behavior when you do select into and like I like this a lot as a sort of a thing now some one like one thing that I always get questions about because like you know like a big part of my consultancy is helping people get away from bad habits and you know of course no lock is a fairly well known best habit and while we’re while I’m talking through you know various ways to get rid of no lock is generally like optimistic isolation levels are a much better idea people are like but dirty reads I’m like but wrong because optimistic isolation levels don’t allow dirty reads no lock and read uncommitted allow dirty reads optimistic isolation levels you can have like stale reads when you are like read like if you’re reading an older version from the version chain then you would expect right because you’re not reading potentially the most up-to-date version of the row but you’re not getting dirty reads but what a lot like you know where the conversation always seems to go is like well if I take out the no lock in to get blocked yes if I use no lock I can get the I can get a row back yes but what’s in that row and then ultimately like someone will ask well what about read past what does read past do read past is situally situationally a very cool and interesting hint if you’re not that’s why you’re not worried about what’s in the right place if you’re not doing something where you’re not doing something where you truly don’t care about rows that are locked you’re okay with skipping over those so what I’m going to do here is just run this simple update for one row in the users table right so begin train update increment reputation by one where user right where the ID equals 22656 so we run that and this has an open transaction and we’re often running and over in this window I have three queries I have this top one query right where I’m saying give me the top one user by reputation descending this will get blocked right because this thing is open and doing something this query with no locking hint gets blocked if I do read past well notice that I get this row back here right before I go on to no lock I want to show you what happens when I roll this back right so I’m going to roll back that transaction if I run read past with this now I get John skeet with a reputation of 1 million 47 thousand 863 right so this is the top reputation in the table coming back over here if I run this update and I do that with the transaction open and running read past skips over John skeet and I actually get the second highest reputation in the table if you’re okay with this if you’re okay with skipping over locked rows and just getting rows and just getting rows that are not locked return to you read past might be okay but that may not be a okay with your users right like that might not be what they’re quite after if they’re looking for stuff like this and of course with no lock well if we run this now if we run this now if we run this now if we run this now and we look we do get like John skeet again but we get John skeet with his reputation incremented by one is this a big deal for this hmm but if you had an optimistic isolation level what you would get back is his original reputation which is of course make sure that’s done this right so is no lock a big deal here probably not is read past a good fit here probably not is this query getting blocked annoying very much so but uh you would get around all of any any sort of like questionable discrepancy if you were using an optimistic isolation level here under both read committed snapshot isolation and snapshot isolation you would still see John skeet and you would still see him with the high reputation that ends in three rather than the one that ends with four right so his reputation prior to the update is that always going to be correct maybe maybe not uh the the important thing to understand here is that uh not every query needs the same locking guarantees and granularity uh there may be times when you can get away with no lock there may be times when you can get away with read committed there may be times when you can get away with a read past hint i find that most workloads work best under read committed snapshot isolation with specific queries targeted to use a slightly different uh isolation level or locking um locking mechanism when they’re reading through data so that part is up to you right if your workload is like you know if your database is under the default read committed isolation level and the only way for you to get queries to return anything ever is to use no lock hints you are much much better off with an optimistic isolation level than you are continuing with the no lock hints so that’s about it here uh that i mean that that is the end of the demo file you can tell there are no more line numbers after this so yeah we have run out of things to talk about so thank you for watching i hope you enjoyed yourselves i hope you learned something and i will see you in uh another video another time we will we will talk about some more store procedure stuff so thank you for watching goodbye
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.