Intermediate result materialization is one of the most successful tuning methods that I regularly use.
Separating complexity is only second to eliminating complexity. Of course, sometimes those intermediate results need indexing to finish the job.
Not always. But you know. I deal with some weird stuff.
Like anime weird.
Listings!
Regular ol’ #temp tables can have just about any kind of index plopped on them. Clustered, nonclustered, filtered, column store (unless you’re using in-memory tempdb with SQL Server 2019). That’s nice parity with regular tables.
Of course, lots of indexes on temp tables have the same problem as lots of indexes on regular tables. They can slow down loading data in, especially if there’s a lot. That’s why I usually tell people load first, create indexes later.
There are a couple ways to create indexes on #temp tables:
Create, then add
/*Create, then add*/
CREATE TABLE #t (id INT NOT NULL);
/*insert data*/
CREATE CLUSTERED INDEX c ON #t(id);
Create inline
/*Create inline*/
CREATE TABLE #t(id INT NOT NULL,
INDEX c CLUSTERED (id));
It depends on what problem you’re trying to solve:
Recompiles caused by #temp tables: Create Inline
Slow data loads: Create, then add
Another option to help with #temp table recompiles is the KEEPFIXED PLAN hint, but to wit I’ve only ever seen it used in sp_WhoIsActive.
Forgotten
Often forgotten is that table variables can be indexed in many of the same ways (at least post SQL Server 2014, when the inline index create syntax came about). The only kinds of indexes that I care about that you can’t create on a table variable are column store and filtered (column store generally, filtered pre-2019).
Other than that, it’s all fair game.
DECLARE @t TABLE( id INT NOT NULL,
INDEX c CLUSTERED (id),
INDEX n NONCLUSTERED (id) );
You can create clustered and nonclustered indexes on them, they can be unique, you can add primary keys.
It’s a whole thing.
Futuristic
In SQL Server 2019, we can also create indexes with included columns and filtered indexes with the inline syntax.
CREATE TABLE #t( id INT,
more_id INT,
INDEX c CLUSTERED (id),
INDEX n NONCLUSTERED (more_id) INCLUDE(id),
INDEX f NONCLUSTERED (more_id) WHERE more_id > 1 );
DECLARE @t TABLE ( id INT,
more_id INT,
INDEX c CLUSTERED (id),
INDEX n NONCLUSTERED (more_id) INCLUDE(id),
INDEX F NONCLUSTERED (more_id) WHERE more_id > 1 );
Missing Persons
Notice that I’m not talking about CTEs here. You can’t index create indexes on those.
Perhaps that’s why they’re called “common”.
Yes, you can index the underlying tables in your query, but the results of CTEs don’t get physically stored anywhere that would allow you to create an index on them.
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.
There are no guesses at selectivity, and there may be a better order available. It’s your job to figure that out.
Special Indexes
Missing index requests also don’t cover ‘special’ indexes, like:
Clustered
Filtered
Partitioned
Compressed
XML-ed
Spatial-ed
Columnstore-d
Indexed View-ed
What columns are considered?
Missing Index key columns are generated from columns used to filter results, like those in:
JOINs
WHERE clause
Missing Index Included columns are generated from columns required by the query, like those in:
SELECT
GROUP BY
ORDER BY
Even though quite often, columns you’re ordering by or grouping by can be beneficial as key columns. This goes back to one of the Limitations:
It is not intended to fine tune an indexing configuration.
For example, this query will not register a missing index request, even though adding an index on LastAccessDate would prevent the need to Sort (and spill to disk).
SELECT TOP (1000) u.DisplayName FROM dbo.Users AS u ORDER BY u.LastAccessDate DESC;
NUTS
Nor does this grouping query on Location.
SELECT TOP (20000) u.Location FROM dbo.Users AS u GROUP BY u.Location
NUTS
That doesn’t sound very helpful!
Well, yeah, but it’s better than nothing. Think of missing index requests like a crying baby. You know there’s a problem, but it’s up to you as an adult to figure out what that problem is.
You still haven’t told me why I don’t have them, though…
Relax, bucko. We’re getting there.
Trace Flags
If you enable TF 2330, missing index requests won’t be logged. To find out if you have this enabled, run this:
DBCC TRACESTATUS;
Index Rebuilds
Rebuilding indexes will clear missing index requests. So before you go Hi-Ho-Silver-Away rebuilding every index the second an iota of fragmentation sneaks in, think about the information you’re clearing out every time you do that.
Adding, removing, or disabling an index will clear all of the missing index requests for that table. If you’re working through several index changes on the same table, make sure you script them all out before making any.
Trivial Plans
If a plan is simple enough, and the index access choice is obvious enough, and the cost is low enough, you’ll get a trivial plan.
This effectively means there were no cost based decisions for the optimizer to make.
The details of which types of query can benefit from Trivial Plan change frequently, but things like joins, subqueries, and inequality predicates generally prevent this optimization.
See the difference between these queries and their plans:
SELECT * FROM dbo.Users AS u WHERE u.Reputation = 2;
SELECT * FROM dbo.Users AS u WHERE u.Reputation = 2 AND 1 = (SELECT 1);
NUTS
The first plan is trivial, and no request is shown. There may be cases where bugs prevent missing indexes from appearing in query plans; they are usually more reliably logged in the missing index DMVs, though.
SARGability
Predicates where the optimizer wouldn’t be able to use an index efficiently even with an index may prevent them from being logged.
Things that are generally not SARGable are:
Columns wrapped in functions
Column + SomeValue = SomePredicate
Column + AnotherColumn = SomePredicate
Column = @Variable OR @Variable IS NULL
Examples:
SELECT * FROM dbo.Users AS u WHERE ISNULL(u.Age, 1000) > 1000;
SELECT * FROM dbo.Users AS u WHERE DATEDIFF(DAY, u.CreationDate, u.LastAccessDate) > 5000;
SELECT * FROM dbo.Users AS u WHERE u.UpVotes + u.DownVotes > 10000000;
DECLARE @ThisWillHappenWithStoredProcedureParametersToo NVARCHAR(40) = N'Eggs McLaren';
SELECT *
FROM dbo.Users AS u
WHERE u.DisplayName LIKE @ThisWillHappenWithStoredProcedureParametersToo OR @ThisWillHappenWithStoredProcedureParametersToo IS NULL;
None of these queries will register missing index requests. For more information on these, check out the following links:
CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);
It looks okay for this query:
SELECT p.OwnerUserId, p.Score
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20070101'
AND p.CreationDate < '20181231'
AND p.Score >= 25000
AND 1 = (SELECT 1)
ORDER BY p.Score DESC;
The plan is a simple Seek…
NUTS
But because the leading key column is for the less-selective predicate, we end up doing more work than we should:
If we change the index key column order, we do a lot less work:
CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);
NUTS
And significantly fewer reads:
Table ‘Posts’. Scan count 1, logical reads 5
SQL Server Is Creating Indexes For you
In certain cases, SQL Server will choose to create an index on the fly via an index spool. When an index spool is present, a missing index request won’t be. Surely adding the index yourself could be a good idea, but don’t count on SQL Server helping you figure that out.
NUTS
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.
Last week’s thrilling, stunning, flawless episode of whatever-you-wanna-call-it.
Video Summary
In this video, I delve into the exciting world of performance tuning and database management, sharing insights from recent experiences and upcoming changes. I discuss how new features like Windows Server 2019’s Perfmon, which redesigns the user interface to look more modern and interactive, are poised to revolutionize monitoring tools for SQL Server administrators. Additionally, I reflect on my recent speaking engagements at SQL Saturday Portland and the anticipation of returning to SQL Bits in February, where I’ll be presenting new material relevant to the latest database technologies and challenges.
Full Transcript
I apologize for being mildly late. I was trying to figure out how to get the thumbnail set up exactly how I wanted it. I have this strained mirror. I have fun with the mirror. It’s a telescoping thing. I can point it at all sorts of stuff. I have fun. Enjoying myself. The only thing I’m terrified of is that someone might see what’s on the wall behind my monitor. It’s largely pornographic posters. I have fun with the mirror. Just kidding. What do I do with that mirror? I don’t know. Nothing. You can hear me, right? This isn’t like one of those things where I’m talking and no one can hear me and everyone’s having a bad time. That’s going to happen. That’s going to kill myself.
That’s going to kill myself today. That’s going to be the end of it. The end of it. Sound is good. Wonderful. Wonderful. So who has questions? Someone better have questions. Maybe doing this during pass was a bad idea. I assume people who show up here are there. I would expect low attendance. All right. Someone.
I’m going to count to 100. I’m going to count to 100 in my head. If we don’t have any questions, I’m going to get a haircut. Because this is a bit much. Bum, bum, bum, bum, bum, bum, bum, bum, bum, bum, bum, bum.
All right. All right. Well. There are no questions. Oh, there is a question.
How lucky. Varus says, I’m talking with people who want to use GUIDs instead of identity columns because they’re afraid of outages from either running out or having big int conversion errors. Why not just use a big int?
Here’s your identity column. There’s very little downside to that. It would take you until, I don’t know, like the heat, death, and rebirth of the universe to really run out of those. And if the argument has ever been that big ints are 8 bytes and ints are 4 bytes, well, GUIDs are, what, like 30-something?
I mean, what’s the point there? And then you have to do all sorts of weird juggling with your indexes. All right.
Because unless you’re generating sequential GUIDs, then we don’t want a clustered index on that GUID column. Even if it is sequential, if you restart, you might have some weird artifacts when the server comes back up. But if you’re generating random GUIDs, then it can be especially difficult on a clustered index.
So I would probably just say, how about this lovely big int? Big ints are wonderful. All ints should be big ints.
There should be no medium int. Big ints are nothing. Bites be damned. These sort of design questions are funny because people think GUIDs are magical.
And I guess for uniqueness, they are. But they make so many other things. Quite obnoxious.
Non-clustered primary keys on GUIDs. Clustered indexes on other columns, which usually end up being integers anyway. I don’t know.
It’s all weird to think about. Like, I wonder what keeps someone up at night thinking about these things to the point where they have to fall into the GUID trap. SQL Server 2019 is out, huh?
How about a hand for SQL Server 2019 where all of my demos except a few still work? Problem not solved, I guess, is the bottom line there. I don’t know.
It’s cool, though. It’ll be fun seeing how getting batch mode on the reg changes stuff for people. So, Farah says, and what if you’re trying to convince someone who’s afraid of running out of big ints?
How do you communicate this with very non-technical people? I mean, I guess I would say… So, like, the usual thing to do would be to…
What do you call it? What’s that word? Estimate.
I knew it was in there somewhere. Estimate the transactional activity of this application that you’re building. Say, how many applications do you… Or how many…
How many transactions do you expect a day in this application? And then divide that out and, like, see if you can figure out how long it would take. How many days it would take to run out of big ints.
And then, like, okay, if you were wrong by a factor of two, if we have twice as many, if we have four times as many, this is how long big ints will still last for. And I’m willing to bet, I’m willing to throw this out there, that perhaps by the time you approach running out of those big ints, we might have an even bigger int available.
Or we might be quantum computing where integers have no meaning or something. That’s a thing, right? Lee asks, from a fan of window functions.
Yes, from time to time, when they’re necessary. Performance of window functions usually comes down to having an adequate index on your partition by and order by elements. Usually.
At least for me, anyway. Some people can get away. Some people who do demos on AdventureWorks can get away with whatever indexes they want. Other people who have real databases or, you know, databases that are bigger than, like, 20 megs or something, they have different problems.
Yeah. Using that index, they’re wonderful. Otherwise, you better hope that… So if you really want to play with something cool, I don’t know what version of SQL Server you’re on, but if you really want to play with something cool with windowing functions, see what happens when you involve a columnstore index and you get a window aggregate.
Those are super fun to get. If you think windowing functions with the partition over by covering index is fast, wait until you get a window aggregate.
2017. So yeah, what I would do, if you’re on 2017, create a temp table with a clustered columnstore index on it, and then left join to that temp table on 1 equals 0, and you may see a window aggregate function show up, and when you do, you’ll wonder how you ever lived without them.
I think the thing to be careful with with window functions is that by default… Is this a trick to enable set-based operators? It’s an interesting question.
Batch mode operators is what it enables. Batch mode operators. But the thing to be really careful with with windowing functions is that by default, they will work on the range of rows rather than a set number of rows.
You have to set… You have to tell that windowing function to work on rows unbounded proceeding, yada, yada, versus range unbounded yada, yada, proceeding, because the range, when you work with ranges, you can get this crappy on-disc spool of data.
It can be very slow. Whereas with using the rows specification, you can… You get the in-memory spool or something like that.
So that’s another thing to be careful of with the old windowing functions. Watch out for that. It’s a big deal sometimes. Also, they act differently.
Ranges and rows. Much different. It’s always fun to revisit this stuff, because I don’t think about it terribly often.
Because it’s stuff that I’ve… It’s been settled in my head. For so long that I don’t think about it too much. It’s like what I’m going to order at a bar, because I just don’t go to bars that much anymore.
It doesn’t matter. What do you want? The drink. The drink you make. Yeah, look up the syntax for windowing functions. Make sure you understand the full difference.
I actually… Blah, blah, blah, blah, blah. Let’s see here. Window functions.
I bet that post is still up, because Brent seems to like my SEO. So I’ll stick that link in chat.
Forrest asks if I’ve seen much of a difference in performance due to underlying OS, e.g. server 2012 versus 2016. God, you know…
This is going to sound obtuse of me, but when I was a DBA, everything was mostly server 2008.
And then as a consultant, most servers seem to go from 2008 to 2016. I didn’t see a whole lot of people on server 2012.
So I’m not sure that I would be able to give you like a specific performance difference between the two. I’m sure there’s stuff.
I’m sure there’s improvements to Windows in the way that like CPU and memory and IO and everything is handled by the operating system. I’m sure that comes into things.
But gosh, I don’t think I’ve ever… I don’t think I’ve ever looked at someone’s performance problem and been like, ah, shucks. If only you were on server 2016, 19 or something.
That reminds me. I have to start downloading Windows Server 2019. You know what I’m really excited about? The new Perfmon. New Perfmon looks like a cool video game. It’s all like 3D and bejazzled and stuff.
It’s fantastic looking. I can’t wait. I can’t wait for that new Perfmon. Let’s see if I can find a link.
New Perfmon. Yeah, buddy. Yeah, buddy. Stick that link in chat. In case anyone’s interested in looking at the new Perfmon.
It looks so cool. It’s all like Power BI. There are like charts and graphs and stuff. I think you can search for like which object you want instead of having to like just like scroll through that absurd list.
It looks so good. I can’t wait for the new Perfmon. I might start using Perfmon. I’m so bad at using Perfmon.
Like when people are like, I have a problem with blah, blah, blah. I’m like, we’re not using Perfmon. Because A, I’m not good at it. B, I get really annoyed at how all the measurements can be like different scales. Like one being at 100 can mean one thing.
Another one being at 100 can mean another thing. And then none of it makes sense. I get annoyed at trying to figure out which collectors I need. I’ve just been really, really bad at Perfmon.
So I quit Perfmon. Until I saw that Perfmon. That new Perfmon. And now I’m excited about Perfmon again. I’m very excited about Perfmon.
I can’t wait for new Perfmon. It’s exciting times for Perfmon. Very excited.
It’s going to be a good time. Better or worse than PSS-Diag. You know, I’ve only ever run PSS-Diag when it’s been asked of me.
I’ve never, like, experimented with it as an application, like, on my own. And it seems like it’s, like, for some reason it just feels like it’s too late in the game for me to do that. Like, I’m not going to spin up PSS-Diag today and be like, ooh, look what I can do.
I feel like, you know. Like, those blog posts would have been great in 2008. Right?
Or, like, something like that. Now, if I’m like, look at this cool thing I can do with PSS-Diag. Someone’s like, yeah, but, you know, I have a monitoring tool. And it looks bad.
It’s just, it looks ugly. And I don’t know. Like, spits out a text file or something. And start, like, using, like, SQL Pal or SQL Nexus or whatever those things were that weren’t, like, the old troubleshooting tools.
Stuff that people use then. I don’t know. My dear, or rather, our dear friend Sean Gilardi seems to enjoy PSS-Diag.
So, maybe there is something. Maybe if someone starts blogging about PSS-Diag today, like, how do you use PSS-Diag? Damn it.
Maybe he’ll give it a shot. Maybe he’ll see what happens. I’ve heard various rumors that other consulting companies are big fans of using PSS-Diag as a data collector. So, I don’t know.
Maybe there’s hope for it. Maybe there’s hope for me. Maybe I’ll become the king of PSS-Diag, the prince of PSS-Diag. I don’t know.
We’ll see what happens. But I’m really excited about Perfmon. I think I might start using Perfmon, like, casually. The way it looks now.
Whoever redesigned that was a genius. A genius! Pure genius. I was out in Portland last Friday. Doing a pre-con for SQL Saturday Portland.
That was a lot of fun. I forget how many people were there. Good chunk of people. Good chunk.
Good chunk of happy learning people. I had someone join late, too. So, there was, like, one person who couldn’t get a seat in the room. They had to, like, use two chairs as a desk. So, I appreciate their tenacity in the matter.
Yeah. It’s a good time. Yeah. Total server performance. I like that material a lot.
I generally like the flow of the day. I like teaching people about when hardware sucks. And how queries look when hardware sucks. And then kind of getting into how queries can still be bad, even when hardware is good.
You know? I think it’s a good set of lessons for the day. I am working on… Yeah, you were there at SQL Bits.
Which I… Even more exciting. Holy cow. Simon tweeted earlier today that he has a contract for SQL Bits on his desk. I thought it wasn’t going to happen.
I was, like, nervous. Because February… Like, usually I know if I’m going to be at SQL Bits in, like, August. And this year there was nothing. I, like, emailed them. I was like, what’s going on?
I didn’t hear anything. And I was just like, man, is this not happening this year? Like, am I going to have the saddest year of my life in which I don’t go to SQL Bits? And then there was a tweet today about he has a contract for it. So, can’t wait.
Can’t wait. I would even be happy if it was in Manchester again. Like, if SQL Bits is in Manchester, I will be there. I will go to the Britain’s Protection. I will hang out.
That was a good bar. That was a fun place to get drunk. With Penal. So, yeah, I would happily do that. You know, it’s a fun session.
You know? I’m working on new stuff, of course. You know, I got to keep the material rolling. I got to keep it fresh. Especially with 2019 out, I got to make sure that I’m teaching people about not just, you know, stuff that can go wrong today, but stuff that still isn’t fixed in the future. You know?
It’s a… People keep saying that, like, you know, performance tuning is dead. I’m not going to need performance tutors anymore. I’m like, yeah, okay. Okay. I believe you.
I believe you. Me and the… Let’s see. I don’t know. I don’t have a count right now, but I would bet that I have had about 60 clients this year. And that’s not bad for a fella just starting out in his first year of consulting all by himself.
I would bet that performance tuning is not quite dead. Thoughts on accelerated database recovery. So accelerated database recovery is a feature that makes rollbacks very fast.
Rollbacks used to be very, very slow, single-threaded duty head operations. Now they are very fast. They are nearly instant because of something in the database called the persistent version store, which I hope will be used to get rid of spools and execution plans.
But that’s besides the point. But yeah, it looks cool so far. It looks like a fun time.
It’ll be interesting to see what that does to a few different things, like database sizes. It’ll be fun to see what it does to… What do you call it?
Like, when I played with it, cleanup was, for some reason, really slow. It took, like, 17 minutes to clean up 7 gigs or something like that. I was unhappy with that. Lee says, oh, boy.
Lee, yes. Have you ever had query store refuse to force a query? No error, and it says it’s forced, but it won’t use it. Yes.
Yes, I have. In fact, in my blog post today, I talked about how a query that I wrote with a hint and said, SQL Server use this version of the execution plan for all of these queries, and it refused to do it. Outright refused.
I would use a query, like a use plan, like if I set up a plan guide for it, it would use that. But it would not… Query store refused to do it.
I was very upset about that. Kendra Little… Actually, the link might be in the post today. But Kendra Little has a blog post about morally equivalent plans, which I find fascinating because… It isn’t in there.
Okay. So I’ll… Little… Little… Morally… And…
Uh… Uh… Uh… There we go.
There we go. Yes. Kendra is fantastic. So there’s a good link in there. Um…
Yeah, it’s… Uh… So I had… I had query store refuse to… So the problem that I was facing was I had an entity framework query that had, like, a whole bunch of left joins inside, like, a derived join. And then that derived join joined back to, like, a base table.
And it was, like… It was pretty… Pretty crazy what was going on inside there. And, um… The query had a 70 second compile time.
7-0. 70… 7-0 second compile time, meaning that query… It took that query a minute and 10 seconds to get an execution plan. And…
Uh… When it was done, it finished in, like, 600 milliseconds. And the… Since it was entity framework, there wasn’t, like, a lot of rewrites we could do. But I could use a, uh…
I could use a force order hint on the query. And that would get it to finish instantly. Because rather than spend a whole lot of time trying to rewrite join orders, we could…
Or I could tell the optimizer to just join the tables in the order that the query is written. And when I did that, it finished instantly. And when I tried to force that plan in Query Store, Query Store did not honor that plan.
It didn’t say it failed. It didn’t say, no, thank you. It just said, cool. I appreciate the advice. And it just kept on doing what it was doing. Taking 70 seconds to compile this plan.
So… Made a plan guide. Plan guide worked. Query finished instantly. Every single time after that. Happily ever after.
Oh. Suppose that’s why they paid me several of the bucks. Get query…
I get query optimization. I optimize query optimization. From 70 seconds to 0 seconds. It’s amazing. It’s amazing.
Right? That was probably one of the more fun problems that I’ve run into recently, too. You know, is… When you’re a consultant and you work with enough people, you kind of see… I would say, like, if you took a bingo card, you could probably win it once per client if you put, like, the most common issues on there.
It’s very, very rare that someone has, like, a new, exciting, dangerous problem. No, it’s… No, it’s…
I would imagine… So, like, query store is one of those funny things where, A, you have to have enough people on 2016 who, B, turn it on, and C, look at it, and then D, try to solve a problem with it, and E, have that problem be forcing a query plan to really get adequate feedback on if it’s going to be a new problem. And, like, the future is working well or not.
And… And… Not enough people get all the way to E. So, I think there’s probably some bugs and some issues in there that Microsoft has yet to, you know, fully flesh out just because not enough people are using it. It would be like, for example…
An example that’s very close to home for me. An example of the writing of SPBlitz Query Store. Query Store enabled where they would run…
They would run those things. Look at it. Boy. You know, there might be a million bugs in there that I don’t know about because not enough other people have looked at stuff. I mean, I’m mostly annoyed that the way the Query Store tables are designed, it makes it impossible to get reliably fast queries from them.
But that’s another matter. How much consulting do you do for Azure SQL DBs? Not a lot.
Not a lot. Done some. Done a few. Even… I even consulted for someone using Azure… What do you call them? Elastic pools where they scale up as query traffic gets more intense.
And they were using a whole lot of columnstore and still having some issues. But, yeah, not a lot. I don’t hear a lot from those people.
Which is, you know… You know, I won’t say that’s fine with me because I don’t want to, like, you know… Act like I don’t want to consult for people who are using Azure SQL DB. But it is rather more difficult to, you know, run some basic checks and queries against Azure SQL DB.
Because of how it might change and because cross-database queries and everything are awkward. And, you know, there’s a lot of stuff that’s different. There’s a lot of stuff that, like, you can’t do or change with Azure SQL DB that would make my recommendations useless.
So, I don’t know. I’m happy if people are happy. Managed instances, though.
I can’t wait until I start getting some people on those. Because those are cool, fun, exciting, sexy new… I don’t know.
It’s like new lingerie for servers. I’m excited about those. Very excited about those. I can’t wait to hear what Microsoft ends up rebranding managed instances as. Azure SQL data warehouse is now Synapse or something.
Sounds vaguely like a gaming keyboard. So, I’m not excited about that. Whatever.
Anyway, we’re about at the half hour mark. You lovely few have kept me company for long enough. You can stop desperately trying to think of questions to ask. I should be here next week.
Maybe, probably, hopefully. We’ll see. Thanks for showing up. And I will see you next time. Goodbye. Bye.
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’ll sometimes see people implement retry logic to catch deadlocks, which isn’t a terrible idea by itself. The problem that may arise is when the deadlock monitor takes a full 5 seconds to catch a query, which can block other queries, and may generally make things feel slower.
Different Locks
An alternative is to set a lock timeout that’s shorter than five seconds.
DECLARE @lock_try INT = 0
WHILE @lock_try < 5
BEGIN
BEGIN TRY
SET LOCK_TIMEOUT 5; /*five milliseconds*/
SELECT COUNT(*) AS records FROM dbo.Users AS u;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 1222 /*Lock request time out period exceeded.*/
RETURN;
END CATCH;
SET @lock_try += 1;
WAITFOR DELAY '00:00:01.000' /*Wait a second and try again*/
END;
While 5 milliseconds is maybe an unreasonably short time to wait for a lock, I’d rather you start low and go high if you’re trying this at home. The catch block is set up to break if we hit an error other than 1222, which is what gets thrown when a lock request times out.
This is a better pattern than just hitting a deadlock, or just waiting for a deadlock to retry. Normally when a deadlock occurs, one query throws an error, and there’s no attempt to try it again (unless a user is sitting there hitting submit until something works). Waiting ~5 seconds (I know I’m simplifying here, and the deadlock monitor will wake up more frequently after it detects one)
The big question is: are you better off doing this in T-SQL than in your application?
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.
As far as indexing goes, I couldn’t find any performance difference between these two. They both take about 1 second.
CREATE INDEX smoochies ON dbo.Users(CreationDate, LastAccessDate);
CREATE INDEX woochies ON dbo.Users(LastAccessDate, CreationDate);
Indexing strategy will likely rely on other local factors, like any where clause filtering.
Monolith
A similar pattern will work across two tables:
SELECT MAX(x.Score)
FROM dbo.Posts AS p
JOIN dbo.Comments AS c
ON p.Id = c.PostId
CROSS APPLY( VALUES( p.Score ), ( c.Score )) AS x ( Score );
Though this is the worst possible way to write the query. It runs for around 10 seconds.
The indexes I have for this query look like so:
CREATE INDEX thicc ON dbo.Posts(Id, Score);
CREATE INDEX milky ON dbo.Comments(PostId, Score);
Reversing the key column order helps — the query runs in about 3 seconds, but I need to force index usage.
Of course, this is still the second worst way to write this query.
The best way I’ve found to express this query looks like so:
SELECT MAX(x.Score)
FROM
(
SELECT MAX(p.Score) AS Score
FROM dbo.Posts AS p
) AS p
CROSS JOIN
(
SELECT MAX(c.Score) AS Score
FROM dbo.Comments AS c
) AS c
CROSS APPLY( VALUES( p.Score ), ( c.Score )) AS x( Score );
The cross join here isn’t harmful because we only produce two rows.
And it finishes before we have time to move the mouse.
Mousey
Likewise, the faster pattern for a single table looks like this:
SELECT MAX(x.Dates)
FROM
(
SELECT MAX(u.CreationDate) CreationDate
FROM dbo.Users AS u
) AS uc
CROSS JOIN
(
SELECT MAX(u.LastAccessDate) LastAccessDate
FROM dbo.Users AS u
) AS ul
CROSS APPLY (VALUES (uc.CreationDate), (ul.LastAccessDate)) AS x (Dates);
Because we’re able to index for each MAX
CREATE INDEX smoochies ON dbo.Users(CreationDate);
CREATE INDEX woochies ON dbo.Users(LastAccessDate);
Of course, not every query can be written like this, or indexed for perfectly, but it’s gruel for thought if you need specific queries like this to be as fast as possible.
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.
This was originally posted by me as an answer here. I’m re-posting it locally for posterity.
The two reasons that I find the most compelling not to use SELECT * in SQL Server are
Memory Grants
Index usage
Memory Grants
When queries need to Sort, Hash, or go Parallel, they ask for memory for those operations. The size of the memory grant is based on the size of the data, both row and column wise.
String data especially has an impact on this, since the optimizer guesses half of the defined length as the ‘fullness’ of the column. So for a VARCHAR 100, it’s 50 bytes * the number of rows.
Using Stack Overflow as an example, if I run these queries against the Users table:
SELECT TOP 1000
u.DisplayName
FROM dbo.Users AS u
ORDER BY u.Reputation;
SELECT TOP 1000
u.DisplayName,
u.Location
FROM dbo.Users AS u
ORDER BY u.Reputation;
DisplayName is NVARCHAR 40, and Location is NVARCHAR 100.
Without an index on Reputation, SQL Server needs to sort the data on its own.
NUTS
But the memory it nearly doubles.
DisplayName:
DisplayName, Location:
This gets much worse with SELECT *, asking for 8.2 GB of memory:
It does this to cope with the larger amount of data it needs to pass through the Sort operator, including the AboutMe column, which has a MAX length.
Index Usage
If I have this index on the Users table:
CREATE NONCLUSTERED INDEX ix_Users ON dbo.Users ( CreationDate ASC, Reputation ASC, Id ASC );
And I have this query, with a WHERE clause that matches the index, but doesn’t cover/include all the columns the query is selecting…
SELECT u.*,
p.Id AS PostId
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.Id
WHERE u.CreationDate > '20171001'
AND u.Reputation > 100
AND p.PostTypeId = 1
ORDER BY u.Id;
The optimizer may choose not to use the narrow index with a key lookup, in favor of just scanning the clustered index.
NUTS
You would either have to create a very wide index, or experiment with rewrites to get the narrow index chosen, even though using the narrow index results in a much faster query.
NUTS
CX:
SQL Server Execution Times: CPU time = 6374 ms, elapsed time = 4165 ms.
NC:
SQL Server Execution Times: CPU time = 1623 ms, elapsed time = 875 ms.
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.
This was originally posted as an answer by me here, I’m re-posting it locally for posterity
Sup?
For SQL Server queries that require additional memory, grants are derived for serial plans. If a parallel plan is explored and chosen, memory will be divided evenly among threads.
Memory grant estimates are based on:
Number of rows (cardinality)
Size of rows (data size)
Number of concurrent memory consuming operators
If a parallel plan is chosen, there is some memory overhead to process parallel exchanges (distribute, redistribute, and gather streams), however their memory needs are still not calculated the same way.
Memory Consuming Operators
The most common operators that ask for memory are
Sorts
Hashes (joins, aggregates)
Optimized Nested Loops
Less common operators that require memory are inserts to column store indexes. These also differ in that memory grants are currently multiplied by DOP for them.
Memory needs for Sorts are typically much higher than for hashes. Sorts will ask for at least estimated size of data for a memory grant, since they need to sort all result columns by the ordering element(s). Hashes need memory to build a hash table, which does not include all selected columns.
Examples
If I run this query, intentionally hinted to DOP 1, it will ask for 166 MB of memory.
SELECT *
FROM
(
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u
OPTION(MAXDOP 1);
If I run this query (again, DOP 1), the plan will change, and the memory grant will go up slightly.
SELECT *
FROM (
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u
JOIN (
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);
There are two Sorts, and now a Hash Join. The memory grant bumps up a little bit to accommodate the hash build, but it does not double because the Sort operators cannot run concurrently.
If I change the query to force a nested loops join, the grant will double to deal with the concurrent Sorts.
SELECT *
FROM (
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u
INNER LOOP JOIN ( --Force the loop join
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);
The memory grant doubles because Nested Loop is not a blocking operator, and Hash Join is.
Size Of Data Matters
This query selects string data of different combinations. Depending on which columns I select, the size of the memory grant will go up.
The way size of data is calculated for variable string data is rows * 50% of the column’s declared length. This is true for VARCHAR and NVARCHAR, though NVARCHAR columns are doubled since they store double-byte characters. This does change in some cases with the new CE, but details aren’t documented.
Size of data also matters for hash operations, but not to the same degree that it does for Sorts.
SELECT *
FROM
(
SELECT TOP (1000)
u.Id -- 166MB (INT)
, u.DisplayName -- 300MB (NVARCHAR 40)
, u.WebsiteUrl -- 900MB (NVARCHAR 200)
, u.Location -- 1.2GB (NVARCHAR 100)
, u.AboutMe -- 9GB (NVARCHAR MAX)
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u
OPTION(MAXDOP 1);
But What About Parallelism?
If I run this query at different DOPs, the memory grant is not multiplied by DOP.
SELECT *
FROM (
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u
INNER HASH JOIN (
SELECT TOP (1000)
u.Id
FROM dbo.Users AS u
ORDER BY u.Reputation
) AS u2
ON u.Id = u2.Id
ORDER BY u.Id, u2.Id -- Add an ORDER BY
OPTION(MAXDOP ?);
There are slight increases to deal with more parallel buffers per exchange operator, and perhaps there are internal reasons that the Sort and Hash builds require extra memory to deal with higher DOP, but it’s clearly not a multiplying factor.
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 delivering my Total Server Tuning material, which has been a hit at a whole bunch of events this past year. It’s an eye-opening full day of training where you’ll find out all my favorite ways that things can go wrong with SQL Server hardware, queries, and indexes.
And of course, how you can outsmart SQL Server.
Which is pretty hard.
Like, doctors work on it and stuff.
Attendees has a choice to either follow along with me on their laptops, or just watch in horror as familiar events unfold before their very eyes.
If you want to follow along, grab a copy of the StackOverflow2013 database. It’s about a 10GB download, which turns into a ~60GB database.
Fair warning: if you’re gonna follow along, you’re gonna have a tough time on skimpy laptop hardware. My personal laptop is 64GB of RAM and some pretty fast cores. At least they were until Intel started patching things. Most demos are on SQL Server 2017, but I’m going to be showing you stuff from SQL Server 2019 as well.
See you there!
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.
It seems like every time I check out a server, the query plans are a nightmare. Users are freaking out, and management’s coffee is more Irish than Colombian.
Many times, the issue is that people are using presentation layer functions for relational processes. The where clause, joins, group by, and order by parts of a query.
These are things you should actively be targeting in existing code, and fighting to keep out of new code.
Nooptional
When you’re trying to get rid of them, remember your better options
Cleaning data on input, or via triggers: Better than wrapping everything in RTRIM/LTRIM
Using computed columns: Better than relying on runtime calculations like DATEADD/DATEDIFF
Breaking queries up: Use UNION ALL to query for either outcome (think ISNULL)
Using indexed views: If you need to calculate things in columns across tables
Creating reporting tables: Sometimes it’s easier to denormalize a bit to make writing and indexing easier
Using #temp tables: If you have data that you need to persist a calculation in and the query to generate it is complicated
Note the things I’m not suggesting here:
CTEs: Don’t materialize anything
@table variables: Cause more problems than they solve
Views: Don’t materialize unless indexed
Functions: Just no, thanks
More Work
Yes, finding and fixing this stuff is more work for you. But it’s a whole lot less work for the optimizer, and your server, when you’re done.
If that’s the kind of thing you need help with, drop me a line.
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.
If you guessed that they’d all return 1, you were right. That’s because DATEDIFF isn’t always very smart about measuring time.
All it measures is that the unit of time you’re interested in has increased or decreased.
Even though January 1st is the day after December 31st, the year is different, so it says there’s a year difference between them. Same with the month query.
For day it makes total sense here, but if you wanted to see a 24 hour difference, it might not go so well.
Anyway, it may not be measuring what you think it’s measuring.
Precision
If you want more precise measurements, you’re gonna have to get on that datemath post I wrote recently.
For example, to replicate DATEDIFF for this query:
SELECT COUNT(*)
FROM dbo.Posts AS p
JOIN dbo.Comments AS c
ON p.Id = c.PostId
WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) > 1
AND p.PostTypeId = 1
AND c.Score > 0;
You’d have to do something like this:
SELECT COUNT(*)
FROM dbo.Posts AS p
JOIN dbo.Comments AS c
ON p.Id = c.PostId
WHERE p.CreationDate < DATEADD(YEAR, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, c.CreationDate), 0))
AND c.CreationDate > DATEADD(YEAR, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, p.CreationDate), 0))
AND p.PostTypeId = 1
AND c.Score > 0
But all that tells you is that the creation dates have different years. It doesn’t tell you if those creation dates are fully a year apart, either by measuring 12 months or 365 days (I know, leap years. Can it, Smokey.).
If you want dates that are a year apart, you need to do something like this:
SELECT COUNT(*)
FROM dbo.Posts AS p
JOIN dbo.Comments AS c
ON p.Id = c.PostId
WHERE p.CreationDate < DATEADD(YEAR, -1, c.CreationDate)
AND c.CreationDate > DATEADD(YEAR, 1, p.CreationDate)
AND p.PostTypeId = 1
AND c.Score > 0;
But to illustrate how inaccurate DATEDIFF can be, let’s look at the first few lines of this query:
SELECT DATEDIFF(YEAR, p.CreationDate, c.CreationDate) AS YearDiff,
DATEDIFF(MONTH, p.CreationDate, c.CreationDate) AS MonthDiff,
DATEDIFF(DAY, p.CreationDate, c.CreationDate) AS DayDiff
FROM dbo.Posts AS p
JOIN dbo.Comments AS c
ON p.Id = c.PostId
WHERE DATEDIFF(YEAR, p.CreationDate, c.CreationDate) = 1
AND p.PostTypeId = 1
AND c.Score > 0
ORDER BY YearDiff, MonthDiff, DayDiff;
The beginning of the results look okay. But towards the end of the dates with “one year” difference, things look uh…
Back To The Minors
Admitting Is The First Step
If you need precise date measurements, you can’t always rely on DATEDIFF.
Especially for larger gaps, you can get some rather odd results depending on how you’re defining what qualifies for your requirements.
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.
