UNION and UNION ALL seem to get used with the same lack of discretion and testing as several other things in T-SQL: CTEs vs temp tables, temp tables vs. table variables, etc.
There are many times I’ve seen developers use UNION when result sets have no chance of being non-unique anyway, and many times I’ve seen them use UNION ALL when there would be a great benefit to discarding unnecessary duplicates.
Even worse is when the whole query is written incorrectly in the first place, and both DISTINCT and UNION are dumped all over queries to account for unwanted results across the board.
For example, someone may test a query in isolation, decide that DISTINCT needs to be applied to that result set, and then use UNION when appending another set of results to the final query. Throw in the typical slew of NOLOCK hints and one is left to wonder if anyone even understands what correct output might look like at all.
The answer to most questions about the correct way to write a query of course hinge on the quality of the underlying data, and any observed flaws reported by end users or QA testers.
This all becomes quite difficult to wade through, because developers may understand the correct logic, but not the correct way to implement it.
Just An Onion
To start, let’s flesh out what each operator means in the most basic way.
Using a nifty SQL Server 2022 function, and the power of batch separator loops, we’re going to load the numbers 1-2 into two tables, twice.
CREATE TABLE
#t1
(
i integer
);
INSERT
#t1 (i)
SELECT
gs.*
FROM GENERATE_SERIES(1, 5) AS gs;
GO 2
CREATE TABLE
#t2
(
i integer
);
INSERT
#t2 (i)
SELECT
gs.*
FROM GENERATE_SERIES(1, 6) AS gs;
GO 2
Doing this will provide a unique set of the numbers 1-6 from both temporary tables.
SELECT
t.i
FROM #t1 AS t
UNION
SELECT
t.i
FROM #t2 AS t;
Which is not logically equivalent to doing this:
SELECT DISTINCT
t.i
FROM #t1 AS t
UNION ALL
SELECT DISTINCT
t.i
FROM #t2 AS t;
The first query will not only deduplicate rows within each query, but also in the final result.
The second query will only deduplicate results from each query, but not from the final result.
To avoid playing word games with you, the first query will return the numbers 1-6 only once, and the second query will return 1-5 once, and 1-6 once.
Some additional sense can be made of the situation by looking at the query plans, and where the distinctness is applied.
soon
To put things plainly: if you’re already using UNION to bring to results together, there’s not a lot of sense in adding DISTINCT to each query.
Precedence, etc.
To better understand how UNION and UNION ALL are applied, I’d encourage you to use this simple example:
/*Changing these to UNION makes no difference*/
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
/*Changing these to UNION makes a difference*/
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3;
Specifically, look at the actual execution plans for these as you quote out ALL from the lines below the second comment.
You may even be surprised by what comes back when you get to the final UNION-ed select!
Orthodoxy
There has been quite a bit of performance debate about UNION and UNION ALL. Obviously, using UNION incurs some overhead to deduplicate results.
When you need it for result correctness, I’d encourage you to think about a few things:
The number of columns you’re selecting
The data types of the columns you’re selecting
What data actually identifies a unique row
I’ve come across many queries that were selecting quite a long list of columns, with lots of string data involved, that did a whole lot better using windowing functions over one, or a limited number of columns, with more manageable data types, to produce the desired results.
Here is a somewhat undramatic example:
DROP TABLE IF EXISTS
#u1;
SELECT
c.CreationDate,
c.PostId,
c.Score,
c.Text,
c.UserId
INTO #u1
FROM dbo.Comments AS c
WHERE c.Score IN (2, 9, 10)
AND c.UserId IS NOT NULL
UNION
SELECT
c.CreationDate,
c.PostId,
c.Score,
c.Text,
c.UserId
FROM dbo.Comments AS c
WHERE c.Score IN (3, 9, 10)
AND c.UserId IS NOT NULL;
DROP TABLE IF EXISTS
#u2;
SELECT
y.CreationDate,
y.PostId,
y.Score,
y.Text,
y.UserId
INTO #u2
FROM
(
SELECT
x.*,
n =
ROW_NUMBER() OVER
(
PARTITION BY
x.UserId,
x.Score,
x.CreationDate,
x.PostId
ORDER BY
x.UserId,
x.Score,
x.CreationDate,
x.PostId
)
FROM
(
SELECT
c.CreationDate,
c.PostId,
c.Score,
c.Text,
c.UserId
FROM dbo.Comments AS c
WHERE c.Score IN (2, 9, 10)
AND c.UserId IS NOT NULL
UNION ALL
SELECT
c.CreationDate,
c.PostId,
c.Score,
c.Text,
c.UserId
FROM dbo.Comments AS c
WHERE c.Score IN (3, 9, 10)
AND c.UserId IS NOT NULL
) AS x
) AS y
WHERE y.n = 1;
In the first query, we’re doing a straight union of all the columns in the Comments table, which includes the Text column (nvarchar 700).
In the second query, the UNION has been replaced by UNION ALL, and I’m using ROW_NUMBER on the non-text columns, and filtering to only the first result.
Here are the query plans:
wild times
If you’re looking at the second query plan and wondering why you’re not seeing the usual traces of windowing functions (window aggregates, or segment and sequence project, a filter operator to get n = 1), I’d highly suggest reading Undocumented Query Plans: The ANY Aggregate.
Like I said, this is a somewhat undramatic example. It only shaves about 500ms off the execution time, though that is technically about 30% faster in this scenario. It’s a good technique to keep in mind.
The index in place for these queries has this definition:
CREATE INDEX
c
ON dbo.Comments
(UserId, Score, CreationDate, PostId)
INCLUDE
(Text)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
Is UNION Ever Better Than UNION ALL?
There have been a number of times when producing distinct results has improved things rather dramatically, but there are a couple general characteristics they all shared:
Producing unique rows, either via UNION or DISTINCT is not prohibitively time consuming
The source being unique-ified feeds into an operation that is time consuming
Here’s an example:
CREATE INDEX
not_badges
ON dbo.Badges
(Name, UserId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
CREATE INDEX
not_posts
ON dbo.Posts
(OwnerUserId)
INCLUDE
(Score, PostTypeId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
GO
DROP TABLE IF EXISTS
#waypops;
CREATE TABLE
#waypops
(
UserId integer NOT NULL
);
INSERT
#waypops WITH(TABLOCKX)
(
UserId
)
SELECT
b.UserId
FROM dbo.Badges AS b
WHERE b.Name IN
(
N'Popular Question', N'Notable Question',
N'Nice Question', N'Good Question',
N'Famous Question', N'Favorite Question',
N'Great Question', N'Stellar Question',
N'Nice Answer', N'Good Answer', N'Great Answer'
);
SELECT
wp.UserId,
SummerHereSummerThere =
SUM(ca.Score)
FROM #waypops AS wp
CROSS APPLY
(
SELECT
u.Score,
ScoreOrder =
ROW_NUMBER() OVER
(
ORDER BY
u.Score DESC
)
FROM
(
SELECT
p.Score,
p.OwnerUserId
FROM dbo.Posts AS p
WHERE p.OwnerUserId = wp.UserId
AND p.PostTypeId = 1
UNION /*ALL*/
SELECT
p.Score,
p.OwnerUserId
FROM dbo.Posts AS p
WHERE p.OwnerUserId = wp.UserId
AND p.PostTypeId = 2
) AS u
) AS ca
WHERE ca.ScoreOrder = 0
GROUP BY
wp.UserId
ORDER BY
wp.UserId;
Executing this query as UNION-ed results gives us a query that finishes in about 3 seconds.
not bad!
Note that the Distinct Sort operator chosen to implement the desired results of the UNION reduces the rows from 13,300,793 to 2,406,354. This is especially important when Lazy Table Spools are involved.
Here’s the query plan when it’s executed with UNION ALL:
bad!
Execution time goes from 3 seconds to 33 seconds. You may notice that the numbers on the inner side of the nested loops join are much larger across the plan, and that the Lazy Table Spool goes from about 900ms (2.587 seconds minus 1.612 seconds) to taking about 18 seconds (20 seconds minus 1.7 seconds). The Nested Loops Join also suffers rather dramatically, taking nearly 9 seconds, instead of the original 300ms, largely owing to the fact that it has to deal with 946,677,558 additional rows.
You’d suffer, too. Mightily.
Championship Belt
Choosing between UNION and UNION ALL is of course primarily driven by logical query correctness, but you should fully consider which columns actually identify a unique row for your query.
There are sometimes better ways of identifying uniqueness than comparing every single column being selected out in the final result set.
When you run into slow queries that are using UNION and UNION ALL, it’s usually worth investigating the overall usage, and if using one over the other gives you better performance along with correct results.
Where UNION can be particularly troublesome:
You’re selecting a lot of columns (especially strings)
You’re attempting to deduplicating many rows
You’re not working with a primary key
You’re not working with useful supporting indexes
Where UNION ALL can be particularly troublesome:
You’re selecting a lot of rows, and many duplicates exist in it
You’re sending those results into other operations, like joins (particularly nested loops)
You’re doing something computationally expensive on the results of the UNION ALL
Keep in mind that using UNION/UNION ALL is a generally better practice than writing some monolithic query with endless OR conditions in it.
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.
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I dive into a detailed review of the rules in the SQL query stress tool maintained by Erik EJ, a dear friend and colleague. While I’m not as familiar with non-SQL code, I managed to navigate through some of the C#-related rules and offer my insights. We cover a wide range of topics, from ensuring proper primary keys and natural keys to avoiding unnecessary `SELECT *` queries and using explicit column lists in `INSERT` statements. Along the way, we discuss best practices like wrapping transactions, using `TRY-CATCH` blocks for error handling, and avoiding cursors unless absolutely necessary. I also touch on some less common rules such as the use of `NOLOCK`, which can be risky in a production environment, and the avoidance of certain functions in the `WHERE` clause that can hinder query optimization. By exploring these rules, we aim to improve our coding practices and ensure more efficient and reliable SQL queries.
Full Transcript
Erik Darling here with Darling Data. You have to forgive me. I’m very excited about this because it’s not often that I get to do these kind of reviews, so you’ll have to pardon my giddiness at this sort of thing. Anyway, he’s not a dear friend. There goes my hand. But I do hope someday I can call Erik EJ my dear friend. You’ll probably hear me talk about him on this channel in the context of the SQL query stress tool that, you know, Adam Mechanic originally made and that Erik now, not this Erik, this Erik up there, the smart-looking one up there in the corner, now maintains. But you posed a really interesting thing earlier, and it’s a project where when you build a DAC pack, there’s this code analysis that will run on there, and I was checking out some of the rules for it. If you follow this link, you’ll eventually get over to this link. And there’s some very interesting stuff in there.
I’m actually going to shrink myself for this video a little bit to make sure it stays, make sure that we can see as much of the screen as possible. So there I go, down into the corner. I’m going to be much smaller than usual in this one, which for some of you just might be a relief. Might be a welcome relief to have a much smaller Erik in this video. I wish I had a balloon. I would have taken a little hit of helium when I shrunk myself. But we’re going to talk through some of the rules. I’m going to talk through some of my thoughts on some of the rules.
And hopefully we’ll all end up in a better place. So let’s start off at the top there. Missing natural key. So I should probably say at the beginning of this that I don’t read non-SQL code too well.
But if you’re the type of person who does, and you can read, I believe it’s all C-sharp stuff, since it ends in .cs, you can read through what a lot of these rules do. I’m going to sort of stumble blindly through just some of the descriptions. And if I’m wrong about anything, you can just ignore me. I promise. I’m okay with being wrong when it comes to this stuff.
Because, you know, you’ve got to be wrong sometime in your life. So first one, missing natural key. I’m not really sure how you could add a natural key to a table. You may have intentionally denormalized a table. You may be using entity attribute value tables for things.
I’m not really sure how the detection works on that. So we’re going to move on. The next one up is table missing primary key. You know, most OLTP systems, you want that. If it’s a data warehouse, who cares?
If you’re the type of person who has primary keys and foreign keys and unique constraints and stuff in a data warehouse, may we never meet would be unfriendly. Avoid wide primary keys. Yeah, I’m cool. I’m generally cool with that.
Primary keys should avoid using GUIDs or wide VARCAR columns. Sure. Yeah, yeah. It’s not a very good choice, admittedly. The one thing I’ll say in favor of GUIDs, though, is it’s really hard to run out of them.
It’s very easy to run out of integers. I’ve seen that a lot of times. These days, I think, if you’re just not sure, make your primary key. Make that identity column a big int. You’ll have a hard time going wrong there.
If I hear anyone from the peanut gallery talk about storage size and how big ints are 8 bytes and ints are 4 bytes, you can bite me. It’s a wrong thing to concentrate on. Maybe if you were on crappy old spinning disks on a 32-bit system, then…
Well, I mean, you wouldn’t have big ints on a 32-bit system, would you? Anyway, keep going. Index on foreign key columns on both sides of a foreign key should be indexed.
Hell yeah, they should. Avoid long car types. Yeah, absolutely. That’s a dumb thing to do. I’d say that most of the time, you should avoid car and car types anyway.
There’s nothing wrong with bar car and bar car anyway. Let’s see. Avoid using select star. So, there are a couple places where this won’t matter.
And I’m not sure how… Again, me not being able to read. Me no read code good.
I’m not sure how in-depth the rule checks are. But if you do select star, like, in an exists clause like this, or if you do select star in, like, a cross-apply or, like, a derived join or something, but then you name the columns outside of that, it’s not, like, using select star for real.
It’s, like, you know, it doesn’t matter for the exists, and for here you actually pick the columns at some point. So, this is one place if the rule doesn’t go deeper.
It might want to. I’m not… I’m terribly, terribly inexperienced writing these types of linters. I don’t know how hard that would be, but…
You know, something to consider anyway. Let’s see. Non-transactional body.
Wrap multiple action statements within a transaction. Sure, I would say… It would even go a step further. I think there should be a check to see if set exact abort is set to on in a stored procedure, because if you’re going to put multiple action statements into a transaction, you probably don’t want to leave any of those hanging if the transaction fails and not have all of them rolled back.
So, that’s probably something you want to think about there. Low identity seed value. Start identity columns used in a primary key with a seed of 1,000 or higher. I have no idea why that’s a thing.
To be very honest, you have no clue why that’s a check. Equality compare with null rule. Equality and inequality comparisons involving a null constant found.
Use is null or is not null. You’re going to have a tough time if you use an equality predicate on null. You’re going to have a bad day.
Let’s see what else we got here. Unused variable. Variable declared but never referenced or assigned. To be honest with you, I’m relieved every time I see a variable get declared and then never used. Usually when that happens, they get used in a where clause.
And I think that should actually be a check. If you’re using local variables in a where clause, that should be in the performance check there. Let’s see.
Unexpected or expected error handling. Wrap multiple action statements within a try catch. Hell yeah, I’m on board with that. Top without order by. Top without order by, you guarantee nothing. Unless you wrote your query very, very carefully to only guarantee one row can ever match for something.
Yeah, you’re going to have a bad time if you write top without an order by. I’ll even go a step further and say if you write a top with an order by, but that column in the order by is not unique, you could also have a hell of a time, especially if your query goes parallel. Let’s see.
What did we get here? Implicit column list. Unused. Always use a column list with insert statements. Hell yes, you should. Don’t be lazy. Unused input parameter. I don’t think I care too much about that, but you know, clean up after yourself, I guess. Avoid deletes without where rule.
And then we have an unbounded update. So kind of two ways of saying the same thing. A delete statement without role limiting conditions and update statement without role limiting conditions. So again, I can’t read the code.
I’m not that smart. But there are a couple ways that you can write delete and update queries that do bound things that might look unbounded if you just focus on what’s underneath the delete or what’s underneath the update. So these would certainly bound any update or delete happening, but it might just seem unbounded if it’s just looking specifically for a where clause.
Redgate SQL prompt does this too, where like if you don’t have an explicit where clause on a modification query, it’ll freak out at you. You might have a join. You might, you know, have whatever going on there.
With the Redgate SQL prompt, you can actually fool it because you can actually put where one equals one. And just because it sees the word where after an update or delete or something, it’ll be like, oh, cool. You got, you got it.
Good job. You can just put some condition that always evaluates the true. And it’s like, oh, well, smart. Did it. You’re never going to delete everything in the table. You’re never going to update everything in the table because where one equals one, good enough for me.
Anyway, let’s get back to the list. Let’s get back to dark mode. SSMS is very bright, especially when I’m this little in the corner. So let’s see.
What else we got here? Avoid joining tables with views. I mean, I agree you should avoid it. Sometimes it is unavoidable. Sometimes it is inevitable. But, yeah, views are often quite deceptive in how bad and how deep.
How awful they are. Now, it’s not that, like, the view itself, right? Like, views as a construct, you can put totally fast queries in a view.
The problem is that people often hide really bad queries in views. So the worse the stuff you put in the view, the worse going into that view gets. And views often end up turning into sort of like a query results junk drawer where you just keep adding left joins and columns and more craziness to it.
And eventually life just spirals out of control. So, you know, avoid joining tables with views. I’m not going to say that’s wrong.
I’m just going to say that, you know, it’s really what lies beneath in the view that is bad rather than just the concept of a view itself. It also, it could be an indexed view, right?
Indexed views are usually pretty spiffy when you join to those. Let’s see. Incomplete or missing join predicate. So I guess you could figure this out if you were looking at, like, foreign keys and they’re like a multi-column foreign key and you’re only joining on one column from it or something.
But I’m really not sure how you would figure out that part. Consider using exists instead of in rule. So consider using exists instead of in when used with the subquery.
All on board with that. In and not in, I’m fine using that. If you have a list of, like, literal values, like, you know, like strings or numbers or something. But as soon as you have to go look at values in another table, absolutely.
Use exists and not exists instead. Let’s see. Avoid exec or execute. Use parameterize sp execute SQL instead. Hell yes, I am all on board with that.
Avoid order by with numbers. Yeah, that’s really annoying. Bush league laziness. You don’t, like, if you have an expression in your query that’s, like, long and complicated, the nice thing is, because of the laws of logical query processing, you can just put whatever you name that expression in the order by, it works.
You don’t have to repeat all that stuff. You don’t have to, like, copy and paste some big long math. Down in your order by, you just say, like, math equals big long math thing and then order by math. And then you’re fine.
Let’s see. Unspecified type length. Do not use string types without specifying a length. Hell yes, you shouldn’t do that. It’s dumb. Unspecified precision or scale.
Do not use decimal or numeric data types without specifying. Yeah. If you don’t do that, you might as well just use float. Lazy son of a gun. Consider column prefix rule.
Consider prefixing column names with table. Yes. Yes. Make your query readable. Make sure people know where things are coming from. The least you can do is basic decency. Avoid use of hints.
I’m big on hints, so I’m not going to touch that one. Sometimes you know better than the optimizer. Avoid using car index. Avoid using car index function in the where clause.
So this one’s a little weird because I know from reading further down that there’s a general check for functions in the where clause, which I assume would cover stuff like is null, substring, replace, left, right, car index, pad index, any of that stuff.
So it’s a little weird that there’s like a solo rule for that up here because car index is not the only thing that’s crappy in a where clause. Also, when we’re looking at stuff like this, it’s not just the where clause.
If you use functions in a join clause or if you use functions in an order by, you can also be in for a really bad time because you screw up SQL Server’s ability to use indexes in a nice orderly fashion.
It’s not like SQL Server can’t use indexes. It just can’t use them as well as it can when you write things cleanly in a way that doesn’t, you know, is null, replace, replace all the letter M with the number 52 and order by that or something. Ridiculous.
What else do we have here? All right. Avoid use of or in a where clause. So if you use or on a single column, like in the context of the Stack Overflow database, if you were to say where post type ID equals one or post type ID equals two, that’s not going to hurt you too much.
I mean, you’re going to be way better off if that is indexed in a way that makes finding ones and twos helpful. Where things get really screwy is when you spread that across columns, especially across columns and different tables.
That can certainly get messy, especially when you start involving things like joins, because if you have a left join and you’re like where like inner table column equals this or outer table column equals something else, you end up with these late filters in your query plan that get really awful.
I would say a better check would be to avoid the use of or in a join clause because if you do like join, like I’ve talked about this a million times on my blog and videos, but if you do like join, like users to post on like, you know, this equals this or this equals that, joins with or clauses really screw things up like 99% of the time or something.
I made that number up, but it’s close enough for me. Let’s see. Avoid cursors.
Well, sometimes you need a cursor, right? Sometimes, sometimes you need a cursor, especially like if you have a store procedure that like dumps a bunch of like stuff into a temp table and then cursors over that temp table to call another store procedure.
There’s not like a better way to do that, right? If you place that with a while loop, it’s not like you buy yourself anything. So, um, I like it as general advice, but you know, sometimes, sometimes you gotta use that cursor. Alright, uh, use of nolok.
Do not use the nolok clause. Hell yes. Don’t do it. It’s bad for you. Uh, especially because if, if you’re examining some code that’s going to end up in a DAC pack, it’s production code.
If you’re using nolokins in production code, you’re not writing production code. You’re not writing production quality code. Uh, let’s see here. Force delay. Do not use wait for delay statement and store procedures functions and triggers. Hmm.
If you have a store procedure with, I mean, the, first off, the good news is you can’t use wait for in a function because that’s a side affecting thing. It’s like you can’t use like, like new ID or new sequential ID or some of the other, uh, built-in stuff for that. Um, so that’s, that’s at least one thing.
You can probably remove the check for functions. But, um, if I have a, I mean, triggers, it’d be kind of stupid to use it, but, you know. But, uh, store procedure, sometimes you put a loop in a store procedure. You should have a wait for in there so you’re not just zooming through things.
Uh, let’s see. What else do we have here? Um, do not use set row count yet. It’s deprecated. It’s kind of silly. Alias tables rule.
Consider alias and tables. Hell yes. Make your queries readable and understandable. Uh, object schema not qualified. Yeah. If you’re not qualifying object schema, you’re a bad person. Uh, avoid select into of temp table or table variables.
Well, I disagree with this one. Um, A, you can, well, I mean, A, you can’t select into a table variable. That’s, this is not allowed. You can’t do it.
Um, but for temp tables, I think it’s a very useful trick sometimes. Um, especially because, uh, with select into like 2016 plus you get nice fully parallel insert plan. It’s usually a good thing.
Um, I think I understand where this check is coming from though, because like if, you know, uh, the way that the query engine might infer some of the data types with select into might not be exactly what you would expect. So like it’s not always, like sometimes that happens, but for the most part, select into is pretty safe. Uh, possible side effects of implicit CAS.
The arguments of the function zero are not of the same data type. I, I don’t, I don’t understand that one. To be honest with you. Uh, raise error.
Uh, I, I don’t think I care much about that. Uh, excessive indexes on a table. Well, for me, indexes are all about quality over quantity. You might have what looks like excessive indexes, but all of those indexes are really useful to a lot of queries.
So, uh, I try not to, I try not to call indexes excessive unless I know a lot about how those indexes are used. Um, let’s see. Use of approximate data type.
Do not use rail or float. Yeah, pretty much. Uh, ambiguous column name. Avoid using columns that match other columns by name, but are different data type or size. That’s a really good one.
Uh, I remember a long time ago, I think I wrote a script or two to find those sort of things in a database. So I’m glad that this is a rule here because especially if you need to compare those columns to each other, then like, you know, you have two string columns.
One’s a varchar, one’s an nvarchar. Bad performance time. Uh, let’s see. Um, uh, expression reducible to constant. Uh, I don’t get it. So we’re going to skip that one.
Uh, do not use deprecated types rule. Yeah, you shouldn’t use text and text or image types. That goes for Microsoft too. Uh, duplicate overlapping indexes. I actually want to read the code for that one at some point because I’m, I would, I would like to see how they do it.
I would like to see if that’s easier in C sharp than it is in T SQL because it’s a real pain in the butt with that. Uh, explicit collation. Eh, whatever.
Uh, object level option override. I don’t know what that one is. Unsafe identity retrieval. Yeah, yeah. Don’t use added identity. Especially if you have multiple rows going into something. Uh, do not mix DML and DDL.
That’s, that’s actually an interesting one. Um, it’s not like it’s going to cause you any problems, but I’m all on board with this because I like when things are nice and organized. I would even go a step further and say like, you know, you shouldn’t be declaring stuff like halfway or most of the way through a store procedure.
You should be declaring as much as you can. Well, you should declare everything up at the top where you set it. It’s up to you.
It was between you and your maker. Um, you know, I guess for stuff like cursors, that’s a little different because you don’t want to like, you know, start opening and declaring and opening cursors up here. If you’re actually never going to use them, you might actually never actually get to a point in the code where those happen. Uh, let’s see.
Um, ordinary. Ordinal parameters used. Uh, always use parameter names. Hmm. So that’s interesting because there’s actually, um, a known performance increase, uh, when you use ordinal, uh, or positional parameters with, with, um, uh, natively compiled code. So, you know, not that anyone uses that anyway.
Uh, permission change in store procedure. I don’t, I don’t, I don’t care about that. Uh, invalid database options. I don’t know what that means.
Past because SQL Server is going to compile an execution plan for all of that, regardless of if, uh, your code path is actually explored. So, um, you get like cardinality estimation and query plans based on like a code path that might never have happened. And so you get like zero row or like one row expected and you get like this dinky little query plan.
But then you execute the store procedure and you actually hit that part and it’s like a lot of roads start jumping around. And yeah, it’s a bad time. Uh, let’s see here.
Uh, consider caching get date to variable. Uh, so if you need, uh, so there are two things with this. One, if you’re like updating a bunch of tables to like have a column be equal to like get date or assist date time, it makes more sense to cache it in a variable because then when you do the update, they’re all going to have the same, like you set the column to the variable.
They’re all going to have the same matching time rather than like the time of when the update happened. But if you’re going to put that in a join, oh, I guess, I mean, I’m going to say join or where clause because it could be either one. But, uh, if you’re going to put that in a join or where clause, you’re going to screw yourself because you’re going to get the local dense, local.
So you can get the density vector estimate and your queries performance probably going to stink and you’re going to stick a recompile hint on it or I don’t know, whatever. Uh, but yeah, so there are times when you should do that and times when you shouldn’t do that. Uh, avoid not for replication. I hate replication, so I avoid replication.
I am not for replication, even though I have two kids. Uh, let’s see. Nested views. Again, you know, views are only as bad as what you do with them, but nesting views, you know, you end up with multiple junk drawers and that’s not fun at all. Uh, unacred string pattern. Uh, try to avoid patterns and start with percent.
And yeah, that’s generally pretty crappy. Sometimes unavoidable, but you know, uh, aggregate of unique set. Avoid using distinct keyword inside aggregate functions. Well, sometimes, again, unavoidable.
Uh, I think the, you know, where things are interesting with that is because, like, if you’re doing that on, like, a single column in your query, uh, it’s generally not, like, that bad. Like, especially, I think I talked about this in a blog post recently. If you’re, like, saying select from users, left join post, and you want to count stuff from the post table, you’re going to want to, well, actually, no, it’s not distinct anyway.
Never mind. Uh, but, uh, sometimes you do need to count distinct things. Where things get tricky with SQL Server for queries that aren’t using batch mode, um, multiple distinct aggregates really make a crappy query plan.
Performance really eats it on that. Like, one, you can, one is usually tolerable, but, like, multiple distinct aggregates, things go out the window. Um, noisy trigger.
Well, I suppose you should put a silencer on that. Uh, so yeah, avoid returning results and set no count on your triggers. Yeah, absolutely. Uh, use of inequality.
Well, that’s not, that’s not quite right. Those, those are perfectly sargable things. If you, if you, if you want to figure out if something is not equal to something, I don’t know what else you’re going to do in a way that would be sargable. This is more about indexing than anything else.
So, um, make sure your indexes match your, match your where clause and things, things will go a lot better for you. Ah, this is probably my favorite one. Dangling cursor.
That’s what I’m going to be for Halloween this year. I’m going to be a dangling cursor. Uh, so yeah, close your cursors, deallocate your cursors. It’s generally, generally a good rule to follow.
Uh, filtering on a calculated value. Avoid wrapping columns within, so this is what I was talking about with car index. So this whole thing, avoid wrapping columns within a function in the where clause. Yes, avoid that.
Uh, let’s see. Function and data modification. Avoid the use of user-defined functions with update, insert, delete statements. So that one’s interesting because you can get around a lot of the Halloween protection issues with that if you use the schema binding hint with your, or the schema binding directive or option or whatever you want to call it. Uh, in your functions, you can get around, you get, you fix the Halloween protection stuff.
So, um, maybe the check looks for that. I don’t know. Uh, non-member test and predicate. Avoid using not in predicate in the where clause.
Well, it’s not exactly right either. Uh, not in is fine if you have a list of, like, literal values, like 1, 2, 3, 4, 5. But, uh, as soon as you go out and touch another table, you might come back with no’s.
It causes a lot of stuff. Uh, let’s see. Unindexed membership test. Consider indexing the column referenced by in predicates in order to avoid table scans. Oh, sure.
Index columns that are in your where clause. Index columns that are in your join. Seems like reasonable advice. I’m not sure why in gets special treatment.
Uh, let’s see here. Existence tested with join. Consider replacing outred. Yes. Absolutely. Do that. Table variable and join. Avoid the use of table variables and join clauses.
I’m on board with that, too. Uh, they tend to not be, not shine so bright when you do that stuff. You know, you know, we don’t want to get into the nitty-gritty of it, but I, on the, in general, I agree with that principle.
Avoid column calculations. Avoid the use of calculations on columns in the where clause. Absolutely.
That should also go for join clauses. Uh, quality test with mismatched types, data types. Yes. Make sure that your data types match. It’s a great thing. Uh, update of primary key.
Who in their right mind would do that? I mean, developers. So, you know, who knows what, who knows what they get up to when you’re not watching. So, I assume there’s a good reason for that check. Um, let’s see.
High join count. Well, yeah. I don’t know how many joins they consider high, but, uh, that, that, that, that, that can, that can cause issues. Uh, missing clustered index.
Hell yes. Yes. OLTP workloads generally should have clustered indexes. Manipulated parameter value. Yeah. Avoid modificational parameters prior to use. I mean, not just in select queries.
I mean, any, any query that you, you do that could be detrimental. Especially if, again, join. I mean, definitely where clause, though. Procedure level recompile option.
Consider using, yes, that’s actually good advice. Uh, query level recompile hints. Do a lot more than store procedure recompile hints. Enumerating for existence check.
When checking for existence uses, exists instead of count. Well, this one I disagree with a bit. See, when you use exists to check if something exists, uh, SQL Server internally sets a row goal. And when you set that row goal, you can end up with some really wacky query plans.
Or some really slow query plans. So I’m going to run three different queries here. Uh, the first one is the exist check, where you’re going to see row goals kick in and performance not be so great.
The second one is just one that I found amusing. And the third one is where, uh, select count big, uh, is actually, actually performs better than the exist check. So we’re going to look at these query plans now.
I didn’t set no count on. I should, I should be fined by the DACPAT committee. So, uh, when you do a select, so this is the, if, this is the, just the plain if exists. And we get this tiny little loopy little navigational plan here.
And, uh, we get this plan because SQL Server, uh, sets a row goal with these, um, these semi joins of one to just check if something is there or not. And depending on how the optimizer views its odds of finding something quickly, you can get these really, really sort of goofy query plans. And this part is particularly spectacular because we look through 37 million rows trying to find that one magical row, but we don’t actually end up finding anything.
So that drags on for a very long time. Uh, the thing with count is that if you just say we’re greater than zero, you end up with all the same execution plan, right? That’s no better, right?
You end up doing the same thing in here. Where count can be better is when you give SQL Server some loftier goals. So I said where count is between one and the integer max. I should have done the big int max, but, uh, cause I’m doing count big, but pay no attention to the little man in the corner.
But this query, and despite having a very strange query plan, this runs for 12 seconds. This runs for nine seconds. This query down here runs for three and a half seconds.
But because SQL Server is unable to set that, uh, that row goal, uh, in the same way, we get a nice query, uh, well, I mean, come on, get up. Uh, you know what? Screw it.
We’re just going to move that a little bit. SQL Server does some weird stuff with this query plan. I admit this is a bizarre looking query plan, but the performance of this is better. So, uh, I wouldn’t necessarily always want to do exists over count. Um, correlated subquery, avoid the use of correlated subqueries.
So this is sort of like the view thing. Uh, the correlated subquery is only as bad as what you put in it and is only as slow as the indexes you have to support it. So I’m not sure I totally agree with that check.
Uh, the ones down here, uh, UDF with system prefix procedure with SP. Well, I’m guilty of all of that. Uh, use of default schema. Um, I’m not sure, I’m not sure what that is.
And then general naming rules. I’m not sure what those are. So anyway, um, some good, a lot of good in here. Some stuff, I don’t know if it needs to be fleshed out more. I don’t know if there’s maybe some, I don’t know if maybe there is actually some stuff to do the additional stuff that I’m talking about and test first things a little bit further.
But, uh, I do think that this is a very good start to making, uh, setting developers on the right track. This just might need a little bit more detail and a few more levels of figuring out what’s going on before, um, before you flag these things as issues. Now, one thing I do like about this is it has a column called ignorable.
And a lot of this stuff is flagged as ignorable at times. So you don’t necessarily have to, um, punish yourself with, with all of these checks. Uh, especially if you, um, especially if you know that you’re doing okay with some of these things.
So, anyway, uh, a big thank you to, uh, Eric EJ and, um, I’m sorry, I, uh, there was a, I don’t know, actually, is it in here? Let’s see. Um, yes.
Well, actually, that’s just MS SQL build. Uh, so a big thank you to Eric EJ and to Jay Mazak. I apologize if I pronounce that incorrectly, uh, for putting this together because I do think it is a net positive for developers. But, um, you know, some of the rules, a little weird to me, but, uh, I don’t know.
Perhaps, perhaps, perhaps they’re better than they seem on the face of things. But, anyway, uh, thank you for watching. Uh, hope you enjoyed yourselves.
Hope you learned something. You liked the video? Give it a thumbs up. This is a longer one than usual. This is a half hour. Jeez Louise. Windbag I am. Uh, and if you like this sort of SQL Server content, please do subscribe to my channel. Um, that’s all.
All right. Cool. Thank you. Goodbye. I hit the button. Stop recording.
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.
Fixing Parallel Row Skew With TOP In SQL Server (With A Brief Re-Complaint About CXCONSUMER Waits)
Video Summary
In this video, I delve into the issue of parallel skew in queries and how it can lead to suboptimal execution plans. After receiving feedback on my previous video about CX consumer weights and detecting parallel skew through CX sync port weights, I decided to create a follow-up focused on fixing these issues. We start by examining a problematic query that took 15 seconds to complete due to uneven parallelism, with most of the work being done on a single thread despite an eight-way degree of parallelism. The solution involves introducing a `TOP` operator within a `CROSS APPLY` to better distribute rows and balance the workload across threads, resulting in a more efficient execution plan.
Full Transcript
Erik Darling here with Darling Data. I did tweak some audio settings, so hopefully there’ll be less noise gate cutoff of things, and only when I’m actually silent will you not hear any of the weird background stuff that goes on in my office or the frequent sirens that go by my house in lovely, crime-free New York. So today’s video, I don’t actually, I’ll tell you here, a while back I recorded a video about CX weights, like CX consumer and CX sync port, how to detect parallel skew in queries and how to figure out if that’s why your query is slow, and some of the feedback that I got from that, both on YouTube and and story and they have spend.
Y Oh,unnoV Mod and they notes3 very, but silk. how do I fix it? So finally got around to recording a video about how to fix it. So let’s do that.
Let’s talk about fixing it. But let’s go back in time a little bit and let’s look at what the problem is and some of the other surrounding stuff. So let’s make sure I have query plans turned on here. Actually, let’s make sure I have query plans turned on here ahead of time too, so I don’t go and screw anything up. And I’m going to run this whole kit and caboodle right now.
And we’re going to get a little bit of execution plan noise on this, but that’s okay because we’re doing stuff up here and then we’re doing stuff down here. But the bottom line is that this query should take around 15 seconds to complete. And there we go, right at the 15 second mark. There we go. If you look under my armpit, you’ll see 15 seconds down there. It’s amazing. It’s immaculate, right? So what happened? Why? What went on with this awful query? This abomination? Well, let’s start by looking a little bit at weight stats. And one of my chief complaints about a lot of things in SQL Server is that, you know, at some point in time, someone at Microsoft said, CX consumer weights, no big deal. Ignore them. Don’t ever think about them. But I think we can see pretty well here that CX consumer weights were a big deal for this query and that we should maybe pay attention to them because it’s a lot of time spent waiting on the old CX consumer.
Now, in the original video, one thing I noted was that the CX sync port weight, the time on that was just about even with the query execution time. And I think that might be another way that no one has said we should ignore yet to detect parallel skew in a query, which is great, you know, awesome, cool. So let’s look at the query plan. And let’s look at where things went wrong. And then we’ll look at how to fix it, how to fix it. All right. So the bad part of this query, or rather where the problems start in this query is over here, when we have this serial zone, it gets flipped to a parallel zone, and then sent into a nested loops join. And what happens is if we look at the properties of this thing, actually, that was really well set up. Usually, I’m not that well prepared, but here we go. All right. Well, actually, this part’s a little bit off. Maybe this wasn’t the greatest idea in the world. Let’s see if I can slide that over a little bit. There we are. All right.
Maybe that’s the secret. Maybe I’m just not loud enough. I should be loud and dumb. I watched the movie American Fiction last night, finally. It’s a very funny movie. And there’s a line in it that really stuck out to me, where it said, I forget, I can’t remember. I’m not good at actor names. I can never remember anyone. Like, I don’t know. I’m just bad at it.
But the star of the movie said something like, the dumber I act, the richer I get. I got a kick out of that. And I was like, wow, I got to try acting real dumb. Because apparently, lots of people who act real dumb make a lot of money. So maybe that’s a good tack for me to take in my career. I’m going to start acting dumb or dumber than usual, maybe. I don’t know. Maybe I’m not acting dumb enough. But if we look at the details here, I can stop moving around. We can see that all these rows end up on a single thread. That’s an eight-digit number. Because I have two fingers left up. So that’s an eight-digit number there of rows. So that’s 10 million rows, I think. Ended up on a single thread. And of course, we have a fully parallel execution plan. But having 10 million rows on a single thread kind of just makes it a serial execution plan. It doesn’t matter how many threads you throw at it. If all 10 million or if rows heavily skew towards a single thread, it’s not going to be a good time for your parallel plan. Especially a parallel nested loops plan.
And you should watch my video about parallel nested loops to learn more about why. It’s a good video. If you like this video and you subscribe to my channel, you will get notifications every time I pass on one of these remarkable nuggets of knowledge to you for free.
Amazing things will happen to your career. You might go from junior DBA to regular DBA to senior DBA to staff DBA to principal DBA to president of the DBA society. You never know. You never can tell. But the thing here is that this situation proceeds throughout the query plan. The 10 million rows still all on one thread. There you can see that. I can zoom in a little bit to make life a little bit easier than some of you old fogies who watch with your bad eyes. And if we highlight this arrow right here, I mean, it’s going to be fewer rows on this one. Oh, what happened there?
Oh, you know what? The compute scale I messed things up. You can look at this one. But still, all of the rows are on a single thread, right? So we had essentially a parallel query where one thread did all the work, right? And that’s not good because this query ran at a degree of parallelism of eight. The ocho, as they say. Eight, right? And the reason why I brought up the weight stats over here, right, where you can see CX consumer loud and clear as a session level weight stat is because if you look in the query plan, CX consumer, at least I hope it’s going to not be there because it’s never been there before. If it’s there now, I’m going to lose my mind. But it’s not.
Okay, great. So I was actually writing a blog post because when this stuff originally started coming around, one of the things that I complained about was that lock weights didn’t show up in query plans. And then I went to complain about it in another blog post. And apparently they show up now. So I should probably actually write a blog post to correct my other blog post. That’s not a bad idea.
If someone can leave a comment on this video reminding me to do that, I would really appreciate it. So yeah, so CX consumer weights do not show up in here, but those CX sync port weights do, right? So we can see all that going on in there. That’s 15 seconds about, right? That’s about how long our query executed for. And if we look there, if we actually come back to the results, CX sync port, 14,779 milliseconds, which is 14.779 seconds. And well, that’s, you know what? Horseshoes, hand grenades, government work.
We’ll take it. That’s close enough for me. All right. I don’t know why this window moved. It had no business moving. So we’re going to pop that back over there. And then, actually, before I move on, I should show you the part of the query that caused the problem, right? Because we have a problem area in this query. And that’s right here, this cross join, where SQL Server’s like, yep, one row is going to come out of there. And sometimes cross joins can be fine. Other times like this, we end up with, I don’t know what we just saw, where we have a fully parallel plan. SQL Server didn’t do a good job of splitting rows out to all the parallel threads.
And this is one of those unfortunate things, because we can’t really control what SQL Server does when we write things so plainly, right? We have very little control over what, how SQL Server is going to generate a query plan for these things. And with a cross join, I mean, there’s really nothing to correlate in here, right? We can’t do anything in here, because this max is an expression here, and we can’t really do anything down here. Like we could try with having, but that just gets weird and awkward. So the only place we can really apply the filter that we get from here is down in here, in the where clause. And now, this is where our trouble lives, right? It’s this whole thing. SQL server gets a single row from this and passes it along to the whole rest of the query. If we go back to the query plan, this area here, oops, I don’t need that tool tip. This area here is where SQL Server is getting that max, right? We have the index scanned, and then we get a top one, and then we aggregate that down to the, to get the max creation date. And then we use that max creation date is a filter in here, right? So we have where the post creation date is less than that scalar operator, which is an expression, which is how SQL Server has identified that max aggregate inside the cross join. All right. Cool. Got that sorted out. We think we know what the problem is. This query has very uneven parallelism. We know the part of the query that is causing that issue, right? It’s this part here, where it goes from a serial plan to a parallel plan. And then that’s where one, or rather one thread does all the work with all 10 million some odd rows. So what we can do to fix that in this, in this situation, and this is a technique that works quite well in many places, is to introduce another top into the query. So what we’re going to do is rather than cross join, we’re going to cross apply.
And inside that cross apply, because it’s safe to do so here, because we’re getting a single thing back from here, we’re just going to say, I want the top one. Oops, that’s not what I wanted. Wait, where did you go? That was strange. We’re going to say, we just want the top one from here. And then inside the cross apply, we’re allowed to do something that we’re not allowed to do inside of a cross join, which is have this inner correlation, right? So we can push that up into here. And I don’t know if you noticed from when I just ran this, but it didn’t take 15 seconds. It took zero seconds, right? So we can do more work inside of this, and we end up with a different execution plan overall, right? Rather than have that serial zone that turns into, come on, man, help me out. There we go. Nope, I almost had it.
There we go. Rather than have that serial zone coming out of the cross join, we now have a parallel zone down in this part of the query, right? And the nice thing about top, the great thing about tops is, even if this didn’t happen, what top does, aside from when it’s on the inner side of a nested loops join, what top will often do is force a serial zone in your query plan. And if your query goes from parallel to serial to parallel, one thing that it has to do when it comes out of the serial zone is distribute stream, right? It has to go send all those rows out to multiple threads so that you can, or rather so that your query can process rows with all the parallel threads that are available to it, right? Great, good stuff. So, excuse me, the Microsoft documentation on top, or rather on parallelism kind of sucks because it seems to infer that top forces your entire query to run single-threaded, but it doesn’t. There is just generally a serial zone going into a top operator, except when that top is on the inner part of a nested loops join, which it is here. We have our nested loops up here, we have our outer part of the nested loops up here, and then our inner part of the nested loops is down here. But this ends up pretty good for us. And it’s a pattern that you can generally apply. It might not always result in a query plan that looks like this, but what adding another top in can do is gather streams where it goes into the serial zone for the top, and then distribute streams where it comes out of the serial zone and back into a parallel zone as it leaves the top. And the results are all the same, and the weight stats for this, of course, look quite a bit better, even though we somehow managed to rack up five seconds of CX Consumer in a query that executed for 668 milliseconds. It’s a fun time, right?
Bit of a mind bender there, I admit. What were all those CX consumers consuming? I’d like to consume some of what they consume. But anyway, this was, I thought, a reasonable solution and a pattern that you can apply generally to your queries. If you’re examining a parallel query and you’re wondering, why the hell is this thing so slow? And you look at any of the sort of little arrows or any of the operators where data is passed along, and you don’t see something like this.
All right. This is a pretty even good parallel row distribution on parallel threads, right? There’s about 3,000 on each. That means every thread is going to do a pretty normal amount of work, or pretty equivalent amount of work. If you see all or most, or even like, like if you’re looking at the numbers, and let’s just say for the sake of having something to talk about that thread four had like, I don’t know, like two, three, four more digits than the rest of them, then that would be a pretty good, pretty good place to start with. Your plan is to figure out, well, how can I get better thread distribution so that I can have my parallel plan, my parallel threads, my parallel workers all doing an equal amount of work? Because if you don’t do that, one of them’s going to start expecting a raise, and you give one of them a raise, they’re all going to want a raise, and it’s going to be really hard to explain to them that, well, this one thread did 10 million rows worth of work, and you did zero. You don’t deserve a raise. Sorry. It’s not in the cards.
We had record-breaking fiscal year, but, you know, you get a bowling party, and you’ll get laid off next week. Fun times, right? All right. Anyway, I got other stuff to do, so I’m going to wrap this up. Top is your friend when you have uneven parallel threads, and you want a way within your query to naturally gather streams into a serial zone and redistribute them, hopefully in a more even manner, going back into a parallel zone. Cross-supply is another very useful thing for this, because, again, cross-supply will quite frequently be optimized out to a nested loops join, and when you get a parallel, well, I mean, when you have a, I’m going to say this very specifically, when you have a parallel nested loops join, and you have a top on the inner side of the nested loops join, that top will no longer introduce a serial zone into the plan. So, use your tops wisely. All right. Cool. Thank you for watching. Hope you enjoyed yourselves. Hope you learned something. If you like this video, please do give it the old thumbs up. If you like SQL Server performance tuning content, well, this is a good channel to subscribe to for it, as far as I can tell. It’s all I talk about, so might be worth doing that. Ever closer to 3,000 subscribers. I don’t know what I’m going to do when I hit that number. I might just drink on camera. You can all see what I’m really like.
All right. Anyway, that’s enough for today. Thank you for watching, and I will see you in another video at another time in another place. Actually, most of that was a lie. I’m going to be here. We’ll, might probably be another time.
Same bat channel, though. I’m not, not moving out yet. Anyway, that’s good enough.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
A Little About SOS_SCHEDULER_YIELD Waits In SQL Server
Video Summary
In this video, I dive into the fascinating world of SOS scheduler yield in SQL Server, providing insights that can help identify when your server is under CPU pressure. I share a personal anecdote about my vacation and how it influenced today’s content, making the explanation more relatable. We explore the states of queries—running, runnable, suspended, and sleeping—and use analogies like going to a bar to order drinks to explain these concepts in an easy-to-understand manner. The video also delves into practical examples using my demo server and a smaller VM called Server Tuning, showcasing how SOS scheduler yield accumulates under different query scenarios. By the end of this session, you’ll understand why high SOS scheduler yield is often indicative of CPU contention and learn strategies to mitigate it.
Full Transcript
Erik Darling here with Darling Data, the darlingest data there ever will be. And in today’s video, we’re going to talk a little about, because I like doing those little about videos, we’re going to talk a little bit about SOS scheduler yield. Fresh back from vacation, tan, rested, ready, haircut in full effect, feeling good. I tried to record this video a number of times and just been sabotaged by various things that were really too annoying to go into detail on, so we’re going to skip that. And we’re just going to talk about our good friend SOS scheduler because I think it’s an interesting way. It can be a pretty decent indicator that your server is under CPU pressure if SOS scheduler, just like a number of correlating factors in there, but like if SOS scheduler, is under CPU pressure if SOS scheduler is under CPU pressure pressure when SOS scheduler yield is constantly way at the top of your wait stats, and the total number of hours of SOS scheduler yield is always like at or near your server’s uptime or even over your server, especially over your server’s uptime, it’d be a pretty good sign that your query’s under CPU pressure because what SOS scheduler yield sort of indicates is that queries are waiting for CPU attention. The more time queries spend waiting to get CPU attention, the less time that means that they’re actually like running and like being productive little queries. But SOS scheduler yield basically means is that your query is ready to go. It’s ready to run, ready to rumble. I didn’t say the whole line so Michael Buffer can’t sue me, but it’s basically your query is good to go. It’s not waiting on like reads, writes, locks, pages, memory, disk, anything else, CX stuff. It’s just waiting for a CPU to say, come on, come exhaust your quantum with me and we’ll have a grand old time together. So the three, the four main states that your query can be in, there’s running, which just means your query is on a CPU doing stuff. It’s active, it’s going, right? Runable is when you see SOS scheduler yield weights. Suspended is any, basically any non-SOS scheduler yield weight, like any other weight type you see is going to show a suspended status, right? If you’re SOS scheduler yield, you runable, you’re not waiting on any other resources. Any other resource weight, your query will show it suspended while you go get, like you’re not allowed to get near a CPU until you have all the stuff you need. And then there’s sleeping, which means your query is finished and you should probably close some connections, fix connection pooling, whatever you did to make your query go to sleep and not, not close things out, hold locks and block a whole bunch of people and you know, that whole thing. Anyway, SOS scheduler yield weight, I usually like to explain this as like you going into a bar and trying to order a drink. Now, you’ve got this sort of list of points up here and one thing that I want to point out about this list of points, point about the points, is that it has been a real long time since I’ve been carded for anything. You know, I started drinking in bars in my early teens because my voice changed early, basically had the same voice since like 1993. I could, I started being able to grow sideburns really young and I started smoking cigarettes really young. So I quit obviously because responsible thing to do, but you’re a 13 year old with a deep voice and sideburns. You walk into a bar and you smack down a pack of cigarettes and a Zippo and you just order like whatever cheap domestic beer. No one really questioned you. I just kind of act like a jerk. Bartender’s like, well, he’s been here before. So some advice for the young kids out there, all the youths who watch my SQL Server video. But it’s sort of like, you know, you are the query.
You walk into a bar. The bartender is a CPU. Your job is to have everything you need to get that bartender’s attention and say, this is what I want and have the bartender start working on it. So various things might get in the way, needing an ID, needing money, bouncers, or a long line of patrons who are also trying to get to the bartender to get their attention, to get them to make their drinks, right? And the last thing you want is a bunch of drunk people waiting a long time for drinks.
That’s a recipe for a fight, right? Cutting in line, elbowing, that scene. So one thing about SOS Schedule Yield is it’s always going to have high signal wait time because it is a CPU signal. It is your query waiting for the CPU to say, yep, come on over, do your quantum dance with me, and you and me will get these results out to SSMS faster. Who would return results anywhere?
Export to Excel, I guess. Dump it into an Excel file like everyone else does. So when you have a lot of queries running and not enough CPUs to service them all, you may end up with really long lines of queries waiting to get CPU attention, which is when you’ll see SOS Scheduler Yield Waits really spike up, which is when your server might be under a lot of CPU pressure. Some interesting things that I find slow queries down and also keep CPUs very busy are things that have frequent executions, right? Like scalar UDFs, at least when they’re not in lineable, they get called once per row that they have to process, cursors, loops, stuff like that.
Those things will generally keep CPUs very busy and interfere with query processing stuff. Just keep the lines really long. It can be annoying. One thing that you don’t want to do in response to, well, actually you should step back a little bit. So one thing that you might also see if SOS Scheduler Yield Waits like really get high is you, not like, you know, casual drug use high. I mean, like time high. High on time. One thing that you might see eventually is as these lines grow is you might start hitting thread pool weights. And thread pool means that you’ve run out of worker threads.
And some people have the tendency to throw a whole bunch of, to manually increase the number of worker threads that SQL Server creates, is willing to create as queries execute. And what you’re going to find then is that SOS Scheduler Yield Waits are going to get driven even higher because you’re going to have way more worker threads vying for CPU attention. So that’s usually not a good response to thread pool and high SOS Scheduler Yield Waits because it’s the analogy I always use. It’s like adding like thousands of screaming kids to a classroom without adding more teachers to help the screaming kids. It’s a bad idea. So what we’re going to look at is sort of how SOS Scheduler Yield Waits accumulate in a vacuum. And then we’re going to look at how they accumulate when we have a bunch of queries lined up waiting for them. Now, for the initial stuff, we’re going to be using my normal demo server, SQL Server 2022. It’s about 100 gigs of memory. It’s about 16 CPUs on my fabulous new Vinovo laptop workstation beast. And then we’re going to use a much smaller VM called server tuning. And server tuning, I didn’t want to connect. I’m sorry. It’s not what I actually wanted. Go away. I just wanted to look at the settings. Server tuning is a much more modest VM.
It only has about 14 gigs of memory and two CPUs. So what we’re going to look at is when we throw a whole bunch of single threaded queries at that and look at how SQL Server sort of manages them. So we’re going to stick this into a new window. And this store procedure, well, this is just temporary store procedure. I actually have to switch this out. I’m not sure what happened.
I installed, like this used to run for about 10 seconds with the old number, but I like installed a bunch of like system updates and stuff. And all of a sudden, all of my, like it takes way more iterations for that to get up to 10 seconds of execution time. So I don’t know what happened, but it happened. And apparently there was like some like CPU fix that made them faster or something.
I don’t know. Maybe Windows 11 just got better at dealing with the P core and E core thing. I, I genuinely don’t know. But what this is going to do is some is sort of like the effect of like, if you have a scalar UDF or if you have a loop or a cursor or something where we’re just going to like, this is like attack, like swarm of gnats, stinging the crap out of you, biting your butt.
And what we’re going to do is dump some weight stat data about SOS schedule yield into a temp table, run the procedure, and then diff it. The reason why I use temporary, well, store procedures, but temporary store procedures here for this is to avoid async network IO weights. It can get annoying.
So let’s kick this, run this whole thing. And this should take about 10 seconds. If I lift up the right arm, we’ll see the time ticking away right about here. And 10, 11, close enough. So in 11 seconds of just incrementing this up, we have about 2,700 weights on SOS scheduler yield, and only about three milliseconds of total wait time on it. Two of those three seconds, probably because of some rounding stuff, were on signal weights. So that’s kind of what I was talking about. SOS schedule yield always going to be on the high side of signal weights. But what’s interesting to me, I think, here is how much work we’re able to get done in this loop while it’s executing before we hit an SOS schedule yield. So this is actually a very productive loop, right? It’s like we get a lot of, like, iterations done before the query’s like, okay, step off, my good, right back on, right? So cool, right?
It’s good stuff there. Now let’s look at when we have just one big slow. This should also run for about 10 seconds. I had to make some adjustments to this one too, to have it run for 10 seconds, because, you know, my CPUs just got awesome overnight. So we’re going to do the same thing here. We’re going to run this big query that just does a bunch of cross joints to a tiny table with a few extra union alls. We’re going to make it run at max.1, and then we’re just going to get a max of all this stuff. So let’s just kick this whole thing off. And this should also run for about 10 seconds, right about here. 10, oh, 10 on the nose. So this one here, just about the same number of waiting tasks in that time on, or waiting, like, waits on SOS schedule yield, and also just about the same wait time and signal wait time, right? Cool. So single threaded query running for 10 seconds, completely unfettered by anything else doing anything. There’s no other, nothing else is using CPU on my home laptop. Generally racks up a small amount of SOS scheduler yield, because we’re just off and right back on, right? We’re not, like, waiting in a big, long line to get back on, to get CPU attention, to get back on a CPU to run. So single threaded, right? Let’s do this same thing, except let’s, oh, wait, we’re in the wrong tab there. Sorry about that. Let’s do the same thing, except we’re going to run this at DOP 8. And this is just to show you that parallel queries, because we have multiple CPUs engaged, we’re scheduling DOP worker threads, they’re going to rack up a whole lot more waits on SOS scheduler yield. And that’s really the point of this thing. So we’re going to do all this. This should also run for just about 10 seconds. 10 on the nose. Look at that. Amazing, amazingly timed query. So what do we end up with? Well, we end up with a lot more waiting tasks, right? We have 20,000 there rather than like 2,300, 2,400 with the single threaded query.
And we also have a lot more wait time. And of course, because we have more wait time, we have a lot more signal wait time waiting to get CPU attention. So all those little parallel queries will drive up both the number of waits on SOS schedule yield, because we have DOP threads able to like get scheduled on CPUs and they all have to drop on, hop off, hop on, hop off, do their thing. And yeah, that’s about that there. So parallel queries are going to necessarily drive up SOS scheduler yield because they have more active requests on CPUs. And those active requests all have to get co-scheduled. So you have DOP threads getting on for four milliseconds about, you know, exhausting the quantum, stepping off, stepping back on. And depending on how lucky you are making, like hopefully all your parallel threads end up in different places and you’re not trying to like co-schedule stuff around, that would be fortunate. But yeah, that’s parallel queries. You’re going to see more SOS scheduler yield waits. Now, what we’re going to do is we’re going to, well, I already have that done, actually. We’re going to use SQL query stress by the lovely and talented Eric EJ on Twitter and GitHub and everywhere else. He took over maintaining SQL query stress when Adam Mechanic said, I’ve had enough of you SQL Server people. I’m going to go use Postgres like a normal human being.
And yeah, so Eric EJ, thank you for all, thank you for all you do. So I have this query already in SQL query stress and I have this window with SP who is active ready to go. I’ve sort of trimmed down the column list so we can just get to the stuff that I really want to show you. But if I run this and this is running at DOP1 and I keep hammering away on who is active, because this server only has two CPUs assigned to it, what we’re going to see is usually there’s going to be two requests active, right? So these two are running, these are doing stuff and the rest of these are runnable, right? So they’re like ready to go. They have everything they need to execute, but they just don’t have CPU attention. And if we run this a bunch of times, sometimes there will be one, sometimes there will be two. But in general, what we’re going to see is the same pattern where a bunch of queries are like, please give me CPU, please, I need it. Why are you making me wait for the CPU? And one or two queries just actively able to use CPU. Now, some of this does depend on timing and query placement on CPUs.
Like when there was only one active in there, it was probably just very, very bad luck with like where SQL Server chose to throw one of those queries. I can kill this off. I think I’ve pretty much exhausted the point on that. But this is when you see very lots of queries that are runnable, right? Like this, like if you have like, you know, constantly have like a long list of queries running and that most of that long list is runnable and very few things are actually executing, it’s a good sign that there’s some pretty serious CPU pressure, at least in that moment on your server. You can correlate that to more, to very high SOS schedule yield weights to figure out if it’s something that constantly happens or if it’s just something that happens once in a while when like things really heat up and you have like a really busy time on the server. So, you can look at SOS scheduler yield weights in general. I prefer SP pressure detector. I forget what, you know, something’s weird there. But if you run this and we look at the weight stats, I think I updated it recently to fix an issue. But if you look at weight stats, well, there’s SOS scheduler yield. Despite my, despite my fantastic demos, it is not very high on the list. The server has been up for a while, but I just haven’t been doing much to it. We have no hours of wait time, but what can you do? I suppose if I really wanted to burn my CPUs out, I could, I could do that. But I’d like this, I’d like this to last a little while. Not in a rush to spend another like five grand on a laptop because I burned the CPUs out after a few months. So, if anyone out there has like lots and lots of Azure credits they want to donate to me, I’d be happy to show you what CPU burnout looks like on Microsoft servers. So, when you have very high, so let’s say you’re in a situation where you constantly just have high SOS schedule yield weights, or when you hit your busiest times on the server, you have very high SOS schedule yield weights, you have lots of queries that are runable, but not run in, they can’t get CPU attention. The options that I would generally prescribe, well, getting faster CPUs is kind of tricky. Most folks are in the cloud or on a VM where, you know, you can’t really pick and choose that stuff. Back in the good old days when you get to build your own servers, you could be like a real gamer type and get like a lot of really fast cores.
So, you could have like, you know, like three, four, five gigahertz cores rather than like the older generation, like one to two point something gigahertz cores. That could help like queries get faster, like do more work within the time that they got on a CPU. You could also just get more CPUs when expand the number of CPUs available to work on queries. The more of those you have, the less time queries are going to be spent, the less time queries are going to spend vying for CPU attention in the crowd, waving 20s and 50s and 100s, trying to get the bartender to make them a drink.
You could also make sure that, you know, you have your various settings are in the right place. Parallel, parallelism settings can help with SOS schedule yield weights. Turning max stop down can sometimes help with that because you have fewer parallel threads spinning and trying to get CPU attention. Turning cost threshold for parallelism up can sometimes help because then you have fewer queries that are eligible for parallelism. It’s not always a great fix because, you know, you just like long-running serial queries are just going to, it’s unpleasant, right? You can cause a lot of performance regressions changing those settings. Those are global settings. You probably, your better bet would probably be to add hints to specific queries that are messing you up at those times.
You could also do some good old-fashioned queried index tuning, right? The faster queries can run naturally, the better off you are, the less time, less CPU time they’ll need. It’s all good for you. It’s all up. And again, stuff that stresses single CPUs, long-running serial queries, user-defined functions that can’t be inlined, cursors, other types of loops. And another big one that came up, actually it was something that we talked about with Joe Obisch on the last episode of Bit Obscene, is scheduled tasks and jobs that all occur simultaneously. You have a bunch of jobs that run every hour, 10 minutes, but on the hour you just end up busting your CPUs for no good reason. All right. Anyway, I have a dinner reservation. I’ve been talking for 20 minutes, parched. And I’m just happy to have finally gotten through this video without sabotaging. So if you liked the video, give it a thumbs up. If you like this sort of SQL Server content, subscribe to my channel. You’ll get notifications when I post these videos.
It’s good for you. Strong teeth and healthy bones, they say. And as always, I hope you enjoyed yourselves. I hope you learned something, and I hope that you’ll keep watching. So thank you. Thank you for watching. Thank you. You’re a great person. Follow me for more tips about underage drinking.
Probably don’t. Not a good idea. Don’t let your kids watch this. Unless you’re cool with that. I don’t know. Anyway, I’m gonna go now. 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.
Views get a somewhat bad rap from performance tuners, but… It’s not because views are inherently bad. It’s just that we’ve seen things. Horrible things.
Attack ships on fire off the shoulder of Orion… I watched sea-beams glitter in the dark near the Tannhäuser Gate. All those moments will be lost in time, like tears in rain…
The problem is really the stuff that people stick into views. They’re sort of like a junk drawer for data. Someone builds a view that returns a correct set of results, which becomes a source of truth. Then someone else comes along and uses that view in another view, because they know it returns the correct results, and so on and so on. Worse, views tend to do a bunch of data massaging, left joining and coalescing and substringing and replacing and case expressioning and converting things to other things. The bottom line is that views are as bad as you make them.
The end result is a trash monster with a query plan that can only be viewed in full from deep space.
When critical processes start to rely on these views, things inevitably slow to a crawl.
I’ve said all that about views to say that the exact same problem can happen with inline table valued functions. I worked with a client last year who (smartly) started getting away from scalar and multi-statement functions, but the end results were many, many layers of nested inline functions.
Performance wasn’t great. It wasn’t worse, but it was nothing to gloat and beam over.
The Case For Views
Really, the main reason to use a view over an inline table valued function is the potential for turning it into an indexed view. If Microsoft would put an ounce of effort into making indexed views more useful and usable, it would loom a bit larger.
There are some niche reasons too, like some query generation applications use metadata discovery to build dynamic queries that can’t “see” into inline table valued functions the way they can with views, but I try not to get bogged down in tool-specific requirements like that without good reason.
Both views and inline table valued functions offer schemabinding as a creation option. This, among other incantations, are necessary if you’re going to follow the indexed view path.
But, here we find ourselves at the end of the case for views. Perhaps I’m not digging deep enough, but I can’t find much realistic upside.
While doing some research for this, I read through the CREATE VIEW documentation to see if I was missing anything. I was a bit surprised by this, but don’t see it as a great reason to use them:
CHECK OPTION
Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.
If you’re into that sort of thing, perhaps this will make views more appealing to you. I’m not sure I can think of why I’d want this to happen, but 🤷♂️
The Case For Inline Functions
Now that we’re squared away on views, and we’ve made sure we’re starting with the understanding that either of these module types can be lousy for performance if you put a lousy query in them, and fail to create useful indexes for those queries to access data efficiently.
What would sway my heart of stone towards the humble inline table valued function?
Parameters.
Views can’t be created in a way to pass parameter directly to them. This can be a huge performance win under the right conditions, especially because if you use cross or outer apply to integrate an inline table valued function into your query. You can pass table columns directly in to the function as parameter values. Inline table valued functions take the ick away.
You know how with stored procedures, if you want to use one to process multiple rows from a table, the most workable approach is to use a loop or cursor to assign row values to parameters, and then execute the procedure with them?
Just an example, if you had a stored procedure to take (to make it simple, full) backups, it would be handy to be able to do something like this:
EXEC dbo.TakeAFullBackup
@DatabaseName AS
SELECT
d.name
FROM sys.databases AS d
WHERE d.database_id > 4;
But no, we have to write procedural code to get a list of database names, loop through them, and execute the procedure for each one (or some other close-enough approximation).
Kinda lame, SQL Server. Kinda lame.
Rat Race
When I first came across this oddity, I probably thought (and wrote) things like: “though this is a rare occurrence in views…”
Time has tried that line of thinking and found it wanting. I’ve seen this happen many, many times over now. It’s funny, the more things you learn that can go wrong in a query plan, the more things you become quite paranoid about. The mental checklist is astounding.
Let’s start, as we often do, with an index:
CREATE INDEX
p
ON dbo.Posts
(OwnerUserId, Score DESC)
INCLUDE
(CreationDate, LastActivityDate)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
Now, before we move on, it’s worth noting that this issue is fixed under certain conditions:
I’m not sure which CU this fix was released for in SQL Server 2019, it’s not in any that I can find easily
You’re on SQL Server 2022 and using compatibility level 160
From my testing, it doesn’t matter which compatibility level you’re in on SQL Server 2017 or 2019, as long as optimizer hot fixes are enabled.
/*Using a database scoped configuration*/
ALTER DATABASE SCOPED CONFIGURATION
SET QUERY_OPTIMIZER_HOTFIXES = ON;
/*Using a trace flag instead*/
DBCC TRACEON(4199, -1);
/*SQL Server 2022+ only*/
ALTER DATABASE StackOverflow2013
SET COMPATIBILITY_LEVEL = 160;
For our purposes, we’ll be using SQL Server 2022 in compatibility level 150, with query optimizer hot fixes disabled.
No Problemo (Query)
Here’s a view and a query, where things work just fine:
CREATE OR ALTER VIEW
dbo.DasView
WITH SCHEMABINDING
AS
SELECT
p.Score,
p.OwnerUserId,
p.CreationDate,
p.LastActivityDate,
DENSE_RANK() OVER
(
PARTITION BY
p.OwnerUserId
ORDER BY
p.Score DESC
) AS PostRank
FROM dbo.Posts AS p;
GO
SELECT
p.*
FROM dbo.DasView AS p
WHERE p.OwnerUserId = 22656;
GO
The reason this works fine is because the where clause contains a literal value, and not a variable or parameter placeholder.
iron seek
Everything is how we would expect this query plan to look, given the indexes available.
Si Problemo (View)
Where things become wantonly unhinged is when we supply a placeholder for that literal value.
CREATE OR ALTER PROCEDURE
dbo.DasProcedure
(
@OwnerUserId integer
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
SELECT
p.*
FROM dbo.DasView AS p
WHERE p.OwnerUserId = @OwnerUserId
/*OPTION(QUERYTRACEON 4199)*/
/*OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160'))*/;
END;
GO
EXEC dbo.DasProcedure
@OwnerUserId = 22656;
GO
Note that I have a query trace on and use hint here, but quoted out. You could also use these to fix the issue for a single query, but my goal is to show you what happens when things aren’t fixed.
Here’s what that looks like:
asbestos
Rather than a seek into the index we created, the entire thing is scanned, and we have a filter that evaluates our placeholder from 17 million rows and whittles the results down to 27,901 rows.
No Problemo (Function)
Using an inline table valued function allows us to bypass the issue, without any hints or database settings changes.
CREATE OR ALTER FUNCTION
dbo.DasFunction
(
@OwnerUserId integer
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
p.Score,
p.OwnerUserId,
p.CreationDate,
p.LastActivityDate,
DENSE_RANK() OVER
(
PARTITION BY
p.OwnerUserId
ORDER BY
p.Score DESC
) AS PostRank
FROM dbo.Posts AS p
WHERE p.OwnerUserId = @OwnerUserId;
GO
This changes our procedure as well:
CREATE OR ALTER PROCEDURE
dbo.DasProcedure
(
@OwnerUserId integer
)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
SELECT
p.*
FROM dbo.DasFunction(@OwnerUserId) AS p;
END;
GO
EXEC dbo.DasProcedure
@OwnerUserId = 22656;
And our query plan goes back to normal.
fastigans
Even if you don’t have this specific problem, it’s often worth exploring converting views to inline table valued functions, because quite often there is a common filtering or joining criteria, and having parameters to express that is beneficial in a couple ways:
It better shows the intent of module and what it can be used for
It prevents developers from forgetting filtering criteria and exploding results
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.
Whether you want to be the next great query tuning wizard, or you just need to learn how to start solving tough business problems at work, you need a solid understanding of not only what makes things fast, but also what makes them slow.
I work with consulting clients worldwide fixing complex SQL Server performance problems. I want to teach you how to do the same thing using the same troubleshooting tools and techniques I do.
I’m going to crack open my bag of tricks and show you exactly how I find which queries to tune, indexes to add, and changes to make. In this day long session, you’re going to learn about hardware, query rewrites that work, effective index design patterns, and more.
Before you get to the cutting edge, you need to have a good foundation. I’m going to teach you how to find and fix performance problems with confidence.
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.
Common table expressions remind me of clothes in the 70s. A bunch of people with no taste convinced a bunch of people with no clue that they should dress like them, and so we got… Well, at least we got the 80s afterwards.
The big draw with common table expressions is that they filled in some blanks that derived tables left unanswered.
The first problem with common table expressions is that most people use them like nose and ear hair trimmers: they just sort of stick them in and wave them around until they’re happy, with very little observable feedback as to what has been accomplished.
The second big problem with common table expressions is that the very blanks they were designed to fill in are also the very big drawbacks they cause, performance-wise. Sort of like a grand mal petard hoisting.
To bring things full circle, asking someone why they used a common table expression is a lot like asking someone why they wore crocheted bell bottoms with a velour neckerchief in the 70s. Someone said it was a good idea, and… Well, at least we got the 80s afterwards.
Much like joins and Venn diagrams, anyone who thinks they have some advanced hoodoo to teach you about common table expressions is a charlatan or a simpleton. They are one of the least advanced constructs in T-SQL, and are no better or worse than any other abstraction layer, with the minor exception that common table expressions can be used to build recursive queries.
Other platforms, enviably, have done a bit to protect developers from themselves, by offering ways to materialize common table expressions. Here’s how Postgres does it, which is pretty much the opposite of how SQL Server does it.
By default, and when considered safe, common table expressions are materialized to prevent re-execution of the query inside them.
You can force the issue by doing this (both examples are from the linked docs):
WITH w AS MATERIALIZED (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
Or go your own way and choose to not materialize it:
WITH w AS NOT MATERIALIZED (
SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;
You don’t get those options in SQL Server as of this writing, which really sucks because developers using other platforms may have certain expectations that are, unfortunately, not met.
Likewise, other sane and rational platforms use MVCC (optimistic locking) by default, which SQL Server does not. Another expectation that will unfortunately not be met for cross-platform developers.
Common Table Cult
The amount of developer-defense that common table expressions get is on par with the amount of developer-defense that table variables get.
It’s quite astounding to witness. How these things became such sacred cows is beyond me.
First, there are times when using a common table expression has no impact on anything:
WITH
nocare AS
(
SELECT
u.*
FROM dbo.Users AS u
WHERE u.Reputation > 999999
)
SELECT
n.*
FROM nocare AS n;
WITH
nocare AS
(
SELECT
u.*
FROM dbo.Users AS u
)
SELECT
*
FROM nocare AS n
WHERE n.Reputation > 999999;
SQL Server is at least smart enough to be able to push most predicates used outside of common table expressions up into the common table expression.
One example of such a limitation is when you put a windowing function into a common table expression:
WITH
nocare AS
(
SELECT
v.*,
n =
ROW_NUMBER() OVER
(
PARTITION BY
v.UserId
ORDER BY
v.CreationDate
)
FROM dbo.Votes AS v
)
SELECT
n.*
FROM nocare AS n
WHERE n.VoteTypeId = 8
AND n.n = 0;
If VoteTypeId were in the PARTITION BY clause of the windowing function, it could be pushed into the common table expression portion of the query. Without it in there, it has to be filtered out later, when the where clause also looks for rows numbered as 0.
But this does bring us to a case where common table expressions are generally okay, but would perform equivalently with a derived table: when you need to stack some logic that can’t be performed in a single pass.
Using a common table expression to filter out the results of a windowing function just can’t be done without some inner/outer context. Since objects in the select list are closer than they appear, you can’t reference them in the where clause directly.
I’m fine with that, as shown in the example above.
Common Stacks
Stacked common table expressions are also “fine” up to a point, and with caveats.
DECLARE
@page_number int = 1,
@page_size int = 100;
WITH
f /*etch*/ AS
(
SELECT TOP (@page_number * @page_size)
p.Id,
n =
ROW_NUMBER() OVER
(
ORDER BY
p.Id
)
FROM dbo.Posts AS p
ORDER BY
p.Id
),
o /*ffset*/ AS
(
SELECT TOP (@page_size)
f.id
FROM f
WHERE f.n >= ((@page_number - 1) * @page_size)
ORDER BY
f.id
)
SELECT
p.*
FROM o
JOIN dbo.Posts AS p
ON o.id = p.Id
ORDER BY
p.Id;
The reason why this is okay is because each common table expression has a single reference. There are two points in the query plan where data is acquired from the Posts table.
uno dos!
Where things get tricky is when you keep doing it over and over again.
Attack Stacks
Take a query like this, and imagine what the query plan will look like for a moment.
WITH
top5 AS
(
SELECT
p.*,
n =
ROW_NUMBER() OVER
(
PARTITION BY
p.OwnerUserId
ORDER BY
p.Score DESC
)
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
AND p.PostTypeId = 1
)
SELECT
u.DisplayName,
t1.Title,
t2.Title,
t3.Title,
t4.Title,
t5.Title
FROM dbo.Users AS u
LEFT JOIN top5 AS t1
ON t1.OwnerUserId = u.Id
AND t1.n = 1
LEFT JOIN top5 AS t2
ON t2.OwnerUserId = u.Id
AND t2.n = 2
LEFT JOIN top5 AS t3
ON t3.OwnerUserId = u.Id
AND t3.n = 3
LEFT JOIN top5 AS t4
ON t4.OwnerUserId = u.Id
AND t4.n = 4
LEFT JOIN top5 AS t5
ON t5.OwnerUserId = u.Id
AND t5.n = 5
WHERE t1.OwnerUserId IS NOT NULL;
An utter disaster, predictably:
moo 🐮
We hit the Posts table a total of five times, or once for each reference back to the original common table expression.
This is not a good use of a common table expression, and is a pattern in general to avoid when using them.
Think of common table expressions sort of like ordering a Rum Martinez. You might be happy when the results eventually show up, but every time you say “Rum Martinez”, the bartender has to go through the whole process again.
There’s no magickal pitcher of Rum Martinez sitting around for your poor bartender to reuse.
That’s called a Shirley Temp Table.
Pivot Peeve
This particular query could use a temp table to materialize the five rows, and re-joining to that would be cheap and easy, even five times, since it’s only five rows going in.
WITH
top5 AS
(
SELECT
p.*,
n =
ROW_NUMBER() OVER
(
PARTITION BY
p.OwnerUserId
ORDER BY
p.Score DESC
)
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
AND p.PostTypeId = 1
)
SELECT
t.OwnerUserId,
t.Title,
n
INTO #top5
FROM top5 AS t
WHERE t.n <= 5;
You could also also just PIVOT this one, too:
WITH
u AS
(
SELECT TOP (5)
u.DisplayName,
p.Title,
n = ROW_NUMBER() OVER (ORDER BY p.Score DESC)
FROM dbo.Posts AS p
JOIN dbo.Users AS u
ON p.OwnerUserId = u.Id
WHERE p.OwnerUserId = 22656
AND p.PostTypeId = 1
ORDER BY
p.Score DESC
)
SELECT
p.*
FROM u AS u
PIVOT
(
MAX(Title)
FOR n IN ([1], [2], [3], [4], [5])
) AS p;
For all the problems PIVOT can cause when misused, this is a full 19 seconds faster than our most precious common table expression query.
With a half-decent index, it’d probably finish in just about no time.
PIVOT TIME!
I’d take this instead any day.
A Note On Recursion
There may be times when you need to build a recursive expression, but you only need the top N children, or you want to get rid of duplicates in child results.
Since you can’t use DISTINCT, TOP, or OFFSET/FETCH directly in a recursive common table expression, some nesting is required.
Of course, we can’t currently nest common table expressions, and to be clear, I think that idea is dumb and ugly.
If Microsoft gives us nested common table expressions before materialized common table expressions, I’ll never forgive them.
WITH
postparent AS
(
SELECT
p.Id,
p.ParentId,
p.OwnerUserId,
p.Score,
p.PostTypeId,
Depth = 0,
FullPath = CONVERT(varchar, p.Id)
FROM dbo.Posts AS p
WHERE p.CreationDate >= '20131229'
AND p.PostTypeId = 1
UNION ALL
SELECT
p2.Id,
p2.ParentId,
p2.OwnerUserId,
p2.Score,
p2.PostTypeId,
p2.Depth,
p2.FullPath
FROM
(
SELECT
p2.Id,
p2.ParentId,
p2.OwnerUserId,
p2.Score,
p2.PostTypeId,
Depth = pp.Depth + 1,
FullPath = CONVERT(VARCHAR, CONCAT(pp.FullPath, '/', p2.Id)),
n = ROW_NUMBER() OVER (ORDER BY p2.Score DESC)
FROM postparent pp
JOIN dbo.Posts AS p2
ON pp.Id = p2.ParentId
AND p2.PostTypeId = 2
) AS p2
WHERE p2.n = 1
)
SELECT
pp.Id,
pp.ParentId,
pp.OwnerUserId,
pp.Score,
pp.PostTypeId,
pp.Depth,
pp.FullPath
FROM postparent AS pp
ORDER BY
pp.Depth
OPTION(MAXRECURSION 0);
To accomplish this, you need to use a derived table, filtering the ROW_NUMBER function outside of it.
This is a more common need than most developers realize when working with recursive common table expressions, and can avoid many performance issues and max recursion errors.
It’s also a good way to show off to your friends at disco new wave parties.
Common Table Ending
Common table expressions can be handy to add some nesting to your query so you can reference generated expressions in the select list as filtering elements in the where clause.
They can even be good in other relatively simple cases, but remember: SQL Server does not materialize results, though it should give you the option to, and the optimizer should have some rules to do it automatically when a common table expression is summoned multiple times, and when it would be safe to do so. I frequently pull common table expression results into a temp table, both to avoid the problems with re-referencing them, and to separate out complexity. The lack of materialization can be hell on cardinality estimation.
In complicated queries, they can often do more harm than good. Excuses around “readability” can be safely discarded. What is “readable” to you, dear human, may not be terribly understandable to the optimizer. You’re not giving it any better information by using common table expressions, nor are you adding any sort of optimization fence to any queries in them without the use of TOP or OFFSET/FETCH. Row goals are a hell of a drug.
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’ll be brief here, and let you know exactly when I’ll use IN and NOT IN rather than anything else:
When I have a list of literal values
That’s it. That’s all. If I have to go looking in another table for anything, I use either EXISTS or NOT EXISTS. The syntax just feels better to me, and I don’t have to worry about getting stupid errors about subqueries returning more than one value.
For IN clauses, it’s far less of an ordeal, usually. But for NOT IN, there are some additional concerns around NULLable columns.
Of course, actual NULL values really screw things up, but even when SQL Server needs to protect itself against potential NULL values, you can end up in performance hospice.
Historical
First, a little bit of history. With NOT IN. Take the below script, and flip the insert into the @bad table variable to use 2 instead of NULL, after you’ve seen what happens with NULL.
DECLARE
@good table
(
id int NOT NULL
);
DECLARE
@bad table
(
id int NULL
);
INSERT
@good
(
id
)
VALUES
(1);
INSERT
@bad
(
id
)
VALUES
(NULL); /*Change this between NULL and 2*/
SELECT
records =
COUNT_BIG(*) /*Should be 1, or something*/
FROM @good AS g
WHERE g.id NOT IN
(
SELECT
b.id
FROM @bad AS b
);
You’ll see pretty quickly that NOT IN gives you wonky results when it hits a NULL.
This is known.
Imaginary NULLs
Let’s take the below setup. Though each table allows NULLs in their single column, no NULL values will be inserted into them.
CREATE TABLE
#OldUsers
(
UserId int NULL
);
CREATE TABLE
#NewUsers
(
UserId int NULL
);
/*
But neither one will have any NULL values at all!
*/
INSERT
#OldUsers WITH (TABLOCK)
(
UserId
)
SELECT
p.OwnerUserId
FROM dbo.Posts AS p
WHERE p.OwnerUserId IS NOT NULL;
INSERT
#NewUsers WITH (TABLOCK)
(
UserId
)
SELECT
c.UserId
FROM dbo.Comments AS c
WHERE c.UserId IS NOT NULL;
The real lesson here is that if you know that no NULL values are allowed into your tables, you should specify the columns as NOT NULL.
I know, it’s scary. Really scary. Errors. What if. How dare.
But these are the kind of adult decisions you’ll have to make as an application developer.
Be brave.
Protect Your NULL
The big problem with NOT IN, is that SQL Server goes into defensive driving mode when you use it under NULLable conditions.
I don’t have a license because it would be irresponsible, and I’ve lived my entire life in big cities where having a car would be more trouble than it’s worth. But I assume that when I see people complain about drivers not knowing what to do the second there’s a rain drizzle or a snow flurry somewhere in the area is a similarly annoying scenario out there on the roads. All of a sudden, seemingly competent drivers turn into complete basket cases and drive like they’ve got a trunk full of dead bodies clowns.
Here’s an example of a bad way to deal with the situation, vs. a good way to deal with the situation:
/*Bad Way*/
SELECT
records = COUNT_BIG(*)
FROM #NewUsers AS nu
WHERE nu.UserId NOT IN
(
SELECT
ou.UserId
FROM #OldUsers AS ou
);
/*Good Way*/
SELECT
records =
COUNT_BIG(*)
FROM #NewUsers AS nu
WHERE NOT EXISTS
(
SELECT
1/0
FROM #OldUsers AS ou
WHERE nu.UserId = ou.UserId
);
Note the very professional formatting and correct syntax. Ahem. Bask.
Count to 10 while you’re basking.
Results
The resulting execution plan for each example should be illuminating. Here they are:
enabled buttons
The NOT IN version takes 15 minutes, and the NOT EXISTS version takes 415ms.
Reality Bites
Since there are no NULLs, the first query returns the correct results. But the amount of work SQL Server has to do to make sure there are no NULLs is both absurd and preposterous.
If you’re like me, and you want to throw the query optimizer in the garbage every time you see a TOP over a Scan, you might say something like “an index would make this really fast”.
You wouldn’t be wrong, but most people either:
Take bad advice and never index #temp tables
Create nonclustered indexes on #temp tables that don’t get used
Well, you get what you deserve pay for.
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 end up converting a lot of derived joins, particularly those that use windowing functions, to use the apply syntax. Sometimes good indexes are in place to support that, other times they need to be created to avoid an Eager Index Spool.
One of the most common questions I get is when developers should consider using apply over other join syntax.
The short answer is that I start mentally picturing the apply syntax being useful when:
There’s a small outer table (FROM), and a large inner table (APPLY)
I need to do a significant amount of work on the inner side of a join
The goal of the query is top N per group, or something similar
I’m trying to get parallel nested loops instead of some alternative plan choice
To replace a scalar UDF in the select list with an inline UDF
In order to use the VALUES construct in an odd way
Most of this is situational, and requires a bit of practice and familiarity to spot quickly.
Both cross and outer apply can be used in similar ways to subqueries in the select list, with the added bonus that you can return multiple columns and rows with apply, which you can’t do in a normal subquery.
What Apply Does
The way to think about what apply is doing when a query runs is supplying a table-valued result on the inner side of a join for each row supplied by the outer side of a join.
Here’s a simple example:
SELECT
u.Id,
u.DisplayName,
u.Reputation,
p.Title,
p.Score,
p.CreationDate,
p.LastActivityDate
FROM dbo.Users AS u
CROSS APPLY
(
SELECT
p.*
FROM dbo.Posts AS p
WHERE p.OwnerUserId = u.Id
AND p.PostTypeId = 1
ORDER BY
p.CreationDate DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY
) AS p
WHERE u.CreationDate >= '20131230'
ORDER BY
u.Reputation DESC,
p.CreationDate DESC;
We’re getting everyone from the Users table who Posted a Question in the final days of 2013, ordered by when it was Created.
For every qualifying User, we get a tabular result showing the Title, Score, CreationDate, and LastActivityDate of their question.
You can picture it sort of like this:
tabular
Some Users have have more than three results, and some may have fewer than three results, but since the query is self-limited to only the first three, our query sets a row goal and quits once three are found.
More About The Query
Because the Users table is correlated by Id to the Posts table on OwnerUserId, we need to make sure we have a good index to make that access seekable.
apply-ular
Also because we’re only looking for Questions (PostTypeId = 1), and ordering by the most recent ones (CreationDate DESC), it’s also a wise idea to have those in the key.
It’s also worth talking over an interesting point in the query itself: The select from Posts inside of the apply is doing a select star, sure, but the outer query is only pulling a few of the columns. The optimizer can recognize this, which means we don’t need a gigantic covering index to make this query fast. We also… Don’t really need a covering index at all in this case. Just the key columns are good enough.
CREATE INDEX
u
ON dbo.Users
(CreationDate, Reputation, Id)
INCLUDE
(DisplayName)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
CREATE INDEX
p
ON dbo.Posts
(OwnerUserId, PostTypeId, CreationDate)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
Efficiency Unit
An efficient query plan when using apply will look like this:
hello.
An inefficient query plan using apply will look like this:
oops
If you’re experimenting with apply, either cross or outer, pay close attention to the query plans. If someone says “cross apply is always slow”, you can bet they stink at indexes.
Choices, Choices
The choice to use apply at all depends on the goal of the query, and the goals of the query tuner. It’s not always a magic performance bullet, but under the right circumstances, it can really make things fly.
The choice to use cross or outer apply depends on the semantics of the starting query. An inner join commutes easily to cross apply, and a left join commutes easily to outer apply.
One important difference in how the joins are implemented is in the optimizer’s choice between normal nested loops, where the join is done at the nested loops operator, and apply nested loops, which is when the join keys are pushed to an index seek on the inner side of the join.
The optimizer is capable of transforming an apply to a join and vice versa. It generally tries to rewrite apply to join during initial compilation to maximize the searchable plan space during cost-based optimization. Having transformed an apply to a join early on, it may also consider a transformation back to an apply shape later on to assess the merits of e.g. an index loops join.
Just writing a query using apply doesn’t guarantee that you get the apply nested loops version of a nested loops join. Having solid indexes and easy to search predicates can help push things in the right direction.
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.
