To illustrate this problem a little bit better, I need to use a slightly different query.
While a tidy group by was nice to illustrate some of the simpler points about lookups, it overly complicates things when we want to involve other columns.
The second we need anything else in the select or order by portions of a query, we need to apply aggregates to them, or include them in the group by.
And you see, once you set up a query to return the TOP N rows, there’s an expectation that users get to choose the order they start seeing rows in. As long as we stick to columns whose ordering is supported by an index, things will be pretty stable.
Once we go outside that, a TOP can be rough on a query.
Order By CreationDate
Even if we order by CreationDate in descending order, with the index created in ascending order, we don’t need an explicit sort operation to put data in order.
SELECT TOP (1000)
p.CreationDate,
p.OwnerUserId,
p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131015'
ORDER BY p.CreationDate DESC;
There are some additional possibilities for this kind of thing with multi-key indexes that we’ll talk about later in the series, but for now this is a good enough illustration of indexes putting data in order.
big chain
Order By Score
Let’s ask for data in a different order now. Score is a convenient villain, because there are lots of times when you might want to see things by a highest whatever metric is commendable to be high. Like not blood pressure, probably.
SELECT TOP (1000)
p.CreationDate,
p.OwnerUserId,
p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131015'
ORDER BY p.Score DESC;
blown out
Without an index that has Score in the key, we need to physically put the data in order to fit the requirements of the query. Note that in this case, the optimizer no longer sees any benefit to using our nonclustered index.
Why Do We Care About Sorts?
Without jumping too far ahead, Sorts need extra memory to run.
You know, that stuff you cache data in, and you don’t have enough of already?
Yeah, that memory.
The same memory that gives you a conniption every time PLE fluctuates.
How much they ask for is going to depend on:
How much memory you have
What max server memory is set to
The size of the data you need to sort
If we aren’t able to get enough memory, or if we don’t ask for enough up front, data could spill from memory to disk.
When spills get big enough, they can sometimes cause performance issues.
This is a good time to start talking a little bit more about indexes, so we can understand more about how they work and help us solve problems.
Thanks for reading!
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.
We don’t need to add a ton of columns to our query to have index usage change, but we do need to go back in time a little bit.
Here’s our query now, with just a one day difference in the where clause.
SELECT p.CreationDate,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131015'
AND p.OwnerUserId = 22656
GROUP BY p.CreationDate;
SELECT p.CreationDate,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131016'
AND p.OwnerUserId = 22656
GROUP BY p.CreationDate;
Not quite Halloween, but I’ll take the spooky factor.
The other difference is that now we’ve got the OwnerUserId column in there, which isn’t in our nonclustered index.
It’s in the where clause, not the select list, but if we added it there it would have a similar effect on the query. Either way, we have to do something with this new column, and we have to get it from somewhere.
CREATE INDEX CreationDate ON dbo.Posts(CreationDate);
Things Are Looking Up
The query plans for these will look a little bit different.
what’s it for
If you’re not familiar with what just happened here, a Key Lookup is a trick the optimizer has up its sleeve. It uses two indexes from the same table to satisfy one query.
We find rows in our nonclustered index that qualify for the filter on CreationDate. That’s a smart start!
Then we join the nonclustered index to the clustered index to find any columns we might need. Remember the clustered index has all the table columns in it.
Stuff like this is made possible by nonclustered indexes inheriting clustered index key columns. Crazy, right?
The Point At This Point
There are many internal details to explore around Key Lookups. There are even some interesting things about how clustered index keys get stored in nonclustered indexes.
What you need to know about Lookups right now is what they are (which we talked about), and that they represent a choice the optimizer has when it comes to index usage.
If you create a narrow index, say one that satisfies some part of the query like the join or where clause, but doesn’t fully contain all of the columns referenced in your query, it may not get used reliably. The usage is decided based on cardinality estimates. The more rows SQL Server expects, the less likely it is that your narrow index will get used.
For example, it may only get used when the value for CreationDate is estimated to return a small-ish number of rows. Parameterization and plan re-use can make this even more confusing.
Next, we’ll look at how we can encourage the optimizer to choose narrow indexes, and the problems we might run into.
Thanks for reading!
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.
Yesterday we created an index, and things went pretty well. Good for us. By next week we’ll have six pack abs.
Today we’re going to look at things that I see happen pretty often in queries that mess with effciency.
If one were to sit and think carefully about the way B-Tree indexes are implemented, where key columns define order, one may see these problems coming a mile away.
Then again, one might expect a full formed datababby system to be able to figure some of these things out and use indexes appropriately anyway.
General Anti-Patterns
I’ve posted this on here a number of times, but here it goes again.
Things that keep SQL Server from being able to seek:
Function(Column) = …
Column + Column = …
Column + Value = …
Value + Column = …
Column = @Value or @Value IS NULL
Column LIKE ‘%…’
Some implicit conversions (data type mismatches)
Seeks aren’t always necessary, or even desirable, and likewise scans aren’t always bad or undesirable. But if we’re going to give our queries the best chance of running well, our job is most often to give the optimizer every opportunity to make the right decisions. Being the optimizer is hard enough without us grabbing it by the nose and poking it in the eyes.
To fix code, or make code that looks like that tolerable to The Cool DBA Kids™, there are some options like:
Computed columns
Dynamic SQL
Rewrites
Different options work well in different scenarios, of course. And since we’re here, I might as well foreshadow a future post: These patterns are most harmful when applied to the leading key column of an index. When they’re residual predicates that follow seek predicates, they generally make less of a difference. But we’re not quite there yet.
The general idea, though, is that as soon as we write queries in a way that obscure column data, or introduce uncertainty about what we’re searching for, the optimizer has a more difficult time of things.
Do It Again
Let’s compare a couple different ways of writing yesterday’s query. One good, one bad (in that order).
SELECT p.CreationDate,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131225'
GROUP BY p.CreationDate;
SELECT p.CreationDate,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE YEAR(p.CreationDate) = 2013
AND MONTH(p.CreationDate) = 12
AND DAY(p.CreationDate) >= 25
GROUP BY p.CreationDate;
The mistake people often make here is that they think these presentation layer functions have some relational meaning. They don’t.
They’re presentation layer functions. Let’s see those execution plans. Maybe then you’ll believe me.
horse and carriage
Things are not so hot when we pile a Mess Of Functions™ into the where clause, are they?
I mean, our CPUs are hot, but that’s generally not what we’re after.
The Use Of Indexes
We could still use our index. Many people will talk about functions preventing the use of indexes, but more precisely we just can’t seek into them.
But you know what can prevent the use of nonclustered indexes? Long select lists.
Next time, we’ll look at that.
Thanks for reading!
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.
Yesterday, we started with a query, and wanted to add an index to make it faster. Remember, indexes help queries find and relate data by putting it in order.
In the weird old days, we might use SET STATISTICS TIME, IO ON; to measure a query. If you’re still using old (unsupported) versions of SQL Server, I understand if you have to continue to use those.
For those of us living in the present (and near past), we can get the detail we need from actual execution plans.
Look, you’re probably getting those anyway if you’re trying to tune a query with any seriousness. You might as well make the most of them.
Something New
When most people look at execution plans, they neglect one of the most important parts: operator properties.
Once you got a plan in front of you, you can either highlight an operator and hit F4, or right click on one and choose “Properties”. A little window pane should appear over to the right in SSMS, and it’ll have a whole bunch of useful information in it that doesn’t show up in the tool tips you get when you hover over operators.
mcgruff
What you can see over there in detail is information about reads and operator time.
signs and measures
Interpreting Stuff
Logical reads is the number of 8KB pages the query read through. I used to put a lot of stock in tuning queries to do fewer reads, but I’ve run into too many situations where a faster query did more reads. I no longer consider it to be a great indicator of performance being better.
If logical reads incidentally go down, great. If not, whatever.
The reason this is better to get from execution plans is because if you use the SET STATISTICS command, it only tells you reads at the table level, not at the index level. If you have more complicated query plans that use multiple indexes from the same table, you can’t really tell which did what. Getting it from the query plan is easier to interpret.
Down the bottom is some more useful information about CPU and elapsed time. I know what you’re thinking: that’s incredible, I can see how long each operator runs for. And that’s true; you can. But it’s a little more complicated depending on what kind of plan you’re looking at.
If the plan is all row mode operators, time is cumulative going from right to left
If the plan is all batch mode, time is per operator
If the plan is a mix, then the time will also be mixed
It’s a little confusing, I know. But that confusion is going to become more common as you nice people start using SQL Server 2019 and seeing Batch Mode On Rowstore. Otherwise, you’ll really only see Batch Mode when columnstore is involved.
If you use this feature enough, you’ll undoubtedly run into rowmode query plans where the times are inconsistent, especially around parallel exchanges. Don’t take it personally — accounting for parallel thread and operator times is difficult. Things will most often even out as you read through the plan.
For more detail on timing stuff, check out my videos here and here on it.
For Real Though
Let’s look at our query with and without an index.
CREATE INDEX CreationDate ON dbo.Posts(CreationDate);
SELECT p.CreationDate,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131225'
GROUP BY p.CreationDate;
It’s pretty easy to see what got better by looking at the execution plans.
better butter
With an index on CreationDate, we avoid the clustered index scan and the need to go parallel to compensate for not having an opportune index.
We can seek easily to the dates we care about, because they’re ordered in a way that makes it easy to find them.
slick
Okay, Great
Now we know how to figure out if we did something right when adding an index.
Tomorrow we’ll look at ways you can write your queries that might interfere with indexes being used efficiently.
Thanks for reading!
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.
The most common types of indexes you’re going to see in your life are of the rowstore variety.
As much as I love columnstore, it’s probably not something you’re going to see a whole lot outside of data warehouses. Sure, some folks will have reporting over OLTP, and might find some utility for them, but they can be tough to manage with all those tiny modifications.
Let’s stick with the stuff that’ll help you the most: clustered and nonclustered rowstore indexes.
There are important things to know about indexes, and we’ll get more in-depth later on. For now, let’s talk about how they can help a query.
Clustered
Our table has a clustered index on it, which is also playing the part of a primary key. The primary key attribute makes it unique, of course. By default, if you create a primary key, it’ll also be used as the clustered index key. If you only create a clustered index, it won’t be unique by default.
Let’s not get bogged down there, though. Here’s our index.
so clustered
We can identify clustered indexes and which columns are in them pretty easily in SSMS.
To simplify a bit, the clustered index is all these columns:
every single one
Ordered by this column:
businessing
That’s Great But
Having the Id column in order doesn’t help us find data in other columns, because they’re not in order.
Let’s say we wanted to find posts by CreationDate. The values for it aren’t in an order that helps us search through them.
where are they now?
If our query is only concerned with the CreationDate column we can create a single-column index on it. As queries become more complicated and involve more columns, we need to consider wider indexes sometimes so that they stand a better chance of getting used, but we’ll come back to that later.
Here’s our overly-simple query.
SELECT p.CreationDate,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131225'
GROUP BY p.CreationDate;
And here’s how the optimizer decides to answer our query.
down and out
We have to scan all of the data pages in the clustered index looking for CreationDates that match our predicate.
Make It Plain
It’s not such a crazy idea to create additional indexes to speed up queries, but how exactly do they do that?
What is it about indexes that magically make queries go faster? According to the title, they put data in order, so let’s go with that.
It’s easy enough to create a helpful index here.
CREATE INDEX CreationDate ON dbo.Posts(CreationDate);
Tomorrow, we’ll look at ways to see if our index gets used, and different ways to measure if it improves our query.
Thanks for reading!
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.
Every time you push F5, decades of research, code, and math kick in and have their way with your query. No, really. They do.
Your query is a question that the database has to answer, and not only does it have to make sure that your syntax is correct, and the objects you’re asking questions of exist, but it also needs to figure out the cheapest way to come up with that answer.
How you design tables and indexes and constraints, and write your queries, can help better choices get made. But we’re getting ahead of ourselves a little bit, aren’t we?
The first thing we have to talk about is the difference between how you write your query, and how the database sees your query.
It’s Trees All The Way Down
You start your query with a select, list a bunch of columns, maybe calculate some expressions, or do some aggregations. Sometimes you can throw a top or distinct in there for fun.
Then you mosey on to from, maybe with a join or forty, and hopefully a where clause that’ll filter out some rows. Good stuff. I’m proud of you already.
If you’re feeling extra spicy (or if necessitated by the abovesaid aggregation), you might group by some columns. It’s nice to not get error messages, after all.
After that, you’ve got your order by, if you so choose. Or if your query needs data in a particular order. Remember, without an order by, you don’t get data in a special order.
Of course, there are some less common things you may do along the way. You might use cube or rollup, a having clause, or use offset/fetch.
But the point is that the optimizer rearranges things a little bit. That old dog goes from > where > group by > having > select > order by.
Wild, right? But what does that mean for you?
It’s Tables All The Way Down, Too
When you run a query, the results are tabular, but not necessarily a table. That’s why you’re allowed to do certain things when you nest parts of the query that you can’t do without some nesting.
For example, I can reference an alias in the order by, because the optimizer processes the select list first:
SELECT p.PostTypeId,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
GROUP BY p.PostTypeId
ORDER BY records DESC;
But I can’t reference that alias in the where clause, because it hasn’t been materialized yet.
SELECT p.PostTypeId,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
WHERE records > 1000
GROUP BY p.PostTypeId
ORDER BY records DESC;
I know, I know. This is what having is for. But you’re jumping ahead again.
The point I’m getting to is that with a little nesting, we can take those tabular results and work with them in a way that’s totally tubular.
SELECT *
FROM
(
SELECT p.PostTypeId,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
GROUP BY p.PostTypeId
) AS not_a_table
WHERE not_a_table.records > 1000
ORDER BY records DESC;
Virtually Yours
The thing is, that internal query isn’t physically materialized anywhere. The result set is computed, and the filter is applied against that result.
That means you have to calculate it every single time. The same goes for if you put it in a (non-materialized) view. That view will run the query and calculate the result and return it to the user, but it won’t physically exist anywhere.
For example, let’s look at two logically equivalent queries.
SELECT *
FROM
(
SELECT p.PostTypeId,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
GROUP BY p.PostTypeId
) AS not_a_table
WHERE not_a_table.records > 1000
ORDER BY records DESC;
SELECT p.PostTypeId,
COUNT_BIG(*) AS records
FROM dbo.Posts AS p
GROUP BY p.PostTypeId
HAVING COUNT_BIG(*) > 1000
ORDER BY records DESC;
When we run them, the optimizer comes up with identical physicalimplementations of both queries.
little red corvette
If I run them both a few times, the operator times will wiggle back and forth some, but the plans don’t change, and they both take in the neighborhood of two seconds each.
The point here is that there’s no performance advantage right now to either query form. So if this is something you need to calculate frequently, you might start looking at ways to physically persist the result. That way queries that need it would have to do less work.
Tomorrow, we’ll start to look at how indexes can help things out.
Thanks for reading!
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.
I’m going to be taking a break in August, but I still want the Blogs To Flow™ while I’m letting my brain meat get its groove back.
So this month I’m going to run some fundamentals posts that I like teaching people about. Apologies in advance to people who expect a constant flow of more advanced stuff.
Why fundamentals? Because I still run into people struggling with the basics more than I run into people struggling with more advanced stuff.
Lots of people think they have advanced problems, but they really just screwed up something basic.
Anyway, my hope is that if you learn the basic stuff the right way, you won’t have to un-learn a bunch of bad habits later.
As far as hardware and settings go, I have 64GB of RAM, and 8 cores. That means my settings are:
MAXDOP: 4
Cost Threshold for Paralelism: 50
Max Server Memory: 51200
If your hardware and settings don’t exactly match those, you may get different results. These things matter, apparently ?
Stay Curious
You have everything you need to work along with these posts. If you have questions on anything, you can run your own experiments to try to answer your questions.
It’s not that I don’t want you to comment to ask them, but I’m not going to be checking in on stuff as regularly, and I don’t want you to think I’m ignoring you when you could be learning independently. You don’t need my permission to do that!
Over the course of the month, I’ll be talking about how queries get executed, query plans get made, along with table and index design, wait stats, and more. I hope you’ll stick with me, even this material is stuff you’re already comfortable with.
Thanks for reading!
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 wanted to share some insights on crafting effective abstracts for conference sessions. As someone who has presented at various SQL Server events and continues to do so, I’ve learned that the key lies in striking a balance between intrigue and detail. The first line of an abstract needs to be compelling enough to grab attention—like a movie poster or Netflix thumbnail. It should pique interest without overwhelming with specifics. Following this, the title is crucial; it must clearly convey what attendees can expect while also catching their eye. I often reference Adam Mechanic’s approach as a benchmark, reminding myself and others that clear, engaging content is key to drawing in potential attendees.
Full Transcript
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
أي KHERRI. What a Friday, huh?
what a Friday it’s always exciting when it’s Friday because I know exactly what I’m going to be doing all weekend working working, working, working working on things for you lovely people out there working on things will hopefully make your lives easier less painful you know all that good stuff all that good stuff hopefully I can give you ways to make your job like maybe not hate your job as much or something, I don’t know like little things, right?
like little things that you get out of life not hating it not hating life is typically an admirable perhaps not achievable but admirable goal we should all strive to not hate life so how is everyone?
I assume you can hear me since no one’s complaining about not being able to hear me and Streamlabs is at least telling me that my microphone is receiving input but who knows who knows what these eyeballs have in store who knows what’s behind these eyeballs?
there we go we have message number one 99 to go until Twitch congratulates me on people 99 to go oh my goodness is that Chrissy here?
I’m terrified now now I’m terrified I’m going to say something I’m going to say something incorrect about seltzer and get yelled at see I’ve got two cans of seltzer here if you’ve been to my previous streams you know that I take seltzer very seriously take seltzer incredibly seriously and this this LaCroix seltzer right here is perhaps one of my least favorite seltzers of all time you know why?
the bubbles big and soft when you open it they sound big and soft like that’s a big soft opening and when you drink it it’s barely a seltzer it’s barely a seltzer the bubbles just pass over your tongue too quickly this Canada Dry the official seltzer of Canada it’s much sharper bubbles when you open it ooh you hear that?
bah it’s a big pop it’s a big heart these are good aggressive bubbles they spike the tongue they get on there they make your tongue feel like you’re drinking a seltzer it’s the big stuff it’s the good stuff big soft bubbles if you like big soft bubbles thank you thank you if you’d like to know more about it I will post things about the mic and headphone combo when I’m done but to me I feel like I’m still junior level with all of this with all this streaming stuff I feel like just very entry level I’m just showing up like hey I have a great screen hopefully I won’t elbow it and knock it over it’s like silently praying that I won’t fall backwards unless I ruin the illusion and see my dumb posters beavis and butthead artwork the best streamer on the whole twitch plant I don’t know I’ve never taken my shirt off shown my feet or played a video game so I think that there are people who are light years ahead of me with streaming who I think could easily topple me as in the best streaming category we are going ASMR with soda bubbles you’re going to learn a lot about soda bubbles today a lot you know what I think what I might start doing so that I can meet in the middle with much better streamers than me is I might get the headset with cat ears so I can at least be sort of cute on stream could at least be like a little cute do like a few cute things I could do like some paw stuff like this and then maybe I would catch up maybe I would catch up with with other streamers who are who are much better than I am much more talented than I am much better setups than I am I get I see what happens to me is I get weirded out because I watch people who stream and record streams and I think I think they have either much more talent with video editing than I do or they have like a company that does it for them because they get all these like cool graphic popovers and like sound effects and like things are like queued up properly like they’ll be like if you want to see more of my content like and subscribe and like a little ding ding ding bell will go off and like the button will show and you’re like what how’d you do that me I’m just like there’s a button somewhere on the website you’re looking at press it it’s okay I would like to have you back having people here gives me self-esteem when I have eyeballs when I have eyeballs I feel better especially on a Friday I feel better on a Friday all right so let’s get some nonsense out of the way let’s see it’s still hard for me too I hear slobs helps with the glamour but I haven’t so that’s what I use right now I use Streamlabs OBS and I think without the help of Drew Fergwell I would I would have I would have a much much crappier slob setup it there was a bunch of weird stuff that went wrong at first like I was starting up like when I share these screens with you they’re RDPs to either a VM that I have local on my laptop or a VM that’s running on the desktop that I have down here and to my right so for like SQL demos and stuff I go to the desktop so that I don’t have the streaming stuff competing with the demos right because some of them can get pretty intense when I used to record videos and everything was local on my laptop if I ran a demo that was particularly stressful on the hardware like if I was intentionally making SQL server boot on memory or on CPU the video recording would start to suffer like my voice would go all and like the camera would get weird and like pixelated so I realized pretty quickly that I had to like offload that stuff somewhere else but when I first started doing it I would do what I normally did when I did like Camtasia or something and I would just be like okay well I’m just going to hit I’m just going to type RDP down in the bottom and the RDP window is going to come up and I’m going to share that but what got messed up was that slobs couldn’t detect the window when I typed in RDP I had to type in MSTSC and Drew helped me figure that out Drew also helped me get my act together with the size of the canvas I was working on and the chroma key thing and getting things worked out so there was just a lot of stuff that were not for some help from some smart kids I never would have figured out on my own but as much as I’ve encouraged the smart kids to blog more about things they know about streaming they haven’t so it might just be on me to write the dumb kid distillation of what the smart kids taught me how to do so it’s tough so slobs does help with stuff but I just don’t feel like I’m a super advanced user and you know like adding in the crazy pop up here’s a thing like right now I don’t even have the slobs ability to not have my arm I can’t figure this out I end here that’s the end of my world and I wish I wish that I could figure out how to get like myself to just sort of be big enough to reach across the screen but without like making me so big that I cover up a lot of the screen without it like I just want I want to be able to move my arm in bigger directions but some of that is like office space limitations too like I don’t have the most amount of space right now but hopefully hopefully we’ll see what happens so we’ll get a couple silly things out of the way we are or we I am going to be presenting two online classes about performance tuning SQL Server it’s a full day of learning and it also a ticket to that class will get you access to all 24 25 hours of my recorded content if you use the coupon code floating above my head there that for some reason powerpoint has told me is a typo we’ll fix that we’ll fix you powerpoint if you use that you will get 75 bucks off the face value of a ticket and I don’t know that’s that I hope to see you either on July 10th or 24th those are both Fridays one of them is next Friday and one of them is I don’t know see if I could do this in movie style then it would be Friday and then next Friday and then Friday after next and I don’t know if they line up quite that well but they’re both Fridays in July so we have that to look forward to have that to look forward to so let’s get out of this enough enough advertisement as they say and let’s look at we’re going to look at a blog post first the first thing we’re going to do before we write one single second of a thing on the screen we’re not going to read the whole thing because you can tell by the size of this scroll bar over here that it is a long post and there’s a lot of information in this well actually there’s a lot of comments on this post apparently there’s more comments than post maybe but it is a fairly long post and there are a fair bit of words in it what I’m going to do though is I’m going to stick the link into chat so that everyone has it if you want to read it if you want to ignore me and read this if you want to save it for later either way is fine with me I won’t judge I appreciate I appreciate that you would come here and then click on that at all but this is a blog post by my old pal Adam Mechanic and he wrote this Adam you know spoke quite a bit Adam was you know quite adored and admired by the SQL server community until he left us for Python and Postgres and whatever other things he’s doing I hear he cooks with sea urchins a lot these days I don’t know he’s a very fancy person very fancy person but he wrote that he used to speak a lot he used to you know do a lot of pre cons SQL Saturdays user groups conferences all that good stuff and he was like he wrote what I thought were were very good very detailed abstracts and intros to his pre cons and so whenever I’m sitting down to to write anything new about what I’m going to teach people about I always like to go through this and just kind of you know like remind myself of a few things and I think one of the most important things is is this header right here because this is something I always mess up I try to write I try to write an abstract that I would think was like cool or funny or you know that like like I would look at and be like oh yeah I’d want to show up to that and that like well that’s not entirely wrong what that leads to a lot of the time is me not giving enough detail about what I’m going to be doing I sometimes make the mistake that like people might see my name and people might see like the title of the the session and people might see like sort of a round up of things that like things that I put in the abstract but like without enough detail without enough me saying like here’s like exactly what I’m going to go through the thing is that like sometimes I find that sometimes I find that tedious to read and sometimes I don’t know if that’s what’s going to grab people so what I try to do is have a mix in there I try to make that like first line like pop that first line has to have like some zing to it that first line has to be like like this is what’s going to grab you and make you want to like maybe read the rest of it or at least skim enough of the rest of it to like be like yeah I’m into that right like like like like you know like the cover of a movie back when movies used to have covers you know you’d go to like blockbuster be walking down the aisles and be like looking for a movie to rent you’re looking around and I know I know this is going to age about as well as a phone book analogy for indexes but that’s okay it’s okay you we will work through it we’re grown-ups we’re adults we’ll hold hands we will make through make it through this together but like has to pop right has to be interesting now like you know if you’re scrolling through Netflix or like Hulu or whatever service you use they don’t sponsor me either the only the only sponsor I’m after is Canada Dry Canada Dry if you’re watching I’ll work for Seltzer but so like when you’re when you’re like looking for something to watch like like either like the the picture that you see or like that the first line that you that you see has to kind of grab you right a lot of the times with with conference speakers you know there’s a cult of personality around them you know if you show up to a conference looking to learn something in particular or you’re looking at a conference website looking to learn something in particular there’s a list of names in like your mental rolodex of what things that you work on things that you’re interested in and you know you might like see a name that matches up with that you know whether you know it doesn’t matter what it is and have to be SQL Server it could be anything right like if you’re really into HADR you might have read like Alan Hurt stuff and you’d be like oh yeah I gotta go with Alan Hurt right like stuff like that there’s a mental rolodex of people who do that kind of work and if you are going to a conference or a session or let’s just let’s stop saying going to let’s say attending because going to these days is not it’s not a thing if you’re attending a conference or a user group or anything mentally you would you mentally associate this person with doing that thing like like that that’s like one of the first things that grabs you if you don’t like see then if you don’t see a name that grabs you immediately then you might start looking at titles so the title has to be really clear and the title has to grab you once you make it past the title well do people really want to sit there and read 10 paragraphs of explicit detail about everything you’re going to cover and learn and talk about and all like little bullet points and factoids and everything I don’t think so I mean I’ve never I’ve never had it come to the point where someone was just like you know I’m on I’m on the fence about attending your session I’m this close I just want to know if you’ll cover this one specific topic that wasn’t listed here like I’ve never had that like in you know I think sort of generally people understand from the title and from like the zinger what you’re going to get into and so I try to like you know get really specific with that stuff so like the title has to be specific but it has to be catchy and memorable and the the first line like the stuff that you put into the first into like that first paragraph or so the first few sentences not even like a full paragraph like like two to three sentences has to like grab people a little bit and it has to be something that people identify with you know if you are going to be teaching level 100 stuff or level 200 stuff that’s totally legit you can totally do a full day of teaching people you know like how to SQL with training wheels on I’m not against doing that it’s not typically what I aim for but it’s you know if that’s if that’s your jam go for it but sort of like generally you know I aim for somewhere in the I aim for an average of 300 but the day is gonna spend time between 200 and 4 like on the 200 side and on the 400 side I want an average of 300 I want people who maybe aren’t so advanced to be able to get to 300 and I want people who want really advanced stuff to also be satisfied with going above the 300 mark so you know you try to straddle the 300 no one’s really doing 500 you can’t do 500 500 is would be very difficult to do in an hour 500 is still pretty tough to do in a day 500 requires so much technical background and detail that it’s it’s difficult to properly humanize and I think the average conference attendee doesn’t appreciate that you know there are there are select few people who appreciate that but if you want to really really like reach out to a wide audience and get like like a like appeal to a bunch of people saying that like you know you’re gonna spend the day at 500 a that’s a tough mark to hit it’s tough to go eight hours at 500 eight hours at 500 stuff eight hours at 400 is tough eight hours at 500 is like I think you would have to be David DeWitt to spend eight hours at 500 but so like you know try to straddle it’s like try to straddle things right like set expectations for what we’re gonna do and set it appropriately you know I think that everyone learns something at some level regardless of whether it’s that 200 300 400 but you know you do you do have to you do have to give people mental breaks and you know giving the people who show up for the 400 level stuff a mental break is good and giving the people who show up because they need to get to 300 some like like like like totally it’s like like really tough stuff to think about down the line that’s also good right like challenging people is good but you don’t want to lose people right so it’s sort of a sort of like a it’s a fine balancing act he says I had a week course at 400 I reckon it was exhausting well yeah that sounds that does sound exhausting I think a week even a week at 200 is exhausting like I think like you know like beyond a day you’re that’s that’s a tough one to pull off it’s tough to pull off so there’s there’s lots of important stuff that goes into figuring out a what you’re gonna say what you’re gonna say about it you know you’re gonna have people you’re gonna have to get people in quickly you’re gonna have to get people to invest quickly because they have a lot of choice especially these days there’s a ton of choices out there for people to go and you know get their learning from everything is online and that just makes everything so much more accessible he says it was way over me if I’m being honest I was good for two days really yeah so you know there’s um if if it’s constant 400 after I think you’re right at probably after two days the the glamour wears off and you’re just like constantly bludgeoned by despairing facts and crazy niche stuff that you might have to do and be aware of and learn in these very specific scenarios but the other I think so like that brings up something in it because I think I think another really big problem with constant 400 level stuff or I mean forget 500 level stuff anyone who says they’re presenting at 500 I’m not sure I’m not sure like that I think that like like you would have to really carefully qualify someone talking about something at a 500 level like it couldn’t just be someone who like just uses it I think you would have to be talking to someone who like was part of the design or development team of a product or feature in order to get 500 but I think a lot of the the problem or the a lot of the difficulty with a constant stream of 400 is you’re just playing SQL jeopardy you’re listing off facts all day you’re listing off stuff that might never apply to people you’re listing off stuff that you know like you might have seen once in 20 years of working with whatever technology you’re talking about and you feel compelled to tell someone about it because just maybe just maybe it’ll it’ll help them one day but you know that’s what’s when you start that’s that’s that’s when like the the checklist stuff comes in and the SQL like the you know I guess this the this subject jeopardy stuff kicks in it just gets really tough to stay at that like get like get at that level and stay at that level that’s a it’s a tough one so there’s stuff in here that’s very good to consider when you’re writing an abstract right appealing to your audience figuring out who they are I start a lot of my abstracts with saying you know you’re a DBA or developer who’s been working with SQL Server for x number of years you know identifying the crowd be like oh yeah that’s me you know you have tough performance tuning problems like XYZ and XYZ can be you know XYZ doesn’t have to be terribly specific like XY doesn’t have to be like and you have a lot of problems with with like paging queries or you have a lot of problems with like I don’t know whatever else right like you don’t know how to read execution like there’s a lot of like it’s like like overly specific stuff that you can put in there but you need to be able like like this is like for me some of that stuff is like what goes later on like when we get into detail when I’m trying to figure out what problems you need to solve I’m like you have a tough time like figuring out where to start you have it you don’t you don’t know where the problem is like you know you have performance problems but where are they is it the queries the indexes is it like you know is it like the way your tables are designed are your server settings terrible is your hardware like underpowered like like like where do you start right like people who are just looking at SQL Server and like maybe open activity monitor or like run SP who or SP who to and is it just like the I don’t know what happened because SQL Server is tough like that SQL Server does not make it terribly easy does not make it terribly easy to figure out what happened they make it very easy to see sort of they make it easy to see what’s happening there’s a lot of stuff you can capture hitting f5 but you know if if something was wrong you know at the rate that users report it you know last Thursday or two hours ago or something you know this might not be a lot you can figure out or do with that so you know a lot of people do have problems a lot of people do have problems with figuring out you know just where to begin with SQL Server troubleshooting like and you know it’s it’s my job as a presenter to think to tell them which place I’m going to get them to right carvin says can you please suggest database migration checklist no that’s not the kind of thing that I do but if you really want something good to help you with that give me one second to bring up the commands over here migration here we go if you want something to make that easy there you go but just to make it perfectly clear this isn’t that is not something that I do and that’s not something that I have and if if you have more detailed questions about it I have I cannot I will not have more detailed answers if I had to do it that’s what I would do I would I would I would hit it’s not f5 in PowerShell in PowerShell it’s f8 I would grab that script I would hit f8 and I would I would sit back and let PowerShell do the rest I’m not smart enough to have written it but I’m smart enough to use things that smart people write because that’s that’s basically what the world is is is learning something is like being lucky enough to have smart people around you do things and you just say oh they can make my life easier so let’s see when we’re when you’re trying to figure out who we want to talk to how we want to talk to them right and we and we want to make sure that we do it in a way where you know someone who might be like nervous or just sort of like unsure where they fall into the world well I mean you have you have to be careful with the way that you phrase things and way you the way you word things right because you you want you really do want everybody to be in there like you don’t you don’t want to say like oh like you know you’re you’re a you know beginner IT guy or something like that right you just don’t like you know leave that kind of stuff out so what we need to do is figure out who we want to talk to what we want to talk to them about and then we can come up with the catchy stuff we need we need to know we need to identify a few things first before we go and before we go and even start writing a single thing he says must be utterly nerve-wracking doing presenting as I said it’s no like the presenting part doesn’t really make me nervous anymore streaming makes me nervous because I am just not confident in the technology enough like I I I’m relieved and I find it to be quite miraculous that I could download a few things hit a few buttons and show up on a screen and close to real time in front of people I am amazed by that but like it still makes me nervous it still makes me very nervous I don’t like like nothing I think is bad technology it’s just it’s it’s nerve-wracking like just think like waiting for something to go wrong right like waiting for audio to cut out waiting for video to cut out waiting for like my internet to fall apart waiting for like one of like the receiving servers to just fall apart like just all this stuff that could go wrong when you’re on screen you’re just like oh please don’t fail like like and it’s not because I’m like I would be embarrassed for me it’s because like I don’t want I don’t want anyone who watches me to have a bad experience watching it’s tough enough watching me without technical difficulties so yeah so Adam brings up a good point and this is kind of the point that I was starting to make is people can’t be some people can’t be bothered to read big full paragraphs of words because I like the lack of pretzel so I I tried pretzel for a minute I downloaded it and I started listening through the music the EDM category was deeply deeply offensive deeply offensive but even more offensive was the hip-hop category if you ever want to be deeply offended if you if you like hip-hop at all and you want to be deeply offended look at like listen to the music in the hip-hop category on pretzel you will be so angry it’s it’s horrible it’s horrible read in 2020 let’s see I know that’s in here somewhere yes read in 2020 is there is there a reference to 2020 in here I thought there was so yeah so let’s let’s let’s back out of this blog post because I think we’ve spent enough time in the blog post all right there’s there’s enough in here there’s enough for you to go over later on your own so let’s talk a little bit about what we could present about because usually you know the thing that I get into and that font is just terribly small I don’t know why I don’t know why you’re messing with me PowerPoint’s messing with me all the time let’s make that a nice size font 28 sounds good to me that’s because what I usually talk about is performance what I don’t talk about things that I’m not particularly good at is HA no DR no security hell no not my jams PowerShell I’ve tried it was not I found myself sadly wanting in all things PowerShell couldn’t hack it I was not good I was not I was not good enough I was not smart enough and and power me and PowerShell did not get along what would always happen is uh you know I would and like I would I would I would have something to do and I would think this is what people use PowerShell for this is what people use it for and I would spend some time like searching around for the right commands to run and I would like try some stuff out and then like you know I would start getting closer and closer and then like three four hours later it would just be like me weeping over the keyboard because I couldn’t get anything to work it was just I T SQL has so infested my brain that that is like that is just where I have to go and where I have to stay whenever things get whenever things go outside of that I fall to pieces this is like if I’m not performance tuning something I’m like I don’t I don’t know what it does like like like poking it just like what are you I don’t understand so uh we shouldn’t we should at least get this to now match up right heck yeah so I do performance stuff uh yeah so it’s just it it’s funny how like like someone can be you know very very good at one thing technically or even intellectually or you know and just like you know be able to just like very quickly deeply like understand and grasp things and then look at something else that’s like equally and you’re just like like I don’t know like riding a bike like like riding a bike just like ah pedals like you could be an f1 driver like like be able to speed around tracks at like close to 200 miles an hour and handle things perfectly and then like like you know look at look at look at a bicycle and be like I what is this is this I don’t I don’t know I don’t know it goes too slow for me to figure it out so we have performance which I’m into HA is out DR is out security is out powershell is out but we still have some things within performance right within performance we have some choices do we want to talk about server tuning which would be weight stats hardware settings etc do we want to talk about index tuning and if we’re going to talk about index tuning what are we going to talk about within index tuning we have to set some expectations here because there’s a lot of different kinds of indexes within SQL Server not all of them are typically well used and they’re probably not things that people would expect you to cover but you have to you know if like I think you know you don’t need to say uh no XML spatial or in memory like you probably don’t have to go so far as to say that’s but you should probably specify are you going to cover a columnstore are you only going to cover rowstore like what what are you going to cover within these things now I I love I love columnstore but I do not have the chops with columnstore to spend a full day talking about it the internet is full of blog posts about columnstore that are just sort of rundowns of the documentation you can kind of tell that someone flipped on their laptop you know had like one of the smaller Microsoft databases ran a few scripts and they were just like cool here’s a blog post you they’re missing that sort of deep understanding of columnstore the things you can run into it like the actual like production usage of it like my friend Joe Obish he uses columnstore like a champ he uses columnstore like a champ he but he’s been through serious serious pain learning it without that pain some of that some of that learning just isn’t there and you kind of get this like you know that shady acres pamphlet like just send your data to columnstore it’ll be it’ll chase rabbits all day there will be other data just like it it’ll have friends without like any of like the real deep understanding of just like oh damn don’t do that oh dude just stay if you’re doing that run screaming like bad idea bad idea bad idea you know you get like you get like the glossy pamphlet you don’t get you don’t get the full story there’s a lot of errors and omissions when people have not used something deeply in production and so as much as I love columnstore I am not qualified to talk about columnstore I have never done any big uh you know columnstore migrations I’ve never done any big columnstore tuning projects I’ve done regular query tuning and I’ve you know figured out when people would be better off with columnstore and I’ve helped them you know move some stuff into there but I just haven’t run into like like the bevy of problems that you know you would if you’re regularly working with columns or with ETL stuff like that so we’re gonna not do columnstore if we’re gonna do columnstore I am going to stick generally to rowstore indexes, because that’s where my knowledge is.
I just don’t know columnstore well enough to stand there and answer questions about it. So, if I’m going to do server tuning, this is an interesting one.
This is an interesting one. Because it feels to me like the more you talk about waitstats, the less people want to use waitstats.
Waitstats have very real flaws in them. Particularly the way SQL Server logs them, where they’re just sort of aggregated since the server started up.
You can have very, very unreliable data there. But they’re somewhat helpful to identify big bottlenecks. Big problems.
Big problems. All right? If you see just like crazy waits on something, it can be helpful there. But you can miss a lot of the picture, right?
If you have a bursty workload that’s only busy some parts of the day, and does really nothing else for hours at a time, waitstats become less useful.
So, if you’re going to teach people about waitstats, you have to give them a way to gather waitstats in a way that makes them more useful for them. Not many people have that constant 24-7 pounding workload on a server.
And even if they do, that workload isn’t typically all user-facing, right? Even those servers will have some sort of night maintenance, you know, code rollout, change management, you know, whatever they’re doing, taking backups, running CheckDB, index maintenance, stats maintenance, whatever it is they’re doing, there’s typically some maintenance window for that.
But very few people have the, like, 24-7 need to just constantly be running queries. So, or user-facing queries, I should say. So, if you’re going to do waitstats, you really do need to give people a way to gather waitstats in a way that they can make sense of their workload.
There’s all sorts of stuff about waitstats. Sure, you can hit F5 and you can get sums and averages and percentages, but a lot of the stuff that you can just hit F5 on, you also don’t get, like, how long the server has been up, so you can kind of compare things to that.
Because a really important metric when you’re looking at waitstats is, like, compared to what? Like, the famous economist question. Like, compared to what? Like, how are you doing today?
Compared to what? I don’t know. Like, compared to someone who is staring at an IV in the hospital, probably pretty good. Compared to someone who’s sitting on a yacht in, like, the south of, like, off the south of Europe, you know, doing something fantastic with themselves, probably not as good, right?
Compared to what? So if you’re going to give someone stuff about waitstats, you need to give them a lot, like, a lot to, like, make sure that they know what to compare it to, how to gather stuff, how to read stuff, what waitstats mean, what waitstats are problems.
And that’s a tough gig. Because most of the time, when you start writing stuff like this, you start thinking, well, maybe you should just get a monitoring tool. And you think, maybe I should write a monitoring tool.
And you think, oh, that sounds hard. I should just go work for a monitoring tool. And you think, oh, I don’t really want a real job. And so I tend to stay away from this stuff now.
The thing about hardware, too, is that with a lot of workloads being virtualized or in the cloud, stuff that you can say with confidence about physical hardware changes quite drastically when it comes to virtualized hardware.
You know, there’s stuff you could say about, like, SOS scheduler yield, CX packet, you know, page IO latch, all that stuff. That, you know, on bare metal hardware, you would be right.
Virtual hardware, you would have a lot more to dig into. So if you’re going to start talking about hardware, you kind of have to know a lot about not just, like, the CPU and the memory, but now you have to start understanding virtualization layers, how VMs talk to the virtualization layer, how things might look if you have a lot of VMs on, like, a lot of VM guests on one host, all sort of making crazy requests, asking for resources in different ways.
Like, maybe you have an over… Like, what used to be a concept like, oh, your CPU, like, your SQL Server hardware is just underpowered. Could be, well, you gave SQL to the SQL Server VM enough hardware. The problem is it has to share that hardware with, like, 30 other SQL Server VMs on the same host.
Because everyone who virtualizes is a cheapskate, and they license enterprise at the host level, and they’re like, cool, so everything gets enterprise and goes here. And that’s, like, the new kids on the block version of, like, just stacking a bunch of SQL instances on the same server.
So, like, if you’re going to do hardware, you really have to understand, like, virtualization. You really have to understand VMware, all the little intricacies of things that can go on in there with, like, settings and, you know, how you, like, can, like, have VMs allocated and, like, where they go and, you know, like, crazy stuff, too.
Like, just, like, how, like, what a big difference, like, para-virtual SCSI connections can make over other things. And then, like, you have to, like, you know, like, the stuff you have to get into with hardware can be pretty challenging.
Forget all the stuff you might need to know about the cloud and cloud instances. It’ll be wrong in three months. So, hardware is kind of getting turning into a tougher and tougher subject to teach.
I used to really like talking about hardware because there was, like, some, like, cool stuff that you could show people, like, like if you have a bunch of queries run and run out of worker threads, or when you hit resource semaphore because you run out of memory to grant out the queries, or when, like, your server just plumb doesn’t have enough memory and you spend most of your time waiting on page I.O. latch or whatever it is.
But, you know, like, and, like, well, I still enjoy that stuff from teaching people about it from a performance perspective. You know, you get to the point where you’re like, okay, so, like, is the fix for that in the cloud, like, to just move to a bigger instance size?
Because, like, there’s no longer that challenge of, like, oh, we’re going to order the memory, shut the server down, install the memory, turn the server back on, wait three days for post to test the memory, stuff like that.
It’s just, like, it becomes a lot easier to just say, well, flip a button and see if it goes away, or flip a button and see if it minimizes some, right? So, like, when I teach people about those wait stats now, it has to be in the context of, well, how can we tune the query or the queries or the indexes in order to make better use of the hardware so that we are not pounding SQL Server out the way that we used to?
And then, you know, settings, golly and gosh, I can’t imagine someone sitting through a full day of how to set maxed op and cost threshold. So, the server tuning stuff, I kind of get away from a little bit.
So, within performance, we can talk about, you know, index tuning, and we can talk mostly about rowstore indexes. So, that’s one possibility.
There’s also query tuning, right? And query tuning, but, you know, query tuning should go hand in hand with index tuning. I would say there are certain query writing patterns that should certainly be taught and addressed, but if we’re going to talk about how to tune a query, you can’t just leave out how to tune indexes.
You can’t just leave out how to identify if your index key columns are in the wrong order, if you should fix a key lookup, if you should fix a sort, if you’re getting the right type of join or the wrong type of join because of the way your indexes are designed.
There’s just, like, so much that you need to think about and figure out when it comes to that stuff. The query and index tuning kind of go hand in hand.
There’s a couple of things over in chat. I’ve been watching your video from 11 days ago for the past 30 minutes and realized I wasn’t live. Ha ha ha! Well, now you’ll be able to tell because I’m much hairier. We have OLAP queries in the weekend which screw up all the wait stats.
See, that’s another thing. You can, like, that’s another terrible thing about wait stats is, like, you can’t filter them out based on when they happen. And unless you’re on SQL Server 2017 and you have Query Store turned on, it’s very difficult to figure out which queries are responsible for which waits.
Like, what happened to you? Like, what caused you? Oh, like, that makes a big difference too, right? Like, if you had some, like, big OLAP query come along and cause a bunch of, like, thread pool to restore a semaphore waits, you might look at wait stats overall for the server and be like, holy smokes!
What happened to you? But, then, like, you look at, like, the regular user workload and you’re like, none of that’s happening then. So it’s just like, come on, Microsoft, give me something.
Give me something. Throw me some bone. So within performance, query tuning and index tuning should go hand in hand.
And, I think it’s very important with indexes, specifically, to not, to make sure that people understand you’re not going to sit there and teach them what a B-tree is because that doesn’t help them.
You don’t want to, like, what’s on an index? Like, here’s an hour of DBCC page demos. Like, it’s just, stuff has to be practical too.
Right? Like, if you just sit there and do a full day of, like, this crazy trick that no one’s ever, no one’s going to walk out of there and ever see in their life, they’re just going to be befuddled as to what happened.
Like, what did I do? What did I just learn? If I see this, this one very specific set of circumstances that this consultant up on stage saw once in his 25 years of working with computers, well, eh.
But it’s so great to teach people about IO complexity. Yeah, when they’re really interested in IO complexity and they’re geared up to learn about IO complexity, that’s a great thing.
If you have a bunch of accidental DBAs in a room who are just like, do I have IO complexity? Is my IO complex? Do they really need to learn about how complex IO is or do they need to learn how to find their problems and fix them?
Right? It’s like, if you have people who are like, yeah, teach me about, like I’m a sanded man, teach me about the IO complexity or like, you know, like it’s just someone who has an interest in it because they’re down with it, then like what?
Yeah, great. IO complexity in general, not going to solve a lot of problems for a lot of people. It’s just not. A lot of people are not going to look at IO complexity and be like, oh, now I know why that query is slow.
So query and index tuning should go hand in hand, but it has to be practical things they can use when they leave.
Right? We can’t, we can’t just teach crazy stuff all day long. So we have query and index tuning. We have, we have that.
But there’s a lot of query and index tuning stuff out there. Do we want to specify it? Do we want to say something like for OLTP, for OLAP?
Like, do we want to specialize? I don’t know. I don’t know. Within query performance there’s other stuff too. Right? Like, if we’re going to query and index tune, what if it’s not just the query?
What if there’s blocking? Do we need, do we need to cover blocking as, do we consider blocking to be a performance problem?
Is blocking a performance problem or a concurrency problem? That’s something else we need to figure out. Right? Like, like, like, where do we want to go with this thing?
Like, which areas do we want to cover? Within performance there’s a ton of different things that you can look at. So, what do you think? What do you think out there?
There are, there are, there are people who have been in here listening to me. Listening to me talk and listening to me talk about, like, you know, like performance tuning subjects.
And not just today, like, you know, over the past, like, couple weeks or so that I’ve, I’ve been streaming. What, what things do you find yourself wanting to know more about?
What, what things do you find yourself having trouble with? He says, one thing I have found a gap in with a lot of things I have attended, it’s really looking at queries the size of the ones you see in real life. Presenter needs to get to the point across, but it’s a different world.
Yep. So, and I run into that too. So, here’s the thing. If, if I may, if I spent the time to make every single query big and complicated, and I, and I showed it to you, there’s a lot that you would get distracted by.
You would be looking at the query, trying to figure out what it does. You’d be looking for mistakes. You’d be trying to find this out of the other thing. What presenters need to do is come up with the simplest way to describe a concept, to describe an anti-pattern to look for, to give you something to look for in those big queries and in those big plans that you can single out and try to fix.
It’s not always like, you know, the most germane thing in the world to try to, you know, write a gigantic query that has this one problem in it and focus in on that.
Sometimes you have to say, look, here’s the problem you’ll see. It could be part, it could be a small part of a big picture, but here’s the small problem and here’s how to fix it. Let’s see.
Coyote McD says, does a pre-con have to be super practical? What about a pre-con for nerds who just want to learn how things work? Sure, but that’s a very, very limited audience. If I’m going to do a pre-con, I want to appeal to a wide, to a wide range of people who need, who need help.
Right? The nerds who want to know how things work are, I mean, what? one in, like, like the people who are really, like, ready for that, interested in that, and need that, there’s a much, much smaller crowd than I would aim for.
I want to be able to teach, I want to be able to teach as many people at one time as I can. So for me, it does have to be practical. And, you know, it’s funny, it’s funny the way you worded that because a pre, like a pre-con for nerds who want to learn how things work, sounds pretty practical to me.
But I get what, I think I see what you’re getting at with, like, you want the deep internal stuff. You want, you want that next level in that isn’t common knowledge. And like I was saying before, to get that sort of thing, that’s where you need, that’s where, I mean, say, like, that is where you need a Bob Ward type person who has that, who has access to the, like, who can see the source code, who can see the private symbols, who is, you know, whip crack with window bug, and can, who can, like, give that deeper internal’s knowledge and, you know, do pretty well with it because people would want to learn that from Bob.
There are very few people who, A, people would want to learn that from and there are very few people who I think are ready to and who would fully grasp whatever they’re teaching. So, what you said is actually a very practical thing.
A person who just wants to learn how things work. Right? They just want to, just want to know how to solve a problem. Learning how something works so they can fix it.
Lee says, I guess it’s a fine line between hobby and work. Hobbyists want to get deeper, the next thing I want, yeah, exactly. And you have to, you have to be able to respect both crowds. Right?
And there’s also, there’s also a funny question there, is it’s, does the hobbyist show up to a day-long pre-con to learn? Does a hobbyist get, you know, their work to pay for a pre-con to, like, for them to attend, show up, hang out, learn stuff for a full day?
Does the hobbyist show up for that full day training? The hobbyist might show up to a conference to get some time off work, to get some free travel, show up to a couple few sessions where the title attracts them, but I don’t know if the hobbyist is going for that full day.
Getting the hobbyist into the, if you can, if you find a way to get the hobbyist into a full day, you have cracked a very, very unique market. That is, that is a very, that is a tough, tough nut to crack.
Mostly you, mostly you need the people who, you know, either, you know, they are the hobbyist, or rather, like, yeah, they’re the hobbyist who wants more, who, like, you know, craves more, or, you know, they might be, you know, the people who, you know, who just want to, like, learn how to solve a problem, but their boss is just sick of them being that person.
They’re like, look, we have real problems that you want to solve. Here’s an extra 400 bucks. You’re going to go to this day. You’re going to learn some stuff. Maybe it’ll help. Right? Look at all the crowd that you have.
Yeah, you know, sometimes it’s better than others. Sometimes it’s better than others. This isn’t, this isn’t a particularly riveting SQL Server topic. So I don’t know.
I didn’t expect a big crowd today. But I’m happy for anyone who shows up ever at all. But, you know, if I’m doing something where I’m actually talking about, like, real SQL Server stuff, then you usually have a few more people.
And this is, this is like a weird soft skill one. So I don’t expect a lot of people in here who aren’t just, like, drunk, bored, in Europe after work. Something like that.
So, you know, I had different expectations for this one. Different expectations. But, we’re having fun anyway.
And we’re going to write this thing anyway. So, we figured out a few things. We’re obviously going to talk about performance. But where do we want to go with performance? I do a lot of performance tuning training that ends up hitting pretty advanced stuff.
And I think, well, I think two things at the same time. Sometimes it’s difficult. But I think two things at the same time.
One is that there is probably a market out there for people who are beginners who want to start being advanced.
And then there are also people who think they’re way more advanced than they are. They’re the people who always have a what about but what if but it’s never about anything particularly pertinent or anything that would really work.
So, there’s two crowds out there. If I’m going to focus on a crowd right now as far as material goes, I think I want this crowd a little bit.
I want people who realize that they don’t know what they don’t know. who are having real troubles performance tuning.
They might read blogs. They might, you know, watch videos. They might, you know, they might go to user groups and stuff. But they’re just not making that jump.
They’re not making the right connection to figure, to like get themselves on the path to advanced. So, I want to start, I want to start, I think I would like to, for this one, attract people who need to go, maybe not from like 200 or 300 to 400, but maybe from like 100 or 200 to 300.
I don’t want to have to get crazy deep into stuff. I just want to give people, I want to give people enough so that when they start looking at code and indexes and query plans, they can start like, like thinking for themselves, learning for themselves and fixing problems themselves.
He says, I don’t think he wants to see us. That’s not true at all. I would love to see faces. Like, maybe not in this format, but if, if, if, well, we were live and in person and I was looking out at you while I was doing this, I would be very, very happy to see, see those faces.
So, it’s not like I don’t want to see you. I just don’t think this is a great format to see you in. Like, if I had like a Brady Bunch style, like lineup of faces off to the side, I don’t, I don’t know that that would be helpful.
The CPL puts you and it’s like, connection timing out and whatnot. So, what could we call, what could we call a pre-con?
What is our title going to be? Where we try to, we try to attract people who need to go from like one, two hundred to three hundred.
Like, what’s some good stuff in there? What’s some good stuff that we could call it? I’ll give, I’ll give y’all some time to think. I’m going to, I’m going to give it a few. Let’s see. Maybe, uh, the beginner’s guide to advanced performance tuning.
That might be a good one. Uh, man, you’re failing me miserably. no and no. Uh, damn, Arthur.
Putting me up, putting me on blast like that. Put me on blast. Video freeze for anyone else. I don’t know, but I’ll stop and wait for someone else to answer.
Maybe try refreshing, Arthur. Did the audio also freeze or can you still hear me? Uh-oh, Arthur.
Might want to check that internet, pal. So maybe the beginner’s guide, why are you blue? Oh, because you’re, no, screw.
I’m not changing that. Beginner’s guide to advanced performance tuning that might run. starting SQL, um, uh, let’s see here.
Uh, let’s see. Uh, what would be a good way of starting? Maybe starting isn’t that great of a, maybe that isn’t. Had to reboot.
Like your whole computer? I’ve been playing too many video games, man. It’s got, you know, all that precious VRAM. That precious VRAM is sucked up like video games.
SQL Server, taking the next step from beginning to advance. Okay, that’s got something to it. All right. I don’t know if I necessarily want SQL Server in front of that.
Maybe I could do, you know what, maybe we could, we could combine forces a little bit here. Maybe we could call it, oh, and look at that pasting with full formatting. So I’m going to give you, I’m going to give you probably the most invaluable piece of advice that I have.
When you have text that is formatted in a certain way, like when I paste that text there, it comes up with a background and different fonts and everything. If you click on the Windows icon, oh, it’s not in here.
If you click on the Search icon and you paste something in there and then you copy and paste it out, you get rid of all the formatting. But we’re going to have to fix these words a little bit.
Next. Step. From. Beginner. To. Advanced.
There we go. Using Notepad for that. See, there you go. There’s all, see, there’s all sorts of fun tricks out there. I don’t trust Word.
When he says paste values in Word, I don’t trust that. Every time I paste values in Word, you know what happens? It changes fonts on me. Like, I’ll hit enter a couple times and a font will switch to something else.
It’s never a good experience. I don’t, unless I can, unless I have the raw values from somewhere else, I just don’t trust Word to do anything right. Like, like, you ever try to, like, get Word to, like, like, go, like, you scroll down and then you’re like, oh, I want to put something here and you’re like, hello, and then you, like, write some stuff up here and you hit this and then, like, this just jumps down and then you, like, spend some time trying to get this to work and it’s just like, it just jumps up and down in, like, weird increments on you.
Like, I just don’t trust Word to do anything right. I just don’t. I just don’t ever do it. So, let’s call this title. So, we have two now.
We have Beginner’s Guide to Advancing and we have this one. Oops, come on. Come on. All right.
So, what else do we have in here? What else could we do? We’ll be, I’ll give, I’ll give, I’ll give one more lucky, lucky person a chance.
Come up with a title. Advanced Performance Tuning, Starting SQL, Taking the Next Step from Beginner to Advanced. see, the problem with this one is, we need, we need, we need, we need people to know, uh, that it’s about performance tuning.
If we don’t know that it’s about performance tuning, people will say, Beginner to Advanced, what? What you should know about performance tuning?
I don’t know about that one. It needs to be, it needs to be more, be more action packed. Right? It needs to be more action packed. It’s a bit nebulous. We need, we need something that, we need something that signifies someone is, someone is, someone is starting from the beginner area.
Right? Someone is on the path to advance, but they haven’t quite made it there yet. Right? So, we’ll, we’ll, we’ll come back to that and we’ll think. So we have the title. So we have the titles down.
So, who? There’s performance tuning, find where it hurts and fix it. Woo! I, I like it and I would use that for something else, but you know what?
But I, I, I, but I, what I need to, or what I would need to do with that is I would need to, I would need to figure out how to get, oops, I would need to figure out how to get it to also include the fact that, you know, this is a beginner level class, that this is not going to be, you know, three and like, you know, 400 level stuff.
stuff, and I would need, so I would need like that, that sort of thing in it. We’ll get some, get some capitalization in here.
So, who, who are we talking to? In this case, we don’t necessarily want, uh, DBAs, because, uh, oftentimes, so actually, you know what, let’s not do it by title.
What we don’t want, this is, not about, infrastructure, issues, like, backups, hardware, HADR.
Right? So, accidental DBAs is, is okay. I’m okay from, I’m okay with, uh, accidental DBAs. I’m also okay with software developers. But what I don’t want is, infrastructure DBAs.
I don’t want a junior DBA, who, is consumed by, you know, taking backups, restores, check DB. Not because I don’t want them to learn about performance tuning, but that’s just not where they’re focused right now.
That’s not where, like, that’s not what they’re showing up to a class to learn. I don’t want people to think, I’m going to teach you how to take backups faster. Right? And I want people to learn, like, like, you know, how to, you know, how to, you know, get your availability group to fail over faster.
So, like, I don’t want the infrastructure DBA. I want the, I want DBAs who are doing performance tuning. So, let’s, let’s, let’s focus in a little bit about that, on that.
You’ve, then, performance tuning SQL Server, for, for, let’s see, for a year or two. So, a year or two is probably good.
Um, because that would at least get people in the door who have, you know, looked at a query, query plan, have looked at indexes, have probably fixed some problems on their own, and who are, who have probably gotten to the point where they’ve hit a problem where they had to go read something.
Right? So, like, they’re not totally unfamiliar with things. They just might not have the depth of knowledge on certain things that gets them to the advanced part. So, did the advanced part.
Uh, so let’s say you’ve been, you’ve been performance, like, uh, we want people, let’s, let’s not write the abstract in the who. People who have been performance tuning for one to two years, probably read blogs, watch videos, and are familiar enough with SSMS.
Oops. Uh, query plan, oops. Someday I’ll get it right. Query plans, uh, indexes to not need, um, let’s see.
Well, let’s figure out a different way to say it. Um, let’s say to not know, like, so like, like, what I want to identify is, you know, people who, um, um, people who know what these things are and where to find them.
Like, I don’t want someone who’s just like, but what script should I run to look at my indexes? Like, I want someone who’s a little bit more engaged in that. Uh, uh, and how to find them.
That’s good enough wording for now. So, let’s see here. Lee says, it’s the glue that links different concepts together to provide a solution. That’s the hard part.
Yeah. So, you know, um, whenever you’re tuning a query, you know, there, you know, there could be any number of things that look pathologically wrong with it. You know, it could be something in the query plan with the parameters, the way the query is written, but getting like to the end result of what was actually slowing it down, it could have only been one or two of like the five or six things that you spotted.
Right. Like it could be, and it could be like, you were just like, Oh, Oh, it could be that. Oh, it could be that. But then like, you know, you go hit a five, you look at the actual plan. You’re like, Oh, that’s what it, that’s that one thing.
It wasn’t the five or six other things that will probably go wrong next. It’s that one thing that was wrong now. So, so we want people who have not been doing this for a very long time, but who at least have the wherewithal to know some stuff up front.
Right. Like, I don’t want anyone to ask me like, how do I get an execution plan? You know, how is query formed? Uh, what’s the difference between a clustered and a nonclustered index? I want people who have like some meat on their bones, but I don’t want people who are like up on stage flexing.
Right. I want like some people who have just like kind of got a little bit out of it. So, and I want to know what’s their pain. Do they need to, are they, do they have a tough time reading query plans?
Um, understanding what’s wrong with way, uh, query, ha ha ha ha ha queries written.
Oh man. I buffed that one, huh? designing indexes. Um, like what, what, what, what pain points do they have?
What are they, what are they currently just struggling with? So think back to when you were like a year or two into performance tuning, what kind of stuff, um, what kind of stuff were you just befuddled by?
Okay. Identifying the real body in the bottleneck. Okay. Uh, I find it difficult to find out what I should expect as performances for a query. Parallel.
Ha ha ha ha. Coyote McD, a lot of people are still befuddled, flummoxed, and perplexed by a parallelism as we have recently learned. It’ll be, actually, no, let’s go find it.
Let’s go see where things are at. So, um, my dear friend, Paul White has, has a Twitter poll. Has a Twitter poll.
And if I go search through here a little bit, I will find the Twitter poll. It’s in here somewhere.
There we go. So if you’re on Twitter, I highly suggest that in the next three hours or so, you go and answer this poll.
The poll is a, it’s a good question. Some replies were hidden by the tweet. Ha ha ha.
Good for you. So the answer, or rather the question posed by Mr. White, for scientific purposes. A row mode parallel query runs at max.4 on a SQL Server, 2005 to 2019 instance with eight total cores.
What is the maximum number of threads that can be running concurrently for the query? Notice we’re not saying schedulers or cores. We’re not saying CPU, the maximum number of threads that can be running concurrently for the query.
Oh, Michael, I’m not going to say if you’re, if you’re right, wrong, or anywhere in between.
I am going to say that you are a very, very smart person. And that if more people listen to you, more people would, would be smarter too. He says resource usage versus query speed.
I know that you didn’t say cost, but, you know, resource usage is interesting because what if, so here’s, here’s an example.
What if you have a query that uses one second of CPU, uh, and runs serially? So that, that every time that query runs, it takes one second.
Now let’s say you have, you tune that query and it goes parallel. It now runs at dot four. So it now uses four seconds of CPU, but it runs for 250 milliseconds.
In this case, we had perfect parallelism. Everything teamed up. Gene Omdahl stretched out in his grave, put his arms up and screamed, we did it.
I’m not sure if Gene Omdahl is dead. It just had a good visual to me there. Zombie Gene Omdahl, like we did it. I’m sorry if you’re not dead, Gene.
I apologize. So you used four times the amount of CPU to get the query to be four times as fast. You used, you used more resources, but the end user gets the result faster.
Did you tune the query or not? Did you do better? Is the query better? Can you, is there, is there a, is there a serial plan for that query? That would be 250 milliseconds.
This is, these, see, when it comes to resource usage, it’s a tough thing to gauge whether resource usage has made a query better or worse. The same thing goes for reads too.
Same thing goes for reads. Same thing goes for reads. You can, you can, I have tuned queries, I swear to you, where I have ended up doing more reads, but the query has been much, much faster.
It’s a, it’s a real thing. Kalil says, depends on the query and how many branch. Well, go vote. You have, the link is in chat. You can go vote.
You can, you can tell Paul what you think about his question. So that’s interesting though.
So we have some things, some things in here we have to add. So we had some stuff. Identifying bottlenecks.
Parallelism. Resource usage. By queries. Let’s see.
So what else? What other, what other pain points might people be struggling with in their first year or two of query tuning? Maybe, well, we have designing indexes.
Let’s just, let’s add what’s a good, what’s a good index. Making CTE faster. That’s a good one. Parameterization.
Arthur. Holy smokes. Man, is that, is that, so, I’m going to ask you a tough question because you’re a smart person, Arthur.
How would you teach beginner people about parameterization? Like, like what would be your end goal? Michael says when there are too many indexes.
Too many indexes. That’s a good one too. Because too many indexes can really, really cause things to barf up in the wrong way. And they really cause things to go down the wrong pipe.
So having too many indexes is probably a good thing to identify. I’d be with you on that. I’d be with you on that.
Three most common. Ad hoc, prepared, and procs. Here we go. Prepared and procs.
And so, when you teach them about parameterization, do you also go into forced parameterization? Do you go into, oops, that didn’t, that didn’t go well.
Let’s, let’s scoot that over a second. Parameter snapping, things like that. Maybe dynamic SQL. Ooh la la.
I love the sound. That. So, what about dynamic SQL? What about dynamic SQL? Would you, would you, would you want, you, you have wanted your past just starting out with SQL Server stuff to learn?
Would it be, you know, staying safe by, staying safe, no SQL injection, when to use it.
So, I guess we, we already have, oh, staying safe, when to use it.
So, it’s, you know, it’s funny how much the, the parameterization thing, and the dynamic SQL thing, dynamic SQL thing, come into, come into play together.
Right? So, let’s actually make this, a topic up here. Why didn’t you do what I said to do? You’re very mean to me, Microsoft Word.
So, maybe we’ll, take designing indexes out of there. And we’ll keep, what’s a good index, when there are too many, well, you know it’s about indexes now, so we don’t have to keep that in there.
So, let’s see here. Let’s call this, query, anti, patterns, and what, and stuff that might fall into, this.
So, it’s interesting you say deadlocksly, because, are deadlocks a performance problem? Or, are deadlocks a logic problem? Are deadlocks, well, see, and we talked about this earlier.
So, locking and blocking, are they performance issues, or are they concurrency issues? If I wanted to teach someone, if I wanted to do a day of concurrency, I would be all game, to teach people about locking, blocking, and deadlocks.
I would be all, I would be all about that. But, I’d have a tough time, covering, the amount of ground, that I’d want to cover, with performance tuning, and also getting to locking, and deadlocks.
When it all goes crazy, with triggers and firing keys. So, you know, and that’s, that’s a funny one too. Because with foreign keys, in general, not always, but in general, as long as, you have, pretty good, indexes, to support your foreign keys, then you’re in good shape.
The trouble with triggers, the trouble with triggers, is that, people, are going to do, dumb things, inside of triggers, all the time.
If I, if I were to, try to tell you about, or try to show you, the triggers I’ve seen in my life, where people have written, applications, inside of triggers, that run to account, for like, a decade of business logic, when a single row, gets inserted, not only would you not believe me, but we’d have a hard time, like, tuning that trigger.
People do some real bad things. CLR triggers. No, I don’t, I don’t do CLR much, because I’m not smart enough, to use C sharp.
So, CLR, CLR, well, it seems like a fine thing, and I’ve, I’ve bought books on C sharp, I have them, I’ve started to read them, and you know what always happens? I start typing, and, I, I, I fall over.
I fall over. I just, you know what it is, I think it is, is that I have not had, I have not had, a good reason, to, or rather, I have not had a good application, for CLR in SQL Server, at least one that didn’t already, have a solution to it.
So, like, recently, my dear friend Josh, helped me, write a, a, a CLR utility, to take, all the numbers, out of a string, or all the string, all those, like, alphanumerics, out of a string, or something like that, and like, he was very smart, and good about that, he did it very quickly.
If I were to try to do that, I would have beefed on that thing, for days, probably come up with something, that, like, didn’t even compile, maybe if it compiled, the results would be wrong, I just haven’t had, a good application for CLR.
Is it CLR, so, anything is a bad idea, in, in the right amount, right? I don’t think, see, there’s anything necessarily wrong, with CLR triggers, I don’t necessarily think, there’s anything wrong, with some business logic, and triggers, a lot of what, a lot of what goes bad, in the trigger, is going to be, what goes bad, inside of, other user queries, someone’s going to write, a bad query, someone’s not going to, understand how to, index to make the trigger, go as fast as possible, things like that, like, people are, people are going to, like, you know, write cursors, inside triggers, use triggers, to call store procedures, triggers, one of my very first, consulting gigs, was, working with a client, who had, terrible, terrible problems, every time they, inserted to a table, really quickly, I, like, we, like, I was able to spot it, because I was running, SP who is active, every time they, inserted a row, a trigger would run, that would call a report, that would generate, three different reports, on every single, like, different, on, like, the same table, three different ways, I was able to spot that quickly, but no one else, looking at it, everyone else, was just like, boom, so, like, if there’s a, if there’s, like, a moral to this, or it’s like, sure, don’t put store procedures, that call three different reports, on a table, inside of a trigger, every time you insert a row, is that a good performance tuning topic, I don’t know, so, I think, you could take a lot, of the performance tuning stuff, and apply it, to the bad idea stuff, that people put inside of triggers, I just don’t see, how, like, targeting triggers, is going to really help, Aaron Bertrand, has a really good talk, I think it was at SQL bits, Aaron Bertrand, SQL bits, triggers, where Aaron Bertrand, talks about, some ways to write, more effective triggers, I’m going to close that window, before the video starts playing, but I’m going to stick the link, into chat for you there, so, Aaron Bertrand, has a good, has a good session, on writing, more effective triggers, but a lot of the stuff, that’s bad, that people do, inside of triggers, is bad stuff, that people do everywhere, you know, they’ll put the, the entire content, of the trigger, inside of a transaction, they’ll, you know, you know, call cursors, and loops, and iterate over things, and you know, and like, more understand, how to tune the queries, or the indexes, that go inside the trigger, so I’m like, I’m like, I just don’t think triggers, are like that appealing, of a subject overall, I would rather have people, be able to, learn as much as they can, about query tuning, and be able to apply it, to things that they see, inside of those triggers, to make those triggers, go faster, so when people want to apply, 10 years of business logic, it happens as quickly, as possible, so let’s think about, some other stuff, so we have, stuff about query plans, we have stuff about, query anti-patterns, some stuff that we can put in there, off the top of our heads, it might be, table variables, might be, functions, might be, sargability, might be, implicit, conversion, what are some other things, that we might see, as a query anti-pattern, spiritualizes, that’s something you can put in a class, the good, the bad, and the ugly, I don’t know what you mean, I don’t know what you mean by that, clarify, and I will answer, CTE, yes, well CTE, we know they’re not magic, with all that, CTE, let’s see, what are some other things, that we could stick in there, wait stats, so we, I talked about, why I stay away from wait stats, earlier, you showed up a little late, I’m not going to, talk about it all again, but wait stats, are just not that interesting, to me when it comes to, tuning a single query, wait stats are more of a, server tuning thing, Bosco says, nesting store procedures, again, if you nest, nest store procedures, that’s totally fine, there are actually, very very valid reasons, to nest store procedures, again, what I’d rather cover, is, you know, rather than like, something like that, I’d rather cover, making the code, inside of those, nested store procedures, as fast as possible, so that people can, tune those store procedures, to go so fast, that no one cares, that they’re nested, right, like I see, like I see what you’re getting at, but you know, nesting store procedures, to me, is a good choice sometimes, I’ve actually solved, a lot of problems with that, let’s see, Spare Deli says, I guess you do that already, similar to what you discussed, about triggers, examples of the, this is ugly, this is the bad, and then the good, yeah, so, sure, but you know, again, the triggers are going to be, T-SQL anyway, right, the triggers are going to have, queries in them, the triggers are going to, need to be tuned, in certain ways, so it doesn’t matter, that the code isn’t a trigger, what matters is people, being able to like, get the query plan, look at the text of the trigger, see what indexes were involved, and then start to solve problems, from there, but learning by example, is huge to me, right, because if, like learning by example, is I think the best way, to learn, right, if like, like if, if it’s just a bunch of theory, then, people, people, people leave kind of empty handed, if it’s just all theory, people don’t have concrete examples, of, when things are bad, how to know that they’re bad, how to fix them, stuff like that, then like, what do people really walk away with, more guesses, right, more, more things that they’re not sure about, right, if you give them, if you give them a, a steps, to solve a problem, that’s a, that’s a powerful, powerful thing, teach them how to fish, as they say, right, teach them how to fish, all right, so let’s see, what are some other query, anti-patterns, that we can maybe put in there, let’s see, curious, curious, curious what else, we could maybe, put inside of there, oh, we know what’s a good one, I can’t believe I didn’t think about that, local variables, okay, so I think that’s a pretty good list, so when we say, identifying bottlenecks, maybe we should put, identifying bottlenecks, up under reading query plans, because that’s probably, where we’re going to identify, the bottleneck, that’s like the most reasonable place, to identify a bottleneck, is looking in the query plan, if you just look at a query, it might be pretty hard, to figure out, what in the query, is causing the bottleneck, one of the biggest, one of my biggest pet peeves, if I’m looking at any Q&A site, is someone posts, just the text of a query, and they’re like, I need to tune this, okay, what would you like to do with it, what’s slow about it, right, like, so like, if we’re going to identify a bottleneck, we’re probably going to need, some deeper information, that deeper information, is probably going to be, stuff that we find, in the query plan, so reading query plans, let’s take the question mark, out of there, we’re going to be identifying, bottlenecks, let’s see, go, it says, put UI logic in query, ouch, that is no good, so one crazy thing, that I saw recently, was someone, had, a table, with, a, a binary column in it, and that binary column, and that binary column, could be converted to XML, and the XML contained, the entirety, of each individual user’s, user settings, application logic, things that they had customized, about their application, and, every time someone logged in, they had to go through this table, convert, their row, from, var binary, to XML, and then, search the XML, for different things, it was, one of the ugliest things, I’ve ever seen in my life, it was like, like, just, like, what were those, what happened with your developers, that they did that, the worst part is, that they, they, they fancied themselves, to be X query experts, but they were casting their XML, to Envarkar, and just searching things, as it, as like a SQL blob, and I was just like, bless your little hearts, bless your little hearts, so, let’s see, reading query plans, identifying bottlenecks, query anti-patterns, like table variables, functions, archibility, and plus diversity, local variables, parallelism, alright, so, Coyote McD, if you are still here, what about parallelism, would you want your one, or two year, into SQL self, to learn?
Let’s see, good tables, and normalization, so, good tables, and normalization, is certainly, a, a performance topic, it’s very hard, to get people, to make changes, to table structure, there are, there have been, many, many, many, many, many times, in my consulting career, when I’ve tried, to tell people, well, look, it’s going to be, really hard for you, to, to get the kind of performance, you want, unless you, normalize stuff out, like this one, big wide table, you have, is giving you, a really hard time, within that table, we have a bunch of, groups of columns, that would actually, they’re actually sort of, identify, themselves, as tables, like you might have, columns with a bunch, of prefixes, like client name, client address, client phone, and you might have, a bunch of, of, columns that identify, themselves, as, like things, that just sort of, belong together, like phone one, phone two, phone three, phone four, phone five, things like that, like you might have, those sort of, self identifying tables, with inside, like inside of your tables, and that’s, that’s an okay thing, to tell people about, but that’s a hard thing, to get people to change, it might help them, the next time, they start a project, from scratch, but it’s really hard, for them to get, to change their application, or change their like, table design, and then change their application, to work with the table design, even if you give them hints, like, well you know, you could change this, and then use like a view, that has like the join in it, to sort of do things, it’s, it’s not, it’s not an easy undertaking, there are a lot of gutches in there, so good, good tables, and normalization, sure, but I have about, five minutes worth of things, to say about that, and you just heard it, let’s see, what’s going on here, why repartition streams in there, and why ordering is bad, with parallelism, so coyote mcd, that’s stuff that, you would want yourself to know, after one or two years, of learning about, SQL Server performance, that’s, that’s a tough couple things, for people, like, like if I started talking about, like, exchange spills, or parallel, like, interquery parallel deadlocks, to people who have been working, with SQL Server, for a year or two, I think that, I don’t think that, they would be able to make, heads or tails of it, it would be like, a scare quote, right, it would be like, one of those old movies, about pot, where someone jumps out a window, like, and, and, and the other thing is, if I tell them about that, you know what they’re going to do, they’re going to hate parallelism, forever, they’re not going to trust parallel, query, every time they see a parallel query, they’re going to be like, oh god, is it doing that thing, that guy told me about that one time, is it spilling, what’s going on, maybe I was a little advanced, at one to two years, yeah, yeah, so, you’re, you’re a smart, you’re a smart fella, you’ve, and you’ve, and you’ve hung around, smart fellas, and you’ve invested the amount of time, that would, that would certainly get you, um, past where most people would be, um, uh, but, I think, it would be tough, tough to sell that to, someone starting up, so, Khalil Jamil says compression, sure, so, what about compression, we have row compression, and we have page compression, what kind of stuff, would you want to learn about it, so, let’s see, uh, let’s take these out for now, well, you know, let’s move these a little bit, because we have some questions about these, I’m going to figure these things out, let’s go, X, U, and, and we’ll add compression to the list, and so, what about compression would be interesting to you, for anyone else, for anyone else out there, like, what sort of, if you have any ideas about, like, what your one to two year into SQL Server self, would be into, about parallelism, resource usage by queries, or compression, throw it into chat, we can, we can try to figure this out together, try to figure out what kind of stuff, you might be interested in, this is all stuff I’m game to go into, it’s just, I want to make sure that I’m, I’m going down the right path, for what people, for what would, what would really bake people’s noodles, what would get you going, what would make you, happy to see, be taught, all right, got kind of quiet there, all right, so I think we have a pretty good list of topics, right, so what’s their pain, all of these things, it’s a challenge, knowing what you don’t know, yeah, yeah, it is a challenge, knowing what you don’t know, but it’s, it’s also a challenge, trying to teach you, what you need to know, you know, you, you know, like, just maybe, the thing that you need to know about parallelism, is, whether it’s good or bad, if you, if it’s something you should worry about, like, what it is, what it does, you know, like, or maybe, what you need to know about resource, which is by queries, is this like, like, like, is that, is, is that something that you should be concerned about, is, like, do, do, will queries that use more resources go slower, for compression, it’s, what, like, what kind of compression should I use, I mean, that’s pretty well documented, there aren’t a ton of gutches with compression, I sort of like compression, I just don’t see it get used a lot, the other thing about compression, is it just hasn’t changed much, since like, SQL Server 2008 R2 or so, so like, you know, it’s, it’s another one where like, if I’m going to teach it, I need something very specific to teach people about, you know, like, like, really, it’s, it’s sort of a, it’s like a five minute statement, in a lot of ways, it’s like, compression, it’s good because, this, you should use row compression when, you have fairly unique data, you should use page compression when you have less unique data, I mean, it’s just like, you know, like, stuff that you can just get out of the way pretty quickly, it might not demo well, and it might not be anything that people need to know about, Ken McT says, the very basics of what the optimizer does, so, hmm, okay, so let’s see, let’s see, let’s go back to here, the basics of what the optimizer does, he says, I would have liked something about how to measure changes you make in a better way than just watching the timer at the bottom right of SSMS, so would that go under resource usage, and how to measure changes, uh, yeah, there we go, so parallelism, uh, so let’s just call that, uh, how to set settings, that might be a good one, that might be, uh, that might be close enough, and we’ll, we know what the settings are, we don’t have to worry about that, we know we’re not talking about resource governor, Kendra didn’t show up, because I, apparently I’m not talking about resource governor, so Kendra didn’t show, I was very hurt by that, I’m kidding, I think it’s just, Kendra’s probably just drunk on a couch somewhere, I don’t blame Kendra, it is that time, it is that time, when you’re in the UK, okay, it is that time, it is that time, we’re not talking about, the basics, of, the optimizer, so, Coyote McD, it’s a good suggestion, but I want to ask you, what are optimizer basics, if you, if you had to think about, optimizer basics, would it be, um, you know, uh, optimizer assumptions, like the cold cache, uh, that the data exists, that data is independent, stuff like that, uh, would it be, um, you know, figuring out query plans, or like, like maybe optimizer tricks, like, uh, like simplification, or, you know, contradiction detection, or stuff like that, uh, collapsing sub queries, or expressions, like what kind of, what kind of optimizer, or what would you consider, to be basics, of the optimizer, that would be, or maybe like, uh, optimizer has rules, stuff like that, uh, query plan reuse, so query plan reuse, I would put that up, under parameterization, uh, so I would put that up here, and plan reuse, so that would be a good topic, under parameterization, because that is, that is very, that is very, very good, and that definitely falls into, like a pretty big wheelhouse, of, of subjects, and topics, that you can talk about, with people, that will, like, boggle, and make them angry, and be like, why does it do that, like, who does, design this thing, why are they so great, like, what are they thinking, so yeah, that’s definitely a good one, that we can, um, we can put in there, I would add that in there, uh, has something about statistics, been put down yet, no, nothing about statistics, statistics, but, what about statistics, what’s something about statistics, would you want to learn, how to look at them, uh, how to figure out, if they’re good or bad, you know, there’s like, the ascending key problem, that got, that got kind of fixed, with SQL Server, with the new cardinality estimator, it’s a kind of fixed, but like, what about, like, like old statistics, when to update statistics, because if we’re talking about, when to update statistics, that’s a maintenance thing, oh, it’s off, but I never grasp, the service broker, how to use it, why be better than the, yeah, it’s service brokers, I mean, Remus Rosanu, is one of the finest people, you may never meet, in your life, I still don’t know, why service broker, came to be, um, my old, my old co-worker, Jeremiah, once used service broker, to asynchronously, shrink transaction logs, people often have to, hunt for reasons, to use service broker, Michael Erickson says, I guess it is hard, to understand parameter sniffing, without basic statistics knowledge, so that’s a, that’s an interesting one, and that would kind of, tie into, what happens, with, uh, stats and local variables, uh, maybe, this would also, have something about, stats in it, sagability would certainly, have something about, stats in it, and so would implicit conversion, there would definitely be, stuff about stats in there, and then parameterization, this would certainly, have stuff about, stats in it, because you, you know, when you, when you’re learning about, uh, when you’re learning about, you know, why SQL Server chooses, different plans, then statistics are a big part of that, so Lisa says, let me rephrase that, estimations and how to troubleshoot, when they are out by a lot, you mean aside from updating statistics, statistics.
So there’s, there’s a lot that goes into that, so when, when, so when cardinality estimates are terribly wrong, you know, you have to go back and you kind of have to look at, is it one of these problems?
Did I not update statistics recently? Um, or, did I write my predicate, or my join in a way, that SQL Server is unable to make a good guess?
The other thing that’s a big, the other thing that’s big there, is figuring out when, uh, when inaccurate statistics guesses are actually a problem.
I see questions posted quite a bit about, statistics were right, but everything else was wrong, or, statistics were wrong, but like, and I have, here’s my query plan, how do I fix it?
But the plan is still remarkably fast. So, uh, I think when it comes to statistics in general, rather than have a section on statistics, I would rather weave statistics in, to a lot of the different things I teach, because you can, you can, you can, you can really, I think you can, you can drive home how important statistics can be, when they’re, like when they cause problems, and when they don’t.
So, I would probably want to weave that in. okay, let me say, maybe something about the optimize forehand. All right, so, would I put that under, you know what, I would, I would want to have that under local variables, since optimize for, since optimize for, does just about the same thing there.
So, I would have optimize for, alongside local variables. So, well, optimize for unknown, right? so, let’s make sure that’s specific. The optimize for unknown, would be, would certainly fall into the local variable category.
I don’t, but, you know, that’s something that I would want to be, tie into there, because I don’t want people to walk away from this, with a question like, but what’s the difference between, you know, optimize for unknown, and a local variable.
And the same reason that, you know, you see the question all the time, people asking, you know, like, what’s the difference between, no lock, and, read, uncommitted, right?
Like, that, what’s the difference between, same thing, the difference between local variables, and optimize for unknown, it’s the same damn thing. He says, I guess transactions would fall under concurrency. So, when you’re talking about transactions, when you’re talking about, begin, begin tran, and commit or roll back, everything that happens within that, is subject to query tuning, right?
And we know they’re dangerous, because they increase the chance of blocking. If you update, if you like, say, begin tran, update a single row, then go do, like, go off on some crazy meandering path of doing things, and then finally roll back or commit way down here.
Well, if all of this big meandering path is fast, then that, then that one lock you took up here, that was done, say, 200 milliseconds, 500 milliseconds later, probably not the end of the world.
But, if you take that one update, and then this big meandering path is like, two to five seconds, then that lock becomes more interesting.
So, when it comes to tuning transactions, tuning transactions is more about tuning everything that happens within the transaction. Same thing with triggers, right? Same thing with functions, same thing with store procedures, same thing with anything that contains code in SQL Server.
However, if you have, if you have a whole bunch of stuff that’s slow between a begin tran and a commit or roll back, we can focus on tuning the stuff that’s slow in there. You know, sure, there’s, there, there might be times when you can move the begin tran to like some other part of the code where it really matters.
But, otherwise, what do you tune about a transaction? You tune the underlying queries and indexes, right?
You don’t go in, you don’t, you don’t, there’s like no, like, like hint, and there’s no like option for begin tran that makes things faster, right? The transactions generally would, would be a concurrency thing.
Not that concurrency has nothing to do with performance. It’s just that concurrency is such a topic unto itself with locking, blocking, deadlocks, transactions, things like that, that it’s really tough to sort of, you know, like stick into a performance tuning talk easily.
So we have a good list of things here, right? We have, we have a few different titles up here. And that’s, that’s good.
That’s good stuff. So let’s start on this page. Let’s start on this page. And let’s say, what is our abstract going to be? So let’s just, let’s not say you’re a DBA or developer.
Let’s, let’s take titles out of it. Right? And let’s say something like, you’re new to SQL Server.
And your job is to, let’s say, fix performance problems, but you don’t know where to start.
You’ve been looking at queries and query plans. And, let’s see.
queries, query plans. And let’s just say something.
We’ll, we’ll fill that in later. And something indexes for a year or two, but it’s still not making a lot of sense. Logs, read, give, bury.
Nice. Oh, stop it.
Or if it applies to you, use UUID as a primary key. I’m totally fine with that. I’m just happy if you have a primary key. I’m happy if you have tried so hard to design things properly, that you have a GUID as a primary key.
There are ways to like not have it be so painful. Like if you make your primary key, a nonclustered index, or if you use a, like a sequentially generated GUID, you can have far fewer problems with GUIDs as a primary key.
But most of the time, when I see someone has a primary key, I’m like, you know what? You tried your best. You tried, you tried hard.
And I understand why, you know what? You know, when you think about numbers in SQL Server, you think about ints and big ints, what do they have that GUIDs don’t have? They have an end.
There’s a, there’s a finite number of those numbers until you have reached the end of those numbers. GUIDs, wide open baby.
You can have GUIDs go on forever and probably be unique. Numbers, finite. Even if you start negative and go positive, they are still finite.
finite. Granted, big ints take a long time to go from the negative end to the positive end and hit both sides of that limit. But I have faith that with big enough data, with real big data, you could do it.
All right. Okay. So I’m going to take a quick break and I will be back and we will work more in the middle of the year.
Bye. Bye. Bye. Bye. Bye.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. You know I’m home. You know I’m home.
Where else am I going to be? Leave it. It’s fine. All right. So let’s finish up strong. We’ll go until the hour and we’ll finish up writing this abstract here. The blog is where you give you a very specific advice and you’re not sure if it applies to you or it’s even the problem.
So using some of these advice. So using some of these advice, beyond that, you’re not sure how to measure if your changes are, let’s say, are working. Cool.
Cool. So we got that part. All right. And we’ll say something like, you know, like in this day long, right? So let’s see.
Join me for a full day. All right. It is going to be a full day, right? You know what? I don’t like the way that sounds. You know what I don’t like about that? It starts with join.
Join. You know what bums me out about join? Too punny. It’s too punny for me. Much like LaCroix bubbles are too big and soft for me.
And only Canada Dry bubbles satisfy me. Things that are too punny don’t go over well with me. So in this full day, I don’t know.
Let’s just say something funny in here. Performance tuning extravaganza. You’ll learn all of the stuff.
I’ll get to that in a second. Okay. You’ll learn about all the most common anti-patterns in T-SQL. Oh, I messed that up terribly.
T-SQL querying and indexing. How to spot them. Using.
Oh, come on. I was so close. Using execution plans. Ah, here we go. All right.
That’s a full enough thought. In this full day. Performance tuning extravaganza. You’ll learn about all the most common anti-patterns. In T-SQL querying and indexing. And how to spot them using execution plans.
You’ll also. Leave. Knowing.
The. Let’s see here. What could we call. Some of these things. Knowing.
Why. Why they cause. The problems that they do. And. How you can. Solve them.
Quickly. And. Painlessly. Pain points. Pain points indeed. So I don’t like to say pain points too much.
Because I don’t want. I don’t want people. I want people to. To come to me.
Knowing that they have them. Without me having to point them out. Getting like. Oh. That looks like it hurts. Oh. That looks like it hurts. Oh. How’d you do that? So. Let’s see here.
Let’s see here. So we could add in some specific stuff now. Right.
So. You’ll. Learn. Let’s see. When. Which. Temporary. Object. To.
Actually. No. Let’s start. We’ll get to that in a minute. You’ll learn. How to. Write. Queries. That. Will. Never be.
Slow. I mean. That sounds good. But I don’t know if that’s. I don’t know if that’s totally true. You’ll learn how to write queries. You know what?
Screw it. We’re going to stick with that. It’ll never. Be slow. We have a lot of you’ll learn in here. You know. There’s a lot of you’ll learns. We have a lot of.
You have too many you’ll learns. Do we have. How many do we have? Not that many. It is a bold statement. But I’m a bold human being. I’m like barbecue sauce Lee.
Bold. And tangy. Alright.
That’s all I got. I’m not bald. I’m doing okay. I’m doing okay. So let’s see. Let’s read it a little. You’re new to SQL Server and your job is to fix performance problems. Ooh.
You know what we should do here? Your job more and more is to fix performance problems. But you don’t know where to start.
You’ve been looking at queries and query plans and puzzling over indexes for a year or two. But it’s still not making a lot of sense. The blogs you read give very specific advice.
And you’re not sure if it applies to you or if it’s a problem. No. I don’t like this one. I don’t like that one. Beyond that. You’re not even sure how to measure if your changes are working or even the right thing to do. There we go.
That can be a big assumption. But the nice thing there. The nice thing there.
is if they leave with the materials, they have no excuse not to learn it eventually. Even if they don’t learn it that day, they’ll bring it home and they’ll learn it eventually. So it’s bold.
So you’ll learn is like future predictive. You’ll learn at some point in the future. Might not be today. Might not be tomorrow. But at some point, you will open up that thing that I gave you.
And you’ll say, ah. And you’ll have learned it. So you will learn. You’ll learn. It’s not like saying, you’ll pay.
I would even say if they have the concept that they have no excuse to learn. Yay! I just don’t want to put that kind of thing on people. Like, look, you have no excuse.
It’s not like my mother vacuuming outside my door when I had a hangover when I was a kid. No excuse. Oh, not for the doc.
Yes, not for the doc indeed. So let’s see here. Pretty happy with this. You’ve been looking at queries. You’ve been pulling around the air to have something on a sense. Beyond that, you’re not even sure how to measure if your changes are working. Sorry, my printer just started spazzing out for some reason.
In this full day, performance, tuning, extravaganza, ganza, ganza, ganza, you’ll learn about all the most common anti-patterns in T-SQL queering and indexing. And how to spot them using execution plans. No, we’ll keep that all together.
You also leave knowing why they cause the problems that they do and how you can solve them quickly and painlessly. If you want to… If you want to…
The… Knowledge… And… Confidence…
To tune queries… So they’ll never be slow again… This… Is… If anyone…
Who’s thinking about attending… Watches this video and sees all the typos I’m making… They might change their mind. I found the main barrier for me is not being able to learn about something is my laziness. No excuse other than that.
Well… Lee… I understand that fully. If you want to gain the knowledge and confidence to tune queries… I’ll never be slow again… This is… The…
The what? This is… The training… You… Need. So let’s go back… Let’s see here…
Let’s see… Training you need… All these in one day…
So… No… I… I asked… I asked the attendees… For their ideas. There’s a lot of this stuff that you can…
You can cover… In a day. I would probably cut the line about here. Because I think…
A lot of… So… It’s a good… That’s a good question, Paranoid DBA. Okay… And… If you think about… What’s being talked about here… As…
Like… Come on… Man… Come on back… Where’d you go? Why are you not… Whatever… Screw this… So if you think about… Teaching each one of these concepts individually…
Yes… That is a big… Crazy… Wide open… Day of learning… Right?
You can think about it like that… But… If you tie these all in together… If you tie these things in together… So that…
When they… They learn about… Sargability… They also learn about implicit conversion… And like… You can… You can tie a lot of these subjects in… So that you… You kind of put… You put more of these pieces together into a puzzle…
Spirit of Lies says… The Friday session had a lot of these topics… Minus blocking… Yeah… So… Yes… But… This would be like… Beginner stuff…
So this would be like… Very… Like… Early on entry level stuff… When you’re… When you’re… Like… As you progress through performance tuning… The first thing you have to learn is…
Like… You have to learn the fundamentals of these things… And then as you get more advanced… You can… You can like… You know… You get more…
You get… As you get more advanced… You can apply them to more advanced things… So… What I’ve found over the course of my life performance tuning… Is that… A lot of the reasons why queries are slow…
Hasn’t changed a lot… But there are different audiences… And those different audiences have different strengths… Different weaknesses…
And someone just walking into query tuning… Who needs to know… Like… Just the right thing to do… They might not… They might not need to know… Like…
Lots of super advanced things that you can do with these things… But they need to know what the right thing to do is… Right? They need that basic fundamental knowledge… Of like…
Why do table variables give me a weird plan? Right? What is sargability? Well… Stuff like that… Are CTE better than temp tables? Why…
Like… How come when I use a local variable… This execution plan gets weird on me? You know… Like… If I have a query that’s going slow… How do I know if it has a good index? Things like that… The more advanced stuff…
Is just like… The next stage of… Like… You already understand what an implicit conversion is… We don’t need to talk about that… You know…
What you need to know is like… You know… You’re looking at an execution plan… And… It has a spool in it… And you need to know… Why that spool is there…
And how you can fix that spool… So you’ve gotten to the point where… You kind of know this basic stuff… But getting to the next point… Paranoid D.U.J. says… But that changes in each version of SQL…
No, it doesn’t! It sort of changed for some things in SQL Server 2019… And it didn’t even change in a complete… And like…
Overwhelmingly good way… It does not change in each version of SQL… Implicit conversion has been the same… CTE have been the same… The problems with local variables have been the same… The problem with sargability have been the same…
The problem with functions have been the same… All of these problems have been the same… With only a few changes in SQL Server 2019… And if you think that there are a lot of people…
Who are one to two years into their SQL Server journey… Who are going to be coming to training… Let’s say in the next two to three… Maybe six months… Who are all fully fledged using SQL Server 2019…
In production… You are out of your mind… But most of these things have not changed… With every version of SQL Server…
What’s a good index has not changed… Parameterization has not changed… Parameter sniffing has not changed… How to use dynamic SQL properly has not changed… None of these things have changed…
With the SQL Server… Lee still has SQL Server 2008 instances… Lee no wonder you want a different job… I hope that works out soon for you… So let’s see here…
Let’s finish this up… And let’s get on out of here… It’s been a while… Me babbling on and on to you… If you want to gain the knowledge and confidence… Toon queries so they’ll never be slow again… This is the training you need…
Let’s see here… I don’t know if I want to add anything to this… You know what I’m going to do… I’m going to save this… I’m going to save this…
This PC… I’m going to save this… I’ll save this later… What I’m going to do is save this… And I’m going to sleep on it… Free candy at the end… How about…
Um… And… You also get… Access to all my… Videos… Blah…
Blah… Blah… BING! I’m not giving away my Canada Dry Lee… Canada Dry is my favorite seltzer… It’s much better than La Croix…
Canada Dry is the best seltzer… Never going to give away my Canada Dry… You can’t take my Canada Dry…
Don’t try to… All right… So… I think we’ve done a pretty good job of getting… Getting the…
What we want to teach… Who we want to teach it to in the abstract… In there… Uh… I should tweet that… I’m too lazy to tweet that… Any vodka with it?
No… Not today… Not yet at least… Uh… It’s still… Um… You know… It’s still 3 o’clock here… And… Uh…
I don’t know… I wanted to at least get through this thing sober… Right after this… Right after this… Botsco… Sure… Let me…
Point you… To my website… Where there is… Yet another post… About local variables… Yeah…
Well that’s a bad idea isn’t it? Michael says… I think this would be good for devs that need to write queries right from the beginning… Yes… That is absolutely what I’m going for here… Um… So I want people who…
So like… Expanding a little bit on what Michael said… Because Michael brings up an excellent point… What I want… What I would say about training like this… Is…
That… When… You have… Had a… T-SQL… Application… That’s been around for a while… You most likely have a lot of bad practices in there…
SQL Server developers will show up to… Either… It’s going to be their first day on the job as a T-SQL developer… They’re not going to really be a T-SQL developer… They’re going to be a developer in some other…
Something else… And… You know… They’re going to see what you did in that code that’s bad… And… What’s going to happen is… They’re going to just keep doing that…
They’re going to keep repeating the same mistakes… They’re going to take those mistakes with them elsewhere… And… What I want to do is… Get people to the point where… What…
When they’re writing a query… They’re not making those fundamental unforced errors… They’re not continuing on that… That legacy of… Of poor T-SQL hygiene… So what Michael said is very, very…
On point with what I want to do… I want to give people the right foundational knowledge… So that… You know… They don’t get bit by a lot of… Just the… You know…
Those like… Like… Head… Like… Why performance issues… Right? That’s what I want… That’s what I want… So…
Thank you for… Hanging out with me today… While we… While we talked about… What makes a good abstract… And writing… The abstract… Also thank you very much for your ideas and suggestions… I appreciate it…
It’s nice having people… To brainstorm… With… If you… Would like to join me… Friday… 10th…
Or 24th… I have a full day of online performance tuning… The coupon code… Floating above my head… Will get you 75 bucks off… From there… If you want to get tickets…
You can head over here… And if you buy a ticket… You get free access… To all of my training… Forever and ever… You can look at… What…
My training covers… Over at that link… So… Feel free to click on those… At your leisure… If you… If this is the kind of… SQL Server content… That you enjoy watching… You know…
You can… Hit the little bell buttons… On YouTube… To like… Subscribe… Or whatever… Whatever you cool kids do these days… Same thing for Twitch… If you want to follow me on Twitch… You’ll get notified when I go live… You won’t have to depend on…
Twitter to tell you… Because… We all know how… Untrustworthy… Twitter is… So… Thanks for joining me… Uh… Come on back… I’m not…
I’m not going to be doing one tomorrow… Because it’s Saturday… And… Uh… I’ll probably end up… I’ll probably not be in good shape… For doing a live stream… Plus I think I’m about to get arrested anyway… So…
Thanks for joining me… Um… If I can get through one live stream… Without sirens… I would be so impressed… Thanks for joining me… Uh… Thanks for hanging out… And I will most likely… Uh… See you…
Uh… Next week… For… Some more live streaming goodness… Take care everyone… Stay safe… See you next time…
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 delved into a detailed analysis of query performance and optimization in SQL Server, specifically comparing execution plans between compatibility levels 140 and 150. The primary focus was on understanding how row mode operations behave differently under these settings. As I ran the queries with varying parameters, I noticed significant differences in execution time and memory usage. Compatibility level 140 maintained a relatively quick execution, while level 150, despite using batch mode for certain operators, experienced a much slower sort operation due to single-threaded processing. This led me to explore wait statistics and memory grants more closely, highlighting the limitations of these tools in diagnosing performance issues under different execution modes.
Welcome. Welcome, welcome, welcome. All you lovely people out there. How is everyone doing?
there are people here so you better answer me of course people started disappearing when I came on screen I guess they were disappointed I guess they were disappointed I guess I wasn’t good looking enough when I showed up I’ll have to go try a face mask or something next time throw that on on camera if you guys can hear me through this maybe this will improve if anyone out there has a Bane fetish maybe I can work with you on that no yeah Steve you were born on a pair of skis or something I’m not going to do the Bane voice that’s where things cut off I don’t do impressions I’m not good at impressions never have been you’re at a ski condo now how does a guy who runs a free free message board end up at a ski condo gotta figure that one out I’m gonna start a free message board wife is successful well she’s gotta be supporting a bum like you lazing about in Hawaiian shirts all day do I have any update to my plans on smoking cigarettes in a French graveyard no just not soon enough not soon enough not soon enough there’s no such thing as soon enough when it comes to that is there every every second you wait is just too long it’s too long sooner the better though still trying to get that all figured out suppose I could call an immigration lawyer right still like look here’s what I do here’s what I want to do here’s why I can only do this in France because you’re the only place that has the proper brand of cigarettes and French graveyards and all I want to do is is work from home not bother anybody and contribute a lot of money to local bars and restaurants and and tobacco shops that’s it I will be an ideal French citizen I mean I’ll be quiet my kids not so much I’ll be quiet but I think I think that I think I would like aside from like being probably like mentally incapable of learning French I would I would be an ideal French citizen whole travel ban thing might have yeah you know that’s a little that’s a little unfortunate but you know hopefully a small travel ban now will result in less of a travel ban in the future or at least like let me just get there and then ban travel let me get there and then travel screw it I’ll figure out I’ll figure out how to SQL Server in French I’m gonna you know what that’s how you know what that’s how I’ll learn French that’s how I’ll learn French I’m gonna learn it from SQL Server error messages so in SQL Server there’s a view right we can do oops from sys.messages and it’s oops I’m gonna start off hitting the right button it’s usually a good good idea and if we look in sys.messages we’ll see all sorts of I mean there’s all sorts of text over here from the messages that you can get from SQL Server right so what’s does anyone know the language ID for French offhand I’m fine go look it up go figure out SQL Server language ID French 1036 ooh la la as the French say so let’s see where oops Steve can you please fix SQL prompt how would I have a with there where language ID equals 1036 run this let’s get oh this is how I’m gonna learn French check this out I have all of the SQL Server error messages in French now you need text to speech I do I do I need text to speech and speech to text because I find that one of the biggest one of the biggest hurdles I have to writing is typos and and like I’m like I think I spend more time going back and fixing things than I do actually writing Duolingo my wife does my wife has been doing Duolingo in French with French specifically for like two years now and Duolingo is still giving her nonsense stuff like the like the men are rich and calm or like the men ate all the strawberries or like the cat is black it’s just like the same stuff like like like like just going like she’s regular with it too every single day and just like nothing like like no real advancement after a certain point so I’m gonna I’m gonna learn it from SQL Server error messages and I’m gonna blow her out of the water the prefix date okay so let’s see the colon prefix does not correspond with the table name okay so apparently what I need to do is figure out a way to have the language IDs alternate and what I’ll do is let’s see in six and let’s say order by I’m on message ID and let’s see well you know it’ll have to be message ID and then language ID will that work no because that’s going to order that first if you just order by message ID it should give us that let’s see if that works no no it didn’t work 102 is it did I miss something did I get something terribly wrong randomly would be funny right 1033 why is language ID 10 oh duh why didn’t anyone yell at me 1036 I had that all wrong there we go now we got it now we got it see I was off by one on that off by one let’s set the whole thing off all right now we got it this is great warning so advertisement anyone help me with the pronunciation there note the error in time and contact your system administrator no the error and the error in time and contract your system administrator well no you see numerically I was off by one but physically I was off on the keyboard by one by 80 I was off numerically by 80 on the keyboard I was off by one so I wanted 1036 and I ended up with what 2016 so I was off by one on the left hand that just screwed everything up screwed up everything but yeah this is great so I figured I have a plan now I have a plan column prefix does not match with a this is great yes I’m going to contact everybody query not allowed and wait for oh this is awesome this is truly awesome oh this is going to be fun I have a plan now I was was wondering how I was going to spend my summer vacation and now I know how I’m going to spend my summer vacation this is great so let’s talk about this query tuning thing now I’ve got a store procedure with two statements in it I’m going to run one at compat level 140 you should sort by language ID no if I sort by language ID then it’s going to be all the 103 3 first I want them to be interspersed like this ordering by message ID so that I can see the English version and then the French version now I don’t want both would screw it up because then I would have all the 103 3 first this way works message 101 message 102 message 103 if I have it by if I sort by language ID then 103 3 will sort first and then all the 103 6 will come later and I want them together so I can see the translation it’s a terrible idea Mr.
P. Shaw I’m ashamed of you ashamed of you no you’re not getting it it’s okay it’s okay all right so let’s look at this thing here right we got one query up here that’s going to run a compat level 140 one query here that’s going to run a compat level 150 and let’s go look at what happens when we execute these so just to be extra short let’s recompile and let’s get that going let’s run this run this all right we’re on we’re on to something else now go talk about order by some go talk about order with yourself I don’t want to talk about this anymore we’re on to the query tuning bits so let’s look at these two query plans they both end up pretty quick all right if you look at these this finishes very quickly and this finishes very quickly good good good we have a sort here and we have a sort here and everything is generally pretty dandy with these sorts but then if we go and run this and we look for a different number for the gap right we’re going to supply a different gap we’re going to go from 9 here to 0 here we’re going to keep post type id at 1 though and if we run this the row mode there’s going to be the compat level 140 is still going to be pretty quick but we’re going to have a real problem with compat level 150 you can see that this thing is kind of still over well that that executed it for a little while there right that gave us about 10 seconds total of execution time if we look at the query plans now this is going to be the row mode plan this is going to be the one that executed in compat level 140 and if we look at the sort it’s going to spill a little bit right now knowing what we know about row mode plans and knowing what we know about reading execution plans with these times in them this operator went for this operator ran for about 256 milliseconds and the next one ran for 1.253 milliseconds but it’s a little bit under a second because 253 there’s the 1.253 minus .256 is going to bring us to about a second because remember in row mode plans operator times are cumulative cumulative right so this is actually just running for about a second and the spill isn’t that bad spill level 2 one thread right about a little bit less than 10,000 pages ended up on disk so I’m totally okay with this this did actually pretty good considering this sort is going to continue to be in row mode because we were looking at it in compat level 140 compat level 140 doesn’t allow batch mode for rowstore compat level 150 does at least if you’re nice enough to pay for enterprise edition or smart enough to just use developer edition instead don’t tell the licensing police I said that but you know all the smart kids are doing that so now we have this section of the plan which is pretty okay but looking down here this is where things all of a sudden got bad boom boom boom boom boom boom boom boom boom boom wow I got spam thanks mr.
gamer 2018 let me update your nickname nerd what’s wrong with you jeez so this is since these two operators run in batch mode all right that’s a batch and even though the storage is row stored since this is compat level 150 we’re able to buy or we’re able to buy we’re able to batch run this in batch mode we’re able to run this in buy mode because we bought enterprise edition apparently we bought followers primes and views because we’re famous we want to be famous I wish I could get famous apparently mr.
gamer left made fun of his nickname too much but now this sort since these two operators are in batch mode right we can see a batch mode here and we can see a batch mode tooltip you weren’t working with me there we can see a batch mode here since these two things run in batch mode this is interesting right since these two things run in batch mode the times are no longer cumulative the times are per operator so this sort really did run for nine almost let’s just call it 9.3 seconds this index seek was very fast but this sort was very slow now there’s a funny quirk with sorts in batch mode it doesn’t apply here it applies to parallel sorts in batch mode where the output from them is single threaded the batch can run multi threaded but the output from a batch mode sort is single threaded unless they’re the child operator of a window aggregate we don’t have one of those here we also have a serial plan here so it doesn’t matter everything’s on one thread anyway but this single threaded batch mode operator well it’s kind of funny isn’t that kind of funny spill level 8 and it only wrote 5142 pages to disk so if we go look at the memory for these two queries this one here got about a meg of memory that’s 1024 kb so we got about a meg of memory here and with that one meg of memory we still had to spill out a little bit but we spilled out about close to 10,000 pages but this happened pretty quickly this happened in about a second the batch mode plan gets just about five and a half well let’s just call it five and a half megs of memory that’s close to 5.4 and 5.4 is pretty close to 5.5 so we’ll just stick with this so we get about a 5.5 meg memory grant here we spill out but man this operator runs for nine seconds nine seconds and it spills about half as many pages now what a lot of people will do when they start trying to tune queries is they might care very much about wait stats newer versions of SQL server have wait stats and query plans which can sometimes be helpful you can sometimes find things in there for the query that runs quickly though well we have about 260 milliseconds of IO completion right that’s fine for a query that ran for a second we don’t know what we did for the other second but we know that we had 260 milliseconds of IO completion that’s the only weight that’s stored in this query plan for the query that runs in batch mode this gets even more curious if go to the properties over here and we look at weight stats well we only have 24 milliseconds of one weight reserved memory allocation ext this is not a terribly helpful weight see one of the real dark sides of some of the things that Microsoft adds is that they decide to filter things out for you they decide what you see and what you don’t see in some of these additions to help you troubleshoot problems the thing is having that knowing that this query waited 25 milliseconds on reserved memory allocation ext is not going to help us figure out what’s wrong with this query but neither would looking at what we actually waited on so let’s look at weight stats using my store procedure sp thunderous underscore look at that look at this thunderous underscore that thing that’ll buckle the these human events and we’ll use it to look at weight stats at least as I’m slowly learning the answer to every SQL question is it depends yes but the important thing for every SQL question is knowing what it depends on because if you know what it depends on then you can solve the problem yes the answer to most things it depends but the secret is knowing what it depends on knowing those dependencies is where one gains expertise depends on what you got it you got it that’s the bumper sticker isn’t it so we’re going to use sp underscore human events we’re going to look at weight stats for this one session right so we’re going to focus this one session and we’re going to get some information out of this now the thing with these is that if we look at the weight stats for this there’s going to be just nothing in there right we didn’t generate a single thing that made us get a weight even for like the other plan in 150 we don’t have really anything of interest in here right there’s nothing about weight stats in here blah blah blah blah blah blah not fun not fun at all doesn’t really help us so let’s use sp queries to finish again all right so that first one finishes quickly we’re probably not going to see much for interesting weights there and this other one is going to execute and this one’s actually going to take a little bit longer now right that was like 9 point something seconds before it’s at 10.1 seconds now so this sort actually did a little bit more work on this one actually no it did about the same it just took longer I hate you so using sp human events we get information about query weights at three different levels right and this is because I do a whole lot of work in my store procedure to give you this data at three different levels for the entire time that it ran we had for the total weights we had 999 weights on this mysterious sleep task weight and then at the database level well the only database that was active because this is just my personal computer this is not Stack Overflow production database this is just my personal laptop so there was only one database active that was Stack Overflow 2013 but that will report that we had the 999 weights and we waited 8.2 seconds on them now the other thing that I try to break down with human events is to give you weights by query and database so we can look at things overall by database then by query and database and we get of course some information here oops I did not hit the right button so we get the query text and the query plan of the queries that generated the weights we can see there what happened to it now since this is two statements in one store procedure we unfortunately get the query plan for the whole store procedure I’m working on something to make this better but I don’t quite have it yet so I’m working on something to focus this in it’s almost there but it needs a little bit more work so we at least see the query that caused the wait since this is a plan that comes from the plan cache we don’t get the actual plan if extended events were better if extended events were a tool that Microsoft cared about us using and using happily we would be able to chain things together we would be able to say hey extended events I want you to fire off this event if this other condition meets whatever I want so let’s say that for us we cared dearly dearly near and dear to our hearts we cared well I mean I care about learning French from error messages but let’s say that we cared nearly and dearly about queries that were waiting on sleep task weights what I would like to be able to tell extended events is hey if you find a query that waits on sleep task go grab the actual execution plan for it we can’t do that together we have not extended events that far into the future we cannot chain events together we cannot chain sequences of events together and that’s a pretty big gaping hole in extended events I’m not saying profiler is any better at you can go get that magically from profiler I’m just saying if Microsoft really wanted extended events to be helpful and usable they might want to invest some time in getting people to actually use it by making it more useful I don’t know just me I’m not angling for a job as the PM of extended events or anything that would be a nightmare because it’s all XML and I’ve seen it and it’s ugly but this is one of those things where if you build it they will come Microsoft built a really crappy it was not a field of dreams it was a field of not quite maybe it is a field of nightmares because of the amount of XML so I wish I could chain things together to get something different but unfortunately if I was going to do this and get wait stats and query plans I would have to collect actual plans all the time and that wouldn’t be a lot of fun because then I’d be collecting wait stats and actual execution plans rather than being able to chain things together and be able to only get actual plans after some other extended event condition got past the filter so I have the estimated plan for the query that this sort ran for a long time but you know this is probably a pretty good lesson in and of itself how can you track just one sort you can read the documentation because it is in there there is an object name filter in there you can track just one procedure that doesn’t apply to every single one because not every single one gives you the ability to track just one procedure you can only do that if you’re tracking queries for wait stats I think you can do something I forget exactly what I wrote the documentation so I wouldn’t have to remember all this stuff but if we look at the estimated plans and this is sort of a good lesson about estimated plans general cash plans in general if I told you I had a query running for 10 seconds it would be very very difficult to ascertain if each query ran for 5 seconds or a second and another query runs for like 10 seconds you can go to my website it’s a good place to start it’s all there so if you look at these two estimated plans estimated plans lie to you estimated plans hide a lot of things estimated plans hide a lot of things because they are only estimates this is what goes in the plan cash this is what goes in the query store this is I am collecting an actual post execution plan I cannot get the level of detail that you are after if you look at a few small differences here if we look at this sort in the estimated plan we have estimates for everything we have estimates for all of these things estimated execution mode operator cost IO cost you can read all those things if we go back and look at the actual plan for it oh I have two versions of that open we don’t need two we just need one if I go back and look at the actual plan for this we get actual values we get what the query encountered when it executed for a bunch of things right we get actuals for this we get actuals for this we get well this we get the actual execution mode we get actuals for many things one set of values in here that we don’t get actuals for are costs see all those costs there’s no actual cost that gets updated at the end there’s no actual cost addition to operators to query plans where SQL server says oh I was totally off about how long this would take I was totally off about these costs my bad I’ll go fix that we don’t get that kind of honesty from SQL server all we get is SQL server saying well I estimated that if I was wrong I was wrong my bad my bad I was merely speculating but what’s important here is that when you run into a situation either where SQL server was wrong or where you have been parameter sniffed you end up with stuff like we know that this sort ran for 10 seconds but the cost is merely 1% if we were looking at this query and saying geez costs are super important let’s try to figure out where SQL server spent all the time we would look at this completely innocent index seek and say wow you are half the cost how do I make an index seek faster bad idea don’t look at costs they are lies they are lies because costs are not about your server costing is a general algorithm that has no idea about your hardware how awesome your disks are the great gobs of memory you have any of that stuff costs have nothing to do with you costing is a general algorithm that has to apply well to everybody regardless of how good or bad their hardware is it just so happens that SQL server is general across a wide variety of hardware but they are still not specific to you that’s why there is no actual costs in an execution plan SQL server doesn’t go back and correct those costs nor does it attempt to cache plans with those costs we can see that the cost for all this stuff 86% in an index dear lord we need to make that seek faster what a terrible time what a terrible thing that we have to do what a terrible thing that we are tasked with and look at the actual plan how long did this thing that cost 85% run for 0.001 milliseconds how much did this thing that cost 55% run for 0.002 milliseconds how long did this thing that cost 2% run for 1.5 seconds it gets worse down here where this thing that cost 1% runs for 9.9 seconds SQL server SQL server I wonder if all of the data in Azure if they’ll be using machine learning to correct cost estimates no because the cost estimates still have to work across a wide variety of Azure machines too I mean Azure is not one size fits all in Azure you can get a server with less than one core I think you get a hyper threaded thread in that case but you can get an Azure server with less than one core and the costing would still have to respect that you would only be able to get a serial plan for that because SQL server will say we have half a core probably round up and say we have one core probably not because even if they did that for the current gen of Azure machines think about in five years or 10 years or even in one year what different Azure machines we would have what kind of hardware might be behind them you know you start adding in like all sorts of like weird cool new features and you start adding in stuff like persistent memory and all of a sudden what do we get much much more difficult to figure out what something would cost and all that coyote McD says why do the percentages add up to more than 100% in that particular plan because SSMS is broken because costing is broken everything is broken the world trembles beneath us and we have no idea what holds it up we have no idea so yeah we have we have this thing we have this thing and we’re not really sure what’s going on but what I want to show you here is this is happening in batch mode and this is going poorly in batch mode so in the interest of full disclosure SQL server 2019 has this lovely mechanism for giving queries feedback about memory grants between executions if we run this a second time they’ll both be fast right so SQL server has adjusted the memory here we have gotten more memory on this execution and this sort no longer spills and we no longer have a big spill here the problem becomes really if we run this query a few more times then memory will eventually adjust back down not for that one but for this one the memory grant on this one is back down to 2.3 megs now and if we run this query it’s going to start spilling again because the memory grant will have adjusted down to compensate for needing less memory and this will run for I don’t 10 seconds again 8 9 there goes 10 seconds and look what we got back to spillsville and back to a bad memory grant for this thing super cyber says would century one plant explorer report correct percent values compared to SSMS I know they do some correction to it let’s look see what happens 0.3 1.3 so yeah it looks like the costs are different in these so that’s let’s go let’s see here this is the first statement in there this is 0.3 1.3 60 38.4 and if we go back to SSMS they got 0 to 85 55 so yeah planet explorer does report correct percentages how would force parameterization affect this affect what exactly everything is parameterized this is parameterized this is parameterized I don’t know what you would expect force parameterization to affect we have force parameterization by actually parameterizing things we have nothing that is not parameterized so we have got that so the problem with memory grant feedback is that it can be a bit schizophrenic if you have queries that really do vary back and forth constantly then if we look at this we can go in the execution plan we can go to the properties and we can see come on tooltip don’t go over where I’m trying to look you can look at the memory grant info and we can see oh where is it oh you’re not hiding there where are you hiding why are you not in there am I losing my mind am I losing my mind no I think I’m losing my mind I think I might be oh no because that’s the that’s why that’s the that’s the that’s the 2017 plan if we look at memory grant info for the 2019 plan I knew I was off by something we have this info and we have this information here about memory grant feedback adjusting going back and forth Lee Brownhill says I’ve stopped using plan explorer unless it’s a monster plan I’m looking at I don’t know where so many items are within PE so you’re I think you’re right plan explorer is not good at showing some things but plan explorer is absolutely masterful in showing you the query plans for long store procedures just because we have a store procedure with two statements in it it’s very difficult to navigate statements within a big store procedure using SSMS but with plan explorer you can’t beat this if SSMS had this I think people would stop using plan explorer completely it’s just it’s a magnificent feature it’s a magnificent feature for that but you know for so like the other thing is that we brought an actual execution plan into plan explorer right we have the duration we have the CPU but we don’t have the per operator times in here like we have an SSMS right it’s just not in there now if now we can get it if we go and get an actual plan come on dummy okay fine whatever it’s not going to let me do it but if we went and got an actual plan from plan explorer then it would show us operator times but right now we don’t see the operator times here we can get it if we measure it with plan explorer but if we have an execution plan like this one that has operator times in it for us then that doesn’t import into can’t you add that no you can’t add that no right click and copy yeah I’m not I’m not dealing with it right now I don’t feel like dealing with it so let’s get back to the query at hand here let’s figure out what could we say about this query what could we say about this that would help people trying to look at issues with moving to SQL server 2019 maybe they’re seeing some weird query regressions maybe things are just not going so well for them well we could generally say that we have to beware of regressions when going from row mode to batch mode if we backtrack a little bit for the people who showed up late ungrateful rude people who showed up late when this query executes in row mode everything kind of goes okay for it right maybe not like perfect right but pretty okay this sort operates in row mode runs for about a second it spills a little but you know like not like like I’m not one of those people who you know like fixates on every single spill in an execution plan you know sometimes spills are just going to happen they’re not always the gigantic performance degradation that people worry about but this spill is this batch mode spill ends up being far far worse than if we have the spill happen in row mode and what’s I mean so like just to kind of go back and like you know make sure that everyone understands the row mode spill spills about 10,000 pages and runs for about a second the batch mode spill spills half as many pages let me get that tooltip focused in correctly just finished was it a pizza because I saw that pizza saw that pizza and that pizza looked good the batch mode spill runs for like 10 like 9 seconds here goes to spill level 8 which which means that we had to read data from the spill 8 times but it spilled half as many pages so we can’t even necessarily say you might see bigger spills in SQL server 2019 and that might cause a problem you could say that smaller spills in SQL server 2019 if they happen in batch mode could be a problem but how could you reasonably ask someone to measure that you could say that batch mode sorts are something you have to be careful of but I think a lot of what I would fish pizza good lord monster but I think a lot of what I would maybe go and warn people about with batch mode sorts would be stuff like they output data in a single threaded even if they run in parallel tempdb activity increase so the spill was smaller right like I’m not sure what activity you would measure to get it to see an increase right we have a smaller spill here so we might even see less of it it’s just it’s curious because like how do you tell people what to do what to look for what to deal with you might be able to tell them that they you know if they’re seeing a big uptick in sleep task weights that they could have something on their hands but you know the problem here is also that sleep task is not just for spills at all it’s not just for that it’s quite strange it’s quite strange so Lisa says I didn’t know fish pizza was a thing I wish I didn’t so I would say the one thing the one place I would be okay with fish pizza one of the best things I’ve ever had was Indian pizza it was just like a big piece of naan with basically just piled with Indian food on it and one of them there was like a tandoori fish one and it was excellent probably excellent because there was no cheese involved I don’t know if you ever watched a cooking show once you involve cheese and fish you’re in trouble cheese and fish should not be on a plate together that’s not a kosher thing that’s just like a human thing like just please do not have cheese and fish cohabitate you would have to be such a magnificent chef to make that work but one of the best things I ever had was a tandoori fish pizza knocked my socks off I lost my mind over it it was fantastic I forget what else was on it but holy cow that was good that was good so like you could like say SQL Server 2019 you could like ask for an uptick you could like say well if you see like some queries slowing down and you see like an uptick in sleep task weights maybe that but oh but man that’s a tough thing to measure and like I was saying sleep task weights don’t only account for sort spills they can also account for hash spills they can also account for anything that the people at SQL Server are too lazy to put in a definite compartment sleep task is just like saying it’s almost worse than the miscellaneous weight it’s almost worse than that so what could we tell people to do here what could we tell people to beware of what could we tell people that would help them fix this because what we have is a sort that SQL Server is using to optimize this nested loops join he says I see a lot of sleep task weights on Azure when restoring databases well it’s probably just a sign that your databases are really boring sorry to say you need more exciting data you are putting your computer to sleep spice things up a little bit get something interesting in there stop having dull data so this is a known thing this is not a new thing so if you look at SQL server let’s look at the fellow up by name Craig Friedman optimize IO nested loops is it is it Paul White Paul how did you steal Craig’s blog yes emerald that stuff emerald your data Lee Brownhill says I’m guessing as well as copying the replicas and yes yes yes production DBA activities are very boring they are very boring now where is this darn blog post why are you hiding from me Craig why are you hiding from me let’s let me let me go look over here because I know we have it let’s just go right to the root of Craig’s blog because then we can find it then we can find it very easily so SQL server has a whole bunch of things built in to the optimizer that can help it they can help help it like optimize certain activities one activity that is a frequently used optimization is putting data into order oftentimes if we don’t have an index that puts data in the right order or we just use a different index than the index that has data in the order we would want it in we can end up with SQL server saying you know I’m going to sort this I’m going to sort this for you we’re going to get this all sorted out for you so SQL server has a number of things that it can do and I’ll stick these links into chat so everyone has them operating operating optimizing I by sorting part one part two it’s a two parter it’s that exciting I wish Craig Friedman would come back he works on all sorts of weird no SQL stuff these days but what Craig talks about in these blog posts is things that are built into SQL and these blog posts are not new these are not spring chickens but these are still things that happen and exist inside SQL server that can contribute to anything that you see in an execution plan today this is SQL server 2019 that I’m running these demos on you still see the same stuff happening you still see SQL server optimizer costing things doing things the exact same way crazy today I had a transaction log corrupted sorted out but with heartache yes that would give me heartache too that would give me a lot of indigestion I I hate stuff like that that is not the type of problem I like solving I do not like that because they are heartache problems they are truly heartache problems they are not problems that often have a happy ending to them right it’s like putting down a dog there is no happy ending when it comes to that it’s terrible but these two blog posts very good actually the entirety of Craig’s blog is pretty awesome I would suggest reading it again even though it’s not the newest material in the world it is all still relevant it is all still absolutely relevant everything he talks about in here is stuff that we don’t need this anymore so we see SQL server sorting data putting things in the right order to make this nested loops go faster and if we look at what we’re sorting so we can see what SQL server is doing Craig is still at Microsoft he’s just working on no SQL stuff now Craig is just working on other things now just not working on SQL server stuff apparently that I know of at least he stopped writing and blocking about SQL server so I assume he went on to do other stuff I know traitor what can you say though maybe he did the right thing maybe he got out at the right time maybe he got out at the right time maybe he got out just when he should have maybe he said SQL server is a mistake I need to go work on something else I wouldn’t blame him I wouldn’t blame him SQL server is a tough one so we have sort of an interesting thing here where let’s say that we had written this query in a very specific way because it solved a very specific problem in SQL server prior to 2019 right we have for a small amount of data this runs very quickly now let’s let’s do this let’s do this backwards let’s run this for a of data first we end up with a parallel plan for both of these right and if we look at the properties of this we look at the number of rows we can see that we have some spread maybe not the greatest most equal evenly balanced spread in the world but that is going to be different if we look at this SQL server is solid old 40 year old technology that yes built on the legacy of Sybase built on the legacy of Sybase did this end up so yeah so this is where things get a little bit interesting if we think back let me actually backtrack a little bit so that I can make sure everyone is on the same page when we run this for a small amount of data first right this second execution plan that has the sort in batch mode and has the seek in batch mode right these both occur in batch mode right even like batch mode for row store that whole thing so for some reason for a very small amount of data SQL server is like throw the batch mode at it if we recompile this and we say hey let’s do this for a big amount of data SQL server is like batch mode not so much not so much the seek is still in batch mode but SQL server is like I don’t want to batch mode sort there right we’re not going to see batch mode at all here because we’re having this query up here is executing in 2017 compatibility mode so when this goes parallel SQL server is all of a sudden like I know it’s not going to be good I don’t want to do that it’s not my jam has the thread count spread improved well let’s go look not really it’s about the same you see the threads end up on different rows so Lee you should know this from yesterday with because you have Joe’s post on how rows are assigned to threads via hash algorithm so we’re getting the same rows and they’re going to end up hashing out so the spread isn’t going to really improve here it’s going to be a little bit different what if it’s already in batch mode we already have batch mode on row store we don’t necessarily need it’s trick with a fake column store index or Nico’s trick with a temp table that has a column store index on it that’s empty we don’t really need either one of those we get batch mode on row store here batch mode sorts have very specific issues where like I said earlier I’m not sure if you caught it or not but batch mode sorts output data single threaded from whatever data comes in so that can cause problems in a parallel plan right so like unless they’re the child operator of a window aggregate then they can output data on parallel threads but otherwise they’re kind of stuck outputting data on a single that’s no good the Joe posts are a weekend reading they’re pretty heavy for my little head yes Joe Joe’s head is like he is megamind Joe’s head is fantastically large it’s got all that brain in it but now what’s interesting here is if we can run this multiple times and this will end up being pretty fast right just without just avoiding that batch mode sort and if we run this for the for the small amount of data this will be reliably fast too so one wonders a little bit with SQL server 2019 is if they start seeing those sort of batch mode sorts like well would you want to force a parallel plan would forcing a parallel plan cause SQL server to change its mind about those batch mode sorts like what could we really tell what is a good call to action for all this what will we tell people to look for what will we tell people that we really need to get ahead of here and it and a tough question because it’s a tough problem because if we happen to run these plans and sniff them for a large amount of data we don’t run into the same problems that we do when they run and we sniff them for a small amount of data I’m not saying this is always going to be the case of course there are times when a big plan would be terrible would cx packet weights change dramatically I’m not sure what you mean because the serial plans won’t have any cx packet weights and the parallel plans are fast and we’re probably not all that consumed with cx packet weights when parallel queries are running quickly if you look at the weight stats over here figure out which cx packet was we’re not going to see cx consumer of course but we on the second one I would be surprised if it was much different because they both finish in a pretty close amount of time cx packet okay yeah a little bit more then a little bit more but probably not enough that I’m terribly concerned about it right because it’s a hundred millisecond difference between one and two if I mean sure absolutely if you know you have let’s see let’s see if we can let’s see if it’s still so here’s an interesting one too is the sort for the fully row mode plan will never adjust because we’re in compat level 140 but the sort for the compat level 150 plan where we end up with the some batch mode operators that will that will adjust the memory grant over here and not like holy cow we really beat the pants off it but we do get rid of this we do alleviate the sort there and we do have just about the same CX packet weights across both of them now so 361 there should be just about the same here too 369 so CX packet weights aren’t going to change dramatically I’ve gotten away from looking at weight stats for the most part on servers they can be helpful at your bottlenecks but when tuning a single query I I’ve never found weight stats terribly helpful and today’s a pretty good example of that like when we looked at weight stats specifically for that query when it spilled we got 8.2 seconds of sleep task and what the hell can you tell someone to do about sleep task weights what can you really tell people to do it’s not a lot there’s not a lot that you can tell people like it’s actionable on sleep tasks like watch out for spills people are already watching out for spills people already have their eyes peeled for spills that’s one of those things that people focus on why did this spill send pages so yeah so eliminate the sort and bam the problem is gone the problem is that if we eliminate the sort here alright so we’re ordering by score descending here if we eliminate the sort here how do we only get the top 500 rows into the app if we take the top out how many rows do we get back and do we want to send all of those rows to the app and then have SQL server sort that so let’s go let’s actually just experiment right why not let’s take the top out of these so we’re no longer going to get the top 500 here we’re no longer going to ask for any ordering here we’re just going to say SQL server go off do your thing return all the rows how do we get only like the top 500 rows in the order we want into the application if we don’t do it in SQL server this returns not too many rows for the first one but we 4,000 rows this returns 4,000 rows but now if we do this for a big chunk of gap we’re going to go from 4,000 rows to a whole lot more rows what if we added a range 1 to 500 filter range based on what but we could generate a row number but then we would have to generate a row number over the entire set and we would have to generate that row number based on some ordering element and that ordering element would have a sort in it if you want the behavior of top your options are to use top or to use offset fetch which are pretty much commensurate within SQL server or your option is to generate a row number and only get that only include rows where the filter on that row number matches what we want to send back but if you want that row number to be ordered in a meaningful way so that we actually get the top 500 rows based on score we need to order by score on the row number which means we have to sort score to get the row number in the right order that doesn’t help us either we still I mean we’re not doing any better here right this one finished and then this one here is still going oh wait no it finished so this took a minute and a half this this returned 1.7 million rows this put this returned 1.7 million rows sure we’re no longer putting data in order but we are running for a pretty long time and we have now shoveled 1.7 million rows into the application and we have now we’re going to ask the application to just cut down on 4 so just to show you what I mean we no longer have the order by here but let’s say we wanted to get things we wanted to generate like the row number over score anyway and we say row number over order by p dot score descending as end now we can’t use this in the where clause directly so we would have to make two changes to this query we would have to not only add the row number here but we would then have to either select we have to turn this into a derived table right and say select star from and do this as x where oops steve fix this thing oh why did you do all that you are crazy it is x where x dot n let’s just do just to have it done between on and 500 so we would have to use as a CTE CTE are garbage stop relying on CTE would it be possible to have an index on score to prevent the sorting yes it would be possible to have an index on score to prevent the sorting but that might mess up other stuff and we do have an index currently on the table it does have score in the include so it’s not in order why is CTE garbage because they don’t do anything useful they don’t fence off queries they don’t materialize data they’re just useless they’re just like having a view or a drive table or anything else they’re not good they don’t help you do anything better so let’s also do select star from this as x where x dot n between 1 and 500 so yes we could change the index to have score in order but then we’d have to disrupt the key columns of the index and if other queries use this index if you know just think of all of the pain that can come from changing key column order in an index because remember key column order matters included column you can have in whatever order you want but if you have key columns set up in a specific way there is a column to column dependency from owner user ID to the diff to post type ID if we put score here or if we put score here or if we put score at the very beginning we would disrupt queries being able to go across that’s a lot of records for a temp table maybe yeah 1.7 could be a lot for a temp table it could also be a lot to stick into an application server because those things are always just murder boxes anyway so we could think about changing the index or adding a different index but we would have to be sure that if we were going to disrupt the order of columns in the key of the index that it was for a very very very very good reason because who knows what crazy legacy application stuff needs the index in this order we could also add a new index that maybe helps things out but then we would have to be sure that new index wouldn’t cause any regressions across other queries and also that new index would actually get used by our query now I haven’t gone down that path of adding a different index and seeing if it gets used I am willing to do that here but let’s just see what happens when we run this with a row number first Zane says it’s more of a created so Zane you’re almost right it’s not an abstraction it’s a distraction it is a complete distraction and you know what we’ll talk about why CTE are silly too what about a column store index what about a column store index tell me what about one you’re going to throw the kitchen sink at me we’re going to have to ask why so when we generate a row number over p.
score we also end up sorting for it here right so this will not help us tremendously I would wager I would wager this would not help us tremendously because we’re still going to have that big old sort now Kelly if you’re suggesting a column store index in order to get batch mode we’re on SQL server 2019 and we already get batch mode for row store which you’ve talked about a little bit here much or maybe you got distracted by CTE and walked away from the webcast for a little bit but we already have batch mode going on in here right problems and he said Zane’s been drinking the Kool-Aid yes Zane loves Kool-Aid I hear but since we had a question about it let’s look at why CTE are stupid of course I misspelled stupid right let’s just say we select top one from users old style top we want to have the new style top in here and since Andy is here we need to take sort very seriously the sort is now batch you have been if you have been paying attention you would have seen that right the entire time the sort has been batch mode the entire time we’ve been talking about it the sort only wasn’t in batch mode when it was parallel the sort was batch mode the entire rest of the time we need to work on your concentration skills so let’s select the top one u.id and let’s capitalize things properly so that our friends in the case sensitive server department do not get angry and let’s just say where id equals 22656 cool I’ve forgotten s there there we go now we’re all sorted out but you didn’t capitalize properly either SQL prompt is broken today let’s see I’m recognizing the delegate for the new style top delegation yes those new style tops hopefully someday hopefully someday I’ll be able to make it so with just running the query inside of the CTE we have one seek to the users table right and if we look at the results that we get back from there we will just have this one column called ID now if we say select star from CTE are stupid and let’s say as C1 we will still get the same execution plan we still have one seek and two users but now let’s go and join CTE are stupid as CTE on that ID column and you know what we don’t even need to get things from other places C1 dot star right and we look at this and we say CTE are stupid and now we look at the execution plan we have two seeks into the users table we no longer have just one and if we go ahead and say join CTE are stupid as C3 on and I don’t care what we do here should I do it on C3 dot ID equals C2 dot ID or C3 dot ID equals C1 dot ID I’m fine doing either one you tell me which whoever answers first I’m going to do what you say we need an Eric blood pressure gauge widget on twitch you know this is cathartic for me C3 equals C1 okay C3 equals C1 here C3 dot ID equals C1 dot ID and if we now run this because CTE are stupid we are now going to have three seeks into the users table CTE are not fun they are not good for you if I add another one just just just because I want you to see it if we join this as C4 all right we go the extra step out of the mile here on let’s just go back to C1 dot ID equals C uh for dot ID this will get a fourth seek into users so generally re-referencing CTE re-referencing CTE will not is not your friend man I need my dev team to watch this desperately good news good news Camaro I do developer training if you would like your developers to learn this and be able to ask questions then boy oh boy we can certainly do that so a CTE would not help us much more here and just to go and you know I’m going to leave this but leave this as is this is going to be the exact same thing as before so if we run this remember remember remember carefully this execution plan this sort was always in batch mode the only time this sort comes out of batch mode is when SQL server says oh you know what oh you know what I would like to run this in parallel and when it runs in parallel well that’s when things things get interesting now what kind of sucks about this is that we don’t get a window aggregate function here I was I was half worried that we would get a window aggregate function but we don’t screw you SQL server 2019 you are not my friend you are not my friend anyway so what could we tell people to do here like what would be what would be what would be the takeaway like we still have this query to figure out what to do like I don’t know the same thing happens if you use table joins I don’t know what that means alternative no sub queries have to execute all that syntax too if you want a stable result set use a temp table use a real table that’s it all you have to worry about so just remember that the query inside the CTE is not materialized anywhere the results aren’t materialized the expressions aren’t materialized anytime you re-reference that CTE you need to re-execute the query inside of that and that means you can do a whole lot of extra work so something like rejoining the CTE to itself would also mess things up tremendously but if you repeat a sub query then repeating a sub query will also re-execute the syntax doesn’t really get you anything it doesn’t really get you anything it’s unfortunate it’s quite unfortunate sounds like a connect item I guess thing is if you if you were to materialize a CTE in any meaningful way then you would need to account for what happens where that data gets materialized do you put an attempt DB do you have a local store for things like that per database how do you manage concurrency there how do you manage rollbacks there how do you manage space the inevitable concurrency issues that come from a whole bunch of queries now trying to use space Zane brings up a good point we don’t know what to do there we don’t know what’s right or wrong there so it would be up to users to or it would be up to Microsoft to give us a hint like option materialize CTE or whatever and it would be up to us to add that and use it there which still doesn’t help people who are on third party vendor apps where they can’t change the code and you know that hint would probably only be on you know the next of SQL server so it might not help people going back all that far and you know it’s just sort of like you add it but at the same time if you’re going to add a hint to materialize a CTE why not just add a temp table yeah exactly so like if you started doing that automatically if you started automatically materializing CTE in temp DB you would be in trouble the only thing I could think of that might make that tolerable is if you used something related to accelerated database recovery where you used a local persistent version store to materialize CTE instead it’s the only thing I could think of that would be neutral ground that would help that out that would have any user craze ifying problems with it you would have to have the database setting you would have to have probably not a server level setting would be dangerous but you have to have the query hint the database scope configuration probably a trace flag then a whole bunch of stuff to turn it off to disable it and like it’s a lot of work it’s a lot of work when people like to add all those hints and settings and everything when really if you just use a temp table you would probably get the equivalent experience of whatever Microsoft would do to materialize a CTE I get that people really want this magic thing but Microsoft doesn’t have a good record of applying any Disney magic to new things so you would probably just get a new thing that is just standing on the shoulders of a bunch of old things there’s no way to have that pan out for free there’s no way for Microsoft to implement materialized CTE without without just like using a temp table behind us yes yes like if you want to see something very funny like people got all wound up about table variables right but if you ever look at a table variable right we don’t even need to put anything in it oh I forgot the word table though nuclear t table there we go well that didn’t go well this isn’t my SQL no back ticks there and then we say select star from t and let’s set statistics io on all right and we we look at this you know this is this is just going to have a temp table behind it anyway so like everything Microsoft does it people are like it’s magic it’s fixed it’s in memory we did it Microsoft solved all the problems it’s not it’s just everything is backed by a temp table everything is tempDB what no one understands is it tempDB all the way down tempDB is the turtles of Microsoft SQL Server hope someone from the tiger watches your channel I’m pretty sure they only watch my channel to print out new things to put on their dart boards yes yes the villain is tempDB all along if you use a temp table with three no no so here’s the difference what’s a good way what’s a better way to show it so let’s say that our query is a little bit more complex right CTE are stupid so with the trivial example I gave you yes it would not be a big deal but let’s say it was users ID where u.id equals 22656 and now let’s do join posts on p.
owner user ID equals u.id and now let’s join badges on b.
user ID equals u.id so now we have a little bit more going on in here you know the same thing will happen if we look at this and if we run from as c1 right if we do this the same basic thing will happen except now as we add references to the CTEin there equals c2 dot id now as we get things a little bit more complicated we start to really see the repetition in the query plan being crappy right and if we add a third one in right we’re going to see that branch come in again right so as c3 on c1 dot id equals oops equals c3 dot id right so now we have a third branch of that so what I mean when I say CTE are garbage is because people what does everyone say about a CTE it makes my code so much more readable what do they end up jamming inside a CTE that 5000 line monster nonsense query that has a filter on the outside based on the four most complicated calculations inside of the CTE and they think that they have performed some active magic performance wizardry by sticking this thing in this query that is the hottest garbage on the hottest day of the year buried 10 feet down on Venus is magically safe and wonderful because it’s in a CTE it’s readable understandable now because I said with before I wrote this query so people tend to put very complicated things inside of common table expressions and when you do that and you start repeating yourself where you touch things from the common table expressions where you touch the code inside it multiple times you start ending up with these repetitions in your query plans and so what I mean by use a temp table instead is if you just said oops and then we said here you would only have to execute that first branch once and then you would have well yes you would have to hit the tables to do the self join you don’t need to expand all of these joins over and over again so like that’s that’s really what I’m getting at because people jam the worst things in CTE and it’s like well it just fixes it so like for putting a single query in there not a big deal right but if you have big complex things in there and you end up needing to execute that big complex thing over and over again you’re in tough shape you are not you’re not in good shape so anyway anyway ah this further off than I thought it would kind of funny but I’m okay with that I’m okay with that so we have about ten minutes left I do want to thank everyone for coming in hanging out watching me kick this queer I am going to have a blog post about this yes they do create a testy Eric and what you know just while we’re here as a thank you for showing up I have two more dates for my I have two more dates for my online performance tuning class Friday July 10th and July 24th if you as a thank you for showing up there should be floating above my head a coupon code that will get you 75 bucks off the cost of the one day training if you want feel like buying a ticket you can go over there and if you sign up and buy a ticket then you get all of the videos on my video training it’s a good 24 25 hours of performance tuning videos that I have available up on my site you get all of those for free if you buy a ticket to the class you get those for life you do not have an expiration date thank you Gino was in the class that I had last week so he is a valid unpaid witness to the things that you will learn in the performance tuning class Eric puts a coupon code above his head so he can flex when he points to it I wish I had anything left to flex here’s the thing I have not been to a gym now since March 5th 7th I forget exactly when I have nothing left to flex there’s zero flex left in me I am really good at flexing my mouse click finger but that is about the end of it I have very good mouse click muscles I have nothing else nothing else I have nothing left to flex I’m going to have to work on that eventually I’m going to have to either move to a state where gyms are open or buy a house in a state where I can afford a house and put a gym in the basement or garage or that’s like my only choices yes I am in New York City so we still do not have gyms open he says agreed class and video sets are legit highly recommended yes thank you Zane and he says 8 ounce chateauneuf de pop curls all of the chateauneuf de pop that I own is currently in my mother’s basement getting ready to go to the summer retreat NYC epicenter yes NYC did not did not do so well yes did not brought to you by Canada Dry I wish if Canada Dry sponsored me I would be so happy they are one of my favorite seltzers and if they sponsored me they would be my absolute favorite seltzer right now it’s between them and polar they are the only seltzers that have strong enough bubbles that get on the tongue and make it hurt a little bit Texas and Florida not doing so hot now hopefully they get things figured out I want to see everyone going back to a happy and healthy world that’s what I’m after let’s see do you ever use one of those soda streams no there’s the so no so I’ve had friends who had soda streams and they did not have very good luck with them they found that the bubbles did not last very long and they could get things quite as bubbly as they wanted to I have very high expectations for bubbles I want strong I want aggressive bubbles I want little scrubber bubbles for my tongue I want get in there get in there so I’ve not heard a review of soda streams that seems to indicate that I could get the kind of bubbles I want out of a soda stream but if someone can point me to how you get the strongest possible fizz into a bottle you can just use standard CO2 and load it up I do that for tonic water Jack Rudy syrup and spray this wow it’s very carbonated is there a good time to put questions into chat during the demos I always feel I ruin the flow of the point you’re trying to make but the delay on the chat doesn’t help no just whenever if I’m in the middle of something that I really want to finish it before answering the question I’m totally fine with questions showing up whenever I like I like having things show up over my head I like having things show up over my head because it lets people know that people are here when they see people are here and active then they’re more prone to being here and active too and I like having things be here and active I want someone to run SP who is active and just see an ASCII image of me pop up here doing it because that’s what’s fun right being here doing stuff talking to people talking to people who I wouldn’t get to see every day anyway you know I’m very grateful to be able to do these to have sort of a setup that works and people who show up regularly to watch me do goofy things it’s nice I enjoy it I enjoy having a bit of an audience alright any other questions anything else you all want to talk about ask about feel inclined to know more about be happy to answer something be happy to answer something stream setup is great thank you Arthur hopefully it stays that way hopefully I don’t end up looking sad and outdated too soon I’m still adjusting to SQL people streaming seem to happen all at once well I mean it’s just sort of circumstances right what else is there to do if you don’t hop on board you you miss out you don’t stream what are you left with you kind of end up with the same people doing the same stuff some people might just blog occasionally some people might blog constantly this does get recorded too this all ends up on YouTube I still record stuff I’d rather go live and talk to people than just talk to a camera I spent 48 hours talking to a camera to get the first round of recorded stuff in and I felt insane at the end of it because it was just like three or four days straight of me recording things and just talking to a camera and while it’s nice to be able to stop and do over if you make a mistake or flip something up still just talking to yourself no no mentions of that so nothing of the sort nothing about hats we’re all grateful for a lack of hat talk all grateful to not have to address hats good times alright so it’s been like an hour and a half wait a minute if you have a condition like this and field one modulus number equals number service yeah that’s not good so it’s going to depend a little bit on where it’s happening so like let’s say like let’s say that you have a query like select count from users where let’s say u dot reputation modulus 11 equals zero run this I mean this is relatively fast because there’s not a lot of data in there and we get like a not great guess here but like what’s even oops not seeing SSMS nope ah there we go I knew it was there somewhere all right so let’s just change that let’s say modulus 2 equals zero count this comes back pretty quick and you know we make sort of a crappy guess here right we make it we were off by a bit in the guess we’re going to have to read everywhere on the table because right now we don’t have an index on reputation but let’s do something so we can figure out let’s do something a little bit different let’s say id equals one actually let’s do 22656 because I know that’s going to come back with something so here we get a very bad guess and we end up scanning the entire clustered index because that’s our only option but if we change this a little bit now we have an index seek because we’re seeking into the id column and the residual predicate on reputation just really doesn’t make a difference so it really depends on what indexes you have what other predicates you have and what else is going on in the query people make a really big deal out of sargability no you’re looking at my SSMS now so I verified that you’re looking at my SSMS because that is what Streamlabs tells me so it really depends on where the lack of sargability is happening so if we have an index or rather if we have a query like this and we’re able to filter we’re able to seek earlier on right we’re able to seek to where id equals zero then the predicate over here on that that sucks it’s not sargable on the reputation column doesn’t make as big a difference if we were to say something like let’s see what other tables are in the post table what else could be doing there select top 10 from users what other columns do we have in there that might be interesting let’s say and display name like well a right well actually let’s make sure that we have our case sets and case sensitivity worked out so now we don’t have an index that’s helpful right so we’re back to scanning this thing if we create some indexes right let’s create index whatever on users let’s do this one on reputation and then display name and then we’ll do one on the opposite direction afterwards oops I didn’t create that I just went right back to the query didn’t I right so now with the leading column being the crappy predicate right we have to scan that index right so that’s not so great there but if we change the order of the index columns now because we’re only doing a trailing wildcard sort on display name it’s not going to be it’s going to be okay ish but now we’re able to seek to the a’s that we care about and the residual predicate on reputation isn’t that big of a deal anymore right so we have a seek predicate down there and then the other one is just not that great so yeah Andy’s right as long as that’s not a variable as long as it’s a literal value then you could totally create a computed column to get around something like that it’s kind of weird logic to me though anyway like why does the modulus math of a number have to equal something in order for it to be to like qualify for it’s like a very weird set of logic like it’s a very odd set of logic but you know I’m going to try not to kink shame anyone here I don’t like kink shaming because I have so many issues but yeah the computed columns are a very good way to get around that and then if you don’t have a computed column data around that then stargability really matters most when it affects the leading column of an index if you have good predicates on other columns and they lead in the index then having the non stargable predicate on a key column that’s later in the index because you were able to row reduction first and it just makes less of a difference let’s see and he says we did exactly this because the app developers do it modules tend to split work into 10 work queues yay app developers they see squirrels everywhere mr.
P Shaw says if reputation modulus one was the only predicate could you get the right query better or is it just going to be bad so the only way you would have to alter table users oops add call add uh chuckles as uh reputation modulus one and you might want to do some art and just to make sure things turn out the way you want it you don’t have to persist it but you would have to index it just like any other column you would have to index it in order for things to turn out well so uh let’s ha ha ha I’m having a good time typing drop come on dummy drop indexes get rid of all the indexes so with this column added but not indexed we’re gonna when we you know we’re gonna have to compute the scalar at some point we’re gonna have to scan the clustered index to get in there right we’re still gonna have that crappy predicate on there but at least sql server will be smart enough to say hey if you add an index to that column we’ll be in better shape so now if we actually just screw it let’s just take the missing index request because in this case sql server is not wrong and it’s missing index request right we add this index on our computed column and display name sql server is able to index let’s see a couple questions here vendors table and code and we can’t alter the table then we’re just stuck yes yeah you yeah like unless the vendor is yeah but if the vendor is any kind of reasonable vendor then making that change is not a problem is the drop index proc available I’ve seen yes you can find it on Brent’s site but now if we have an index on chuckles and display name we are able to seek that predicate on chuckles without anything so computed columns can be very useful can almost be very useful in these cases but just like regular columns they don’t really reach their full potential until they are indexed so if you have computed columns that’s great but you know whatever anyway we’re going to call it here because I need to I’m going to start doing a dance soon and it’s not not not the good kind of dance so I’m going to call it a stream here thanks everyone for showing up I will probably be back tomorrow to do what I don’t know yet I’m going to make something up today but I will see you all back tomorrow thanks for joining remember if you want to join me for a full day of performance tuning stuff you can go to one of those URLs that I am pointing to and you can use that coupon code up there in order to get 75 bucks off so you get a full day of performance tuning training with me and then access to all 25 quite a deal quite a deal thanks and I’ll see you back tomorrow
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 nuances of query tuning and parameter sniffing issues in SQL Server, providing practical solutions for handling these challenges. Parameter sniffing can significantly impact performance when a stored procedure or query is executed multiple times with different parameters. To illustrate one approach to mitigating this issue, I demonstrate creating an index that helps in optimizing the execution plan based on the most common parameter values. This method involves writing a stored procedure that accepts a single parameter and uses it to filter data from the `votes` table in the Stack Overflow database. By counting records and formatting the result for readability, we can see how different parameter values influence the query’s performance and execution plan.
Full Transcript
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you. Thank you.
Thank you. Thank you, Varis. I’d like to thank you.
Bye-bye. Wild. Wild stuff. All right.
Everything looks like it’s working there. That’s exciting. Everything looks properly in place. Minimize that. And let’s close the browser.
Let’s minimize the browser so that we don’t have that distracting us from the important work. That we need to get into. Make sure that we’re in the right database for all this stuff.
Because how else can we live if we are not in the right database? We’ll have to be stuck. Hello, everyone.
Let me… Oh, what the heck. Let’s see. This should no longer… Slideshow.
This should be… Pretty static and in place. There we go. Now we’re looking good. Cool.
So how is everyone today? What are we all up to? What is today? Wednesday? It is. It is Wednesday.
Fantastic. Fantastic. That means… The week is nearly over. The week is nearly over.
We are almost… To Friday. Which means… There are only… Thursday.
Let’s see. Thursday. Friday. Saturday. Sunday. Only four more days of work until Monday. Sunday. That’s going to be fun.
All right. So… I don’t know. Do some small talk. We’ll wait for… Wait for some people to show up in here. Wait for some more eyeballs…
To jump on in. He says… I have the next days off. Not sure why I’m broadcast. I don’t know.
Like… Like… Like… Like… You have the next two days off. And then the weekend, right? Or… Or… Or do you have the next two days off because you have to work the weekend?
Because I’ve… I’ve definitely run into some… Some people who have had that problem. In their life. It’s like…
Yeah. My boss gave me… Just like… Like… Tuesday and Wednesday off. Like… Oh. Cool. Why? Because I have to work the weekend. I’m like… Oh. That sucks. You didn’t really get days off. You just switched days around.
Four days. You’re just making… Hey. Thanks for calling the chat boring. That’s nice of you. And then…
Also… Thank you for dragging down the boring chat with your boringness. Let everyone just pile on. Do we have…
Do we have any other insults for the chat? While… While… While… While Lee is… While Lee is busy. So I’m going to need a couple other people to step up and volunteer to hurl insults at me.
While Lee is off of work. And probably not watching me do anything. So hopefully…
Someone… In the greater SQL Server community will be able to step up. Fill those big shoes. And call… Call my chat names. Today I’ve got proper seltzer back.
I mean… By proper seltzer I mean it’s not flavored. So I feel much better about myself. Much better about myself. Do do do.
Alright. Since I think everyone understands what we’re going to be doing today. I’ll stick this one up behind me for now. Let’s see.
Oh that looks pretty. That looks pretty. So I’ve got over 70 people slated to show up on Friday. And I’m mildly terrified now.
At first I was just like… You know what? I should pack in as many people as I can. And now I’m like… Man I better not screw anything up. No pressure though right?
No pressure. No pressure. Just recalling about it’s gold. Yes. I wish more things that I owned were gold. So that I could sell them.
I could sell them. I could buy gym equipment. And then I would not have to wait until… Maybe phase four of New York’s reopening.
To go see a gym. That would be all that I did. Now I’m curious like…
Now I’m thinking like you know… It’s going to be a lot of people… Or there’s going to be a lot of gyms that are closing. I wonder if I could like buy some… Some like going out of business gym equipment. But then I’d just be very tempted to just to like…
Like just open my own gym. Get the hell out of this computer thing. Computers are terrible. Have you ever used one?
Computers are absolutely miserable. I hate computers. I want nothing to do with them anymore. They crash on me constantly.
They don’t listen to reason. Let’s see. Lee says… Yesterday I learned that CX packet is not to be ignored. I love SQL Server.
Every day I learn something new. So Lee… So Lee… Like why would you not… Like under what circumstances… Should you not ignore CX packet?
I look forward to hearing about this. Let’s see. If you bought a gym with gold… Would you make it a gold gym? No. And I tell you why. Tell you why.
There was a brief period of time… When we lived in Austin, Texas. And I got a membership… At the Gold’s Gym in Austin.
And I paid for a year up front… Because I had some special on it. But we ended up moving out of Austin… After six months.
So like three months in… We were like… I’m out. We’re leaving. Just can’t do it. It was too much of a weird culture shock. Not like politically or anything like that.
But like… Just having lived in New York forever… Not being able to get like… Like real Chinese food.
And like pizza being served on Wonder Bread. And it being hot. And there being bugs this big. There was just a lot of…
Of reasons why it didn’t work. But I was like… You know, Gold’s… I’m leaving for… We’re out of here. We’re leaving Texas. We’re not going to be there.
We’re not going to be here. You know, I’ve… Paid for a year. Is there any way I can get any of that back? And… They said… Where are you moving? I said…
We’re moving back to Brooklyn. And they said… Ah… Well… We can’t give you a refund. Because there’s a Gold’s Gym… Within 25 miles… Of where you’re moving to.
I said… 25 miles? Where is this Gold’s Gym? And they pointed… And like… There was like… Some Gold’s Gym map. Where there is a Gold’s Gym… Like…
Like… Way out on Long Island. There’s one closer in Brooklyn now. But… It wasn’t open then. And they were like… Ah… There’s one… Out on Long Island. I’m like… No…
Do you know… Do you know anything about New York? Is there like… Like… You have like the slightest clue… How things work here. Because no one… Is traveling… From Brooklyn…
Out to like… Wherever on Long Island… To go to a gym… And then come back. Like… It was just… Like insane. If there was one in the city… Or if there was like another… Like a gym somewhere in Brooklyn…
That would be a different story. But they’re like… Like… It was like… 15 miles away on Long Island. I’m like… Well… That’s not good. Gold’s Gym…
Are they like… No… The Gold’s Gym that I was going to… Was more expensive. I want to say it was… Like… 600 bucks for the year or something. Like… It wasn’t a big deal…
That I didn’t get the 300 bucks back. I was just… Like… I was mostly annoyed… That they were like… There’s a gym within 25 miles. I was like… Yeah… That’s going to happen. That’s going to happen.
Let’s see… Uh… You know… I could buy a 24-7 gym. The problem with a lot of 24-7 gyms… Is that… They don’t have… Uh…
Good barbell equipment. So I would have to… Buy the 24-7 gym… And then probably… Import my own barbell equipment. And he says… I used to think it was a safe weight type…
But I found out… That… Query with uneven thread work a load… All… Get even memory grants… Since your memory just sat there… Doing nothing… Wazier…
Yeah… Um… So… I wrote a post a while back… About… That…
Oh… It was over on Brent’s site though… Hang on a second… But it was… Not… I mean… Not exactly about CX Packet. So…
A while back… Microsoft decided… To… Um… Split… CX weights… Into… Two different categories… CX Packet…
And CX Consumer. And of course… Uh… CX Consumer is… Like the coordinator threads… Waiting on… Things coming on in…
And CX Packet is still… Queries going out there… And running and doing stuff. So… Uh… There are… Times when you will have… A… Query…
With… Uh… With skewed parallelism. And on older versions of SQL Server… You will… Uh… You will see that register as… Uh…
CX… High C… You can see that register as… High CX Packet. Uh… Newer versions of SQL Server… You will see that register as… High CX Consumer. So…
Uh… Well, that can be… A sign that… Uh… You know… Parallel queries are… Skewed… In some way… In their row distribution. Um…
Yeah… I mean… Like… It’s certainly something that you could… Uh… It’s certainly something that you could… Uh… Look at when you’re dealing with a parallel query. Um…
But I think in general… If you see high CX packet weights on a server… It’s not necessarily a sign that… Every single query is terribly skewed… All over the place. There is…
There is a… There’s a chance. There’s a chance. You could also just have high CX packet from queries going very, very parallel. There’s also that.
Uh… Let’s see. Let’s catch up a little bit here in chat before we get started. We’ll get started in a couple minutes here. Um… Let’s see.
Ah, yes. Congrats on getting a call-up from Itzikbengan. Yeah, that was… That was a crazy… That was a crazy thing to happen. I was…
I was… I was just as shocked to have that come out of nowhere. I was like, what the hell? That was crazy. All right.
So, uh… Let’s… Let’s get started and talk a little bit about some query tuning. Oh, you know… Just really quick in case anyone who is joining today who hasn’t joined the last couple days. Um…
You know… Uh… Doing a thing on Friday. All day. Advanced performance tuning. If you feel like joining. Uh… You can get a ticket. You can get a ticket for very cheap.
125 bucks. Includes access to all of my SQL Server video training. Which is… 24 something hours of it. And, you know…
Well worth it. Well worth it. He says… It’s something on my list to look into more, though. Or also… Let’s see…
Uh… No… You’re… You’re getting ahead of yourself a bit, Lee. I think you’re just saying words that you’ve heard now. All right.
Let’s get the heck out of this thing. Let’s go look at demos. Because that’s where all the good stuff happens, right? When we start doing demos. Everyone loves demos.
That’s where you learn everything. So let’s… Let’s clear out… Everything. Oops. I didn’t highlight everything. All right. We’re going to create some indexes. And while we create some indexes…
We’re going to look at… Um… So this… This… This store procedure started off as… A… A thing that I wanted to show is…
Uh… One way of dealing with a parameter sniffing issue. So like… You know… You… Have a… Have a store procedure or something else that accepts a parameter. And…
What happens next is parameter sniffing. Because at some point… Despite all of your best efforts to… Reasonably index for things so that you have… Selective columns and…
You know… You… You cover all your bases. Uh… Even… Even if you… Even like… Not every value in every… Every column is going to be…
Selective. And… And… You know… Part of the whole selectivity thing is that… Uh… You know… Values and columns being selected… Or…
Being selective rather… Does kind of depend on how people are searching them. So… You know… If you have a string column… And every string is… Semi-unique…
That’s fine if people are searching inequalities. But as soon as people start putting wildcards in… Selectivity… Kind of gets… Kind of gets…
Can get hurt. The same thing with dates too. Right? So if you have… You know… Date ranges… And someone searches for like the last week… That can be pretty selective. If someone searches for the past five years… That’s not as selective.
If you have… You know… Uh… Prices… And you want to search a range of prices… Things that cost between… You know… Ninety dollars and a hundred dollars… Might be fairly selective.
But things that cost between zero dollars and a thousand dollars… Might be… Not as selective. So… You know… Selectivity… Not only depends on… You know… Uh…
Like… How unique the data is at face value… But also… Kind of how people search that data. Right? So… There’s stuff to think about there. And… Um… Parameter sniffing can really happen…
From… Either situation. Or sometimes it takes a combination of parameters for it to show its face. Like… You need to have like… Like… Like… Two non-selective parameters or something. But this is a store procedure that…
You know… I… I wrote… And I want… And like… I wanted to show one way of handling… Issues with parameter sniffing. And… You know… When I teach about parameter sniffing… I…
I try to keep it as simple as possible parameter-wise. So… This just takes one single… This store procedure just takes one parameter… Uh… For vote types over in the… Over in the votes table in the Stack Overflow database. And just to kind of give you…
Uh… An idea of what that looks like. Hoop… From… From… That’s a good job, right? Never trust a professional presenter. That’s to vote type ID…
Count… We’ll do a big count. Because we are big counters. And we’re also going to nicely format… Our count.
Right? Because… The only thing… More annoying… Than big numbers… Is big numbers without commas. Because they’re hard to read. Especially if you’re as dumb as I am.
And we’ll call this… Records… To hopefully annoy some people. Let’s see. D-back says… Is parameter sniffing something new…
Or it’s been there for years… Like dating back… It has been… It has been a problem… For as long as there have been… Parameters in databases. It’s… Going back to forever. Basically.
Since like… The dawn of databases. It’s… It’s called some different things… In different databases. In Oracle… I know they call it… Parameter peaking.
Or… Like… Bind… Bind… Parameter… Bind peaking… Binding parameter peaking. Because Oracle just calls it… Something a little bit different. But… In SQL Server… It’s been parameter sniffing…
Or parameter sensitivity. Is probably the nicer way… Of saying it. But it’s been around… Just about forever. The term itself… I…
I don’t know. I don’t know… I don’t know the history… Of it that well. I… I… It’s a good question. I don’t…
I don’t think I know… The etymology of that one. It’s not like Query Bucks… Where… We very much know… That it was Kendra Little… Who came… Who came up with the term…
Query Bucks. Because Kendra… Is one fantastically smart cookie. And she says funny things constantly. Oh!
Ha ha ha ha ha ha ha! I made my first big mistake. Does anyone see what I did wrong? Does anyone see what I did wrong? Someone in SQL Passes…
He invented it. Ha ha ha! You’ll have to tell me who that was. I would like to know who that was. But this… This… This… This result set sorted… Very… Very strangely.
And it sorted very strangely… Because I am converting this count… To a comma delimited string. And so now SQL Server thinks… That this is… A string and not a number. So we have to order by…
The plane count… If we want to get things back… Descending. Yes. Arch our ordering. Bummerino. Not my favorite thing in the world.
Let’s see. Do I have Zoom It turned off over here? I do. Wonderful. All right. Cool. We are limited to Zooming… Over here. That is just what I wanted.
So… When we look at… The Vote Type ID column… Over in the Votes table… We have many, many different… Vote Types.
Well, not many, many. We have about 16 vote types. But this is one of those… Selectivity things where… You know, up at the top… We will have… Vote Type 2 with 37 million rows. And down at the bottom…
We have Vote Type 4 with 733 million rows. In the middle… We will have some values… That are a little bit closer together. Like, you know… We will have some that are… Up over a million. And then we will have some…
That are under a million. But still a fairly good amount. And then some that are just… Kind of like… Less than that. Right? So there are some… There are like… Like three or four different… Like how are the categories like… Like three or four different categories…
Of selectiveness… Within this column. And when we… You know, when you think about… Trying to index for selectivity… This is the kind of stuff… That you sort of have to take into account. Because you really do need to…
You know… Look at how people might be… Searching these things. Especially if someone was going to… Search a range of Vote Type IDs. Like if someone… You know… Did not just do an equality on one…
Like someone… These like 40 million rows… 37 million are going to be Vote Type 2. So… Selectivity isn’t just a guarantee. Right? But we have… We got this store procedure.
And what this store procedure does… It was it will search on Vote Type IDs. And that’s what… Sort of this first part does. Alright? We get some data from the Votes table… Where Vote Type ID…
Is equal to Vote Type ID. And we want to find… Votes… Where… I don’t know… We have…
Posts… That… So… I’m going to tell you a funny thing about this demo query. It does not make… The most sense in the world… Given the…
The stack overflow schema. But… It works really, really effectively as a demo. So I’m not even going to try to explain… What this thing is actually looking for. What I’m going to do is just show you… The monstrosity…
That is the parameter sniffing that goes on here. So… For these queries… All of these vote sniffings… Vote Types 1, 2, 3, 5, and 10…
I’m not going to run these in front of you… Because they run for a pretty long time. If we go look at the saved query plans I have for them… Vote Type 1 runs for around 15 and a half seconds.
Alright? Vote Type 2 runs for around… 35 seconds. Vote Type 3…
Another 15 seconds. 12, 15, 14… So like 13 and a half. So… These are all fairly long running queries. And these are all… Ones that I run with recompile.
So there’s no sniffing here. These are just plain slow. These just don’t do so hot… When SQL Server comes up with an execution plan for them. And that’s sad because…
A lot of the times when I talk to people… About parameter sniffing issues… A lot of them think that recompile… Is just a straight up… You know…
Golden ticket to solve the problem. Sometimes it is. Sometimes you can… You can go pretty far with a recompile hint… Placed in the right… In the right… In the right spot. And you can avoid… Dealing with…
With parameter sniffing… Because SQL Server is just going to come up with a… Good plan each time. But you can also run into just weird stuff… Where SQL Server… Like you know… Goes and just picks a bad plan.
Even with recompile… SQL Server is capable of picking a bad plan. Even if you compile a query… Specifically… For a parameter.
You can have a bad plan for that. For all these vote types… So 4, 6, 7, 8, 9, 11, 12… We have… Fairly good and fast plans.
These ones I do feel… Pretty safe running in front of you. Right? So all of those finish in just about 2 seconds. Right? We are on… Oops.
Didn’t go over quite far enough. There we are. About 2 seconds. So we’re in good shape there. Right? And if we look at the execution plans… These are going to get much different query plans… From the ones that we saw over here.
Right? These are all going to… These are going to get… Like you know… Slightly different plans than we saw… From like the plans that recompile.
Right? There’s a lot of like hashing… And craziness going on over here. And like big parallelism… And scans and all that. And over here we have something… A little bit different.
TZH said… SQL Server picks a bad plan. Certainly not. Yes I know. It’s insane. You know… But that’s the thing about working with databases. Is that… You’re not special. Your query isn’t special.
Your indexes aren’t special. Your schema is not special. I mean… Maybe it is special. But here’s the thing. Is the optimizer is a Swiss army knife. It is a general purpose utility.
And… It needs to be good… Across a wide variety… Of hardware… And indexes…
And schemas… And selectivities… And… And target… Like… It has to be good across all these different things. And that’s tremendously hard. And most of the time… It does pretty okay. But there’s all…
I mean… There’s all sorts of stuff that… You know… Like… Like you and I can do… To mess with the optimizer. To like make it… Like make it pick something bad. But then there’s… There’s just… You know… Blind spots.
There’s… Things that the optimizer… Is strong at. And… You know… There’s… Just sort of… Weird edge cases where… You know…
It… It makes some funny… Inference… Where like something is… Just cost it all wrong. So… You know… There’s that to consider. You know… It’s not like the optimizer is… Designed to come up with a perfect execution plan…
For every possible query. It’s sort of a general purpose thing… To find a good enough… Cheap enough plan pretty quickly. And… I’m… I’m cool with that. I’m cool with that because…
Uh… People still have to call me to tune queries. So… What we’re going to do is focus in on… A couple… Vote types.
We’re going to look at vote types 4 and 1. We’re going to look at 4 and 1 because… These… Two queries… Have… Fairly different run times. But also…
They have very very different distributions. I don’t want to sit around waiting for vote type… ID 2. Because vote type ID 2 runs for about 40 seconds. And I don’t want to sit… I don’t have enough to say… To kill 40 seconds every time I need to run this.
This one runs for about 15 seconds. I can… I feel very comfortable filling 15 seconds. With either… Um… Badums… Whatever’s…
Uh… Humming… Asking how you’re doing… Uh… Telling you what I ate for breakfast… Uh… Complaining about… Not being able to go to the gym. And just various other things. There’s like…
Fun stuff that I think… You know… I think… I like talking about weather. Weather is a big one. Weather is a huge one. So what I’m going to do is… Even though we ran this with recompile… And there’s nothing really sitting around in the plan cache…
To make this thing useful… Or to make this… To be useful to us running this. What I’m going to run… Is the… Store procedure for vote type ID… For…
Just alone by itself. And this is going to finish really quickly. And we’ll get this… This nice kind of… Like… This is like a wonderfully… OLTP-ish… Execution plan. We have…
Nested loops joins… And we have a little… Little tops… And little seeks… And just all sorts of nice little things going on. I also want to point out… Something very, very annoying…
About… Uh… Execution plan warnings. If you notice… This nested loops join… Right here… Has this red X over it. Right?
The sassy red X. And if we look at the… We look at this operator… We look at the tool tip for it… It’s going to tell us that we don’t have a join predicate. And this is so absolutely wrong… Because we have a seek here…
And the seek predicate… Is looking for… Is seeking to a very specific… Owner user ID… In the post table. And…
This is searching for… A very specific user ID over here. So any rows that come out of this seek… Because the seek is going to be coming from… User ID values over here. So we’re going to pass out…
One user ID at a time… To the nested loops join. That nested loops join… Is going to go down here. And because this is… An apply nested loops… Right? We have this outer references… Marker in the tool tip.
Which means it is apply nested loops… Not a regular old nested loops join. What they… What some might call… A naive nested loops join. Because we have this… Outer references thing here… We know that we are doing…
What’s called apply nested loops. Which means we’re taking… Basically one… Like one user ID at a time. We’re taking… We’re putting that through the nested loops. We’re going to seek here… And seek here for that same…
For that same user ID. And then we will… Whatever we join together here… In this nested loops join… Is going to be a match. Because we are searching for one at a time…
From these two things. So this happens very, very quickly for us. Right? And that’s pretty good. And now we’ll actually see… For vote type ID 1… Things be a little bit better than 15 seconds…
Even though we’re using an execution plan… That was not… That did not… That SQL Server did not optimize… Specifically for vote type ID 1. Amazing, right?
When we use a plan… That was created for a different parameter… This actually finishes a little bit faster… Than it does when we come up with an execution plan for it. Specifically for that vote type ID.
Now… This… The operator times here are probably going to look a little confusing. He says, sorry… But why does it say no join predicate then? Because the warning is very naive.
The warning is not aware of… The fact that we are doing apply nested loops… And it’s just kind of dumb.
Sorry. That’s all it is. It’s just not smart. I wish there was a better reason… But it’s just stupidity. Probably something just…
It got implemented by a summer intern… And the summer intern did not… Think about all the marvels and miraculousness… That can occur within an execution plan.
It’s just not terribly well thought out. Most of the time you can ignore it. There is… Like… I think when it first got added… People used to freak out about it.
I think a lot of why it got added at first… Was back when people used to write old style joins… Where they’d write from table, table, table… And put the join condition in a where clause.
Where we’re… Like, you know… It was fairly easy to… Like… Forget a join condition… And sort of end up with like a crazy Cartesian product from things. But, you know…
And I don’t know… Maybe apply nested loops wasn’t around… When this thing got added as a warning. I just don’t know. But it’s pretty poorly done. But, you know… We have this no…
We have the no join predicate warning… Even though… Anything that we seek to here and seek to here… Is going to be a match… Because it’s the same user ID that gets brought out here. It’s just not very smart. So…
Looking at this execution plan… It’s a little confusing… How things go. So… If we look… If we look at the properties of the plan…
We look at the query time stats… We’ll see that… Let’s see… Question… What compat level do you run that DB on?
This is 150. 150. 150. We’re on SQL Server 2019. You don’t mind going to look over here. Oh!
That’s SQL Server 2017. I’m in there… I did not… Get rid of that database. Or I did not get rid of that server connection. Now SSMS is going to make me wait. Stupid SSMS.
If only Azure Data Studio was any good at anything… Other than developer eye candy… I would like to use something other than SSMS someday. I’ve tried other IDEs…
For SQL Server… Like… What’s that? JetBrains 1… Or like… Beaver DB… Or whatever it is…
None of them had the same flavor. So let’s see… There we go… There’s 2019. So let’s go look at… Stack Overflow… Properties…
Options… This is going to be… Compat Level 150… There we go… There we go… Oh, don’t worry… It was my own stupid fault for not… For not disconnecting that server…
When I switched over to use 2019. Yay! Alright? So looking at this execution plan… We can see that… So like… When SQL Server… Runs…
Queries in batch mode… Right? We can see a lot of these… Operators are going to be run in batch mode… Even though this is… This is all rowstore. This isn’t columnstore stuff. This is a new SQL Server 2019… Engine feature.
It’s only for Enterprise Edition… But me being… Me using Developer Edition… Means I get… All the wonders of Enterprise Edition… For free! Ha ha ha! Go take it live to prod!
But because we have so much… Batch mode stuff going on in this plan… A lot of these operators… Are just going to be… Tallying up individual times… That’s a big difference between…
Row mode plans… And batch mode plans… Now… This query plan is going to have a mix… Of row and batch mode in it… We can see that this nested loops join… Is in row mode…
So what we’ll see is… You know… Like all… Pretty much all of like… The accumulated stuff… From here… Here… This sub tree… Kind of rolled up to this nested loops join…
But this hash match aggregate… Which is in batch mode… Does have… Sort of the majority… Of the 8-ish seconds… That this thing ran for in it… Now I know it’s spilled… And that’s not…
That’s not nice… But you can see that… In total this thing ran for… About 8.5 seconds… And if you want to see… Another cool engine feature… With SQL Server 2019…
Batch mode… Oh… Did we still… We still spilled… Let’s give this another run… Wow… Oh… That’s a batch mode spill… Maybe it’s just never going to get better… Are you ever going to figure it out?
No… You’re never going to figure it out… So much for that… So like what I was going to show you is… Under memory grants… What we should get… Is this thing…
Adjusting memory grants… And saying… Ah! I didn’t give you enough… Let me give you more… So… We are just not… We are just… I think just not moving up fast enough…
I wonder how many… Runs… I wonder if I can do this like… A few different times… And actually get… The memory grant… Yay! It only took 4 executions…
To get the memory grant… Right… But now this finishes much faster… Doesn’t it? Now this finishes in a reasonable amount of time… But I want to show you a downside of that… If we go back and we use…
Vote type ID 4… Now we are going to have a different warning over here… That says… We have an excessive grant! No!
And now if we run this again… That will probably go away… And we will use… We will ask for far less memory… And if we go back to this one… We will go back to spilling… So… Nice try!
I guess… But this is going to go back to being slow… Is there a way to tell Dev Edition… No there isn’t… If I wish there was… People have been asking for it… For as long as… There have…
There has been Standard and Enterprise Edition… But Microsoft has not budged… Don’t know why… Don’t understand why… So like the… Like the… I think the… The most compelling theory that I have…
For why… There is no… Button… To make Developer Edition… Act like Standard Edition… Is because they want developers to go out… And get all these secret performance features…
And be able to use… Like fully… All of the programmability features… And they want… Yeah exactly… They want you to taste the good stuff… They want to get you hooked…
On Enterprise Edition… And then by the time… And then if you try to go live on Standard… And everything falls apart… Then…
Well… All of a sudden… Your CPU cores are worth $5,000 more apiece… But anyway… So we just saw… One of SQL Server 2019’s fancy features… So actually this is a 2017 feature…
Batch Mode Memory Grant Feedback… Was a 2017 feature… But with Batch Mode… Being available on rowstore in 2019… It is sort of new to rowstore… You could do all sorts of things…
To fake SQL Server out… To make it think that queries were… Going to get like fully… Like Batch Mode… Batchy Modey on 2017… Even if they weren’t… With like…
You know… Empty temp tables… With columnstore Indexes on them… But… Not here… Not here… We just get it for free… And by for free… I mean with Enterprise Edition… So not really for free at all… Right?
But so… That kind of stinks… Let’s see… The cost jump… Isn’t a small one… Can’t think of how many people… Do that and just think… Okay… I’ll spend… Yeah… I mean… It all depends on the organization… Right?
Some people… Some people might… Find that out… And just switch to Postgres immediately… I wouldn’t blame them… So let’s run… So let’s re… We recompiled vote sniffing… Right?
So we looked at what happened here… When… When vote type ID 4 runs first… And then we run vote type ID 1 next… Right? What happens… Vote type ID 1 uses the plan… For vote type ID 4…
And… What do we get? A plan that runs for about… Seven, eight seconds… And then… Now let’s turn the tables… Let’s run… This query for vote type ID 1…
And we get this plan… Which is a pretty decent plan… On 2019… The plans that I showed you over here… Were actually for 2017…
I should need to… I need to re-screen cap those… Because things are a little bit different… Lee says… We’ve had issues of late… With new performance improvements… For 2019… On our managed instance…
I would love to hear more about those… Please tell me more… And now when vote type ID 4 reuses… So this one… So this actually is a 2019 improvement… So one thing that’s actually very cool…
With SQL Server 2019… Is… We get… These handy dandy… Adaptive joins… Right? I love it…
So let’s look at… What happens… When we post this for… When we run this for a truly… Villainous vote type ID… See vote type ID 5… Is going to get a very very similar plan…
To… What happened… For vote type ID 1… We’re going to have this same sort of… Adaptive join thing going on… Except this one just doesn’t go as well…
So that… When we ran it for vote type ID 1… What happened? Fast. Right? When vote type ID 1 got its very own…
Gym jam plan… Everything was great. Now… I don’t know. We’ll see what happened. Microsoft support turned off…
TempDB recompilations… Which stopped our instances falling over. That is a very bold statement. I would like to hear more about… How they turned off TempDB recompilations.
Do you mean recompilations from temp tables… Or from table variables? Because… Because table variable deferred compilation… Is a new thing in SQL Server 2019.
I would love to hear more about this one. That is a… That is a big sentence, Lee. That is a big sentence…
Some advancements, huh? Okay. Around…
Around table variable deferred compilation… Or something else. Inquiring minds. Still trying to get… If you’re still trying, Lee… You may never… Your efforts may be in vain. I don’t know.
I don’t know. I don’t know. I don’t know. It’s all craziness. Ooh.
Ooh. We have… We have a… We have a blog post. Let’s…
Let’s actually bring this up… In the browser… So everyone can see it. Let’s be nice. Let’s be nice people. Ooh.
Joe Sack. Handsome Joe. Oh. Don’t you just love when Joe Sack blogs? I love when Joe Sack blogs. I get so excited when Joe Sack blogs.
He’s like one of my favorite bloggers. So let’s see. Reduce recompilations for workloads… Using temporary tables across multiple scopes. Ooh.
That is a mouthful. Let’s see here. Create or alter outer proc that creates a table and then executes an inner proc. No, no, no. This is fine. This is the kind of railroading that I live for.
And then inner proc will insert into outer proc and select ID. Okay. Let’s see.
We create a temporary table. Ooh. CU5 included a fix for that feature. Yes. It sure did. Lucky CU5. Thanks.
Thanks for that, CU5. The CU5 stuff for the scale UDF inlining was especially troublesome. Temporary tables across multiple scopes. Who would do that?
If you have to ask that question, you have not lived the nightmare life of a consultant who has seen what many, many independent software vendors do to SQL Server. Where you’re like, have you used a database? Do you know what to do with a database?
So let’s see here. The end result is a reduction in unwarranted recompilations and associated CPU overhead. The number of occurrences with the blue line representing batch requests a second and the green line representing SQL recompilations a second.
So that is quite a bit of recompilations up here and quite a low number of batch requests a second here. It’s funny because they share very similar spikes and peaks and valleys in those. And this one over here, wow, you push that way up.
The feature was enabled after we saw improve throughput. Wow. Thanks, Joe Sack. Everyone say thank you to Joe Sack.
Everyone go on Twitter and say thank you to Joe Sack. Let’s see. There we go.
Let’s thank you, Joe Sack. I love Joe Sack. But yeah, they turn that off.
And suddenly our post-deployment scripts would work and the instance would stop falling over to the other node. Well, that sounds like a win to me. Sounds like a big win to me. I would like it if that stopped happening.
But now I have to ask, Lee, what are you doing with temp tables that made your managed instance fall over during deployments? Now I’m fascinated. Now I need to know what’s going on with these deployments.
All right. So when we run this stored procedure now. So we saw vote type ID 1 get a fast plan. We saw vote type ID 4 get a fast plan.
We even saw vote type ID 1 use vote type ID 4’s plan and be okay. But what struck me, what caught me out is just so crazy and odd. Just so bananas.
Is when I run this query with vote type ID 5. This thing takes 2 minutes and 17 seconds. Now the blog post that I wrote about this, I was using a full-size version of the Stack Overflow database where this thing would run for like 15 minutes. I’m using the 2013 version for this because, you know, I just don’t want to make you wait 15 minutes.
All right. That’s bad news. Lee says, nothing special.
I checked this section of the script. It’s the Wild West with those managed instances. Okay. All right. I mean, I’ll believe you on this one. I’ll believe you on this one. If we look at where a lot of the time is spent in this query plan.
This index seek is not slow. This compute scalar is not slow. This index scan is not slow.
I know everyone freaks out when they see index scans. But this index scan is 129 milliseconds. I’m pretty hip to the speed on that. And I’m pretty cool with this one taking 167 milliseconds.
Now, since this join is adaptive, we’re going to see a second potential join down here. But you can kind of tell by the width of this line that it didn’t do any work. A lot of the time in this query is going to get spent here and here.
All right. You can see that pretty well. If we go look at the total execution time on this, we will get a long time.
Long time. Why are you linking to other people’s Twitch streams in here? I’m going to ban you for spamming Coyote McD.
I’m going to kick you from the chat forever. Dead to me. Dead to me. So what happens up here? Something kind of interesting happens where this hash join is running. And let me just bring up the properties of it.
Because the properties of it is where we get all of the information kind of happening at once. So the execution mode over here is batch. And if we look at what it’s up to, we can see that it is a bitmap creator. And it is true that it has created bitmaps.
All right. So we created a bitmap and we use that bitmap down over here in this part of the plan. This index scan and this index scan.
Now, you can see that there’s a fairly bad estimate from one of these. All right. One of them is pretty good.
The other one is pretty bad. But if we look a little bit closer. Oops. There we go. Look a little bit closer.
We have a predicate that wants to probe with this optimized bitmap filter. What are we even doing here? What are we doing here?
We are trying to apply a bitmap filter to this index scan. It is not going well. And actually, you know what? I take that back.
It might go spectacularly well. The problem is that we get a very, very bad estimate because of that. Now, I’m going to stick a link into chat for a very, very smart post. If I can learn how to type.
By the patron saint of SQL Server Performance, Paul White. It’s about batch mode bitmaps. And you can kind of get a feel for just how naive a lot of the cardinality estimates for batch mode bitmap filters are.
And since this hash mode semi-join over here is a bitmap creator and we use a bitmap, we get a pretty bad bitmap estimate. He says, I still don’t fully understand bitmap’s role. Yes.
They are. They can be rather difficult to grasp. If you might help you understand a little bit. Let me grab another link for you. Do, do.
Where are you? There you are. Ha, ha, ha, ha, ha. Where is? Yes. Yes. I just need to make sure to mute myself from there. So, I have a video, an old style video.
An old style video. Over on YouTube. About useful and useful, useful versus useless bitmaps. That might, might help you get some understanding on that.
Don’t watch it now though. Because it’ll just be distracting. And if you hear, if you hear me talking from two different videos at once, your, your head might explode. It might not be the most fun time in the world for you.
So, this is not a particularly good plan for, for vote type ID 5. And it’s not a particularly good plan because of the bad estimate that we get from that bitmap over here. Where way, way, way, way, way more rows come out of this operation than expected, right?
We expect 6,000 and we get, let’s see, 3, 0, 4, 9, 3, 4, 1. So, we get back 3 million. And worse, when we join the 3 million rows from posts to the 8 million rows from badges, we get just about everything.
All right. So, we have another bad estimate. What’s the CPU time on what?
Coyote McD. Be specific about on that. And I will happily answer you. So, we get this adaptive join. And this adaptive join is a join just like any other.
You want to know what the CPU time on this is? Let’s look. Why am I, why am I not saying where you go? There we go.
Yeesh. I don’t know why that took me so long. There you go. So, 11,000 seconds. 11,000 milliseconds, rather.
So, that’s fun. But the elapsed time on that’s a stinker, right? What happened there? What happened to you? Let’s look at it. Oh, that’s not too bad.
It’s not so bad at all. You could do far worse than that. So, we’ve got this adaptive join. And this adaptive join spills because we have just some bad guesses going on over here.
How much it spills? Not terribly bad. There are hash spills, though.
So, hash spills can get kind of weird. But really, and what struck me, I think, most while I was trying to figure out what was going on here is that if we had a better guess, SQL Server might do things differently. If SQL Server had made a good guess about how many rows might come out of, like, one of these, we might see an aggregate over here somewhere.
So, we see an aggregate over here. SQL Server decides to smush a whole bunch of rows down to one row here. But I think if SQL Server understood that we were going to end up with, let’s see, 217292920 with a nine-digit number of rows here.
Because remember that we have to take all the rows that come out of this index seek. We build a hash table here. And then we have to probe the result of this join.
Which, when you have a guess that’s off by 605,000%, becomes quite a chore. So, all the results of the index seek on the votes table, right? Which is correct, right?
That’s an accurate guess, right? That’s 100% there. SQL Server was spot on with this one. SQL Server was spot on with this one. But when we get here, and we have to all of a sudden take the rows from the votes table, and we have to probe into the results of this adaptive join, probing into the 217 million rows, yeah, the 217 million row result of that adaptive join is unpleasant.
Now, it’s unpleasant because that’s just a ton of rows. Like, all of a sudden, like, this thing running for two minutes and 20 seconds makes a lot of sense. Because that’s just a lot of work to do.
That’s a lot of probing to do. That’s an unhappy amount of probing to do. What I thought was very, very funny, kind of about these circumstances here, though, was if we take the recompile out of the equation, right?
Let’s just take recompile out. Let’s leave recompile out of it for now. Let’s run for vote type ID 4, and let’s get this. Wait, no, we do have to recompile the proc because I used it for something else.
There we go. Let’s get vote type ID 4, and let’s run that. So now we have the vote type ID 4 plan. And let’s get the vote type ID 5 plan.
Let’s parameter sniff on purpose. Let’s have a parameter sniffing party, you and me. Let’s all get together and do that. When we reuse the plan for vote type ID 2, what happens?
It finishes almost immediately, right? That runs in about two seconds if we look at the query time stats on that. Two seconds.
So let me ask you a funny question. If you had a query that started showing up on monitoring is running for five minutes or two minutes and something seconds, and you saw that maybe the compile time parameter and the runtime parameter were different, you were like, oh, we got a parameter sniffing problem.
But it’s not quite that, is it? Because if we run this store procedure for any of these queries except vote type ID 5, we get the exact same execution plan that we get when vote type ID 5 runs.
There’s no real difference between them. And these all finish relatively quickly. It’s the same execution plan over and over and over again.
And we’re recompiling all of these. So we’re coming up with an execution plan specific for 1, 2, 3, and 10. But when 5 runs, whoo! When 5 runs even for itself, the execution plan for 5 sucks.
When 5 uses another, even any one of the other tiny little plans, 5 does fine. We almost have reverse parameter stuffing because the plan that we get for vote type ID 5 sucks for vote type ID 5 very specifically. If any other query uses a plan specific to vote type ID 5, it’ll do just fine.
If we recompile this, and we run for 5 first, oops, you know what? We’re not going to run for 5 first. We’re going to run for 1 first because 1 gets the same plan as 5.
It gets the same adaptive join plan. We get all that stuff. And we rerun this for 4. 4 does just fine with this plan. 4 just shrugs it off.
4 is like, you know what? I got this. It’s when vote type ID 5 gets that really, really bad guess. Everything just goes to crap. So how can we fix vote type ID 5?
How can we remedy vote type ID 5? How can we get in there and start messing with things? Well, one way might be a temp table.
All right. So if we create a temp table called votes, and we put that initial part of the join or the initial part of the query that filters down to the vote type ID. Right.
So because remember, Lisa has a query hint. Sure. What query hint? Tell me which query hint to use. So if we filter down to just the vote type ID we want first, what we can do is take the results of just vote type ID 5, isolate that, isolate the cardinality involved there, and then maybe we can do a better job with this query or with this execution plan. Maybe stabilizing that will be a good idea.
Let’s sacrifice one of these things to be vote type ID 5, and let’s see what happens. Now, my computer over there is humming, so I know we did some work. I know we did some work.
And what happens here? Well, that’s pretty all right. Oops. That’s pretty all right. Do an index seek over here.
Do a couple of stream aggregates over here. We insert the results into our temp table over here. The whole thing takes about 1.5 seconds. And now then we have a pretty quick query over here, too.
Now, notice something. We still end up with the exact same execution plan that we had before the temp table. Right.
So what happens in here is nearly identical. We start off with the votes table up here. Here we compute a scalar. We have a hash join. We have an adaptive join. And then we have posts and badges over here.
So we make just about all the same mistakes, but we just don’t have that same terrible performance. Pretty crazy, right? So this is another place where paying too much attention to costs and estimates and all sorts of other things like that can sort of be detrimental to query tuning.
The estimate isn’t as wrong. No, it is just as wrong.
That is off. Well, I mean, okay. So, yeah, that is slightly less wrong, but it is still wrong. It’s not as wrong, but it is still pretty wrong. So it went from 6,000 to – so it went down by – I don’t know.
I’m not exactly sure what an order of magnitude is. I hear it’s a big number. But, yeah, this went from 605,000 to 6,000. So it’s still wrong by a big chunk.
But I don’t know. Like, what would I fix in here taking 1.9 seconds? I just don’t know what I would do in here. And if we go look at the query time stats over here, what would I fix?
I don’t know. I don’t know what I could fix in here to do better. This thing up here is still up to the same nonsense with the optimized bitmap.
The optimized bitmap is still the reason why we get this not good situation here. I don’t think it occurs down here, though. Oh, it does.
Interesting. So I didn’t notice that before, but the optimized bitmap actually gets applied to the badges table this time, too. In the previous plan, we didn’t get that. We didn’t have that extra bitmap-y goodness. So that’s actually a fun new thing that I just noticed in here is the bitmap gets double applied.
I wonder if we have a bad estimate here. Yes, we do. Now we have a bad estimate here. Now we have an estimate of 490%.
In the old plan, this was spot on. So we actually got two bad estimates from the same bitmap. Isn’t that sweet? It’s so nice when bitmaps just work out like that. It’s so nice.
It’s so nice. So let’s look at something a little bit different. So we looked at a couple of possible different ways, or we looked at one way to rewrite this using a temp table up here. I think my main beef with this, though, is if we had to do that for vote type ID 2, if I run the same proc for vote type ID 2, we have to put kind of a lot of rows into a temp table.
Probably more like if this was a highly concurrent store procedure, if this was something that was running a lot, it was like really just like, you know, like a critical part of the application, we could end up, you know, like kind of having to do a lot of work, right?
Like in some cases, like we would, like we might end up having to put a lot of rows into a temp table. This also takes about 10 seconds total, which I’m like not thrilled with either. No, and it’s just, and it’s all spent up here, right?
It’s all up in this top part. So like, I don’t know that I’m thrilled with this. Like, I don’t know that this is like, like, I don’t know that I’m really psyched on the, like, like that for the temp table. I don’t know that I just want this thing like pounding around on temp TV.
So let’s look at a couple different ways that we might be able to rewrite this. Now, this first one was all my idea. This one was mine, I think, at least as far as I know.
I’m going to show you another one from a smart friend. But this one here, we’re going to, so what we’re going to do is rather than, you know, use the votes table to drive the temp table.
We’re going to use the post table instead. So we’re going to use that first join between posts and badges. And we’re going to have an exists back to the votes table to make sure that we really narrow down the number of rows that we care about, right?
So let’s, oops, that’s F4, not F5. And that ends up pretty quick. Oops, I didn’t turn on execution plans there.
Let’s try that again. I’m going to have to drop this P table, right? Can I get an expert witness on dropping a temp table? We’ll just say drop table P.
You know why? Because we can do that. We are live. We are live in dev. We can do whatever we want. We can do what we please. All right.
So that takes about 2.7 seconds. And that’s a pretty reasonably small number of rows, right? Like I’m not going to cry over a 200,000 row temp table. And then if we run this query, just looking for what’s in that temp table instead.
And I’m going to, actually, no, I have to redrop P, don’t I? So I’m going to run that all at once now.
I wanted to make sure that we had the temp table insert isolated to see what that looked like. So now when we run this, that’s about, boy, that varied a little bit, didn’t it? Yes, select 1, 0.
And then this part runs relatively quickly. All right. So we simplified this part. I don’t know that I’m thrilled with, like, this part, this temp table insert. I don’t know.
Maybe I’d stick with the votes one. So, like, I don’t know. Like, so thinking about, yes, select 1, 0. It doesn’t do anything. It doesn’t exist.
So thinking about, like, what I would do with a situation like this, where if I knew that if, like, at some point, like, I could have a query that runs about a second faster and uses potentially more tempDB or have a query that takes an extra second but uses a reliably small amount of tempDB, I might just opt for losing a second. And depending on my system, depending on, like, sort of, depending on, like, you know, the, like, what I care the most about. If we’re just going purely for time, I don’t know.
Maybe I would pick the slightly more abusive to tempDB query. But since we have a choice, we can figure it. Maybe we don’t need to do that at all.
So this was user submitted by my dear friend Paul. My dear friend Paul from New Zealand. And he came up with this query, which is insane and typical of Paul outdoing me in every possible way.
And this doesn’t need a temp table at all. All right. So we’re going to create this.
We have to create a slightly different index in order for this to really shine. But it runs pretty well. Lee says, I realized I’m guilty of getting it fast rather than thinking about cost. Well, you know, don’t think about query cost.
Query cost is a not good metric to think about. You know, the cost that I was thinking of was more related to, you know, resource usage with tempDB. Which, you know, tempDB is kind of made for that.
TemDB is, like, kind of made to take a beating. But, you know, you can certainly run into strange contention up in tempDB. You know, if I really wanted to spend a long time on this, I might try to run, I might try to use, like, O-Stress or something to run a whole bunch of copies of this all at once. And then, you know, kind of, like, you know, see if, like, I could hit some, like, like, what kind of tempDB contention I could hit.
Sort of, like, you know, just, like, running, like, 20, 30 copies of this. Option max.80. Coyote McD, here’s the thing, man.
You have servers where you could use, well, you can’t use max.80, can’t you? You can use max.64. You could use max.64.
TZ says, are the differences between temp tables and table variables any different in newer versions? They are only slightly different in SQL Server 2019. If you, you know what, if you tune in tomorrow, I’ll talk about that.
I think that’s a good topic for tomorrow. So, tune in tomorrow, and we’ll talk about the difference between temp tables and table variables in SQL Server 2019. That seems like a good, that’s, you know what, that sounds like a winner.
Now I’m mad I didn’t think to do that one today. It’s talking about this stupid blog post query. All right, so, with this, let’s see how this goes.
Let’s turn on execution plans, though. Let’s make sure that we get that. And once again, Paul has absolutely wrecked every single thing. He’s a madman.
So, this finishes in 700 milliseconds and doesn’t require a temp table at all. It is kind of strangely written. It is kind of strangely written.
And we do have to force the index here. I forget exactly what happens if we don’t. Yeah, it goes back to using the other index. So, you know what, let’s get rid of that one.
Come on, Pally. Let’s put index on votes. There we go.
Now let’s see what you do. There we go. So, even without the index, so without the index and with just that index in there, we actually, we do pretty well.
And this is without a temp table at all. We do still have this annoying warning on the nested loops join, which is completely wrong, but that’s okay. Okay. But that’s okay.
What we don’t have, though, or what we, actually, more importantly, what we do have. What we do have, it’s very, very important. And this is, I think, what makes the biggest difference in here.
If we think logically about all the things that we’ve looked at with this query and not liked, the biggest one is that SQL Server was not choosing to aggregate any of the join stuff prior to a join. None of the join columns are getting aggregated before we went into the join.
Like, logically, like, you and me thinking about it out loud, like, there’s, like, if we know that a column is not reasonably selective, a column is not reasonably unique, the column is going to have a fair amount of duplicate values in it, we would probably want to aggregate that down to as few values as possible to go into the join.
And that’s what happens here. We seek into the votes table, but then we also pre-aggregate our join column a little bit. If we hover over this, you will see that we aggregated the user ID column from the votes table down to however many, however few rows we could reasonably aggregate it down to.
So let’s see here. We started with, let’s see, 3, 5, 1, 1, 7, 3, 3. So we started with 3.5 million rows, and we were able to aggregate that down to 267,000 rows, which is a substantial reduction in the amount of work we would have to do at join time.
And then this has to do a lot of work. Let’s see. Coyote McD says, that’s a lot of seeking.
Does RCSI make a big difference in lock overhead on this one too? Yes, but let’s see. What would be a good way to…
Show that. You know what? We can just come back over here to this one. We need 4 and 1 and 1 and 4.
So what Mr. Coyote McD is asking about is sort of a follow-up blog post to this, where if we run this, what we’ll get is… So this is one that I think went out yesterday.
It was about how read queries need to take locks too. Right? Read queries take locks.
Unless there’s a lookup in the query. They don’t accumulate locks the way modification queries do, though. They don’t build up over time. They just kind of get taken and released.
Again, unless there’s a lookup in there. If there’s lookups in there, you’re screwed. Locks can hold on for a while. But if we run this query between 4 and 1, the vote type ID 1… Actually, I’m not sure which…
Did we? Yes. We’re going to do the same thing. So looking at this now, this ends up taking a whole lot longer to get those rows in there. Even though it takes 7.5 seconds to put one row into this temp table versus it taking nothing for this one.
Right? And now we could play with it a little bit. I don’t think we have any batch mode operators in here.
I want to double check, though. Because we might get enough of a memory grant connection to help with that. But I don’t think so. No.
So this two stream aggregates aren’t going to be valid for batch mode. Right? They’re not going to be batch mode eligible. The compute scalar is in row mode. This compute scalar is in row mode.
This index seek is in row mode. And this sort is also… So this is all row mode stuff. So we’re not going to get batch mode memory grant feedback on this. We would just get the same crappy 7.5 second plan over and over again.
At least I think… Pretty sure we need that batch mode stuff. Oh, it was a little faster. Did we? Oh, no.
I guess it did. Let’s go look. Oh, yeah. Yes, adjusting. Good. Oh, that’s nice. I wonder what we’re getting that from. What a nice touch.
Thanks, Joe Sack. Thanks again, Joe Sack. I’ll draw another heart on your head later. But what’s interesting, though, is if we run this for vote type ID 1 first… Well, this is faster.
I don’t want to go parallel there. But vote type ID 1 and 4 run much more quickly. And so… But what Coyote McD is getting at was sometimes you do have…
There can be overhead. Let me grab the blog post that he’s referencing so that everyone has it. You don’t have to go searching for it.
The last thing I want anyone to have to do is go searching for things. It is that under some circumstances, the locking overhead of reading data from a table can be particularly loathsome. So one way to test that is to, let’s say, add a page lock hint here.
And we will get… So long as the demo gods are smiling upon me. Well, that could have gone better.
Highlighting was never my specialty, folks. I apologize. I apologize. Demo gods are not smiling upon me.
This is what happens when you go off script. Thanks, Coyote. But at least this part… At least for this one, the index seek was a lot. It was 1.5 seconds before. It was only half a second this time.
So thank God for that. Thank God for the little things. Oh, yeah. That’s fun.
Triple click for a single line highlight. Yes, I know. But I don’t… I’m an old-fashioned guy. And I just…
I just highlight things. I would have messed up the triple click and just… And done something silly there, too. Anyway, it’s been like an hour. And I need to go hang out with my kids, apparently.
So I’m going to get going and do that. If we have any final questions, anything… Anything you want to ask about, know about, SQL Server stuff, you can…
Now is an okay time to do that. Let me go back over to… This one here. And let’s go look at… This.
All right. I’ll hang out here for a minute. See if anything comes into chat. Let’s see.
Thanks to one of your videos, I found a deadlock in a database, which I solved with an index. Nice. All right. Mr. P, you’re welcome. Mr. Pshaw, you are absolutely welcome.
Lee says, thanks to the stream. No, leave. You can always ask more questions. That’s what I’m here for. If you don’t ask questions, I don’t have answers. Isn’t that sad?
Just all these things locked away in my head that I don’t have answers for. That I have answers for. That I just can’t use.
All right. I will try to get on a little bit earlier tomorrow so you fine folks in Europe don’t have to stay up past drinking time to hang out with me.
But I’ll announce it on the old Twitter and whatnot when I want to go live. So I’ll see you all tomorrow. Thanks for joining me. Take care. And, you know, be excellent to each other.
As a wise man once said. All right. adios. Audrey. So, see you.
I don’t know.
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.
