Stored Procedure IF Branching and Deferred Compilation In SQL Server
Video Summary
In this video, I delve into the concept of deferred compilation in SQL Server, specifically focusing on how it can mitigate issues related to if branching within stored procedures. I explain that when SQL Server initially compiles a plan for a procedure containing if branches, it often does so based on initial parameter values, which can lead to unexpected behavior later when different parameters are passed. To illustrate this, I demonstrate an example using temporary tables and show how deferred compilation can help in such scenarios by deferring the actual compilation until the temp table is populated. Additionally, I provide tips for those interested in consulting services or training, including reasonable rates for my consulting work and membership options to join our channel community.
Full Transcript
Erik Darling here with Darling Data. And this video is going to be about how deferred compilation can help with some if branching issues that you might see in SQL Server. Primarily, one of the big problems that you run into with if branching is that all of the plans in your procedure will get compiled when SQL Server first compiles a plan for the store procedure, right? So if you have if branches and you’re thinking, well, the, like the compile time things that I pass in, SQL Server is never going to look at this branch because we’re not going there yet. Wrong. It compiles a plan for the entire procedure based on the initial set of compilation values. So if some of your parameters have null values assigned to them, well, you might, you might run into some very strange stuff when the, when that branch actually does execute because you will be executing it with values. So that can get very strange. So that can get very strange. But there are, there are of course exceptions to that. And I don’t mean like option recompile at the statement level or with recompile at the store procedure level. There are things in your store procedure that might make that untrue that cause deferred compilation. So we’re going to look at an example of that today. If you would like to hire me for consulting, because that’s what I do for a living. I consult and I make SQL Server.
I deliver faster in exchange for money. It’s my, it’s my gig. It’s my whole thing. And remember, my rates are reasonable. Yeah. All right. Been a while since I said that on camera, I think. Uh, if you’d like to buy my training also at a reasonable rate, you can do that. Uh, if you would like to become a channel member, you have a variety of reasonable rates to choose from. Uh, monthly plans are as low as $4. Four. Crazy, I know. Uh, if you would like to become a channel member, uh, that’s, that’s the way to do that. Uh, you can ask me office hours questions, uh, you, where that, that is free. Um, if you, it’s gonna be $25, $50, because, um, you’re spending clearly, you, you’re all spending far too much money on illicit substances. And, uh, it is degrading your ability to form coherent questions.
So, you need to start giving that money to me instead. I’m, I’m a safe keeper for, I am, I, I will get incoherent. Uh, and of course, if you like this channel content, please do like, subscribe, tell a friend, all that good stuff. Uh, you can tell a family member too, if you have a family member who you would like to torture with. These videos. Uh, advanced T-SQL training will be coming to DataTune Nashville March 6th and 7th and March 13th and 14th at Data Saturday Chicago. Uh, two wonderful events. Get out there in the world, you know, wear some sunglasses, look cool. Maybe we can get incoherent together. Who knows?
Uh, two wonderful events. Don’t know what that would look like. But, uh, for now, we must, we must muddle through December, January and February, and we must do that somehow. All right. Let’s look at this thing. So, um, when you use temporary objects in store procedures, um, for everybody, that’s going to mean temp tables. For people in certain situations, uh, certain SQL Server situations, uh, that would also mean table variables.
If you are getting the table variable deferred compilation, uh, intelligent query processing feature, uh, in your query plans. Um, only you can find that out. I can’t tell you that. You have to figure that out. But for temp tables, uh, you can see this happening. So, um, let’s actually add, uh, down here. I want to add a dbcc free proc cache.
Go just in case. So we’re going to look at this store procedure. Uh, I mean, a temp table is going to get created, but it’s not going to get used, right? So like we’re going to have two joins to a temp table, the stuff, I don’t need to put data in the temp table. It’s just to show you that the temp table, uh, will cause deferred compilation. Right.
And we’re going to have that in both of these queries, but let’s make sure this store procedure is run to not use a temp table. And let’s do a dbcc free proc caching and let’s run the store procedure using the reputation parameter first, right? So if we do this and we look at the execution plan and granted, I haven’t created any helpful indexes for this.
That’s really not a performance demo. It’s just a behavioral demo. And we look at the parameter list. We will see that, uh, over here we have, uh, compile and runtime values for, uh, reputation is 500,000, right? So, uh, the initial compilation for the store procedure was done with reputation at 500,000.
And that is also getting the actual execution plan, the runtime value for this execution. The reputation parameter gets used in this query against the users table. The score parameter gets used against the post table in this query.
So, uh, if we run this now and we say, uh, for score equals nine, nine, nine, nine, that’s four nines. And we look at the execution plan and we do the same thing as last time. We look at the parameter.
Where are you hiding from me? There we go. We look at the parameter list. We will see that the runtime value was nine, nine, nine, nine. But that’s not right.
That’s not right. The, uh, compile time value for this is null. All right. So SQL Server sniffed a null value and it’s doing cardinality estimation based on that null value. We have a one row estimate here, right?
So we got 12 rows back. There was one when we SQL Server estimated one row for null. We found 12 rows, uh, uh, for based on what we were looking for, which I don’t know, is that weird? Maybe, but, um, you know, not, not really.
There were four rows with a score greater than or equal to nine, nine, nine, nine. But, uh, there was a one row estimate from the null compile time value. So, uh, this is, you know, something worth noting.
If you are, if you have lots of if branching in your store procedures and there are lots of different parameters that get used in different if branches. And as you pass things in, you might actually supply different values up here.
Things can get pretty weird, right? It’s not, it’s like, like a, like another layer to a parameter sensitivity issues. But what I want to show you now is if we go and we say we put these left joins in, and I’m only using these very spurious left joins to show you that the behavior of involving a temp table.
This is the deferred compilation that you get, uh, from all temp tables and some table variables depending on your SQL Server version edition and, uh, all that good stuff. Uh, we rerun this and recreate the store procedure.
Let’s clear out the plan cache just to make sure. And what we’re going to do is the same old boring thing here. Well, we run this for reputation equals 500,000 first. And we’re going to get about the same thing happen for the first execution where the, um, compile and runtime values are both 500,000.
But now if we run this for score equals 9, 9, 9, 9. And we look at the, look at the execution plan. Of course, you got to flip that around a little bit.
Now in our parameter list, we have a compile and runtime value of 9, 9, 9, 9. So if you’ve heard me go on and on about if branching and store procedures and how, you know, like, like, like the behavior that we looked at without the temp tables can mess you up. Um, if you’re using temp tables in your if branches, you might not see that exact behavior because SQL Server might be deferring compilation of those, uh, queries until, uh, the temp table is populated, uh, and the query runs against them.
Right. So until SQL Server has to compile a plan that uses that temp table, then you might see this instead of the other behavior where it’s sniffed and null, like in the last set of, uh, runs of this. So just kind of something kind of interesting there.
Um, that’s about it on this one is usual. I don’t know if that there’s a very loud truck horn outside. I don’t know if that’s picking up on the microphone and it won’t know until the recording is complete, but it was perfectly timed with the silence there.
Anyway, I hope you enjoyed yourselves. I hope you’ll learn something and I will see you in tomorrow’s video. Uh, I forget what day tomorrow is.
I usually do, but we’re going to be there and we’re going to have fun. All right. Thank you for watching.
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.
A Neat Trick with Using SELECT to Assign Variable Values
Video Summary
In this video, I delve into the nuances of variable assignment in SQL Server using `SET` and `SELECT`. Specifically, we explore how these commands behave differently when no rows are found, highlighting the peculiarities of `SET` where it overwrites variables with nulls, while `SELECT` maintains or overwrites them based on the result set. I demonstrate a clever workaround to make `SET` behave more like `SELECT`, especially useful in scenarios involving multivariable assignment within loops. This technique ensures that your code remains robust and avoids potential infinite loops or unexpected behavior when dealing with non-existent rows, providing a valuable lesson for anyone working with dynamic SQL assignments.
Full Transcript
Erik Darling here with Darling Data. And we have a fun little video today where we’re going to talk about how, like, if you, like, back in, like, the, I think if you watched, like, the, like, the earlier, like, Learn T-SQL, either, if you, if you were kind enough to purchase the course, well, thank you. But if you saw some of the, like, preview videos here on YouTube, one of, in one of them I talk about the difference between, some of the differences between set and select when assigning variable values and how the behavior is strange and how, like, set is kind of annoying because you can’t do, like, multiple variable assignments with it. But you can with select, but select and set act differently when a row is not found and how variable values are either maintained or overwritten with nulls.
So we’re going to talk about how to make select behave more like set in that video when you’re doing multivariable assignment. I mean, technically it would work with single variable assignment too, but you may not want to write this into every single query. Anyway, down in the old video description, boy, oh boy, if you, if you want to hire me for consulting, you, you can do it.
The power is in your hands. As long as the credit card is in your hands, the power is in your hands. You can buy my training.
You can become a supporting member of the channel. Likewise, you can do things for free. Free stuff.
Ask me office hours questions, though that may soon cost $5. $10. Maybe up to $25. Because some of the questions that come in, I think, I think, I think some, some form of angel dust was involved with that.
And if you enjoy this content, you can, of course, like, subscribe, and tell a friend or two or three or a thousand. However many you have, just break out the old Rolodex, flip through, shoot everyone a fax, say, hey, check out that Darling Data YouTube channel about SQL Server. Out in the world, yeehaw!
Nashville and Chicago in March. Back to back weekends, 6th and 7th, 13th and 14th. I will be doing pre-cons at both on Advanced T-SQL. And you should go.
You should come see me. You should go to the events. You should get out and support the wider data community before, you know, I don’t mean to sound morbid here, but any time you do something could be the last time you do it. So, get out into the world and do some, do some good and fun things, like come to data platform events.
Anyway, Merry Christmas. Let’s look at how to make set behave more like select. So, the first thing we have to do to set this adventure up is look at a couple selects from the users table.
You will notice that if I select, and I’m going to show off a fun SSMS 2022 thing here. I’m going to dynamically zoom in on these results. ID5 returns Mr. John Galloway.
I don’t know John personally. He seems wonderful. I appreciate when, I appreciate that he is in my first name boat where everyone probably looks at his name like in an email or something like that and still finds a way to spell it incorrectly when they type it. So, we have John Galloway here for ID5.
And then for ID6, we turn no rows, right? There is not a single result down here. There is nothing. I’m not hiding anything from you, I promise. But that is for ID6.
Now, if we were to do this, let’s say under normal circumstances, and we were to run this set of queries here, where we declare some variables, and we set them equal to stuff for ID5, and then we look at the contents, and then we set them equal to ID6, and we look at the contents. These are the results that we get back, right? Because no row is produced for ID6, nothing is overwritten, right?
We may just have the same variable values in here. Golly. Anyway.
Pardon me. We have this here. So, this can get people into a lot of trouble if they are unaware of this behavior and they are assigning variables to something in a loop. And then all of a sudden, they stop finding new values.
And so, they just keep either assigning nothing to this row and, like, reprocessing whatever row is in that current set of variables. Or, I don’t know, just, like, infinite loop, right? They’re just, like, they’ll, like, process this thing multiple times, or they’ll end up in an infinite loop processing the same thing over and over again, right?
Not a good time. Something to be very much aware of when writing this sort of thing, writing anything that does variable assignment. Now, set, of course, doesn’t do this, but you can’t do multivariable assignment with set because what happens is you quickly find out you can’t do it, right?
Unless you hit the, like, we would have to write three separate set queries to the user’s table in order to have that work. So, something that I picked up while working with a client was that you can have a dummy row, right? And you can do something like this with the values clause.
You could, of course, just say select null or something if you felt like it. You don’t have to use values, but I like to use values because I don’t have enough of them. Quite frankly, there’s a real shortage of values in the Darling household.
So, every opportunity I have to use values, gosh darn it, I try to get it in there. So, what you can do is something like this where you say select from values and then outer apply to your query that would do the variable assignment and then at some point do the variable assignment out here. You couldn’t do it in here.
That wouldn’t work. So, if we do this and actually I think we have a few things to run down here. Run all this stuff. Now, what we have is John Galloway set when we hit ID 5 and then we have overwritten those rows with null.
Or rather, we have overwritten those variables with null values when we did not find a row for ID 6. So, this is a nice way of making sure that your loop hits some null check condition or something when it doesn’t find anything to do. And you can do this without having to like figure out like, hey, what are the current, like what are the last set of values that I just processed?
Do I have that same set of values? Or like something like that. You know, I guess you could, you know, if you’re feeling fancy, you could do something with like row count and be like, well, did I find any rows there?
No? Okay. Well, this is just another way of doing that. This might actually be a little bit more flexible in a lot of ways because, you know, if you find nulls, you might have to go do something else or something.
Right? You don’t want to necessarily like break out of stuff. You just might want to say, hey, these are all null. Or, hey, one of these is null and it shouldn’t be.
Or, hey, two of these are null and they shouldn’t be. Or something like that. Right? There’s like all sorts of things that, you know, having this assigned would allow you to, you know, maybe explore within your loop and say, hey, that ain’t right. So, just a neat kind of trick that I learned about.
And I thought you would enjoy. That’s all I had for this one. Thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And I will see you in tomorrow’s video. I do forget what the topic is. You’ll have to forgive me. But it is one of these three windows over here. So, there is something more to talk about.
Isn’t that wonderful? All right. Thank 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.
A New Query Hint to Override Batch Mode on Row Store Heuristics
Video Summary
In this video, I share an exciting new query hint that I recently learned from a fellow data enthusiast, who prefers to remain anonymous for now. This hint allows you to override SQL Server’s batch mode heuristics, which can be particularly useful when the default settings don’t quite meet your needs. I demonstrate how this works by showing a simple example where applying the “use hint override batch mode heuristics” option changes an index scan and top sort from row mode to batch mode on rowstore, highlighting its potential benefits. Additionally, I point out some quirks in SQL Server Management Studio 2022 that might affect your experience when using this feature, such as a keyboard shortcut issue with the execution plan tab. Overall, this hint offers a handy workaround for situations where other methods of enabling batch mode on rowstore are not feasible.
Full Transcript
Erik Darling here with Darling Data. And we have an exciting video for you today. Because, not me, but someone out there in the world who is very handy with a debugger, I’m going to protect their name because I don’t necessarily want them to get yelled at by Microsoft in case this is explosive information. But there was a new query hint that I was made aware of from Russia with love. And it is something that I think I am going to find very, very useful in my life. And it is a query hint that allows you to override the batch mode heuristics. And by this I mean SQL Server in 2019 introduced the batch mode on rowstore feature.
The problem is that even with that feature, like SQL Server 2019 Compat Level 150 Enterprise Edition, even if you check enough boxes to get to the point where SQL Server will start applying heuristics to your queries, those heuristics may not always kick in when you want them to. And you may like have to find yourself doing stupid things in order to have batch mode on rowstore kick in for your queries. And you may not want that because the heuristics might be stupid. If you remember my parameter sensitivity training videos, I talked a lot about the parameter sensitive plan optimization and how it has heuristics and decides when or when not to kick in, which you may or may not disagree with.
And then furthermore, of course, we talked about its poor bucketing strategies, but that is way too much for this video. We have other cool stuff to talk about. So down in the video description, you’ll see all sorts of helpful links. You can hire me for consulting, you can buy my training and you can become a supporting member of the channel.
These are all things that do require you giving me money. You can do things for free. We all like free things.
You can ask me office hours questions. You know, I do appreciate a thoughtful question from the audience. And of course, if you enjoy this content, you can like, subscribe, tell a friend, all that good stuff.
If you want to see me out in the world, it’s going to be a few months, but hey, that’s okay. It’s going to be cold and you’re not going to, you know, you wouldn’t like me when I’m cold anyway. Data Tune in Nashville, March 6th and 7th.
Tickets are on sale for pre-cons there. And Data Saturday, Chicago. Well, tickets are on sale for pre-cons and a regular event in both of these things. And then Data Saturday, Chicago, March 13th and 14th.
I’ll be there as well with the pre-cons. So buy my book. Buy my book. Buy my book.
And so come see me out in the world when I’m nice and warm. Maybe I’ll wear some shorts. Who knows? We might get crazy. Anyway, I remembered to change my deck image because we are now in the December month. And I better watch out.
I think there’s a mistletoe on there. So there might be some smooches in your future. But it is a Christmas miracle. All of our friends have all of their arms. I think little Timmy over there grew his arm back.
So we can all… We still have a ghost in here. A leftover ghost. A Christmas ghost. Not sure if this is past, present, or future.
But we have a Christmas ghost. Leftover from Halloween. So I guess we didn’t take all the decorations down. Some of our decorations have been repurposed.
Anyway. Let’s talk about this hint. So first thing I’m going to do is just show you this wonderful hint. It is a use hint.
So we need to do the whole option thing. And we need to say use hint. Override batch mode heuristics. Rolls right off the tongue as many of these hints do. This hint will not appear in the sys.dm exec valid use hints DMV.
Much like some of our other favorite hints like enable parallel plan preference. Which, you know… Again, since Microsoft started using this in their code to create the disk and vector indexes.
I assume it’s safe for everyone to use in production. Because Lord knows they don’t test crap. But we’ve got this one now.
How nice. How lovely. So the first thing I want to do is just show you the query plan for this without the hint. Applied.
Applied. And if we run this and we look at the execution plan. It’s nothing terrible. But we can see that this index scan… This is not like, wow, look at how much better performance is. This is just to show you that it’s there and functional.
Then this is like the first demo query that I had where I could show you that quickly. And I just wanted to get this out quickly. So, you know, I guess more interesting stuff will happen in the future. But we can see the actual and estimated execution mode for this index scan is row.
Likewise, we can see this top end sort also occurring in row mode. All right. Cool.
Most of the other operators in the plan are not eligible for batch mode anyway. For example, the repartition streams. But rather the parallel exchanges. So gather streams, distribute streams, gather streams, blah, blah, blah.
Nested loops, not eligible. This sort would be eligible. But this top would not be eligible. But now… Actually, this is a good time to tell you about a strange buggy thing that currently is a problem in SSMS 22.
SQL Server Management Studio 2022. Is that control and R, the keyboard shortcut that allows me to quickly sort of hide results, doesn’t work from the execution plan tab. I’ve opened up an issue about it.
But you have to either click back to the script or click back to messages or results in order to hide them. Which is a little strange. I don’t know how or why that happened. But I’m sure Aaron and co will get that fixed very quickly.
But anyway, if we allow this hint to work its magic and do its thing. Override batch mode heuristic. Look how patriotic this is.
Look at this red, white, and blue over here. Hello, dark mode. If we run this now and we look at the execution plan, we will see this index scan now operates in batch mode on rowstore. Right?
There’s batch mode and there’s rowstore. And we will also see that our top end sort over here is also in batch mode also on rowstore. But you can see the batch happening there.
This sort I don’t think gets it. Oh, thanks tooltip. No, this sort for some reason remains batch mode free. But these two operators over here changed to batch mode. So if you are in a situation where, and again, control and R coming back to bite me.
If you’re in a situation where you want to see if batch mode on rowstore will work for you. And for some reason the other more common tricks for getting it to work are not options for you. Such as creating an empty non-clustered columnstore index filtered to a result that can produce no rows.
Or, you know, doing the left join on 1 equals 0 to a columnstore organized object that is empty. And you can’t make those changes. Then, you know, this hint might be the one for you.
I think the other nice and convenient thing about this hint is that you can create plan guides and stuff in query store. Or using it. And then you could, you know, have SQL Server use query plans and stuff that apply the hint.
So there are upsides to this that perhaps the other tricks do not make available to you. Anyway, something I learned. Something I thought I’d pass on to you.
Because I like you. I think you’re smart and you’re funny. You’re good looking. Everyone likes you. And you, I don’t know.
Let’s just roll out some superlatives here. I think you’re the most. What? Just that. Anyway, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about something equally compelling in the world of SQL Server. All right.
Goodbye.
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.
When does make sense to use tables in user DBs rather than tables in tempdb to hold intermediate results in large stored procedures? Assume no hardware difference on user db drive vs tempdb drive.
We are currently suffering from parameter sniffing issues in our stored procedures (SP) due to the heavy use of IF statements for validation, permissioning and case handling. We are aware that we could use dynamic sql to improve the problem as it would only create and cache execution plans when it’s triggered, as opposed to regular sql statements where plans will get generated even if it’s not executed for the first time the SP is called. Does this mean that we will have to change every sql statement to dynamic sql (bloating the whole SP)? Alternatively, we thought of transferring our SP code to the app level and trigger each sql statement / blocks of SQL statements with Dapper, however let the backend app handle the IF statements. Would this help with parameter sniffing – given that it wouldn’t reuse plans like SP if it isn’t triggered? We are not really familiar we Dapper either, and we would like to know what you think about this (disadvantages and trade-offs). We are also open to other suggestions to tackle this challenge. Thanks in advance 😀
Azure SQL Db have Automatic tuning (identifying expensive queries, forcing the last good execution plan, adding indexes, removing indexes). Does it work?
Erik this is reErik this is really P*ss*ng me off and I can’t find the answer (and yes I’ve tried AI). How do you stop SSMS (22) from putting square brackets [] round all object names? Arrgghhh please help me before I bite my own head off with frustration. (from one of your loveliest channel members) xxx
is it okay to truncate table tables? i read aboit dropping them being bad.
In this video, I dive into a series of community-submitted questions during an office hours session. Erik Darling from Darling Data tackled topics ranging from when it makes sense to use tables in UserDBs versus TempDB for intermediate results in stored procedures, to dealing with parameter sniffing issues and the pros and cons of using dynamic SQL or moving logic to the application level. I also discussed Azure SQL DB’s automatic tuning features and why they fall short of expectations. Additionally, I addressed the frustration of SSMS22’s habit of adding square brackets around object names and offered potential solutions. Lastly, we explored whether it is acceptable to truncate temp tables within stored procedures for better tempDB management. It was a packed session with plenty of valuable insights!
Full Transcript
Erik Darling here with Darling Data and it is a fine Monday, which means we need to have an office hours. Hey, you didn’t see that coming, did you? Which I answer five community submitted questions of varying degrees of quality. And I don’t know. I don’t know. Maybe I should figure out a way to let people vote on these things. I don’t know. Anyway. Down in the video description, you will see all sorts of helpful links. You can hire me for consulting, buy my training, become a supporting member of the channel. All of these things do require money. But you can do free stuff as well. Like, ask me these office hours questions. Maybe I should start charging a dollar for those. Because then people might put a little bit more into them. Who knows, though? And of course, if you like this content, you can always like, subscribe, tell a friend, tell a multiple friends if you have multiple friends. Your mother and I would be very proud of you if you had multiple friends. If you would like to catch me out in the world, I have a couple confirmations for starting off the 2026 conference season. I should have some more of these coming up. There’ll be some pass-on tour dates, probably, and, well, some other stuff, too. We’ll see how life goes.
But Datatune Nashville. I actually announced this a little bit early. My fault. They just officially announced pre-cons today. Tickets go on sale today. So if you go to the Datatune Conf website, you should be able to figure out how to buy a ticket to come see me. I’ll be doing Advanced T-SQL at Datatune. And then Data Saturday Chicago. More Advanced T-SQL. Someday I’ll learn my lesson, right? But tickets are on sale for the pre-cons there. So please do go ahead and buy those and come see me and hang out as the weather starts turning more baseball-y and can all get back to wearing normal clothing, I hope. But with that out of the way, oh, you know what? It’s December. I actually have a new image for this. I just haven’t changed it yet.
But so I’m going to switch over to the Christmas one for the next video, I promise. Typical oversight on my part. Not updating the images there. But I have a brand new one for the month of December. So we’re going to get all Christmassy up in here. But anyway, we’ve got, again, some questions here. And ZoomIt is being a pain in my entire body.
And let’s start way up at the top. Let’s make sure we have one, two, three, four, five. We have five questions. I counted correctly. We are off to a great start, my friends. When does it make sense to use tables in UserDBs rather than tables in TempDB to hold intermediate results in large store procedures?
Assume no hardware difference on UserDB drive versus TempDB drive. I would say almost never. Unless the data that you’re loading is something that you, is like something maybe for like a staging table.
And if the process fails, you don’t want to like restage it. You know, like you might want to preserve the data to say what the hell happened. Then it makes total sense to use a physical table in a user database rather than a temporary table.
The rest of the time, I’m going to get pretty much a hard no from me on that. I can’t think of a lot of exceptions to that rule off the top of my head. But like honestly, unless you need to preserve that data for some, you know, forensic analysis of, again, like what went wrong here type thing, I can’t see a good reason for that.
And ZoomIt is still being a pain in all of my body. Let’s see. Oh, well, it’s letting me draw more things.
It’s just not letting me get unzoom. That’s fun. There we go. Hey, thanks, buddy. All right. This is a long question.
It’s really just like, hey, maybe you need a consultant. We are currently suffering from parameter sniffing issues in our store procedures due to heavy use of if statements for validation, permissioning, and case handling. We are aware that we could use dynamic SQL to improve the problem as it would only create in cache execution plans when it’s triggered, as opposed to regular SQL statements where plans will get generated even if it’s not executed the first time the SP is called.
Does that mean that we will have to change every SQL statement to dynamic SQL, bloating the whole SP? Alternatively, we thought of transferring our SP code to the app level and trigger each SQL statement blocks of SQL statements with Dapper. However, let the backend app handle the if statements.
Okay. Would this help the parameter sniffing given that it would reuse plans like SP if it isn’t? Well, we are not really familiar with Dapper either.
And we’d like to know what you think about this disadvantage in trade. So, um, uh, it sounds like the, the problem you’re hitting is not, uh, I mean, not like sort of the classic parameter sensitivity issue where, um, you know, uh, the cache plan for one set of parameter values is not a very, uh, efficient plan for a, a different runtime set of parameter values. Um, what, what you’re dealing with more is the null sniffing thing.
Um, where I think, you know, in, in general, um, like, like you, like you, you kind of went all over the place with like, like the, like bloating the plan cache and stuff. Uh, but, um, like you, usually what people get concerned about in these situations is, um, that like, let’s say you have a store procedure and I actually have a similar video, not exactly this, but a similar video coming up, um, I think probably tomorrow or the day after. But, um, the, the problem that you run into is that when SQL Server, like you have multiple parameters for a store procedure and maybe one set of parameters is not supplied values for and SQL Server sniffs a null for them, then you run into issues.
So, uh, in general, uh, you really only want to do the dynamic SQL where, uh, the, the sniffing occurs, right? So, um, uh, I’m not terribly familiar with Dapper either. I have, I have run into like many people who didn’t feel like writing dynamic SQL and just handled all the, like, what am I going to run in the app code?
That’s totally fine. That’s totally valid. I don’t have a problem with you doing that. If you want, if you want to learn Dapper and figure out how to, you know, uh, run that stuff, go ahead and do it.
Uh, but you know, the typical, um, you know, the, the typical sort of caveats apply to that, you know, make sure that your, uh, parameters are strongly typed in Dapper. And make sure that your code is parameterized, right? So those two things are going to be the big important thing.
Otherwise you will have a completely different set of terrible problems. Uh, but, um, in general, when you run into this issue, the only code that needs to be put into the dynamic SQL is the code that, um, that would, that would potentially sniff a null parameter value. And then on execution, use a terrible plan based on that null parameter value.
So I think, I think that’s, that’s my final thought on that. Um, but no, you don’t have to make everything inside the store procedure dynamic SQL. Um, you could like certainly group batches together in dynamic SQL, but you know, uh, that gets a little, it gets a little funny looking to me.
Uh, Azure SQL DB have automatic tuning, uh, identifying expensive queries, forcing the last good execution plan, adding indexes, removing indexes. Does it work? Let me ask you a question.
If something like this worked, do you not think that you would see a lot of people just on the roofs, on the balconies, singing its praises, right? Tearing up their SQL Server training, taking down their SQL Server. Like we don’t have to worry about it anymore.
Azure SQL DB have automatic tuning, identifying expensive queries, forcing last good plan, adding indexes. Indexes, removing indexes. We would just not have to be concerned for one moment.
Would we? The answer is it sucks. I’ve seen people use it. The index tuning garbage, the forcing a last good plan.
Well, it forces the last better plan, but that might not be a good plan. And sometimes it doesn’t even force anything. And other times it like the fail, the forcing fails.
And then you end up with a whole different set of terrible things. So, no, if something like this truly worked, you would hear about it. This would be a major breakthrough in database management.
But it doesn’t work. It tries to work, but it doesn’t work. So, no.
No, it doesn’t. No, it doesn’t. Eric, this is Rerick. Hi, Rerick.
Nice to meet you. This is really pissed. Well, I guess passing me off. Well, that wouldn’t be any better.
I don’t know. Anyway, and I can’t find the answer. And yes, I’ve tried AI. Well, I’m glad that you used AI and then you finally, you know, deigned yourself to ask a human being. How do you stop SSMS22 from putting square brackets around all object names?
Arg. Please help me before I bite my own head off with frustration. From one of your loveliest channel members, XXX. Well, all right, lovely channel member.
I have good news and I have bad news for you. The good news is that your head will probably taste lovely. The bad news is, did I say bad news already?
Anyway, the bad news is I don’t think that option is in SSMS22. I went through all the, like, script your stuff out settings. And I didn’t see anything about not putting square brackets in there.
The one thing that I can maybe recommend is Redgate SQL Prompt, which has a lovely keyboard shortcut that says remove square brackets. And it’ll just strip them all out. Otherwise, it’s like, you know, the whole find replace thing on the square brackets.
But that’s not really fun to do every time, is it? I don’t know. The good news is that SSMS22 is under very active development.
And one could very reasonably make a feature request to say, hey, I miss being able to remove square brackets from the scripting options. Maybe, maybe, maybe you could add that in for me.
There might be a good reason why they remove that. I’m not sure what it would be. But, you know, it doesn’t hurt to ask the people who build the product. What’s up with that?
Is it okay to truncate table tables? I read avoid dropping them bad, being bad. I’m going to assume you meant tempting.
Sorry. I’m going to assume you meant tempting. And, yeah, truncating temp tables is okay.
I’ve not run into a problem with people doing that. Dropping them in a high concurrency environment can certainly add some tempDB contention into the mix because you’re messing with how SQL Server caches and reuses temp table stuff.
So it’s explicitly dropping them. Yeah, at the end of the store procedure, like just drop table, pound sign, whatever.
That can all certainly not be great. But truncating them is okay. I actually have one customer who creates some very, very large temp tables along the way in their procedures.
And, like, midway in the store procedure, they might be done with specific temp tables, and they will truncate those to just clear up some tempDB space so they don’t have to wait for the full end of the store procedure because the store procedures, despite a lot of tuning, they do, like, very, very big calculations and aggregations.
So they are time consuming. And it does help manage tempDB space for them to truncate the larger temp tables when they’re finished using them within the store procedure.
So that isn’t… To me, that’s an okay thing to do. Table tables. All right.
That’s good for me here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we will talk about some… Oh, I don’t know.
Some other grim database reality, I suppose. What else? What else is there? No joy in Mudville, huh? All right. Cool.
Thank you for watching.
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.
In this video, I delve into the challenges posed by parameter sniffing in stored procedures, focusing on the parameter sensitive plan optimization feature. I explore how heuristic weaknesses and poor bucketing practices can lead to suboptimal query performance. Using a practical example of a vote type ID parameterized procedure, I demonstrate how the lack of skewness threshold adherence results in inconsistent execution plans that significantly impact performance for certain input values. The video also highlights the limitations of recompiling stored procedures as a workaround and discusses the frustration with Microsoft’s approach to handling these issues, suggesting potential improvements like smarter bucketing or additional query variants.
Full Transcript
All right, we are back in action. We are doing part three of our parameter sensitive plan stuff. So now we’re going to look at problems with the parameter sensitive plan optimization. So like this thing rolled out and I have not seen any real tweaks or improvements to it. This could have been something real cool, but apparently instead we’re just getting the same fabric, fabric everything. It would be nice if Microsoft treated its enterprise database product with the according respect it deserves, but nope. Nope. So one might think that with a feature named the parameter sensitive plan optimization, that it might act sanely and rationally in its effort to optimize parameter sensitive plans. One might need reminding that we also got availability groups. And availability groups seem to rarely make things more available. Usually it’s quite the opposite. So there are two main issues that we have to deal with when the parameter sensitive plan optimization kicks in. The first one is heuristic weaknesses. All right, and the second one is poor bucketing practices. I would call this poor bucketing hygiene. In fact, I’m going to make that change now. Let’s say poor bucketing.
Hygiene. Hygiene. Ah, I spelled that wrong. There we go. I might still be spelling that wrong. I before E except after C, but that looks funny to me. I don’t know. I’ll fix it later. Maybe we’ll just go back. So we’ll be killing it later. Exactly. And troublesλι the vote sniffing store procedure. So this thing has one parameter, vote type ID, that’s an integer.
It selects some data from the votes table. And the only thing in the where clause is, of course, our one parameter here. But then for everything that it finds that matches with this vote type ID, we have to say, we don’t, we want things that where this didn’t happen, right? Cannot exist, select from badges, join the posts, and you know, this other stuff in here, right? Again, not important business logic. It’s just enough to get us a good demo. So if we were to look at the statistics histogram for our index on the votes table that leads with vote type ID, we might think that this looks pretty skewy. If you’re wondering what this crazy number is, it’s 37 million, right?
This is the most common number. And this is the most common vote type ID, which is two in the votes table. The least common is vote type ID four with 733, right? So if we look at that, we’ll see for that, like that was that crazy number in there. If we convert this to something more readable, we get 37,332,000, etc. Let’s see if this works. Oh, it does look, I can zoom in and the results with my mouse in SSMS 22. Look at that nice clean number. Anyway, I know that zooming that in is going to bite me sometime. So bear with me here. But because the least frequent value in the histogram is vote type ID four at 733 rows, and the most frequent is vote type ID two at 37 million rows, we do not meet the minimum skewness threshold.
And the reason for that is something that I said earlier, where the most common value has to be, or rather the least common value times 100,000 has to be greater than the, or equal to the most common value, something along those lines. But anyway, 733 times 100,000 is 73.3 million. 73.3 million is greater than 37 million. So we do not meet the minimum skewness threshold for the parameter sensitive plan optimization to kick in. And we can see that if we run this, and we say, hey, vote type ID four, what’s your query? What’s your plan?
All right. We get this, right? And it takes zero mil, I guess it takes four milliseconds, right? Okay, you got me four milliseconds. If we run this for vote type ID one, oh, that is big. Look at that. It’s a bit much. All right. That’s a little more reasonable.
If we run this for vote type ID one, this will get very, very slow. Worse, if we run, if we were to try to run this for vote type ID two, we would have a very, very bad time, right? So if we come and look at this saved off execution plan, we will see that this runs for four minutes and 53 seconds in total. Most of the pain in here, well, we spent like 11 seconds up to this point, and then we spend nearly five minutes with this sort spilling. So this is particularly not a good time, right? This is the opposite of a good time. This is not party mode.
What’s annoying is that this is a batch mode sort. Batch mode sorts are much, much, much, much, much slower than row mode sorts. If we come in the query plan and we look at the weight stats though, actually, this is a nuisance to do here. Let’s just look at the plan XML and let’s scroll down.
If we look at the weight stats for this query, we will see almost nothing of use or value, right? We see about four seconds. Oh, sorry. Not even four seconds. We see 1.2 seconds and then seven seconds of, so like 1.237 milliseconds of like a second 230, one second, 237 milliseconds worth of wait time for this query. Microsoft is ashamed of the weight stats for, I think, a lot of the stuff that goes on in here. In an honest world, we would see BP sort, which is the weight that crops up when a sort in a, in a, when we sort data in, in batch mode. And then we would see like either sleep tasks or IO completion, depending on the type of spill that we get. So this, this doesn’t go well. And the plan for vote type ID one is equally noxious where, uh, this thing now takes about six seconds to finish, or I guess it takes about seven and a half seconds to completely finish with a bunch of time spent in the sort over here, which spills a bit as well. So these two other queries using the plan for vote type ID four did not go well. And despite the, I think, giant skewness of, um, the, of the, like the range of values for vote type ID in the votes table, the parameter sensitive plan optimization does not kick in for it. So if we recompile this and, uh, we run this for vote type two first, you know, this will be okay at around four or five seconds, right? We run this, we get, oh, wow, 2.2 seconds. Something, something, something cool happened. So this runs pretty quickly, right? We get this query plan back. I don’t really have any complaints about this for vote type ID two, nor do I have any complaints about this for vote type ID one, right? Vote type ID one. How long do you take? Under a second, right? This is, I’m totally okay with this. This is, this is fine. I don’t mind when vote type ID one and two share a plan. This is, this is much better than the alternatives.
But then when vote type ID four uses this, it’s almost a total waste of time, right? Look at this execution plan. Vote type ID four went from taking like four milliseconds to now like almost a full second to complete because we have a big parallel plan, lots of hashes and scanning and startup costs.
And now vote type ID four is using a bunch of memory too. So vote type ID four, uh, this, this plan is way overkill for this one. And we don’t love this, but what we can do is we can execute, or rather we can insert a dummy row into the table and so that we meet the statistics skewness threshold because one times a hundred thousand is a hundred thousand and 37 million is greater than a hundred thousand. Right? So if we set identity insert on and we put one row of dummy values into the votes table, and then we set identity insert off because we’re done doing that, uh, and then we update statistics.
And I did, I do have to do this with a full scan. Um, I tried to get this to, um, happen predictably with lower sampling rates that took a little bit less time, but instead we’re going to spend 15 seconds making sure we get it right. Right? Rock solid demos. That’s what we care about. We’ll, we’ll waste a little bit of time getting that correct. So let’s, uh, let’s throw a recompile on that store procedure just in case. And what I want to show you is how we know that the parameter sensitive plan optimization is now kicking in. If we run this for vote type ID zero, this is the dummy row that we just inserted.
We get back this, right? And I know it looks weird that there’s a post for this. It’s because I had to insert, um, the, a value of negative 2.1 billion into that row. Um, I couldn’t insert a null cause post type ID doesn’t accept null. So it looks like we actually have a post associated with this, which is a little crazy, but yeah, you know, these things happen. Uh, but if you look at, I guess I could fix that with the case expressions, a case when post type ID equals negative 2.1 billion than null else zero. And, but anyway, if we look at the query text for this, we’re going to punch these little ellipses over here, way down at the bottom, we will have, uh, this stuff, right? This option plan per value thing, right? We have all this stuff going on in here and we will have our predicate ranges in here and we, you know, I don’t know, whatever. But the important thing is that since this is a very uncommon value, this gets query variant ID one. Okay. Now if we run this for vote type ID four, then we get query variant ID two, right? Come over to the execution plan and we look down here and I’m not going to expand the whole thing again. I’m just going to focus in on the query variant ID. Vote type ID four gets query variant ID two. Okay. All good. Everything fine so far. If we run this for vote type ID two, right? We’re going to get our four or five second plan and this is great, right? Or two seconds now, right? Something, something miraculous must have happened. Now I wonder what, what’s going to be weird next. But anyway, this gets query variant ID three, which is fine. I don’t mind query variant ID three here, right? Cause we get a different plan. Vote type ID two gets the plan that it is fastest with and no one goes home crying. The trouble is that if we run this for vote type ID one, vote type ID one will be bucketed alongside vote type ID four, right? And we get the same plan that we got last time. Uh, I mean, I guess it’s about a second and a half faster at six seconds versus 7.5 seconds, but I’m still not thrilled with this. Right? And if we look at the query text that we got for this one, what do we get? Look at query variant ID two. So now vote type ID one and four are still sharing plans.
And I’m just going to like, when we look at how this breaks out, it’s, it’s really unfortunate. So this is the way that the data or rather, this is the way that the parameter sensitive plan optimization will treat this, right? So, uh, up at the top is vote type ID two, which is unusually common down at the bottom is our dummy row vote type ID zero, which is very uncommon. And every other vote type ID, despite massive skewnesses, right? 3.7 million, 3.5 million, 3.5 million, 2 million, 1.2 million, 800,000 down on to vote type ID four at 733 gets bucketed in together.
This is not a clear sign that someone does not like you, does not respect you and does not care about you. I don’t know what is, right? Cause this could obviously be vastly improved upon, but no, this is what we get. All right. This is what we get. Uh, I don’t love it. I don’t love it one bit. So let’s get rid of our dummy row. All right. Let’s say goodbye to you and let’s update statistics again with the full scan. And you know, um, I, I don’t, I guess I don’t understand some of the rationale in there. Um, I feel like, you know, there, there could be maybe, uh, an additional query variant ID, or maybe there could be some smarter bucketing or something along those lines in order to make this a little bit more reasonable. But you know, someone had to build fabric.
It was probably all the people who could be improving that. So let’s just make sure that we got rid of our dummy row successfully. That looks good. We started one now instead of zero. So that’s great. So what’s really annoying with, especially with the, um, uh, situation that we just saw there is let’s say that, you know, we were like, Oh, well, you know, maybe, maybe I can force one of those queries to use a different plan. The trouble is that, so the parameter sensitive plan optimization does something similar to using dynamic SQL in a store procedure and that it sort of detaches the statement, uh, in the procedure that gets the different plan from the store procedure itself. And so they all get different query IDs. So, Oh, stop jumping. So for the last three executions that we just had here, we got query IDs, 31884, 31883, and 31882. If we wanted one of these, uh, let’s, let’s say that, um, we wanted, um, to use a different plan. We can’t cross boundaries here. So like query ID, 31884 can’t use plan ID 6890. That I don’t disagree with because of course, like if you could force a query ID to use any plan ID you wanted, you could have a completely different query. And like the query plan would just be like, I think just different tables and a different, like nothing would make sense. Like nothing would line up. So I’m glad that this gets avoided, but it’s a little frustrating that we can’t like re-bucket. Like we can’t choose which buckets things go into. We can’t say, I want this, uh, value to use this query variant ID, right? We can’t, we don’t have a way to sort of guide the here, the heuristics in a way that would make sense for us.
So this ain’t great, right? So like there, there is a store procedure where you can force a query ID to use a particular plan ID, but it would not work for us here because the query ID, plan ID boundary can’t be crossed in a way that we want. So we’ve done a fair bit of character assassination now in our dealings with the parameter sensitive plan optimization, both in it not kicking in heuristically when I believe it should and for the way that it buckets things. But I’m sure that it will work great for you. I’m sure that when you go and use it in real life, everything will be peachy, keen, perfect. You’ll never have to seek a bit of help, do a bit of tuning, nothing like that.
So most of the time with parameter sniffing, it’s a matter of comparing different plan choices choices and often making some query or index adjustment to give the optimizer fewer choices and sort of guide it towards a plan choice that works reasonably well for everyone. That is, that can’t always happen though. Some spans of data are just far too different in order for that to work. Like sometimes you can get it like, you know, fix some stuff, like, you know, fix some indexes up, you know, um, you know, things like that. And Antigual server will choose a plan that’s generally good for like, you know, any set of parameter values. You can totally get to that point.
Other times you do have to break things out further and dynamic SQL gives us a way to, uh, look at, rather dynamic SQL gives us a way to generate queries and guide SQL Server to the correct optimization path for different things. One way that we can fix the current store procedure we’re looking at though is just by using a temp table, right? So since there’s one part of this code that is sensitive to parameters, which is the votes table, and there is one part of, um, uh, like the query that, you know, really messes things up. If we isolate that one part of the query and we dump all the stuff from, uh, votes depend on based on whatever vote, vote type ID we pass in, into a temp table, we can get pretty good performance across a variety of vote type IDs.
I’m going to say pretty much, I’m going to say everything except vote type ID two. So vote type ID one, we do a simple insert into our temp table. And now the second query in here finishes in zero milliseconds, right? Uh, vote type ID four, we run this, use this, uses the same plan.
And everything finishes perfectly fine here, right? Every like, this is maybe faster than it was before. The problem that we would run into is if we were to run this for vote type ID two, right? This would take a lot longer because we would be, you know, like, you know, get 37 million rows rather than dealing with the number of rows that we had for all the other ones. So this, this gets a little bit more bleak. If I had to deal with a situation like this in real life, I might go for a hybrid approach. I might have an outer store procedure that figures out if I’m running vote type ID two, and then I might just run the regular version of the store procedure where the query takes four or five seconds. And if vote type ID is not equal to two, then I might use this and dump it into a temp, I might, I might use the temp table approach. So that would just look like, you know, the vote sniffing procedure would just do the normal thing and run the query for vote type ID two. And the temp table approach would run for anything that was not two, right? So that’s one way of handling this situation. And then all this stuff would be fine. But we can get even more creative when we start using dynamic SQL. Like I said before, there are all sorts of fun things that you can do, and you can choose how you want things bucketed. So, you know, you might say something like if for any of these vote type IDs, just add one equals select one, you might say for any for vote type ID two, you you’re special, you get two equals select two. And you might say for these vote type IDs, add three equals select three. And those literals, the one equals and the two equals and the three equals will get you three different execution plans. You can also add in different hints depending on what values come in here. So for example, you might say for these, I really like a merge join, hopefully a serial merge join. You might say for vote type ID two, I really like a hash join.
You might say for all these other vote type IDs, I really prefer a loop join. So there’s all sorts of things where with dynamic SQL that you can do and control a lot better than I spelled option wrong. That’s silly of me, that you can do and control better than when you are using when you are just allowing SQL Server to use a parameter sensitive plan optimization. You might even go a step further and say, I think like I know from testing, right? I don’t just think I don’t just have a feeling I know from testing that when I search for any of these vote type IDs that I do best when I optimize for vote type ID equals one. And of course, vote type ID equals two, we should optimize for vote type ID equals two because vote type ID equals two is crazy. And then for maybe this group of vote type IDs, I tested all this and vote type ID 15 works the best. You might also go a step further and say, hey, I think that I want each of these vote type IDs to cache and reuse its own plan. And you could do that by tokenizing the dynamic SQL and then replacing that token with the vote type ID at runtime and then executing for that specific vote type ID. And SQL Server will generate a new plan per vote type ID, cache it and reuse it. So as long as it stays in the plan cache. And if you’re in a situation kind of like I talked about earlier, where, you know, you have date ranges that you’re focusing on, and you want to figure out and like, you know, sometimes for the most part, these date ranges are very, very small and narrow, like an hour or a day of data. And these searches are very fast, because you are all set up and indexed and everything’s good for these. But then every once in a while someone throws a big date range out there, and like everything does grinds to a halt and gets terrible. Well, you can even do something like this and say, you know what, if these are more than three months apart, then I want to recompile. Right? And just say, come up with a new plan, and then probably go back to the normal plan after that. Or, you know, you might have to, you know, maybe do a little extra work to like re recompile a plan, re re, I actually lost on how that that sentence works, but that’s okay. But dynamic SQL is your friend. Just a few things about it is to make sure it’s parameterized.
Otherwise, you’re not doing a good job. Make sure that objects, any like, you know, database schema, table, etc. names have a wrapped in quote name to prevent any weirdness. Make sure that it’s formatted nicely. So when you print it out, you can read it. And if it’s coming from a store procedure, then make sure that there’s a comment in there that tells you which store procedure is coming from.
And if it’s being generated in the like, if you’re generating dynamic SQL in the application, it’s really helpful to put a little comment in there that tells you which module or which thing executed it. So that when you find it, you know how to trace that back to the part of the application that generated it. So with that, I am done. I did manage to fit this in three in three videos, I don’t need to jump out the window. This is a fantastic day. That’s just about it there. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you next week where we will start afresh with all sorts of office houry things and things and stuff and things. All right. Thanks for watching.
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.
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.
In this video, I delve into the intricacies of parameter sniffing and parameter sensitivity in SQL Server. Starting off with some humorous self-deprecating remarks about my past Data Community Summit 2025 presentation, I aim to make a complex topic accessible and engaging for viewers. We begin by defining key terms like parameter sniffing versus parameter sensitivity, explaining how the optimizer uses parameter values for cardinality estimation in busy OLTP systems but highlighting where things can go wrong when queries get compiled with skewed data or variable runtime conditions. Throughout the video, I use practical examples to illustrate these concepts, such as comparing execution plans and discussing local variables vs. formal parameters, all while providing a clear explanation of why parameter sensitivity is a critical issue for database administrators to understand.
Full Transcript
Erik Darling here with Darling Data. And I’m going to spend the next few videos doing the material from my past Data Community Summit 2025 regular session on parameter sniffing. And hopefully you find it as enjoyable as the millions of people who showed up on Friday morning to hear me deliver this. So we’ve, we’ve, you, you, you’ve got to see it. We’ve got that going for us, don’t we? Anyway, uh, welcome to your HR mandated parameter sensitivity training. I’m Erik Darling and blah, blah, blah, all this stuff. If you’re watching this, you’re at least moderately aware of some of this material. Um, I will try to remember to put the link to, uh, the thing in here, uh, but I make no promises. And if you don’t like it, I don’t know, leave. Um, but we’re going to start by defining some important terms here. Uh, the first one being, uh, sort of differentiating between parameter sniffing, uh, and parameter sensitivity. Uh, so parameter sniffing is when the optimizer uses values assigned to one or more formal parameters for cardinality estimation, right? And this is a thing that it, you know, I, I appreciate that it does because I work with a lot of OLTP systems that are pretty busy.
And if SQL Server were coming up with execution plans, every time one of those little tiny queries came in, um, I think things would go a whole lot less well. It’d be like, I don’t know, some, some hit every time the, the, the, like a query opened a connection and ran something. Um, if you would prefer that behavior, you can go use Postgres, right? Because there’s not really a plan cache there, right? So this is the generally okay thing that SQL Server does. Where it gets a bad name from is, uh, where it gets a bad name from is, uh, when you have a problem where a query gets compiled with some parameter or set of parameters and the cardinality estimates that produce a cached query plan for re-execution, uh, do not really fit the mold for, uh, what the current runtime set of parameters is looking for. Uh, so this is, this is what the sensitivity part is where, uh, you have skewed data or perhaps your queries are, uh, either like you have like actual physically skewed data in an index, right?
Like one value accounts for like, like a lot of rows in your table and other values account for very small numbers of rows in the table. That’s data skew. You could also expand that to be like, okay, well, you know, uh, I have a date range and sometimes, uh, or rather most of the time when I’m searching that date range, it’s like the last like hour or 24 hours or I don’t know, week or something.
And then every once in a while I have to search for the last like six months or six years of data. And all of a sudden, uh, that, that breaks the mold, right? Cause like, you know, searching for way more data within that range, uh, the query plan for searching for like this much data as usual, like those two things conflict, right?
That’s not, that’s the thing that we have to deal with. That’s the problem that we need to focus on today. There are also all sorts of general performance issues, right?
And a lot of these things can sometimes feel like parameter sensitivity, you know, sometimes, uh, you know, you deal with more data in a temporary object than others. And maybe there’s a plan reuse thing in there. Uh, there’s all sorts of other stuff that you can do that just craps all over your queries that is not parameter sniffing, but often people, you know, the, the first thing they’ll blame is like the most convenient set of words that they’ve heard about performance problems.
And like, like, I want to sound smart and be like, Oh, might be parameter sniffing. Right. So like, you know, look, SQL Server has a lot of features, right?
And, um, you know, in a database, it is real easy to shoot yourself in the foot, uh, with performance problems. And a lot of the features that SQL Server has really just make it harder to miss. Right.
So like just very good aim with some of those performance, with some of these features. So, uh, the first thing we need to do, aside from wait for this green screen to come down, I don’t know what your problem is. Sometimes if I lean away from it, it goes away.
Uh, the first thing we need to do is separate queries that are sometimes slow for other reasons. Right. Like say sometimes a query gets blocked and people are like, why is this running for 10 seconds? And it’s like, well, cause it got blocked for 10 seconds.
I mean, as soon as it, as soon as it was unblocked, it was fast. Uh, other times you might run into something that’s slow because you don’t always read data from memory. Sometimes you have to read data from disk and sometimes that is uncomfortable.
So just for example, uh, if we run a checkpoint and I drop clean buffers here and, oh, I’d must have all, must’ve had a big buffer pool on that one. Uh, sometimes, uh, you might run into a situation, make sure a query plans are turned on here. That’s a good first step.
Uh, where, you know, you run a query and you’re like, wait a minute, is this parameter sniffing? Right. Right.
Cause I am using parameterized dynamic SQL here, but did my parameter get sniffed? Was something bad? And the answer is no. Sometimes you get stuck reading pages from disk. So these two queries obviously have time differences between them.
Right. Uh, almost like 10, is that 10 X or a thousand X? I’m not, I don’t know. Let’s just say a thousand X, right? Right.
600 milliseconds versus six and a half seconds. But the whole problem is that this query had to read data from disk. If we go look at the weight stats, I don’t know how I’m going to manage this one. If you go look at the weight stats for this, this is a lot easier in person because I could, I can move things around a lot.
But the weight stats for this indicate that we spent, uh, 41 seconds reading pages from disk into memory. Right. So in this parallel plan, all eight threads had to go out to disk and read stuff.
Right. And we did not have a good time with this. But this isn’t parameter sensitivity, right? This is more like environmental sensitivity. This is more like your SQL Server can’t cache the data that it needs.
And maybe it can’t keep the buffer pool full of the stuff that it needs because you don’t have enough memory or because you don’t generally have good indexes. Or maybe you write queries in a really crappy way where you can’t like, you know, seek the stuff and you have to scan all your indexes. There are all sorts of possibilities for this.
Um, page compression is a great way to start fixing this too. But there are all sorts of reasons that might, that might be the case. But this is still not parameter sensitivity. Right.
Because that, like that query, that second query ran and it reused the same plan that the first one had. But it was a lot faster because the data was already in memory. So, um, what kind of code is parameter sensitivity?
Is parameter sensitive, right? Yeah, words. Uh, so it usually touches table with skewed data in them, right? So, like, you know, uh, I think, uh, if you’re working with the Stack Overflow database, um, you know, John Skeet would be an indicator of skewed data, right?
Uh, user ID 22656. Uh, lots of rows in the post table. Lots of rows in the comments table.
Um, and so his user ID leads to a lot of interesting problems. You can also run into it when, uh, you know, like not with the, like with things that are like inequality predicates where sometimes they may find way more data than others. Right?
So, like, you know, it’s really easy to imagine that with a cache plan using something like this. But you can also run into that with like predicates. You can run into that with in and not in stuff. So there are lots of ways, lots of places you can run into skewed data.
Now, I think like is a particularly fun one. We’ll look at an example with that in a few. But other things about it is you will be using formal parameters and not local variables. Right?
Local variables do not have parameter sensitivity issues because their cardinality estimation. is treated much differently from that of formal parameters, literal values, and other such things. Um, we’re not too concerned with the concept of simple parameterization here.
Because it’s really only used for queries that qualify for what’s called a trivial plan. And trivial plans do not really have any cost-based choices. Having cost-based choices to make is where the parameter sensitivity stuff really kicks in.
Because SQL Server made choices based on cost from one example of cardinality estimation for a set of parameter values. And then reusing the plan for a different set of parameter values where the costing would maybe work out differently because of the number of rows involved. That’s where the sensitivity end comes in.
So if we look at execution plans for these two queries, right? We’re just going to get a count from the user’s table. One of them is for reputation two and one of them is for reputation one. I have literal values in my query, in my, both of these queries, right?
But if we run this and we look at the plans, then of course we have to move some stuff around. And actually, no, we can just use the arrows, I guess. Thanks, SSMS.
Thanks, SSMS. Making me do more work. The last time I upgrade you, you fiend. So if we look at these, you see that SQL Server did, like, replace both of my literal values with this at one parameter. And that means that SQL Server gave a, like, was like, hey, this plan seems pretty trivial to me.
And then SQL Server also said, and I think I can apply simple parameterization here. And so it did. And so we end up with this.
And we can also see some artifacts of that because SQL Server did cardinality estimation for reputation equals two. And I guess it was pretty good. It was a pretty good guess, right?
We were only off by, like, 190-something rows or, I don’t know. Doing math on the fly is not my specialty. So this was pretty close, right? But then down here, you see SQL Server reused that same cardinality estimate of 1770.
So SQL Server parameterized, cached, and reused my plan for these two queries with literal values in them. So using literal values does not always insulate you from these types of things. But often the queries that get this are so simple that we’re not really worried about, you know, big performance problems with them.
But anyway, the two most common vehicles for parameterized code in SQL Server are, of course, store procedures and queries that are executed from an application using SP Execute SQL. SP Execute SQL is a store procedure. And when you execute dynamic SQL with it that contains parameters, you are essentially executing parameterized code.
All right. But we’re going to talk a little bit about the main distinction between formal parameters and local variables here. Because sometimes the local variable thing matters and sometimes it doesn’t.
I’m going to start these two queries off running. And then we’re going to talk a little bit about that. Oops, that was supposed to. Oh, that was two queries. Sort of jumped around on me.
So the thing with local variables is, and, you know, I’ve talked a lot about this and written a lot about this. But the main thing with local variables is, like, it’s very easy to come up with a demo where there’s one thing in it that’s a local variable and that local variable screws everything up. In real life, often the times when I see code that uses local variables, the local variables aren’t the only thing in there.
Right. Right. So when you look at, so, like, if you’re using local variables, sometimes it’d be, like, other things in the where clause, like other, you know, search arguments in the where clause that might sort of tame the effect of bad cardinality estimation from local variables. I’m not saying that it’s okay to use them all the time.
But I am saying that a lot of the times, you know, you might have other stuff in the code that sort of hides or, like, sort of, like, smudges over a little bit about how bad those estimates can be. And other times you’re looking for, you know, such small amounts of data that it doesn’t matter. This is an example of looking for when it can matter and when it actually doesn’t matter.
So the first time that we run this, we’re looking for, we have a local variable where parent ID is zero. And the second time that we run this, we’re looking for things where parent ID equals 184618. So one query obviously takes far longer than the other.
This one takes 10 seconds. And this one down here takes one millisecond. The reason why all comes down to the cardinality estimates and the number of rows that we actually found. You’ll notice that both of the index seeks into our index on parent ID estimate three rows are going to come out of it.
That’s the local variable density vector guess. Of course, in the first query, we actually find six million rows. And so this was not a good situation.
If you find yourself running into situations like this and saying, SQL Server came through cardinality estimation, well, that’s your fault. But if you run into situations like this where you’re like, oh, SQL Server guessed three rows, but we got 518 rows and everything turned out okay. I don’t know.
Maybe you’re safe for now. But don’t say I didn’t warn you. Local variables can really screw up query performance stuff, especially as things get bigger and bigger. But if we look at the data distribution for stuff in the post table, we’ll see that just because the post table is not well normalized and we have questions and answers living together, we have six million rows for parent ID of zero, which are questions.
And then we have just the top nine or so posts and the number of answers they have. So non-zero parent IDs are answers. The one that has the most rows in it is 518.
So this is where like, you know, like these very low numbers is where SQL Server starts to get these like these estimates for local variables from. But let’s talk about some other stuff. If you want to read in detail about local variables, I have a post there.
It’s a long post, but, you know, it’s a good one. So it’s sort of interesting, like why that happens in SQL Server. And this is sort of a thing.
Well, I mean, it used to be table variables would get sort of a similar treatment, right? You would run code that involved a table variable. And unless you had a recompile hint on there, SQL Server would always guess one row was going to come out of the table variable, even if you had an index on it, because SQL Server doesn’t maintain any distribution statistics about data that is in table variables, right?
So Microsoft changed that sort of recently. In SQL Server 2019, we got this table variable deferred compilation thing, which is kind of a smudgy name, but it really means that when you have code that uses a table variable, SQL Server will defer compilation of queries that touch that table variable until after the table variable gets populated.
So we know how many rows. We have table level cardinality for what goes in there that, of course, doesn’t extend to any like, you know, column level statistical histograms of data in there. But we at least get table cardinality.
So Microsoft could technically do this with local variables as well. It could say, I’m going to defer compilation of any queries that touch these local variables until that local variable has a value assigned to it. Right now, we don’t do that.
Right now, SQL Server says, I’m screw it. I’m going to compile a plan. And in order to do that fast, I’m just going to use these unknown estimates for any local variables in my where clause. If we did this in the future, well, if we did, we can change that.
Right. And if we did the deferred compilation thing where we didn’t compile a plan for any queries that use those local variables and like the where clause. If we did it every time, it would just feel a lot like you’re just recompiling, like just constantly.
If we just do it once, let’s say for a store procedure, and then we just use that plan over and over again, it would just feel like parameter sniffing all over again. And sadly, a lot of people use the local variable thing to get around parameter sniffing. So the internals to do the second thing are actually in the product.
They’re just not available to us. From what I hear, you need window bug and you need to start altering memory addresses in order to expose it. It’s not even like some super secret trace flag, but you could get local variables to act the way that tape, to act with local variable deferred compilation.
I guess it would be if you’re feeling bold and brave and doing it. But like I said, since people historically have used local variables to avoid that, the powers that be have decided not to break this behavior for them. You know, this, of course, could be introduced with some sort of database scope configuration or, you know, query hint or something.
But, you know, we just don’t have access to that yet. So let’s move on because what we have to talk about, at least probably the last thing in this video that we’re going to talk about, is recompile. So I love recompile.
I think it’s a fantastic thing. I think it’s a fantastic technique to use. You know, there are, of course, some caveats to that around, you know, like planned compilation time and execution frequency. But, you know, for like reporting type queries, I don’t see a reason to ever not have a recompile.
You would have to convince me to not put a recompile hint on a reporting query more than you would have to convince me to put one on. But there’s one thing that people continuously screw up. And that thing is where to recompile.
Because there is a difference between recompiling at the procedure level and recompiling at the statement level. So let’s create this index. And then let’s look at this store procedure here.
So this store procedure does have the procedure level recompile up at the top. And I say this sucks because this is really not my preferred method of recompiling. This recompile hint here is, this doesn’t have the same magic powers that it does if you put a recompile hint down lower.
So this actually has the same query repeated twice in it. There’s this one here, which is just a normal query. And then there’s this one here, which has option recompile at the end or a statement level recompile.
If we run this store procedure, which is going to execute both queries, right? It’s not one or the other. This doesn’t go terribly.
But there are some obvious differences between the two execution plans. The first one up here goes parallel and does an index scan. Okay. Second one down here does not go parallel and does an index seek.
This is the one with the statement level recompile on it. If we scroll on down a little bit, you’ll see the option recompile in here. But so the main difference between the procedure level recompile and statement level recompile is that statement level recompile gets you what’s called the parameter embedding optimization.
And you can see that if you hover over the index scan and you look at the predicate that gets applied up here. SQL Server doesn’t seek with this. SQL Server scans the whole index and tries to figure out if various things are null or equal to columns or whatever.
Right. There’s just a lot of stuff going on in here. In the second one, SQL Server simplifies that whole thing and just seeks to where owner user ID equals 22656. Performance isn’t dismal in either of these cases.
But if you’re going to ask me if I’m going to pay the price to recompile something, which one I would prefer? It’s obviously the second one. All right.
Obviously, the statement level recompile gets us a little bit closer to what we like, you know, reality on this. So if we run this with using a different parameter this time, right? Because remember, the pattern in here is this whole wacky, nutty thing, right?
This, you know, the old kitchen sink store procedure type pattern. If we run this with creation date now, SQL Server is going to give us a different execution plan for both of them. Right.
Because we have both a procedure level recompile, which means SQL Server is going to generate a new execution plan for all of the queries in this procedure when it runs. And then we have a statement level recompile, which tells SQL Server, give me a new query plan for this one query every time it runs. So what happens is SQL Server, when we use a different parameter, SQL Server does not reuse the plan from before.
SQL Server gives us a new plan for both of the queries in there. But this one, we still have this whole big predicate. SQL Server tries to figure out all of the knownness or not knownness of all of these parameters.
And in the second one, SQL Server simplifies it to one predicate that just says greater than or equal to 2013, 12, 31, 0, 0, 0, 0, 0, 0, 0. So my strong, strong preference is to use the statement level recompile and to not maybe generate new execution plans for absolutely everything in the procedure and target particular queries within the procedure that might be parameter sensitive or might be using local variables, which can also benefit from the parameter embedding optimization. SQL Server 2025 also has a new feature to try and deal with the optional parameter thing called OPPO or the optional parameter plan optimization.
It’s built closely around the same architecture as the parameter sensitive plan optimization. I have not had great experiences with that yet. The OPPO one.
And we’re going to talk more about the PSPO one a little bit later. The first thing that I want to show you, though, before we, the last thing that I want to show you before we go and before I start talking about something else is the effect that using option recompile can have on queries in the plan cache and in query store. So if we look at this first query up here, and I think if we open both of these, we should be in good shape.
If we look at both of these, right, we’ll have, these are two of the queries that ran from the procedure that we just, sorry, these are the two queries that ran from the store procedure that had option recompile hints on them. If we look over here, or rather if we start, let’s start over here because maybe I should do both. There is no parameter list for this, even though this came from the store procedure.
And even we, even though we know that SQL Server like ran a parameterized query because we have the option recompile hint on here. We have to go find the literal value in here. Right.
Right. And that’s the same thing is true for both of these. If we get rid of these and we look at this without the recompile hint, and I’m going to, I know I’m going to regret doing this because I’m going to have four of these and I’m going to have to figure out which ones are which. So what I should do is go look at the query IDs.
So that’s two, six, seven, eight, two. All right. So let’s get rid of this. So avoid two, six, seven, eight, two. We want two, six, seven, eight, one. One, if we look at the query plan for just this first one and we go to the properties, SQL Server will, this is the one that used the procedure level recompile.
This one we can see the parameter list in, but we’ll just see which ones were null and which ones actually had a value to them. So this is the one that executed where we use creation date. So we have the parameter compiled value in here, but we see the parameter compiled value for the rest of these is all null.
So that was way longer. Well, not way longer. That was just a little bit longer than I expected it to be.
But we’re going to end here and we’re going to pick up next looking at a minor parameter sensitivity example. So we’ve got that to look forward to. So thank you for watching and I’ll see you over in part two of this.
All right. Goodbye.
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.
In this video, I delve into the complexities and challenges of moving production databases to cloud environments, particularly focusing on Microsoft Azure. Alongside my co-host Joe, we explore the nuances of modern cloud infrastructure through a series of candid discussions and practical examples, using the Azure portal as our guide. We dissect the performance implications of various cloud services, questioning whether the allure of managed solutions truly delivers on promises of ease and efficiency. Our conversation touches on topics ranging from CPU performance and storage latency to the ongoing challenges of database management in the cloud, all while critically examining the trade-offs between abstraction and control. Through this exploration, we aim to provide a nuanced perspective for anyone considering or currently navigating the complexities of cloud database deployment.
Full Transcript
This computer. All right. Welcome to yet another episode of the Bit Obscene podcast. Today, I am joined by two special guests. We have a new special guest. We have Microsoft Certified Master, Sean Gilardi, in a completely unofficial capacity. We have our usual temporary co-host, Joe Obish. Wait, temporary? Do you have some kind of plans to get rid of me? Wait, am I being replaced by Microsoft Certified Master? Is that what’s happening?
You are being replaced like AI, like everyone else. Okay. All right. I can live with that. Yeah, I’m migrating my best friend to the cloud, and you’re gone. You’re out of here.
But also, also in an unofficial capacity, I am going to be your completely unbiased moderator, Erik Darling. I have absolutely no certifications or skills to speak of, so I’m just here to make sure everyone doesn’t curse and keeps their clothes on. Anyway, Joe, why don’t you start by stating your position on the current state of the cloud?
And it doesn’t have to be any specific cloud. It can just be the cloud in general. But if you want to name names, I’m not going to stop you. So do you know why they call it the cloud?
I do not know why they call it the cloud. It’s because shit rains down on you. I’m already failing as a moderator. Where is my beep button? I don’t have any certifications either, but I would state my opening position as I’ve been a cloud hater since about nine years.
So I’ve been hitting on the cloud for nine years, and I’m mostly focused on the Microsoft Azure cloud platform. Would you consider yourself an OG cloud hater, or are you more new to the cloud hating game than most others? I mean, I started in like 2016. I’m sure there are people who’ve been hating it for longer than me, but I feel like nine years is a pretty respectable number.
It’s a pretty respectable number. Almost your 10 year anniversary of hating the cloud. Almost, yeah. Are you a proud cloud hater, or are you just sort of a run of the mill day to day cloud hater?
I think I’m run of the mill, because if there are other things to talk about, I’d prefer to talk about those things, with the exception of this very important run of the mill. We’re not a table discussion. Okay. All right. Great. Great. All right. Well, good, good, good opening salvo there. Sean, what is your, what is your position on the cloud?
Any cloud will do. Yeah, I’m more of a moderate on, on that. It’s, I think there’s good use cases for various things. You know, not if we, in just the vein of databases, it’s a hit or miss. I still think there’s some good use cases. Overall though, there’s, I think there’s better use cases for people. But overall.
What use cases, what, what use cases sound good for the cloud? What, what would you, what would you put in the cloud and what would you take out of the cloud? I think there’s some really good use cases for people doing backups who otherwise wouldn’t have backups, right? Sure. So like if someone wrote a piece of software and they wanted to back it up so they didn’t lose it. And maybe like if anything terrible befell their laptop, they wouldn’t just like have be completely out of luck.
Or accidentally deleted the GitHub repo. Yeah. Like that. Exactly. Yeah. Correct. Someone like that should probably. Yeah. That would be good. Yeah. But to, I, I also wanted to go with, I was told in two, about 2009, I was working at a undisclosed location and we had a TAM from Microsoft come in and, and I was a DBA at the time.
And tell me that, you know, I only had two or three more years left as a DBA because everything was being, was going to start going to the cloud. Uh, won’t need DBAs anymore. Everything’s going to be auto everything. The cloud’s going to have everything. Nothing’s needed. You know, you’re everything. So, you know, fast forward, what is this almost 18 years and, uh, you know, here we are with the same issues. In fact, I would argue at least for databases, more issues being in the cloud.
Um, uh, so I, I would say I’m not necessarily an OG hater. I am, I’ve graduated into the, uh, been here long enough that all that is old is new again. And, uh, the same cycle. Sure. So, you know, I mean, I, I think the, the original death of the DBA was that, that, I mean, at least like the first extinction that I survived was virtualization. Right. Cause virtualization, I was supposed to wipe out like DBA is no longer necessary. Everything, uh, is in your own personal cloud.
Yeah. Yeah. Don’t have to worry about anything. Just do backup. Yeah. You know, all that great stuff. And then, uh, of course the cloud was the second, I think big extinction, extinction event for DBAs. And now we have the, the, uh, well, I guess I think AI is more of a general extinction event, not just DBAs, but they’re certainly part of the, part of the fauna that, that seems to be getting, you know, scorched off the earth. Uh, I mean, it got the dinosaurs, so it sure did. It sure did. Uh, you don’t see too many of those around anymore.
It’s a shame. They, uh, they say the same thing in Oracle, or at least at the, the one time I went to open world, which I would describe as a combination of amusement park slash, uh, prison camp. It, uh, wasn’t very fun.
Is that when you got that shirt? Yes. I, I, I, I, I, I’ve been wearing these shirts ever since I tried to avoid a repeat of what happened in open world, but the, the, uh, really rich guy in charge of Oracle was giving some, uh, keynote and I was going to watch the keynote in person, but they wanted to search my bags. Cause I guess everyone hates them.
Hmm. And I, uh, declined the search. So I, I, I, I didn’t get to watch in person. What was in your bag? He said that, uh, that’s under, uh, NBA. Uh, he said that.
Yeah. Pay the pay the pay was going away. That was in like 2017. And I figured, well, if he’s right, there’s no reason to pay attention to him. If he’s wrong, there’s also no reason to pay attention to him.
So I just like. Stop paying attention. There was no way for Larry to win. That was there. No, I mean, he started, he started with the bad opening statement. Yeah.
I mean, don’t you feel bad now? He, he did because of you, he couldn’t get another yacht. Hmm. He had to go buy an island boats or dried up. How’s he going to get to the island?
We don’t know. We don’t know if you got a boat, you gotta get a boat guy. Everybody knows that cat catamaran captains are very, very difficult to come by these days. I’ve watched below decks.
So, uh, I guess let’s, let’s, let’s go back to a Sean statement here because he said, Wait, don’t you want to state your opening position or did you write? I am.
I am a completely unbiased moderator. Oh, I don’t have an opening position. My job is to explore your positions. Okay. Oh yeah. Yeah.
Hey, so we’re going to, we’re going to do a lot of exploring of positions in today’s, today’s bit obscene. Podcast. Uh, so, uh, Sean, you said at some point that there are things that are not so good in the cloud. What, what, what, what are some of your least favorite things to see in the cloud?
What, what, what sort of workloads do not do well in the old cloudy space? Production databases. Production databases.
All right. No, it’s, I mean, that’s a little bit of a thing, but there are times when it’s, yeah, when it’s fine. But when you move your database into the cloud, especially production, right? It be, it’s not a, oh, let’s move this.
Let’s do this. You’re either. 100% entirely in and you move all your applications and all your web and all your content delivery and everything goes to the cloud. Or you’re just going to have a terrible time.
And then once you’re there, it’s kind of no different, you know, as you don’t, you don’t have control over everything. And that’s supposed to be the allure, right? Unless you do a VM, but then that’s not really the cloud.
That’s just a VM in a data center somewhere, which is the same thing as people did with VMware. So, yeah, or hype, virtualization in general. No one runs Hyper-V in production, Sean.
Come on. Don’t, don’t start. They might start now, but come on. Come on. Unserious. I mean, Brogcom did it. Did, did a Hyper-V a favor.
They sure did. It’s like, you know, oh, we have this thing and oh, well, our latency is really high. Oh yeah. That’s because you’re going from Los Angeles to some cloud provider through 17 levels of networking through 150 different ISPs and 17 satellites. Like, yeah, you’re going to be latent.
I don’t know what, well, it was faster when we were, well, yeah. And then the thing is, well, now you got to put all your stuff up there. So then it becomes not a, oh, we’ll do this and it’ll be great. We’ll get some HA, you know, we don’t have to run a bunch of this stuff.
We’ll get backups and some of the other things too. Well, now we have to change our entire architecture. Oh, they don’t have a service for this. Uh, yeah, it’s, you know, oh, we have to change how we do this now.
Oh, we got to re-architect this. It just becomes a whole thing. And maybe a re-architecture is needed or maybe a refactoring is great, but it. Yeah.
You know, a lot of people, a lot of people got sold on this, like a very, like a very easy lift and shift. And, you know, especially for legacy applications with a lot of legacy dependencies, a lift and shift is just not, not really, not really. Yeah, the lift and shift and the high performance.
Yeah. I do see there are some high performing, we’ll just be generic and call them skews out there from every cloud provider. How much performance you actually get from them, eh.
And that’s for the less managed things. And then you get to the more managed things and it becomes much less performance. That, that is, that is something that I have found as well.
Uh, so, you know, um, like, I, I think. Like almost this it’s, it’s weird because a lot of people did go from like physical to virtual. And there was obviously like the abstraction tax on that, but like, it was, it was very weird for me to see people go from virtual to the cloud.
And then there’d be some additional abstraction tax on top of that. So it’s like, you like, like, I remember like doing like, you know, oh, we’re going to like, you know, we’re going to do a physical to virtual conversion. We have to forecast hardware.
Like, you know, like in the past there’s been like, I don’t know, let’s just say like somewhere between 10 and 30% hit on performance and from various hardware resources. We have to account for that. Then like, they were like, okay, we’ll go to the cloud.
We’re already virtualized. So what’s the difference going to be? And then it was just like, wait a minute. Like we got screwed again. Like, no, we, like, we need so many more cores now. What is happening?
Yeah. And it’s again, when I say cloud, I’m not talking about any, any infrastructure. So in particular, it’d be, yeah, it’d be all managed stuff. So anything Pat would be.
Yeah. Like clearly known as Paz. Yeah. Platform as a service for those uninitiated going and watching. Joe is going to share his screen and show us some, some interesting things. Now.
This looks like, this looks like the Microsoft Azure portal. I am. I am so triggered right now. Are you? I am so triggered. So first of all, Sean, the standard series Gen 5 logical CPU is great for most relational work database servers. Yeah.
Cause you know, like when, when you do like in, you know, some like custom 2.3 gigahertz Intel from like seven years ago, I think. How, what a great CPU for my database. But like, I mean, it was a great, seven years ago.
Maybe it was, I mean, I’m sure, I’m sure it’s great for like, like power efficiency. Um, but like. I mean, I don’t know, like, like maybe we’re going to be like donating our extra CPU cycles in the cloud to be like carbon neutral or something. Well, like, I don’t care about the power efficiency of the data center.
Like, I, I got this whole long rant where like, um, you know, like, why do we use databases? We use databases or because they’re supposed to make things easier, right? It’s supposed to make development easier.
You don’t want your developers coming all day based on scratch. Right. And you know, like for a while, like. That doesn’t seem to stop them a lot of the time. Yeah. Like before a while, like things actually were getting easier, right? Like you remember back in the bad old days of 5,400 RPM hard drives and like, like page splits were super important.
And everyone said, oh, index documentation is super important. And, and, and then we like got better storage. We got better hardware.
Like things got easier for a while. Right. And now we’ve somehow made things less easy for our production workloads. And I, uh, I think that computers and software should serve us and we should not be the servants of software.
And for the cloud, it really feels like sometimes you are a servant of, you know, these CPUs that were great seven years ago and these storage latency is, which are unreasonable. They, they, they, they backtrack quite a bit. It’s solved by just paying more, right?
If you just pay more, it just, it gets better, right? The more, it’s like the more, you know, except the more you pay. Yeah.
Nothing wrong with that. Also those CPUs, it’s entirely possible that they have not been patched with various Spectre and Meltdown mitigations and they might still be pretty good. Those patches nerfed a lot of stuff, but you know, just throwing it out there.
And it, one thing I’m curious about is like, there isn’t anything inherent to Azure SQL database, which requires that them run on old crappy CPUs. Right. Cause that’s what they could get off eBay.
Well, I mean, like, I do have to wonder if the lack of diversity and like super high spec CPUs is kind of our fault. Like maybe there’s no demand. Like I, I see, I don’t know a lot about it, but I like, I see Amazon trying to compete on like performance.
Like, Oh, Hey, you, you want to run your database in the cloud? Like here’s a 4.5 gigahertz CPU from last year with 32 gigs of memory per core or, or something. Right.
Whereas like, I, I mean, I’ve never seen an Azure SQL database offering that’s like anywhere even close to that. Oh, like, we’ll like you, you will want more memory. Okay.
We’ll give you eight gigabytes per core. And that’s like our super size option that that’s like all we got. Like, I think there are some that are like 13 gigs per core, but like that, that knob doesn’t turn well for me, especially with a lot of the workloads I deal with, because like, you know, like a lot of this stuff, you know, like, and granted there, there is some like, like far, far down the road wisdom that as you remove IO bottlenecks by like adding memory, you could theoretically add CPU bottlenecks. Because now everything is just banging CPUs up 100% all the time.
But like, for the most part, like most of the workloads that I deal with could survive very easily running at between like 60, 70% CPU with far fewer cores. But they just need way more memory because the amount of data that they’re dealing with is like, just not come, this is not compact well into what you get available through the usual like web portal knobs for adding memory to these instances. Like there have been, you know, there have been some stabs at improving that with like the constrained CPUs and with the memory optimized stuff.
The problem that I run into is that like, aside from, you know, them not being available across all regions, a lot of the time is also that like, you know, the cloud providers, and I’m saying this generally, they know what you’re using them for. They’re like, they’re like, they’re like, they’re like, they’re like, that’s for a database. That’s for your expensive thing.
Of course, we’re going to charge you more for like, like, come on, like, like, you’re not be not putting like your app servers on that. Like, you don’t you don’t need, like, you know, 768 gigs of memory for your app servers. We know what you’re doing with that.
That’s where you’re putting the $7,000 a core thing. Yeah, it’s a there’s definitely a lack of flexibility there. Right. And it makes sense. If you think of it from a cloud providers perspective of if we let people choose, and even if you charge them for what they did, right, this is how much per gig of memory, this is how much per core, whatever, even if you let customers choose, that would be a nightmare. Because at the end of the day, you still have a set of computers with a set of specs. And if you don’t have those at exactly 100% on memory and CPU usage, you’re losing money. Right? So from the cloud providers perspective, whether it be Google, Amazon, Microsoft, whomever, you need to get that packed in, you need that kind of over subscription, almost model in order to just make it work.
Yeah. Right. You’re gonna say, Oh, you want your one fourth of a virtual core for free for 400 hours a month or something. Here you go. We’ll let you do that as a way to get in. But it’s also gonna run on that low end hardware that you’ve already over the five years have, you know, amortized out. You’re just trying to get every every last dollar that you can from it, which I don’t blame them. But at the same time, it’s Yeah, I wouldn’t put a actual production workload on something like that.
No, but like, but cloud providers, the offering is for Azure, like, like, that is the production CPU for Azure, like, for other providers, it might be different. Yeah. And that’s kind of the problem, though, right, Joe, is that we’re sitting here talking about CPUs, you know, brought, Broadwell Skylake, and the whole point of the cloud was it was supposed to abstract you, right? You’re not supposed to care about those things. You’re not supposed to know. You’re not supposed to even know about them, right? Maintenance. Yeah, that’s done for you. You shouldn’t even know, like, all these things that we’ve gone to. This is one of my issues is Oh, well, the cloud is gonna be easier. It’s gonna we’re abstracted from all this stuff. And then what do we get? Well, we’re on a when you were showing that the screenshot, Joe, we’re on a gen five. Well, what about is there a gen six? Did we get on a gen four? What about a gen 68,000 million? You know, why are we we need to be pinned to a to a gen 12? Because we’ve noticed a performance regression in gen five? None of that should be a thing. In my opinion, if we’re going to client like none of that should be a thing on a on a Paz environment.
Well, but you get different offerings like you know, as you said, you spend enough money, you’ll get better performance. I mean, maybe it’s hidden in some like super elite, like diamond platinum level customers only. And I don’t even I don’t even have access to the portal because it would cost more than Eric’s favorite booze. We call that the Walmart club. Yeah, like maybe things I don’t know about but like, like, I’ve I’ve never seen an Azure like, oh, you want 4.5 gigahertz CPUs, you can get them will just cost you a lot of money. Like as far as I know, this is not available. Like you can get a bit more memory, you can get faster discs. I think they have some premium CPU that they say is better, but it’s still not like the best on the market.
No, but a lot of this is the fault of cloud providers, right? Because what they did was they marketed it as like cheaper, faster, more secure, more reliable, all these things, right? There were all these promises and promises made by what the cloud was going to give people. And then people got there, and they weren’t really getting all of those promises met. And then like people wanted to know what was underneath the cloud because they were like, wait a minute, like, we’re not seeing the performance what the like what’s in there, like what’s under this. And so like, like, they had to start like putting these like various things out there. I do think that there would be like, I understand why, like CPU and memory are not separate knobs that anyone can turn at any time to allocate more or turn down like the allocations.
But what always bothered me is that like, there weren’t like, there weren’t like, just like more, especially in the database space, there weren’t more sane offerings, especially for like known product limitations. Like if you take SQL Server Standard Edition, right? Like there’s obviously limits on CPUs, they change from year to year. And there’s obviously limits on memory that have changed at least once. And perhaps there’s another change coming with SQL Server 2025. You don’t have to blink once or twice to confirm that. But like, like, there was almost no normal sized box for Standard Edition that was like, hey, here’s like eight to 16 cores with like 196 gigs of memory. It was always like, well, you can have eight or 16 cores, but then you have like 128 gigs of memory that you can’t touch. And you’re like, I don’t need it. Like, just give me something that, give me a glove that fits, man. Give me a glove that fits.
And then like, uh, manage instance with like, uh, like, like, like their, like their cap on memory is like 480 gigs, I think for a lot of them. And you’re like, like, like, like, I don’t need 128 cores and 480 gigs of memory. I need like, like maybe 32 cores and like two terabytes of memory or a terabyte and a half of memory. Like, like, like, like I like, this is a database, you know, what you’re dealing with. We need the database. We need the memory. You know, uh, and like, you know, trying to add like, you know, like incrementally faster storage offerings behind the scenes. It doesn’t cut it for them for those things.
Either of you guys know why those memory options aren’t really available. Like, like, is it just overall industry capacity? Because in terms of memory, right? Like you buy it once. It’s pretty low power uses, especially compared to your CPU. You know, there isn’t a licensing for anyone on memory at any point. Like, you know, like if any, not directly, but indirectly. There’s yeah, the biggest. So to answer your question, you know, kind of the biggest, uh, items there are going to be the density that you pack it to. And then what skews you want to sell it to, right? Like if you’re trying to really maximize out your stuff, there’s a per core kind of throughput that you can get with memory.
So if you can do, depending on what your bandwidth is for those kinds of DIMMs that are in there, uh, you know, you don’t want to put, if your per core bandwidth is say 80 gigabytes a second, you don’t necessarily want to put in so much memory that pretty much can’t go through it. So if you’re giving out, you’re packing that with whatever CPU you’re getting wholesale, uh, you, you don’t, you basically, you want it to be matched pretty well, right? You don’t want it to be like Eric was saying, you don’t want a server that has four cores and two terabytes of memory.
Cause then you’re going to run out of cores before you satisfy the memory. And you don’t want a server that has a thousand cores and 64 gigs of memory. Cause you’re not going to get a whole lot done.
So for databases, for other things, yes. For database. Yeah. It just seems like there was never a good alignment on cloud hardware, specifically for databases. Like that, like that’s the thing that’s always bothered me the most is like, like even like, uh, like various platform as a service offerings, like no, just no one got the numbers kind of right on that.
Like it always, it always baffled me a bit that like, like, like what, like, like why, why do you, why are these like these insane jumps in CPUs with these like minimalist jumps in memory? And you’re like, I, I, I just never understood like, uh, like even from like a, a slick, a server density perspective, like, like, like, like, like if you’re, if your host VM has like, let’s say 256 cores in it. Like, like, like why not limit the CPUs and bump up the memory and like what people sort of like, you know, be more dense in those spaces.
It’s, it’s, it’s, it was always, it was always odd to me. I have a model, which might not be correct, which is cloud provider buys like a hundred thousand servers or all the same. And then some, some set of servers, but we’re going to use these for the database workloads.
And there isn’t necessarily as much like care and careful attention that we might want because we’re database people in terms of, you know, this subset, these specs are going to be totally different. I don’t know if that’s true. That’s just, just like a, No, I think, I think that’s, that’s probably accurate, but then, then you have cloud providers that now have like bare metal offerings and you’re like, We’ve just come full circle.
Like why, why, why did we do this in the first place? It’s honestly, back to my original kind of statement of, it does make sense to some situations. There was a company that I was helping and, you know, the, the CIO basically said, you know, I’m tired of running data centers.
I’m tired of paying multiple ISPs and dealing with all this stuff. I’m happy to pay more and put it in the cloud and not have to deal with this. Yeah.
When there’s an outage, it’s not everyone that you like, not your employees hair on fire. Exactly. It’s, you know, there’s, there, there are some, there are definitely, there’s definitely some wisdom in this stuff that you can, like the, the, the worries you can offload to the cloud. But, you know, um, Then they get upset.
Well, what’s, what’s the last ETA? What’s the status update? What do you mean? It’s not DNS. What do you mean? It’s DNS. Yeah. You know, like you’d be real careful with those SLAs on the cloud provider stuff too. Real clear.
Cause you know, fine print. Yeah. Cause you know, like you’re, you’re, you’re not an important face to them. Like, like they have, they have big customers who are important faces. Like, like you with your, like, you know, like, I don’t know what, like maybe like a hundred VMs. Like they’re going to look at you and be like, yeah, okay, we’ll, we’ll get to you.
Don’t worry. Like, fine. You’re like, yes, we take a number. We’ll get there. You get, it’s like, you know, deli counter rules with, with some of this stuff.
I have a question for Joe. Oh boy. He was one of the. Oh, wait, wait, wait, wait. The MCM is asking me a question. That’s how this works, man.
This is quite a day. Oh, well, you know. All right. Go ahead. The viewers, what they want. So I’m ready. So are there any workloads that you believe are actually good for the cloud? Yeah.
Like anything that’s, that’s a non-database workload, because it’s not my problem. And since it’s good for the cloud. Um, well, like, like I wouldn’t go so far as to say that. All database workloads shouldn’t be in the cloud because, you know, like I’m naturally drawn towards like super intense workloads.
Like where are I going to push the limits of SQL Server hardware? Like that’s the kind of thing that I like to work on. And I like to think about like, there definitely are a lot of, you know, pretty bog standard workloads out there and they might do just fine.
Um, on, uh, cloud hardware, or at the very least they’re, they’re, uh, they’re today and they haven’t gone out of business yet. So they’re, they’re, they’re, they’re getting by somehow. Um, I think your earlier point was pretty good in that, you know, cause I remember hearing the same thing years ago where like, we were thinking about the cloud and we wanted information about the hardware.
But with respect to SQL servers and their response from Microsoft was, oh, well, why are you even asking that? Like we’re extracting it, like, you don’t have to worry about that. So, you know, like, if, if you go in with, with that, that position of.
Like, I want the best possible hardware. And it’s just some, like, you know, like within the big amorphous cloud, I want the, like, uh, the, uh, densest, like sweetest, most fluffy part of the cloud. All for me.
Right. Like that, that, that doesn’t really. Yeah. I want the part where the rainbows are. Yes. Yes, exactly. No, but like, so like, like for, for cloud workloads. So like, I think we can all generally agree that, um, super high end, super, like, you know, like, like workloads with very high performance requirements.
Are maybe not a great fit for platform as a service. You can, you, when you have enough, when you have enough granular control with like the, like a VM host in the, or like a VM in the cloud, you can probably do some better work there. But for like, you know, someone starting out an application, if you think about like the initial spend that they might have to make on infrastructure, people to manage that infrastructure, people to deal with all that stuff.
Like, like, like that could, that could be a, like a really big hurdle for them to jump. But if someone like is just starting out an application, they can get, you know, lower end hardware, uh, performance is probably not like, like if they over spec some, like a, like a startup startup application, like they can get away with like, you know, maybe the extra, like five, 500 to a thousand bucks a month on some slightly bigger hardware to compensate for stuff. Not have to worry about like any initial purchasing, not have to worry about investing in people to manage it, not have to worry about all the stuff that goes into that.
They can focus on having an app, developing it and making it accessible, right. Getting some money in the door. Then from there, you know, you, you, you get into stuff, but like, you know, I think the, the, the general problems that we’ve dealt with and the problems that we see probably like more day to day is with like, you know, legacy stuff that got pushed into the cloud without really any forethought or investigation.
Uh, like higher end workloads that, you know, like maybe they started there and they like outgrew whatever they were on, or maybe someone just, again, tried to lift and shift it there. And no one was prepared for all of the things that come along with moving an application to a wholesale new environment. Um, you know, I, like I’ve seen some people, uh, you know, like get like, like, Oh, we started moving all our application servers to the cloud and the latency between the application servers and the database server killed us.
So we had to hustle to get the database in there and like the latency is gone, but like performance is completely fell apart. Cause we didn’t do a good job with the migration. So like, like a lot of, there’s a lot of stuff that like, you know, that comes along with the move to the cloud that just doesn’t go well for people.
And like, like, I think those are the problems that we see. And I think that’s where a lot of like, you know, the sort of like the cloud hating comes from. I think you hit the nail on the head though.
Uh, in that’s the quintessential. Perfect use case in my, in my opinion. And, and what you said of, let’s say you’re, you’re an app developer, right? You write in whatever language you write in and say, Hey, I need, yeah, I need a database.
PowerShell. That’s, that’s absolutely what Eric writes. I was like, I need a database, right? I need somewhere to, to put this stuff and I’m gonna, I need a web server and I need this.
I mean, it only makes sense, right? You click through a couple of clicks. Yep. You got a web server, you got a connection string to a database. Like you’re ready to go.
Yep. Right. You, like you said, you didn’t have to deal with, you might not know, you don’t want to spend your time setting up infrastructure and setting up and doing all this stuff. Active directory. Yeah, exactly.
And be working. You could be working on your code. Like you said, getting your product out the door so much faster, so much easier. And then if you need to scale up, great. If you need to spend a little bit more money, great. And then if you need to iterate on that, I’ll scrape.
I think that’s the perfect use case. Yeah. Honestly. You need to hire a consultant to help you save money in the cloud. Great. Exactly. But I, I also agree that, yeah, the legacy was a problem. I even think a lot of the new stuff is a problem, even with the quintessential best use case.
And that’s just because it’s, it becomes, and I’m not, I know this isn’t a talk about AI, but it becomes the same issue I see with AI where, well, it’s someone else’s problem. I don’t actually need to understand what I’m doing or know how anything works.
I just know click it goes right. Bigger number, better, more clicks, better. Everything’s fine.
So then when there is a problem or when things don’t scale, right, or when stuff happens, it’s, oh my God, well, I don’t know. Well, this and this and this. And I think that also leads to a lot of quote, you know, bad cloud experiences as well. And it’s, it’s because at some point when you make things so easy, which is good, that you remove a lot of barrier to entry, right?
So friction is gone. That’s a good thing. But then people get lazy. People don’t care, especially in today’s environment where every, no one wants to be an expert. No one wants to understand how things work or how it goes.
They just want to click on the next thing and go and click on the next thing and go. And that’s fine until something breaks. Yep.
And so I think that’s, that’s the downfall of that scenario. Yeah. Like, you know, you get people who are fine continuing to rack up the technical debt without ever paying off any of the interest on it.
That’s, that’s, that’s what I see quite a bit. The other thing would be like people just picking the wrong target for their cloud migration. That’s another big thing where they, they just weren’t prepared for the limitations and restrictions that are the things that they’re not allowed to touch in these various places.
Probably one of the, like the most common ones I see are people who are like, we moved to Azure SQL database. Now, like everything slowed down.
Like what happened? I’m like, like, well, like, let’s, let’s look like if they still have the old server, like we take a look at that. And I’m like, oh, well, this is how you have it set up over here. And this is how it’s set up over there. And they’re like, well, can we change that?
And I’m like, no, like biggest one, probably like cost threshold for parallelism. It’s like, it was set to like, they had to set to like two, like one of my clients had it set to like 200 or 250 when they were like, you know, on their on-prem, because they’re like, that was just, I know that was, but that was what worked for them.
They also had maxed offset to two, which I thought was nuts. But then like, you know, they, they tried setting it to eight once and it was just like massive thread pool weights everywhere, but different story for a different day.
But like, just like not being able to like change cost threshold for parallelism up to where they had it before they were, they had to like, like, it was just like a lot of tuning work to get everything to a point where it was no longer just breeding the server all day.
I think your earlier point was, was good. I’m always doing it as like, I have an existing legacy application. It’s not in the cloud, we’re moving to the cloud.
And, you know, like if you’re starting out in the cloud, if you’re used to performance being what it is, I think it’s definitely easier to adjust. Joe, are you saying if you’re cloud native, that is that what you’re going for?
I mean, like, I have like, I have like two prepared rants here. I’m going to give them both. You can take a breath between them if you need to.
Let me share my screen here. Yeah, go for it. One straight conscious stream of thought. I’m just going to mute myself. Like on this subject, like, I don’t know like where this marketing stuff comes from, where, you know, like, we’re bringing the seats.
You know what I was saying before, like, I don’t think that developers should be spending their time tuning queries if they can. Like, you want developers to make your application better. Yep. But like, even on Microsoft’s website, they always talk about, oh, you, you’ve developed workloads, well, you can put them on the worst hardware possible and even like, you can save money.
So first of all, this is basic tier, five DTUs, IOPS, one to four IOPS per DTU. So that’s between five to 20 IOPS. All right.
I’m going to, I’m going to get my, my virtual stopwatched out and I’m going to count to five. And I’m going to see if I can do it faster than one second. One, two, three, four, five.
0.76 seconds. If I can count faster than the storage is giving me IOPS. 0.76 seconds. I think we have a problem. 0.76 seconds.
And like, I like, I don’t know like why this stuff gets pushed. Like, you know, like developers do make a lot of money. It’s hard to hire good ones. Like let’s make them super inefficient and make them hate their jobs. Cause that’ll be better.
Like, I don’t know like why we’re pretending that something like software industry is like, so cutthroat and competitive. Cause you know, like if, if you look at the profit margins for software compared to like real companies, I mean. 0.76 seconds.
Real companies. 0.76 seconds. It’s not like an order of magnitude almost in, in, in some cases. 0.76 seconds. Like, like, like, like it’s like, I don’t know why I had like software companies like, oh, you know, like, like who we’re giving our developers. 0.76 seconds.
These like, uh, these, these, these, uh, two V core machines that we’re saving so much money. 0.76 seconds. And like, there’s another company and they managed to get development machines down to like 0.5 V cores. 0.76 seconds.
I know we’re saving so much money. 0.76 seconds. And then another company finds a Pentium three server in a dumpster and they throw the workloads on that for developers. And finally, thank God the business is safe. Like, like, like, it wasn’t real cost.
0.76 seconds. So I don’t know, like, I don’t know where this stuff comes from, or like, why anyone. 0.56 Like I it is. 0.Нет serviços here.
0.80 seconds. 000. 0.75 seconds. 0. Cohen would be.
0. enam hour. 0. chartsred żebyarnos to değilif C mitras. 0.ense Zoom um, slow I have Señor thank you, this could hit that button by hitting down tiny 诉, uh, we’ve got that to compte. cloud services they say it is great for developer workloads but that’s a before you go on to to rant number two joe i i echo that though in the i’ve worked at places where you know they’re paying people hundreds of thousands of dollars a year not counting any benefits or anything like that right and then it they make it sound like it’s the end of the world if they have to spend a thousand dollars on a lap on a new laptop for you that’s not 12 years old that has 64 gigs of ram and it’s like why like you said why would you want to hamstring your your highest you know pay like your your main thing with essentially what is what 200 a year you’re okay with spending 200 grand a year but you’re not okay with spending 200 a year to make that work but i i don’t understand i’m with you well there’s there’s two parts of it right like um if if you’re so like let’s say your developers are developing on azure sql database for like an application that works in azure sql database if they’re developing locally on developer edition with like the hardware right all right there they might have a very different idea of works on my machine and like like when it gets then when it gets up there they’re like wait a minute like all this stuff that happened here isn’t happening there and there’s a lot of other weird stuff happening there and i don’t understand it so like i guess like i think there is some like there is some correctness in making and having people develop where they at like on the same platform that the application lives like it would almost be like if you have a like a like oh like we have a sql server application but you’re going to do all your development work in postgres and we’ll figure it out later right it’s just like it’s almost that level of difference between like what you’re like where your development stuff happens and where your workload actually runs so like i think there there is some there is some wisdom in like having people develop there as well but um you know at the same time like if you are what’s a good way to put it like if if you if if you don’t have people who are smart enough to deal with um the way that things work in a in a certain place then they like you you need you need to sort of rethink that strategy a little bit well and to be fair i mean like i agree with you but i shouldn’t be able to kind of fashion your iowa operations well yeah absolutely right like and like like that is the model that microsoft for whatever reason says is great for developer vertical it’s like if you i don’t know if you some like p8 or whatever is is is that hopefully that’s a real thing and you put your dev environment on a pa like i think that’s totally fair um i’d even go as far as to say if it’s too slow for developers it’s probably too slow for your application and you might want to rethink like what you’re doing there um i like that’s also true yeah nowhere clause just uh uh filter in the app it’s better yeah that’s right i’m ready for a rant number two bring it on um and i mean like this is about cloud migrations again and how sometimes you can kind of end up in a state that doesn’t really make a lot of sense and like you know i mean like to be fair like it is our choice like um i’m uh turning into cloud again yeah joe is passing through a cloud um you know because like you you’ve not on the azure sql database or even on the azure platform right like you have you have managed instances you have like vms in azure if you want to do everything yourself and you have azure sql database right and all those have abbreviations that i can never remember don’t forget hyperscale hyperscale is part of azure sql in the cosmos come on come on i’m pretty sure that’s true and you know like oh but like like and i’ve seen guidance which is like you know if you’re doing a lift and shift azure sql database may not be right for you maybe you should use match instances maybe you should use just basic vms you’re everything yourself i have seen that guidance but you know there are some companies out there who are going to lift and shift to azure sql database and i feel like the most natural path doesn’t make sense um so but like you know we we have talked about dtus versus the micro model right like no one knows what what what what a dtu is you uh can’t fold over your your licenses you already have in the vtus so i understand why people would want to pick vcore especially if you’re doing a lift and shift like that seems to make way more sense than utu most of the time and then the second session is do i want an elastic pool or not as far as i can tell it’s it’s the same cost it’s the same cost per core like i i i won the pricing thing and i changed elastic versus not elastic it was the same cost and you know like like you’re probably not gonna have your databases all running at the same time and like it’s you know instead of trying to scope out like i have 20 databases let me figure out the v course for all of them i have 20 databases just you know i’ll throw them in a bucket and i i think i saw sean expressing dismay at elastic pools i’ll all let him chime in after my my rant’s done but like like like if you think about the most natural path you know v cores elastic pools you’ve gone from like an on-prem instance of sql server to instances of sql server and you can connect to it and you can see all the databases but they but they like can’t talk to each other and you have like all of this azure sql database limitations no asian jobs no cross database prairies blah blah blah you have different performance like kind of like like it just feels like like like the original dream of azure sql database where you know you’re you’re one database on a server and you have many friendly databases and you all live and coexist together like it it just feels like like kind of like a poor implementation of what you’re used to on-prem well yeah but that’s why managed instance came along right and it’s like two different two different target audiences yeah no i mean like like it is but it’s it’s hard to find people saying good things about managed instances right but sean you you wanted to say something about elastic pools share share that’s your wisdom no it’s elastic pools is the same issue that that i saw with as you said people on-prem where they put 600 databases on an instance right a lot of people out there don’t necessarily i won’t say no any better but don’t necessarily have the forethought because everything you know everything is a now now now now just now and then you know nothing’s gonna happen just now and just it’s it’s a problem for later and so you get to your two databases in elastic pool and that’s fine then you get to 10 oh look we’re so happy we’re saving money we got 10 databases on our elastic pool then you get to 100 and stuff’s not running right at certain times and you’re like boy i wonder what’s going on but of course nobody cares enough to look into it and then you know for the company and then you get to the whatever the max is and you try to add that extra database you get an error so then you open up a ticket what we’re getting an error when we try to to open this okay what’s the error well the error says uh you’ve hit the maximum you know 500 databases in it but we but we need we need this but but we need 501 yeah we what we don’t understand why we’re getting the error and it’s like first of all wow reading is apparently hard these days and two well yeah you’re gonna have performance problems it’s just and then and then you get the well we shouldn’t have to to scale up or scale down or you get the opposite well we’re having a problem with our last book and what’s going on you know oh well we did a scaling operation this hour and then this hour we did another one then this hour we did another one then this hour did another one and we don’t know why we’re getting different performance different times and it’s just there’s there’s good things to it i’m obviously highlighting the bad things right now there’s a lot of good things to it it’s just the elastic pool i don’t like because as i’ve as eric well knows and as you well know people don’t know what their queries do they don’t know how much performance they actually use on things and they just keep throwing stuff at it until it breaks and then everyone’s super surprised that it breaks like this comes back to like what i was saying earlier it’s like everyone has fallen in love with the abstractions and that you don’t have to think about anymore developers fell in love with orms cool i don’t have to think about SQL anymore i throw it out there like oh i’m in the cloud every cloud provider says hey guess what you don’t have to think about it anymore like you know you go ahead like you know have fun all right like you know like we’ll take care of it all for you and then you know it’s coming along and all of a sudden they have to start thinking about stuff and they’re like wait a minute i’m allergic to thinking no i can’t hey i don’t want to do this it’s like i like oh i was told i wouldn’t need to think about it i was told i wouldn’t need any experts like that eric do you have any do you have any advice for someone who maybe doesn’t want to think about sql server and wants to offload their thinking their thinking to someone else yeah i mean i am i’m a very affordable object for a SQL Server i was gonna say yeah i would recommend eric my rates are reasonable as as everyone well knows so don’t be shy speaking of reasonable i want to tell you something unreasonable all right that would be my uh final pre-prepared rant all right the great clr scandal of 2016. so as everyone knows as your sql database used to support clr functions and well there’s things aren’t functions i’m missing college functions that’s fine they realize that there are some i think it was like a like like like a security vulnerability where in theory if you’re a super sophisticated attacker you could use clr code to like read shared memory and like read other customers data um so then clr got turned off and you know if you were currently using clr in production and as your sql database well that’s too bad it’s turned off and that they have said oh we think we can turn it back on in uh six months um as of 2025 it is not back on in general now i i have heard rumors that you know if you’re a super elite diamond platinum card uh customer you can get your clr functions approved maybe it’s even sean who occasionally approves yeah rubber stamps for you um but you know like a lot of us aren’t super diamond platinum elite customers of microsoft and like the wall like things and like that was really the instant for me where when someone occasionally asks me you know like you know hey joe what’s your opinion i would say well you know what about like i i don’t want my cloud vendor to break my application without any warning like you know i’m not saying microsoft like should have left clr on i’m i’m sure the security problems were very difficult to solve and they probably did their best but in terms of the business reality if you if you have relied on cr and you know you weren’t again the super platinum diamond whatever customer you’re you felt a lot of pain you had to work around and you know and like all of us know how difficult it can be to you know some clr as well if you’re just splitting strings or whatever sure do it in t-sql but there’s all kinds of fun things you can do which are like spooky hashes to do in uh native sql yes like a spooky house and i have i have heard the argument hold on john i have heard the argument that you shouldn’t be doing like i’ve heard the argument that you know if you’re making new application all the restrictions on azure’s database are actually good because you shouldn’t be doing those things anyway which i think is fair like you know like it’s important to make the mess and taste good so you know the the uh the children like to take it like you can say that um and you know like i’ve had the displeasure of using the service broker and transactional uh replication replication so you know like if if we can spare the next generation of developers of dbas from those technologies like okay but it isn’t true that like every single restriction like you know like i had a problem with the hash bytes and at the time i was like kind of a pseudo diamond platinum member so i asked microsoft for help and i thought that that that they would they would resolve the scalability issue instead they said oh why don’t you just use clr and when we did so you know like like if like if the vendor itself is saying hey you know there are some limitations on our platform clr is still good then use i i think the argument that clr functions are an ancient relic of the past to be looked at by historians only isn’t going to hold water especially because i looked at sql server 2025 stuff today and they talked about the freedom to use different programming languages within sql server and you know what functionality used to give us the freedom to use different programming languages in sql server take it away joe clr yeah um go ahead eric no i would you know defend it sean defend it so no my my thing this is again my opinion here just opinion of one guy yes is is that one very handsome gentleman oh thank you just for you uh if you’re using clr you’re doing it wrong that’s just my opinion because you like for your example for hash bytes i i understand the the motive behind that i don’t think putting in the clr is correct i think for the amount of money that you’re paying for sql licensing but if you’re gonna do a very cpu heavy amount of hashing do it on the client side do it on another on another application server why do you want to do it on your database server you know if you look at at some of the stuff that i i don’t know if you remember because this is getting pretty old because i’m pretty old is there used to be uh soap endpoints for sql server remember those right yeah again why that’s what a web server’s for like i don’t know that this is not a there are things to make developers lives easier there are things that are you know there’s a lot of things that can make your life easier for example you can take four grams of ibuprofen a day and feel amazing and nothing hurts it doesn’t mean whatever hurts still doesn’t isn’t broken and wrong it just means you don’t necessarily feel it today well it’s just that’s that’s all mind over matter sean well it is just pretend it doesn’t hurt that’s right but but the clr thing i mean you also have to look at the model in which it runs there’s a bunch of stuff could i see clr for a couple things yeah i don’t agree that the geometry and spatial and stuff data types where it ended up being clr i think those should have been a native item uh i think the format function too yeah i mean there’s there’s a lot of stuff again there’s a lot of stuff i don’t think should be at a database level i was kind of upset when xml got thrown in there do you really want to do xml parsing and how else do we store query plans sean we need our query plans stored somehow xml was the choice so yeah if you if you watch that which i i would i would encourage you to watch that uh intense but you know i think again all that’s old is new again right we’re basically getting soap endpoints again uh you know uh the clr what do you mean with the vector stuff right i mean kind of yeah right like you’re getting all this stuff all over again i don’t i wish there was a different process i again i i don’t have any say into that i don’t know anything about that i’m just saying as a as a dbn developer who would use it there are times when it’s really nice but there’s also the thing of well it lets me do a i’ve been tasked with doing a i’ll just do it and as joe said service broker might fit some of those does anyone here want to deal with service broker not particularly no but like where where i where i sympathize with the need for these things in a database is that so much of that stuff needs to end up in the database anyway like short like like like what you were saying yeah you could use like you could offload the generation of the hashes to uh to an application server something that is far cheaper to have you know three billion cpus in and get it done quickly but all those hashes need to end up in the database anyway so then you’re looking at like getting those all those hashes from the application server into the database and if you’re at the point where generating all those hashes is so computationally expensive that you need them offloaded then like getting them into the databases like that’s going to be kind of ugly too and if you need them as part of some etl process it’s like like like when you’re swapping all this stuff around so like like i understand why all this stuff ends up is an like something you can do in the database like i understand why like you know like why like why people need to do this like where the data lives but um i mean like you’re all you’re you’re also right in that like you know the limitations that are imposed on azure sql database tend to keep developers more honest about what they put in the database right because like obviously a lot of the stuff that like you can’t do there is just like oh they’re kind of like you know what thank god you can’t do that here like like i’m actually kind of relieved you’re not you’re not allowed to do that anymore uh but really let’s be honest the the most bright shining beacon the most the best part of azure sql database is that it uses recommitted snapshot isolation by default can we can i get an amen can i get an amen and read committed snapshot isolation by default when is that when is that coming to on-prem sean tell us all about it no clue you can enable it you can enable it should be but should be by default though yeah there’s there’s there’s something there about um the limitations actually keeping you in check i don’t necessarily agree with limitations but i understand the the need for them in certain locations i again i don’t think any of the offerings from any of the cloud providers are inherently bad i just feel that people look at them and treat they want to see them as new and shiny but also treat them as the old thing that they’ve been using forever yep and there’s a there’s a mindset there’s a dichotomy shift that needs to happen when you do that and so many people are stuck in the old mindset and then they bring that to the new and it causes problems i don’t see a problem if you’re in the new mindset and you’re doing it with minus the no one wants to be an expert everyone wants to be lazy you know oh ai write me a query okay cool copy paste next or like you said orm generate me a query because i’m too lazy to to write a where clause yep you know and instead i get a 17 long union all yeah it’s yeah yeah it’s it’s the the general uh you know like um there there are other words for it for the ai era but you know it’s it really is the the general indemnification of above people like like in everything that they do uh like you know like just abstract all the hard thinking away to these other things and you are free to just do whatever like you know like indulge in whatever dalliances you know give give you a sense of whimsy at the moment and like there’s no one it’s very hard to find people who are very deeply invested in things uh to the point where like you know they could write a book or even like a blog post about something that shows any level of like real technical understanding yeah you don’t have to write a book right it’s just understand like you said about rcsi like when’s that why isn’t that standard how many people could even you know give a reason why that would be a pro or a con right how many people would even know about isolation level yeah well yeah as i found kendra little something about balloons yeah something about yeah kendra little world-renowned balloon expert are they can tell if they are red or not it’s yeah yeah i would know i would like that depending on your isolation level no wait no it was marvels it’s always right it was like black marvels and white yes yeah see but the original the original post on that was jim gray actually r.i.p uh jim gray was the original person to write a post about that and the funniest thing is that post was about snapshot isolation not about read committed snapshot isolation but it has it has slowly morphed into the oh read committed snapshot isolation has all these problems but no the original post was about snapshot isolation but that i mean that my point is like i don’t expect someone i would expect isolation levels to be a basic thing the developer understands like if you’re writing uh something for linux uh or windows right you’re going to use an api that’s publicly available i don’t expect you to understand how every little thing in the kernel works right to use that api but i would expect if you’re having to give a handle to something or you’re having to give a you know whatever to a thing that you understand what that is that you’re supposedly working with right like the base just the basic yeah i was once told by a microsoft support person that snapshot isolation did not apply to comstore because comstore doesn’t store data in pages snapshot isolation only applies to roster data that is a very interesting take so if we have a moment of silence for that um but i actually do want to talk about sean’s uh well why don’t you do the hashing in the application because you know we are people who want to be experts and i like to think that many of our viewers want to be experts too because you know why else would you watch sepa server content on youtube right in fact i even know some experts who won’t even go that far that’s true um can’t consume it undigested uh for our workload and i’m not saying that it’s true you should always do hashing in sql server and i’m sure there’s some cases where it would be better to not do it in sql server um for our workload um for our workload it’s a very big etl process random middle of the night um these hashes can only be calculated like in the middle of of etl so like just and if you want in etl like data size i don’t know like 10 billion rows and 10 terabytes of data so like if i wanted to do in the application like even if it was like free to compute in the application like like zero cpu zero everything like i don’t know how i would get the data back into my 10 terabytes of staging tables efficiently if you do an updated single threaded and like you know like like the updates are going to be way more expensive than the hashes or if i restart all the data again to get parallelism again that’s more expensive like like for i mean like it’s not so much that like the hashes didn’t cost very much with respect to the rest of the workload um well in theory they didn’t cost very much the hash by tip limitation made them cost much more than they should have um i think the other thing that makes it easier for us was you know like we have like a 96 core server 112 core server whatever this is running in the middle of the night like i want that server running as hot as possible i want to be able to like uh like a steak on that server you know like like they’re like you know like talking about like like like like like like alerts for using too much memory like you have cpu at like 90 95 it’s fine it’s it’s 4am you know like who cares like you’ve been a nice bath in the morning yeah like like even if there was some way to get my hashing outside of sql store like i don’t think that would mean fully impact the size of the server so for my workload you know for my special workload which no one else could possibly talk about or know anything about and now the rules apply because it’s my special important workload or it was i don’t think hashing would work on the on an application server and i mean like i don’t know like you know like we can joke about using other programming languages and sql server this is the direction microsoft’s going you can see a lot of them run for a while i mean it’s so whatever whatever feels unreasonable to me too on rare occasion if you have something that that that tsql is very bad at or it’s impossible doing tsql and like maybe it is a maybe it is a shortcut and you’re better off like not doing it in an ideal world but sometimes shortcuts are the fastest way to get your destination yeah so like what right so programming languages outside of tsql that we can use in sql server now it’s like the first one was sort of python and r i believe java came along what what did javascript get added is that one that that we can do now or is that oh not man that would be wild i don’t recall no okay yeah i don’t i don’t have the full list because it’s not something i do very yeah i actually i think i ran python once a sql server yeah it wasn’t very good i don’t know why i was doing it maybe i wanted to write a snarky blog post or something well you’d have to be the that’s with the ml service yeah yeah yeah that’s right that that whole thing yeah that’s a whole data cluster well r.i.p yeah bdc yeah but uh so i guess you know it is somewhat heartening that microsoft is slowly chipping away at the dumb stuff that people would need clr for like 2025 is getting regex and a whole bunch of regex functions we got string ag we got string split so like like like there is some movement in the right direction for people to not have to implement their own dumb stuff um you know like like there are some things getting added that it’s just like oh cool like maybe like light at the end of the tunnel stuff where it’s like oh great like i don’t have to do all this nonsense myself i can do it you know like there’s there’s a there’s something built in for me to do it with so like like that is heartening but um you know i i i it would be nice if uh sort of like when you’re writing t SQL and you run into something that is like generally not a relational strength um you know like like a lot of a lot of the string stuff would fall into that um some of like some like the broader mathematical calculations would also fall into that but like it would be nice if there were you know things you could do like in a like a way that were was relationally friendly but just in a different language and that and that translated a little bit better um you know like like even if you you think about just like you know like something as simple as like a b tree like like people people have this weird uh expectation of indexes that once you get into an index you can go any which way and jump around and do all this other stuff but like like what does a b tree really do what’s its strength it gives you a good starting point and you can go this way or you can go this way from that starting point you can’t go both ways and come back up and jump around and like like you have to do like another piece of work if you want to go in a different direction or if you want a different starting point so like like people do have unreasonable expectations of like databases and certain database structures but it would be nice if there were ways to do relationally friendly things and maybe like a language that wasn’t directly sql i guess is my point like there like there should be a way to to work with data within a database that is maybe not just sql like like sql because obviously capable of a lot of stuff but there are many things like when like like you were talking about xml earlier like some of the xml parsing queries that i’ve had to write i’ve been absolutely insane and like the things that i’ve had to learn about xml just to get data out of sql server i’m like why am i doing this like why is there not just a function that produces this view for me like i shouldn’t have to do this but just enable all the automation yeah okay fine but like still like if i’m trying to read from an extended event file like you know like there should be something that just like a like some sort of built-in function where it’s like okay i want to read data from this file i want to read xml and then parse it i want you to just give me the the contents of the file in a tabular format you like something should be able to like look at that file look at some metadata maybe even look at like a format file like the like bcp and bulk bulk stuff does and be like oh cool this is what’s in the file these are the data types these are the columns like and just like give me a table format for that great like that should be easy but no we’re still having to do all this terrible work you can do it with uh powershell you can do it with powershell online but uh someone but i think but that’s the api right like that that is the api that’s given to you via yeah would it have been implemented different i think it could have been implemented different do i know implement it better no but could someone theoretically smart put a put like an abstraction between me and that to make it easier yes it yeah it would be nice in in your example especially for the xml as as someone who’s also done stupid stuff with xml and then said i’m done with this and walked away is is you know would have been nice food of come back relational right if it would have come back as maybe some type of temporary table that you could have just got stuff on or whatever i don’t know what the implementation would look like like just one of those streaming window functions or streaming um table table functions that like a lot of the other dmvs work from yeah yeah and that would be great and the i think a lot of the thing is you’re trying it’s always round peg square hole right it’s like let’s take something that is is document object model that’s not made to be anywhere near you know it’s made for a specific implementation and hey let’s let’s go ahead and just shave those corners down and shove it through that round hole well okay but you know microsoft are the ones who chose to store many important things in xml system health extended event query plants yeah but nothing switched over to that except for like a couple goofy new views like uh but like this is there’s just so much in sql server that like in order to get information from it like it’s stored in xml so it’s on you to do that there’s like it always struck me as odd that you know you would find these blog posts with like you know like really detailed examples of like how to shred the xml to get this and that from it and there wasn’t just like some abstraction to be like oh i want to select this xml stuff i can just do select star from this thing it shows me all the columns and then i can choose which columns i want like to me that just that seems like hey let’s make life easier for the people who have to deal with this all day not make them also have to be xml experts so like i don’t know it was always weird that that wasn’t a thing but we have straight we have straight very we have stayed very far from the cloud sorry now do unicode no draw the line somewhere yeah no the getting back to the cloud though i like i said at the very beginning i think there’s a lot of good use cases for it i think it’s a tool like anything else you know just like joe it’s a tool and you can use it for good or you can use it for bad you can use joe’s joe’s etl strengths to get 10 terabytes of database loaded in once or you can use joe’s uh evil uh you know whatever i don’t know what his evil name is cloudy joe instead of regular joe yeah not sunny joe and rowdy joe all right sean all right so here’s okay well give me here’s the issue hang on hang on hey joe hang on one second uh i just want to do a time check here sean do you still have a hard stop in about 10 minutes i i can go another a couple more yeah all right cool so joe i’m gonna i’m gonna give you the floor to make your statement but then as is your unbiased moderator in this discussion i do need to get closing statements for the thing and then we will we will end this this episode a bit obscene i don’t know how long this has been at this point but uh a bit of thing yeah it is obscenely long at this point but i’m happy with that it’s not going to be a two-parter anyway joe you have the floor bang the gap uh i’ll start by saying this has nothing to do with the cloud ah but like i feel like there’s a little bit of like uh you’re in trouble now showing like disingenuous a little bit maybe like not sean of course i’m not sure he’s he’s above reproach but like i feel like if you ask some people like hey sql server 2022 uh i want to do a bunch of like regex stuff in sql server some people might say oh well you’re a dumbass like why you want to use all your cpu power doing regex and your very expensive licensing like you should do in the application but now in 2025 where we have like built-in project stuff you know microsoft just uh gives it to you i wonder if those same people would be like oh yeah i mean sure you know like it’s important the according to data where it lives and you can do the filtering as low as possible and you can send less data to the client and you don’t have to do all this comic you don’t have to split your code up and you know network latency blah blah blah so like i do feel and i understand like you know like if something isn’t supported in sql server well it could be natural to say well it’s like you know do it somewhere else but you know it’s kind of like a you know it’s it’s it’s a temporary convenience right because if you go far enough in the future i’m sure every possible useful thing will be supportive 10 minutes in the future yes all right there’s a one is a wonderful thing that you just said i i heard most of the words in it it was good they sounded fantastic all together like that so we’re we’re going to close out this episode of bit obscene we’re going to get closing cloud statements from each of you uh i’m gonna i’m gonna flip a coin to see who goes first well i would but i don’t have any coins uh so uh since since i know all my coins have been confiscated they’re being melted down into pitchforks uh so since we started with joe we’re going to start with sean for the for the closing closing statements here so sean uh the floor is now yours uh until you stop talking yeah the saying the i think the nlb always it’s it’s a tool i think it’s a good it’s a good tool that fits a lot of places but it’s not really the multi-tool right it’s if you’re a multi-tool you do a lot of things you might do some of them okay many of them okay but you’re not doing anything very great right people go to the multi-tool because they’re like well i can go do this i can go do this i can get it done it might it might remove the it might might get the screw up but i’ll strip it or it might uh cut this but it’s not going to cut it really well it’ll but but it’s enough to get the job done and that’s how i see it it’s when you start having to split your offerings so that there’s all these individual specialized things it makes you makes me wonder like is this the right thing or is are people going to know enough coupled with people just not either care i don’t know if it’s not caring or not wanting to learn or what have you you know i’d really be interested to learn more about that from individuals but just the the lack of wanting to be experts just getting onto the thing and going you know i i think the cloud is a good thing um i’d arguably say i’m happy that it’s here for some of the things that it’s given us but as a you know cio quarterly oh the cloud is the hotness put all your stuff in the cloud let’s do it i mean you’re pretty short-sighted for that you got to have you got to step back and say what makes sense how do we put the whole application life cycle in it how do we refactor things or if we’re net new what what do we need to do to get the correct performance get the correct items you know is it a level that we’re okay spending uh because as we’ve seen you know there’s been a lot of companies with blog posts about pulling all their stuff out of the cloud going back on prem saving tens of millions of dollars a year uh even with their costs and and it’s um so so it’s a it’s a tool use it well i have nothing against it uh on the you know just in general it’s just how it’s it’s like anything else how people misuse it and then don’t read the fine print and complain or say oh this sucks it doesn’t well yeah you use the tire as a you know to cut your tomato of course it didn’t work well and the tomato squished i’m you know take 10 seconds and think about it but i don’t know if we can have that today so overall cloud okay if used in the right context like everything else otherwise you know i’ll take a part from apple and say you’re holding it wrong and uh that’s it all right well as long as those 10 seconds are billable i don’t mind thinking about it all right cloudy joe hit us with your closing statements here i would say that if you can’t cook a steak on a server you don’t truly own it and you should fully understand what that means before you make decisions if you want to move things in the cloud um like i i mean i i’m sympathetic to you know i don’t know you have some startup and like the thought of well you know we’re going to get hardware and dsn number one and then we’ll get hardware and dsn number two which has to be far enough away and we’ll set up an availability group and backups and we’ll define our rto’s and uh rpos and we’ll you know do failovers and test our hdr and we’ll we’ll like we’ll like do drills and like you know like uh cut the links and turn servers off and you know make sure that we’re always up when we need to be and we’re not losing too much data i mean like that definitely i mean that’s a pretty intimidating thing right like it’s not like how it used to be in the good old days where you just had a server and uh like under someone’s desk and you know the server and a prayer that yeah that has served as a production um and like i can understand why you wouldn’t want to take all that on why you wouldn’t want to hire someone to do that i mean what you know if you hire the wrong person or you know like like what if you hire a shady data center because you know like plenty of those i mean you don’t even know like what the data center is doing like like they might even be in the cloud for all you know so in your effort to avoid the cloud you might just be taking even even like another step away from what you control um and you know like we already said if you’re starting with the application it’s just easier from the beginning to say okay you know we’re not going to use clr because we don’t believe in freedom and we’re not going to use service broker we’re not going to use asian jobs and we’re not going to do cross database queries and we’re going to be used you know we’re going to live with our 10 milliseconds of vital latency which might spike up to like 30 seconds on occasion it’ll be fine um it’ll work right for us um like i think there’s something to to be said there i do think it’s also probably not easy to make the right decision if you are shifting to the cloud um it does seem like microsoft’s documentation does get better over time but like as someone who’s personally tried to look into this stuff for recently there are a lot of dead links still there are a lot of dead links and some of the dead links sound really useful too but then you click on them and it redirects and redirects and then you get a 404 like like it is surprised i found it surprisingly difficult to find like good cloud content in terms of like you know like hey like we did a migration we learned xyz or like here’s a really clean breakdown of what to choose when like if you want to choose azure yams or managed instances or azure sql tv or even some non-azure you know you want to go on uh amazon or google or i think oracle has a cloud uh maybe we shouldn’t uh shouldn’t shouldn’t talk about that yeah no no one goes to google let’s yeah all right yeah well like i don’t know if it’s all like like uh uh ai slap now or people hold their cards too close to their chest or all the knowledge is locked behind paywalls and you know consultants and fancy training but like i i found it pretty hard to get answers even to what i would think would be some basic questions like which platforms support clr you know like as someone who develops that like i i i want that like loud like red blinking letters like hey if we’re going to use a ccdcql database no clr for you like that is something which you know like it’s a decision microsoft made nine years ago they should fully own it so you know then like that’ll increase our chances of perhaps stumbling into making a good decision so to wrap all that up a lot of people use it i’m sure it’s good for some people i think if you’re starting new it’s definitely easier and if you’re doing a lift and shift hopefully you’re better at googling than i am or you hire the right consultant and you can maybe get to a good enough answer all right wonderful wonderful you said joe what are your closing thoughts eric my closing thoughts my closing thoughts are thank you both for joining us on this joining my temporary co-host and my potential temporary replacement co-host for for joining us uh uh at here at darling data headquarters on this episode of bit obscene uh brought to you by darling data and the good folks at beer gut magazine uh and as always thank you for watching i hope you enjoyed yourselves i hope you learned something and uh maybe one or more of my temporary co-hosts will see you in the next episode of the bit obscene podcast all right thank you very much
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.
Any general thoughts on using indexed views in Enterprise Edition for performance tuning use cases outside of OLAP? Good? Bad? Evil?
Is there a performance difference when using UNION vs UNION ALL inside an EXISTS with 2 or more queries?
Any tips for making DML faster if all I can do is change the query text? I know the OPTIMIZE FOR tricks for changing the shape of the plans, but they never seem to make much difference.
In your opinion, what is the hardest thing for people to grasp about databases?
Okay seriously why do you hate logical read for query tuning?
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.
In this video, I dive into some optimizer rules for SQL Server that I wish we had, focusing on how they could improve query performance and make our lives easier as database administrators. I start by showcasing a new rule in SQL Server 2025 that optimizes queries written using `LEFT OUTER JOIN` with a `WHERE` clause to remove nulls, demonstrating why it’s more efficient than the traditional approach. Then, I explore other rules that could benefit us, such as decoding case expressions that produce literal values and transforming row goal costing into more intuitive query plans. Throughout the video, I highlight how these proposed changes would simplify query optimization and reduce unnecessary overhead, making our work with SQL Server even smoother.
Full Transcript
Erik Darling here with Darling Data. Look at this wonderful logo, this steadfast forever logo of mine. I’ve tried various redesigns of this, but none of them quite have the same feeling, you know? Logo gives me a feeling. Today we’re going to be talking about optimizer rules that I wish we had. And when I say we, of course, I mean SQL Server, but we being SQL Server users, we would have them if SQL Server had them, and that would be wonderful.
I’m going to show you one thing I’ve talked about before, a new rule in SQL Server 2025 that I think is just grand. And then some other rules that it would be cool if the optimizer had available to it. So we’ll do that. Down in the video description, since my job is making SQL Server faster in exchange for money, you can do things to support those efforts, like hire me for consulting. I can make your SQL Server faster in exchange for money.
You can buy my training, and you can learn how to make your SQL Server faster, again, in exchange for money. That’s buying the training. You can support this channel. That’s the sort of kindness of your heart thing. I suppose I am teaching you how to make SQL Server faster. You’re getting most of the money for that one, though, since this is free.
And of course, you can also ask me office hours questions. You can ask as many of those as you want. And like the trained monkey I am, I will answer them. And of course, please do like, subscribe, and tell a friend. I understand if some of you out there want to keep this channel a secret. Keep it under wraps so you can maintain your competitive alpha SQL DBA advantage at work by knowing all this stuff that no one else knows.
But sharing is caring, my friend. So, you know, do spread the good word and all that. By the time you watch this, it will still not be 2026. But these events are obviously happening in 2026 because March of 2025 is long gone. So in 2026, I will be at Datatune in Nashville, March 6th and 7th. And I will be at Data Saturday Chicago, March 13th and 14th.
We’ll be a little bit closer to baseball season. I’ll be about ready to leave the house again. I will have a reason to live, as they say. Okay. With that out of the way, though, let’s talk about these optimizer rules over here. We’re going to go over this way and talk about some stuff. Now, this is my SQL Server 2025 instance. And I got a couple of queries here. And one of them, well, they both have hash join hints.
I’m going to show you why they’re there in a minute because it’s quite frankly embarrassing. But one of them is operating in compatibility level 160. That is, of course, the highest compatibility level if you are on SQL Server 2022. And this rule is not available there. The database default for my database over here is, of course, 170.
But just to be explicit about it, I am telling I’m showing you that this is compatibility level 170. Now, if I run these two queries and we look at the resulting query execution plans, we will see what the rule that I am talking about in action. Now, the first, we’ll go back to the queries for a moment. This is a query pattern that I have had to argue with many a person about over the years.
And I say, you know, this maybe isn’t the best way to do things. You should maybe try writing this as not exists instead. Might be a little bit more efficient. You know, might be something to look at and they’ll say, well, I would, but I don’t know how to write a not exist query. And I say, well, my rates are reasonable. I can help you with that. It’s pretty easy. Right? Not exists. And then a thing.
And then you’re done. Right? So don’t forget your where clause. So this query is essentially looking for rows in the user’s table that don’t exist in the post table. Wonderful. This is, but again, this is not the best way to do that. This is probably how I would say, no, don’t do that here.
But we look at the query plans. We’ll see that the first one has done a hash match left outer join as I have told it to do. And then we have a filter operator. And of course, I’ve talked about all this stuff before in the past. So yes, this filter operator is responsible for removing rows after the join where the ID column in the post table is null.
Remember that it with outer joins that null rows are preserved or non matching rows are preserved. And we can, we can filter them out. We can filter the non matches out. And this, this works well for, well, I mean, it works with the, using the clustered primary key on post because it is not nullable and it does not have any null values in it.
So if nulls are produced by this join, it means that the rows didn’t match. Okay. But the, the, the way that you would normally write this query to get the query plan that I’m about to show you would be using not exist. But SQL Server has a brand new optimizer rule, which says, hold on.
Hang on. I know, I know what you wrote, but I think I know what you meant. And I’m going to do things a little differently. You’ll notice that there is no filter operator here. We just have a hash match left anti semi join.
Have to be very careful saying that one, lest you get sued. So, but this, this query is essentially removing the non matching elements at the join rather than fully joining everything together and filtering it out afterwards. I think this is wonderful. High five, Microsoft. You did it.
The reason I have the hash join hints in here though, is cause golly gosh, without, without those hash join hints, uh, things, things didn’t look good for either one of these queries. Let’s just to show you, right. Let’s, let’s run this. Uh, you know, this is not going as fast as it did that first time.
And, uh, that’s not good. These query plans are embarrassing. Yeah. I mean, the only thing that could make them more offensive would be if they were parallel merge join plans, but, uh, they, they basically follow the same pattern where now we have a serial plan, uh, which is a shame for the number of rows that we have.
Uh, and now we do a merge join left outer join, and then we have the filter and, and then down here, um, of course we have, uh, the, the, almost the same thing where it’s, it’s serial and we have, but this is a merge left anti semi join. Not a good time. Not a good time. Uh, and of course for completeness, another way to think about writing this query aside from not exist would be using the fabulous except operator, right? Just write the query like this and, and count this, right? That, that works pretty well. Uh, remember both of these queries got back, uh, 1 0 3 0 9 8 7.
And if we run this one again with the hash join hint, we will get back 1 0 3 0 9 8 7. And we have our left anti semi join plan. So all that’s great. Right now that is wonderful. All that is fantastic. Let’s talk about some other optimizer rules that might be nice for us to have.
One of them is this. So a lot, a lot, a lot, I’ll see people write, uh, create a view or create or alter a view. If, if you’re, if you’re hit with that. And what they do in the view is have some sort of case expression. And the case expression usually allows, uh, a user to like, you know, uh, supply like maybe a more user friendly search term for something.
Like question or answer or wiki or tag wiki ex, ex, ex ex, ex, ex, ex, ex, ex, ex, exerpt. Uh, well, whatever. Forgive these things.
Uh, but the, the problem becomes that when you, you, you, you, you try to write a query that filters on a case expression like this, um, for the, like the outcome of the case expression, then things get a little dicey, right? So let’s create or alter this view. I don’t know which one we’re doing.
What do you think we did here? Did we create or did we alter? I don’t know. Now, if you write your queries like this, which are not user friendly, because now you’re expecting users to have like memorized whatever data dictionary you have and know that post type IDs of one are questions. Like if we write the query like this, you know what we get an index seek into the, the, the index that we created on post type ID and, and all is well there.
But if, if we write the query like this, where we say, Hey, I want to find where the, the post type. Now this is using the post type case expression column. Well, we don’t use that index so well anymore.
Now, now, now we have to scan that whole index and things slow down a bit. So one rule that it would be really nice to have is, you know, just, and we could start simple here. We could start real easy and just, you know, for, for, uh, case expressions that produce like literal values like this, the optimizer could do a little something to decode that.
I realized that there are much more complicated case expressions in the world that could be written. I’m not asking to take care of all those because that would be nuts. Right. They’re like, you know, even if this were like case, what, like, you know, like a column or something, or maybe a column from a different tape, like different tables, that stuff gets real dicey.
But like, just for like when the case expression has some literals in it, like, why doesn’t the optimizer just sort of like reverse engineer that a little bit? Just like decode the stuff. So we get the nice easy seek plan.
Like why, why, why do you need to beat us up with this stuff? So that’s, that’s not cool. It would be nice if the optimizer could do that. Another one that I, I, I have a particular problem with is the, the costing around, um, uh, the, the row goal costing that leads to query plans that look like this.
So let’s run this thing. And what we’re doing is saying, we’re giving me the top one post, uh, ID from the post table, uh, that doesn’t have a vote, right? Ordered by the ID column descending.
This query takes around five seconds. And you can see most of the time in this plan. I mean, all of the time in this plan really is spent in here, right? Top above scan, almost never productive, right?
This is almost never a good time. There are very few cases where this works. And the, the reason why this, why this query plan pattern kicks in is because of some row goal stuff, right? We say select top one.
That’s a row goal, um, exists and not exists. Make it may introduce row goals in our queries. I have that whole video series on row goals or my presentation. So you can go watch that if you’re interested, even talk about this here, but this, this, this query pattern is almost never useful, right? We do get a missing index request, but SQL Server should really just not bother with that at all.
Like if we say, Hey, just hash join, right? Uh, this does not take five seconds to run, right? This takes, well, I mean, I guess, no, let’s, let’s, let’s give, let’s give one of those fancy optimizer things.
Uh, like, like memory grant feedback a chance here. There we go. Now that thing doesn’t spill anymore. Look at that two seconds instead of five seconds, because we get a hash join plan. Granted, we don’t have a missing index request now, but it would be cool.
The SQL Server would give us the missing index request and the hash join plan and just move on, right? Like why the top above scan? Why the nested loop?
Why, why are you beating us up? If you can’t have a top above a seek, don’t do it, right? Lay that thing out, get away. Right? Not a good time. Another one that I really dislike is the eager index pool.
So if we run this query, right, this query is going to take a little while to run. Oh wait, maybe it’s not. Cause I have an index on there. Ah, crap. Ah, yeah.
Ruined the whole thing. All right. Let’s get rid of indexes. I should have cleaned that up before I started writing this. All right. Let’s get rid of our indexes and let’s just say, do this. Right?
So now we’re going to get the slow plan that I meant to get. All right. This is what I get for like having multiple versions of SSMS open and working on multiple things at once. Not everything goes well on the first try. But this query is slow.
This query is not fun to watch. Right? And you can see this query took about 15 seconds and all of the time in here was spent eager index spooling. Right?
So we have 15.6 seconds here. The whole, well, like 15.7. It’s not that far off, but anyway, all the time is spent doing a nested loops join and building this eager index spool to make the nested loops join more palatable. Okay.
Now, since this query is using cross apply, SQL Server is not really hip to the hash join. Right? Query processor cannot blah, blah, blah. But there are other ways of achieving this that the optimizer could say, hey, I was thinking about building an eager index spool, but instead, why don’t, like, I understand this query. Right?
Seven seconds. And we don’t have the big nasty spool in there. Granted, like, this is still not like a well tuned query, but it’s so much better than the alternative. Eager index spools off of large tables, anything over like a million or so rows, they just completely lose their marbles.
And then, like, eager index spools, they get built single threaded. I can go back a little bit to talk about that. Even in a parallel query, eager index spools get built on a single thread.
Otherwise, they risk sort of deadlocks and stuff. They get loaded one row at a time. It’s very inefficient.
Spools, the code for those hasn’t changed in SQL Server seven. So, like, they don’t have any of the cool new, like, bulk loading and, like, other optimizations that things like temp tables have. So, like, if we come over here and we look at the properties of this and we look at the actual number of rows, we’ll see thread two got all the work.
And this isn’t just a quirk of this demo. This is every single eager index pool that gets built. It’s a little harder to show, like, the row by row loading, but you can see, like, taking 15 seconds for 8 million rows is not the sign of, like, a bulk load.
Right? Like, if you dumped 8 million rows into, like, a temp table just said, like, select into, maybe a second at the high end. Right?
And if you’re especially, I mean, if you’re an Azure, like, like, SQL database or managed instance, it might be longer because their tempTB is on, like, glue and dead frogs or something. But anyway, this would be a nice query plan pattern to have to take the, like, get rid of the eager index pool top one of these stuff and just sort of transform it into, like, a row number, like, type thing. Right?
Because this gets us the same query plan shape just without the eager index pool. Another particularly gross one is when you join on an OR clause. Right? It says, we don’t, we don’t like joins on OR.
Well, I mean, I don’t like joins on OR clauses because of what they do to, like, query plans and performance. But I mean, I guess fixing them is how I make money. So that’s, that’s okay.
But the optimizer really should be able to unroll stuff like this. The optimizer should be able to do a better job of, like, saying, hey, like, like, clippy style, like, like it did with, like, you know, one of the 2025 rule where it was just like, you wrote a left or join and let them check for nulls. Because I know what you meant.
I think I know what you meant in there. So when we run this query, right, and we say, select the stuff from the post table joined here on either the ID in the users table equals owner user ID or the ID in the users table equals the last editor user ID. Right?
We could join on this or this. Anything will do, right? Give me anything back. I don’t even know what this query means anymore. This query takes a rather long time to run. We’re at about 20 something seconds now.
And it’s still going. Chugging along here. Not easy like Sunday morning.
So 31 seconds later, we get this giant catastrophe of a plan. SQL Server should be able to say, hey, I think I know what you meant to do here. All right.
You should be able to unroll this a little bit. This is a very, very simple set of predicates. SQL Server should be able. And I’m using apply here sort of out of convenience. But if we write the query like this, it is no longer a 30 second ordeal. SQL Server just does a C or rather a scan of both of these indexes concatenates the rows and moves on.
We don’t have all that crazy junk with the constant scans and other stuff that we had before. The optimizers should be able to handle these situations, right? And I’m not, again, I’m not saying every super complex case should be covered by this, but there are some pretty easy ones.
Like, again, I’m a high school dropout and I can figure this out. People who work on the optimizer team can unroll this. We’re in trouble.
Right? Now, there is an optimizer rule that I’ve changed my mind about in recent years. I used to, you know, complain quite heartily about, you know, eager about CTE in SQL Server because there is no way for SQL Server to materialize them. Other database engines like Oracle and Postgres and I’m sure many others offer either automatic or hintable, like materialization of CTE.
This, of course, really hurts SQL Server when you reference CTE over and over again because you have to re-execute the query in them. They don’t materialize naturally in any way in SQL Server. So I wrote kind of a weird set of queries to try to find a good way to show you this.
But, like, if you run this inner part of the thing here, we get values 11 through 91 back. And if we run this query, basically what this is doing is showing me which IDs in the votes table sort of give me what the row breakdown is with different modulo math. And this will make more sense in a minute.
But when you look at these numbers, you can kind of see, like, okay, well, if I modulo by 91, I get 581,000 rows. If I modulo by 11, I get 4.8 million rows, stuff like that. So what I want to show you here is kind of why I’ve changed my mind about this in SQL Server.
And it comes back a bit to the eager index pool conversation where, you know, like loading data into the eager index pool, it couldn’t be done in parallel. It’s row by row. There’s no bulk loading, all that other stuff. It’s a bad time, right?
So, like, if, you know, Microsoft are willing to invest time in improving spools, then this would be a palatable optimizer rule. But because they’re apparently not, because again, instead we get fabric, you know, birthday herpes, we get stuff like this. So what I’m going to do is I’m going to write queries that will intentionally, like, load a bunch of data.
Like, this one’s going to put a bunch of data into a spool, but not return any rows. Because what I want you to see from the query plan is how, like, the difference in time between rows ending up in a spool and rows ending up in a temp table. So we do this. This one’s relatively quick, right?
The whole query runs for, let’s see, we have, oh, this thing gets all sorts of weird. But, you know, we have a clustered index scan of votes over here. This takes 1.1 seconds. And then by the time we get to the eager, we can ignore the gather streams operator here.
Just pretend this doesn’t exist. We spend about 600 or so milliseconds getting data into the eager index pool, right? The rest of the time in the plan, this is actually kind of a funny one, right?
Because we spend 1.1 seconds here. The gather streams is just like, oh, well, I take 424. Like, the timing gets all thrown off. Now, this is a completely roll mode plan.
So the time is supposed to go higher as you go from this part of the plan to that part of the plan. But things get all thrown off. And there’s a really funny one at the end, too, where, like, the entire thing has taken 1.6 seconds. But this is like 3.5, right?
But it gets a little bit more amusing, too, if we look at the query time stats. Because the query time stats, they largely agree with the query plan itself. Like, I took about 1.9 seconds.
I didn’t know. Where did 3.5 come from? I don’t know. But you can’t see that it took, like, 1.7 and then 1.6 here. The whole thing is a shambles.
Anyway, you can sort of figure out that it took about 600 milliseconds for the million or so rows to end up in the eager index pool. Which isn’t in the eager table spool, which is what it would take for CTEmaterialization. You couldn’t lazy load that.
But just comparing that with a loading data into a temp table, this does not take that long, right? Like, you see it’s 890 milliseconds here and then only about 100 milliseconds to get the rows in there. So, like, the slowdown is obvious, right?
And it gets worse as more rows come into play. So, if we do that same sort of top 99% query, and granted, like, even the 99% query is loading fewer rows because this is, like, loading everything where ID modulo 51 equals 0. This is only loading the top 99%.
I need that 99% in there to get the eager spool. It has to count the rows, right? So, if we look at this one, you know, we spent 900 milliseconds here. Again, we’re ignoring this thing.
And then we spent almost 8 full seconds loading 5.2 million rows into the thing, the eager table spool there. If we do the same thing with a temp table, it does not take nearly as long, right? It does not take 8 seconds to do that.
This thing takes, well, there’s 1.4 seconds here, and then there’s 1.8 seconds. Here’s, like, 400 or so milliseconds there. So, SQL Server does a much, much better job of loading data into temp tables. If we could make at least the eager version of spools, I mean, all spools should be equivalent with temp tables at this point, but the eager version of a spool were made more on par with the way temp tables get loaded, then CTEmaterialization would be a worthy effort in SQL Server.
But until then, it would suck, right? Not be fun. Not be amusing.
Like, I would be angry. There might be some okay trade-offs, especially with not rerunning some subtree in a CTE over and over and over again, but for the most part, I would be very nervous about big, eager spools getting data loaded into them.
Anyway, I talked for longer than I thought I would. It’s time for my protein shake. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. And today’s Friday, so I won’t see you until Monday, but I do hope you have a great weekend. All right.
Thanks for watching. Bye-bye, Matthew Red Crossed Costord. means
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.