OPTIMIZE FOR SEQUENTIAL KEY In SQL Server 2019

Posted on by Erik Darling

OPTIMIZE FOR YOUR MOM


Thanks for watching!

Video Summary

In this video, I explore a new and exciting feature in SQL Server that addresses the issue of last page contention. Last page contention can significantly impact performance when inserting high-frequency rows into an index’s last page, leading to increased latch waits and writes to the transaction log. To demonstrate its effectiveness, I created a table based on Stack Overflow’s votes table and ran tests with both the `OPTIMIZE FOR SEQUENTIAL KEY` option turned off and on. The results were quite telling: when the option was enabled, there was a notable reduction in page latch waits, particularly for `PAGE_LATCH_EX`, which dropped from around 256 seconds to just 56 seconds within a 20-second sample window. However, I encountered some limitations due to running this on a VM with only eight cores and an NVMe drive, which might not fully leverage the feature’s potential. Despite these challenges, the feature shows promise for those facing last page contention issues and could significantly improve performance in suitable scenarios.

Full Transcript

Hello, out there, and out in the world. This is off to a good start. I’m not exhausted at all. So, I’m supposedly Erik Darling with Erik Darling Data, because no one else will have me. And I’m going to talk about something new, brand new. I’m sorry if this is going to make this video seem dated at some point, but brand new thing that dropped in CTRL. and then Trendish Kitberger after the pet peevels are data. Now if you want ver bösealles, 432 K кол nieuk Stelleיר. Vector Paradjes is a bad port security. And that got the question, again, I’m like the test provider of 433 Washington. Vector Gesch trenders And this is the idea that that controls the picture 333 Davies volvs on кр��ongeb� web video game.

And so, I’m sorry for a packet, but I’m going to take this video весь the image and list. We’ll start right now. And instead we need to look here for a substance. And the show you just took a tweet.

and beyond or a date that was in sequence that started you know at a first of the whatever and ended at the something else of the whatever that SQL Server makes them pretty bad guesses pretty bad off histogram guesses if your stats didn’t update and it’s not that it’s not has nothing to do with that this is to address something known as the last page contention issue what that means is that you if you have an ascending key so it is sort of related if you have an ascending or it could be descending to the ascending or descending key and you are you constantly find yourselves adding high frequency rows to the very end of the index that last page in the index is always going to be under contention because everything is going to want to write to it now this is a brand new feature and I have no idea how it works internally but I’m able to observe a little bit of the effects by looking at weight stats during execution so I have a table here that I designed to absorb some rights that’s based on the votes table and the stack overflow database and I’ve just renamed it votes insert and this is the option to turn optimized for a sequential key either on or off right I’m going to turn it off to show you at first and this is the procedure that I’m going to run using RML utilities O stress and RML utilities I’m going to use this to run this is the procedure I’m going to be running in a big loop I’m going to be running 150 threads on it that should be 150 because when I do 200 threads for some reason I run into some weird stuff so I’m going to make that 150 and I’m going to do it 2,000 times so we’re going to end up with 150 times 2,000 rows in the table by the time we’re done now this is a store procedure it’s boring or I’m just inserting some literal values I don’t want to do one I don’t I’m trying to start as simple as possible so as I do not confuse things down the line so this is going to be my procedure and this is going to be the table that we start with with optimize for sequential key off now I’m going to get some stuff ready I’m going to get oh that’s clear screen I’m terribly unprofessional of me so this is the RML command that I’m going to be running and over here I’m going to be using spblitz first the open source store procedure over at first responder kit org to measure what we do for a 20 second window all right all right so what I’m going to do is hit f5 to kick this off and then I’m going to switch right over to this window and I’m going to kick off the command to start running thing so hopefully this all goes very I’ve been practicing so hopefully this goes very smoothly boom boom boom all right so this is often running and blitz first is measuring what we’ve got going on and this is going to run for if I remember correctly about 15 or so seconds that’s why I have 20 seconds on the blitz first sample just so that when when this runs I have a little bit of padding time in case I screw something up terribly or I don’t know something goes devastatingly wrong now in that 20 second window let me blow up management studio so we can get a little bit more information here when I look at what happened in there we have 1500 seconds of right log weights we have 240 seconds of page latch ex weights 35 seconds of latch sh and 20.6 seconds of latch ex this all took about 17 seconds to run cool good we can see that stack overflows log file got a whole bunch of writes to it and that in that time 162 megs were written there sweet all right now let’s go back and look at what happens when we turn on the optimize for sequential key option turn that on all right so we’re going to drop our table and start from start from scratch again and we’re going to run the same exact stored procedure because I am far too lazy to do anything to have like two copies and try and switch and all that stuff so we’re going to do the same exact thing now this option is turned on now so we recreated with this able this turned on so we’re going to see if there’s any difference in I don’t know time or weight stats or any of that good stuff so we’re going to hit f5 wait we’re gonna get our command ready we’re gonna execute and then run this so now this is often running with optimize for sequential key turned on this is this is what is going to save everyone with last page content I wish there was a better way to judge if you were hitting last page contention like I wish it and it makes me wonder a little bit why there isn’t a why this isn’t the default like I wonder what this changes behind the scenes like I wonder what the trade-off is that this isn’t turned on immediately because this seems like kind of a no-ringer thing to have like because who’s gonna go like if you’re having last page contention problems either you’re very smart and you’re able to judge that or you aren’t or you’re not that you’re not smart you just don’t know what’s what’s happening you don’t know that you’re having this problem so it’d be nice if there was something like internally that would look at and indexes that how they get inserted to and say hey you look like you’re having some contention on the last page let me turn this knob for you I don’t know just something like that you know just something so that you can say it just runs faster that’s what I always like so this finished and this took about 19 seconds and what we can notice here is that there was a dramatic downtick in page latch EX that was around 256 seconds of it for the last 20 second sample now we’re down to about 56 seconds so this did improve another couple things that changed quite a bit our page lat or sorry latch SH and latch EX these are both way down these were at like 30 and 26 seconds before now they’re down to nothing what went up and the wall that I’m hitting with this is right log now there’s a few reasons for this one looks like let’s start off this is a VM that I’m running this in so clearly there’s gonna be some funkiness because VMs always bring some funk into the equation it’s hyper V so there’s not like I mean it’s not terribly complicated whatever I’m doing in here it’s a VM with eight cores and it’s sitting right now on my nice NVMe drive I have a one terabyte NVMe drive that gets crazy fast speeds on just about everything and this is the that’s the drive that this VM is sitting on so I don’t have a slow drive but it is a VM sitting on that drive the other thing is that this VM only has eight cores in it so it only has two log writers whether that’s the type of hardware that this this feature is aimed at is also up in the air I don’t have much much bigger hardware that I could test it on the server that this sits on has 12 cores I don’t think that really gets me much else as far as log writer goes even if I gave so it’s six physical cores 12 with hyper thing I don’t think I even do much better if I had all 12 cores going I’m already kind of weirdly oversubscribed giving it eight oh I know the hyper threading gods are cruel cruel people anyway uh so well this didn’t go great for me I am hitting that weird wall with right log and if I had a bigger server to test it on they got more log writers based on the Numa node core whatever crazy equation they came up with this might turn out better there is a huge reduction in page latch though so that that it does work it it’s probably just not working under my specific circumstances as well as it should um if you’re in a situation where you know you have more cores more log writers uh maybe you’re on physical hardware there’s a lot of things that could be different about this that would get us better right log throughput right now that’s the main thing that I’m hitting anyway I am very hopeful that for the people who have this issue this will be a good a good feature for and that it will help solve that problem I am very much looking forward to learning more about how it works internally I know that the I know that Pam LaHood has promised a blog post about it as soon as some red tape gets cut but I don’t know Pam if you just want to send me what you were going to write I’ll put it on mind we’ll just pretend it never happened all right let’s pretend pretend pretend that it was it was just me looking at things I broke out windbag I was really smart I don’t know whatever I’ll give you 20 bucks all right 50 bucks all right that’s all I got that’s all I got anyway thank you for watching I hope you learned something I hope you enjoyed yourselves and I hope you’re excited for this feature too see you

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Related Posts