I am sometimes asked to write special bits of code by people to solve a specific problem they’re having.
A recent one was for quite an unreasonable situation brought on by a shamelessly written vendor application, where:
Implicit transactions were in use
Bizarre locking hints abounds
Absolutely zero attention paid to transaction handling
Which lead to scenarios where select queries would run, finish, and never close out their connection. Of course, this was bad, because loads of other queries would get blocked by these things that should have just ended their sessions and released their locks and been on their way.
And so I wrote this thing. A thing that I’d always sort of made fun of the concept of, because I’d seen so many bad implementations of it throughout the years.
Most of them would just look for lead blockers and kill them without any consideration as to how much work they’d done, which would lead to even more blocking during rollback.
This one specifically looks for things that have used zero transaction log space.
Here it is. I don’t love it, but I wanted to share it, because it might make you feel better about some code that you weren’t proud to write, either.
Thanks for reading!
/*
EXEC dbo.sleeper_killer
@debug = 'true';
SELECT
sk.*
FROM dbo.killed_sleepers AS sk;
*/
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;
SET IMPLICIT_TRANSACTIONS OFF;
SET STATISTICS TIME, IO OFF;
GO
CREATE OR ALTER PROCEDURE
dbo.sleeper_killer
(
@debug bit = 'false'
)
AS
BEGIN
SET NOCOUNT ON;
/*Make sure the logging table exists*/
IF OBJECT_ID('dbo.killed_sleepers') IS NULL
BEGIN
CREATE TABLE
dbo.killed_sleepers
(
run_id bigint IDENTITY PRIMARY KEY,
run_date datetime NOT NULL DEFAULT SYSDATETIME(),
session_id integer NULL,
host_name sysname NULL,
login_name sysname NULL,
program_name sysname NULL,
last_request_end_time datetime NULL,
duration_seconds integer NULL,
last_executed_query nvarchar(4000) NULL,
error_number integer NULL,
severity tinyint NULL,
state tinyint NULL,
error_message nvarchar(2048),
procedure_name sysname NULL,
error_line integer NULL
);
END;
/*Check for any work to do*/
IF EXISTS
(
SELECT
1/0
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions AS tst
ON tst.session_id = s.session_id
JOIN sys.dm_tran_database_transactions AS tdt
ON tdt.transaction_id = tst.transaction_id
WHERE s.status = N'sleeping'
AND s.last_request_end_time <= DATEADD(SECOND, -5, SYSDATETIME())
AND tdt.database_transaction_log_bytes_used < 1
)
BEGIN
IF @debug = 'true' BEGIN RAISERROR('Declaring variables', 0, 1) WITH NOWAIT; END;
/*Declare variables for the cursor loop*/
DECLARE
@session_id integer,
@host_name sysname,
@login_name sysname,
@program_name sysname,
@last_request_end_time datetime,
@duration_seconds integer,
@last_executed_query nvarchar(4000),
@kill nvarchar(11);
IF @debug = 'true' BEGIN RAISERROR('Declaring cursor', 0, 1) WITH NOWAIT; END;
/*Declare a cursor that will work off live data*/
DECLARE
killer
CURSOR
LOCAL
SCROLL
READ_ONLY
FOR
SELECT
s.session_id,
s.host_name,
s.login_name,
s.program_name,
s.last_request_end_time,
duration_seconds =
DATEDIFF(SECOND, s.last_request_end_time, GETDATE()),
last_executed_query =
SUBSTRING(ib.event_info, 1, 4000),
kill_cmd =
N'KILL ' + RTRIM(s.session_id) + N';'
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions AS tst
ON tst.session_id = s.session_id
JOIN sys.dm_tran_database_transactions AS tdt
ON tdt.transaction_id = tst.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE s.status = N'sleeping'
AND s.last_request_end_time <= DATEADD(SECOND, -5, SYSDATETIME())
AND tdt.database_transaction_log_bytes_used < 1
ORDER BY
duration_seconds DESC;
IF @debug = 'true' BEGIN RAISERROR('Opening cursor', 0, 1) WITH NOWAIT; END;
/*Open the cursor*/
OPEN killer;
IF @debug = 'true' BEGIN RAISERROR('Fetch first from cursor', 0, 1) WITH NOWAIT; END;
/*Fetch the initial row*/
FETCH FIRST
FROM killer
INTO
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query,
@kill;
/*Enter the cursor loop*/
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
IF @debug = 'true' BEGIN RAISERROR('Insert', 0, 1) WITH NOWAIT; END;
/*Insert session details to the logging table*/
INSERT
dbo.killed_sleepers
(
session_id,
host_name,
login_name,
program_name,
last_request_end_time,
duration_seconds,
last_executed_query
)
VALUES
(
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query
);
IF @debug = 'true' BEGIN RAISERROR('Killing...', 0, 1) WITH NOWAIT; END;
IF @debug = 'true' BEGIN RAISERROR(@kill, 0, 1) WITH NOWAIT; END;
/*Kill the session*/
EXEC sys.sp_executesql
@kill;
END TRY
BEGIN CATCH
IF @debug = 'true' BEGIN RAISERROR('Catch block', 0, 1) WITH NOWAIT; END;
/*Insert this in the event of an error*/
INSERT
dbo.killed_sleepers
(
session_id,
host_name,
login_name,
program_name,
last_request_end_time,
duration_seconds,
last_executed_query,
error_number,
severity,
state,
error_message,
procedure_name,
error_line
)
SELECT
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query,
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_MESSAGE(),
ERROR_PROCEDURE(),
ERROR_LINE();
END CATCH;
IF @debug = 'true' BEGIN RAISERROR('Fetching next', 0, 1) WITH NOWAIT; END;
/*Grab the next session to kill*/
FETCH NEXT
FROM killer
INTO
@session_id,
@host_name,
@login_name,
@program_name,
@last_request_end_time,
@duration_seconds,
@last_executed_query,
@kill;
END;
IF @debug = 'true' BEGIN RAISERROR('Closedown time again', 0, 1) WITH NOWAIT; END;
/*Shut things down*/
CLOSE killer;
DEALLOCATE killer;
END;
END; --Final END
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.
Why You Should Avoid People Who Tell You To Avoid Subqueries In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the world of SQL Server performance tuning and take a critical look at the uninformed masses posing as experts on platforms like LinkedIn. These individuals often share advice such as avoiding subqueries and using `SELECT *`, which is not always sound or necessary. I explain why these blanket statements are misguided, providing examples from my own query analysis to illustrate that context matters greatly. By examining a specific query with multiple subqueries, I demonstrate how the real issue lies in inadequate indexing rather than the use of subqueries themselves. The video concludes with practical advice on creating an index that supports all parts of the query efficiently, leading to significant performance improvements.
Full Transcript
Erik Darling here with Erik Darling Data. Make sure that my enunciation is working. My new AI enunciation chip is in place. And I’m recording this video early today because this is the situation that we are dealing with currently in my neck of the woods. And this is, I think that there’s probably a good line out of Ferris Bueller’s Day Off about, how can I be expected to care about SQL Server on a day like this? The only thing we don’t like about today is this little guy down here. We don’t like this one. Because of this, there is a very high chance that I am going to have to turn off my microphone at some point in order to not sneeze in your face and ears during the recording of this video. I’ve already had to use allergy eye drops four times today because my vision would let my eyes get dry. My vision would start to get a little blurry and my vision would start to get a little blurry and I’d be like, oh, the Lord’s taking me. Blurry vision is the first sign. But it turns out just the, we thoroughbred nerds here at Darling Data have all of the allergies and all of the symptoms that come along with those allergies and one of them being dry, itchy eyes. So today we’re going to talk about my least favorite kind of people in the world.
And that is the uninformed masses posing as performance tuning experts on the internet, primarily on places like LinkedIn where people are supposed to be like visibly professional and know what they’re talking about. And there are people who say things like hot SQL tuning tips, hot performance tips. There’s like little fire emojis next to all these great ideas like don’t use select star, like avoid distinct. And like, like don’t use sub queries. And all of that advice is dumb. Like unmitigated dumb. There are times when select star is not harmful. There are times when distinct is not only necessary, but entirely helpful. And there are times when sub queries are just fine. Most of the people who say these things and make these blanket statements have no idea what they’re talking about. They’re like fitness people who are just like, yeah, bro, just eat less and exercise more. You can look like Ronnie Coleman. You can’t, you don’t, let’s not just diet and exercise.
You know, people who are just like, oh, well, you need to, you need to front squat. You know, those, the entire crossfit model with, with the wads and the people just doing idiotic, saying idiotic things and doing idiotic things in order to confuse their muscles. It’s a very, very sad state of affairs. I only know this because I, I, most of my feed is either databases or people doing squats. So it’s, it’s about what my internet looks like. So yeah, um, we’ve got this query here and this query is, you know, got a bunch of sub queries in it. All, they all go to the badges table.
All right. Well, every single one of them goes to the badges table. Even this, even this little exists down here. What you may not know is that exists is also a sub query. All right. This is a sub query. You can, you can, you know that exists is a sub query because if you try to create an indexed view and there’s an exists in it, you will get an error when SQL Server will say sub queries aren’t allowed in indexed views.
And you leave it. What? But it’s, it just exists. It’s just a little joined. What are you worried about? What are you worried about SQL Server? What idiot designed indexed views to not be able to use exists? Who would do that? Well, someone at Microsoft, let’s call them Sam, someone at Microsoft.
Microsoft. So, uh, this query, uh, is it admittedly on the slow side? Is it executed for, if I write, lift up the correct arm, you can probably see under my armpit there about 31 seconds. Uh, that’s not great, but you know, let’s look, let’s look at why. Let’s see. We’re, we’re using sub queries. Why, why shouldn’t we use sub queries?
A lot of people who tell you not to use sub queries will say, do a join instead. I’ve got bad news for anyone who thinks that a sub query does not end up doing a join because I can guarantee you, there are many joins in this query plan to implement all of those sub queries. There are a bunch of them.
A lot of people will also say that sub queries can only use nested loops joins, but there are folks out there. I got to be honest with you. SQL Server lies and obfuscates and, uh, is, is, you know, uh, less than forthcoming about many things. But these are all hash joins. Every single one of them, all hash joins.
Okay. So what, what, what are the slow parts of our execution plan? We’ve got, we’ve got, I think like five or six sub queries in the select list. So are they all slow? Not really. Only a couple of them are slow. And a couple of them are slow because we don’t have a good index to support the query that we’re running. And that’s really the, the root cause of, of many performance problems is just, you don’t have a good index in place to, to answer the question that your query is asking.
Indexing data is really important. And a lot of people are probably the same types of people who tell you to avoid sub queries are probably the same types of people who have no idea what the hell an index does in a database. So if we go and look, there are really only two slow parts of this query plan. I didn’t need that tool tip. SQL Server management studio. There are really only two slow parts of this query. We have two eager index pools that get built in here.
And these are both admittedly, they, they, they, they, they are using nested loops joins. And we, we, and just based on this query pattern and based on my, my just, you know, wizard like knowledge of SQL servers, query optimizer, I know exactly which two sub queries are causing these eager index pools. It’s, it’s going to be these first two, right? If I, if I quote this out, rather if I quote these two sub queries out with the top ones in them, and I rerun this query with all the rest of the sub queries still intact, boy, oh boy, we get a pretty fast execution plan.
And boy, oh boy, look at, look at every single one of these hash joins. We have no nested loops joins. What happened? Did, did someone, did someone on the internet lie to us maybe about sub queries, not being able to use hash joins or only being able to use nested loops joins?
Is someone on the internet stupid or dishonest somewhere in between? Is it, is it malice or ignorance? We’ll never know until we capture them and torture them.
Get the truth by hook or by crook. So wise man once said, So this is really just a case of us having inadequate indexes to, to answer a couple of the questions that our queries have attempted to ask of our data. And if we go and create this index here on the badges table, what we’re going to do is we’re going to fully support not only the two sub queries that we had up, up in the top that were causing the eager index pools, we’re going to help all of the sub queries really.
And this, this index only took three seconds to create. So we’re doing pretty good there. And, and since every single sub query is correlated between user ID and ID, right? We have user ID as a leading column.
And since some of these queries are also ordering, or at least one of them is ordering by date, I’m going to have that as a second key column. And since some of them are selecting name and some of them are selecting date, we’re also going to have name as an included column.
Right now, if we rerun this with all of our sub queries in place, all of these terrible, awful, no good, very bad sub queries. Well, the thing finishes instantly, doesn’t it?
Right? And oh, oh, oh, oh, no, but we have a bunch of nested loops joins now. That’s that, that’s obviously a big performance problem. Right?
All our sub queries using nested loops joins. How will we recover? How will we ever recover from this query that runs in 67 milliseconds? How will we do it? What are we going to tell our, what are we going to tell our boss?
What are we going to tell our wife, our spouses, our children? Like, I mean, you know, if it were me at work, it would be like, what am I going to come home and tell my wife and kids that daddy’s a failure? Because he used sub queries.
And with that, I’m going to end my Friday. Because that’s enough. And like I said before, it’s far too nice a day for me to care about SQL Server.
So, I hope you enjoyed yourselves. I hope you learned something. If you like this sort of SQL Server content, you can join nearly 3,461 other people who subscribe to this channel and watch every single video that I post the whole way through.
If you like this video, thumbs up is appreciated. So are nice comments. I’m going to go have a nice long weekend now.
I’ll miss you. I love you. It’s been lovely. It’s been a great video. But it’s time for me to go have margaritas. And whatever else comes along.
I’ve recently discovered a strong affection for vodka martinis. My wife steals all the olives out of them. That’s okay. Olives aren’t really my thing anyway. But those have been hitting the spot lately, too.
So maybe some margaritas, maybe some martinis, but not in the same glass. It’s a recipe for handcuffs.
Not the bedroom kind of handcuffs with the fuzzy stuff on them. The metal ones that really hurt your wrists. I mean, I guess that could go either way, depending on what you’re into.
But anyway, let’s end that here. Thank you for watching. I will see you, depending on how the weekend goes, if the margaritas and martinis maybe have their way with me.
I might not see you for a while. But assuming that all goes well, I will be back to my regular blogging and recording schedule just around Tuesday of next week.
So I do hope that everyone out there has a great weekend. Except the people who tell you to avoid subqueries. I hope that you suffer tremendously.
You deserve it. Thanks for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Cursors In Scalar UDFs, and Other Performance Pitfalls In SQL Server
Video Summary
In this video, I delve into some fascinating aspects of scalar User Defined Functions (UDFs) in SQL Server. After a busy week teaching at the New England SQL Server User Group and dealing with client work, it was great to finally record something for you all. This session focuses on how scalar UDFs can impact performance, especially when they are not optimized or used inefficiently. I walk through an example where a cursor-based UDF significantly slowed down query execution, demonstrating the importance of using set-based logic and avoiding cursors whenever possible. The video also explores how to convert non-inlineable scalar UDFs into inline table-valued functions (TVFs) for better performance, showing that sometimes just adding a `DISTINCT` keyword or making minor tweaks can drastically improve efficiency. By comparing different approaches with query plans turned on, I aim to provide practical insights and solutions for optimizing your SQL Server code.
Full Transcript
Ahem. Ahem. Ahem. Ahem. Erik Darling here with Darling Data. And it would be completely inappropriate for me to tell you just how much I’ve missed you. This is my first chance that I’ve had to record this week. Last week I was gone, the last couple days, because I had to teach a class up in Boston for the New England SQL Server User Group. Fine bunch of people who, let me show up and get paid to talk about SQL Server for a full day. I had about 40 people there. I had someone fly all the way from Nigeria just to hear me speak, which is wild, because Nigeria, at least as far as I can tell from most maps, is FAR. And, like, I’m only used to people showing up from far away, either around the world, or around the world, or around the world. When I speak at past Summit, because it’s expected, right? Like, people just show up there from everywhere, like Mars. And when I’ve, when I’ve, I’ve actually, there’s been a few times when I’ve, I’ve done talks at SQL Bits in, in the UK, and I’ve run into people there from America who I’ve never run into in America. So, like, just seeing random people in the UK is always funny, and that, that happens. But, uh, this has been very busy.
Since I got back, uh, a lot of client stuff, you know, um, the nice people who pay me so that I can record these videos for free, because I don’t, like, get paid for this. So, uh, if any, if you see anything in this video, and you’re like, hey, maybe we could pay Erik Darling to do that, uh, you can pay me to do that, even if it’s standing here babbling. Which I apparently excel at. Uh, so that’s fun. Uh, in today’s video, ahem, we’re gonna talk about some interesting stuff with scalar UDFs, because recently, as recently as this week, I had to help a client with some scalar UDF rewrites, because, you know, if you’ve been paying attention to me, or really anyone who talks about SQL Server over the last, I don’t know, 15, 20 years, you may have heard that scalar UDFs have some performance issues.
You can’t put all those rows on a single thread, which sucks. The other bad thing about them is, of course, that they don’t run once per query.
They run once per row that the scalar UDF has to process, and depending on where in the query you put the UDF, and how many rows have to pass through that UDF for query correctness, for the sake of query correctness, you could end up with a lot of rows going through that UDF and that UDF executing over and over and over again. So if you, like, have a select top thousand query with a scalar UDF in the select list, you could execute that UDF 1,000 times, maybe more, in order to produce a result from every row that passes through that UDF.
And if you do something extra stupid, like put a UDF in a where clause, and that where clause has to process rows from a table with, like, I don’t know, let’s just say a million rows in it, that UDF may execute a million times, or more, to produce a result which to compare against the predicate in your where clause. So you can imagine, my great amazement, relief, just really breathtaking-ness, when SQL Server 2019 introduced scalar UDF inlining, which is a cool feature. It’s a very neat thing. It’s a very novel idea.
I don’t think there’s, like, one other not quite, it’s like RocksDB, maybe, or DuckDB that can inline scale our UDFs, but it was very exciting when Microsoft SQL Server did it, because, you know, I work with Microsoft SQL Server. I’ve never gotten paid to work with, like, anything else.
Literally anything else. So, at least database-wise. I’ve gotten paid to do a lot of other stuff. But database-wise, SQL Server is it. So I was really, like, dumb, like, wow, they’re fixing it, finally.
But there are a lot of restrictions on it. I’m not going to go to the KB article about it, because it’s depressing a little bit. But anyway, so this is kind of what the UDF that I had to fix started as.
It’s sort of a gaps in island problem, and I’ve retrofitted it to the Stack Overflow database. And the idea that I’m using in the Stack Overflow database is to find the longest streak of consecutive days that a user has answered questions, right?
So if they answered questions from, like, 2008 to 2012, one every single day, that would be a lot of days. I don’t know who does that. You have no life. Sorry. It’s just the way it is.
But the idea is to find the longest streak of consecutive days that a question was answered. And so the form of the UDF that I came across was something like this. Again, this is me retrofitting things to the Stack Overflow database, because Stack Overflow is not one of my clients.
But if you work at Stack Overflow and you’re watching this video, my rates are reasonable. I have several references, both inside and formerly at the company, who would be happy to tell you that you should pay me to do things.
So there is that. Anyway, this is what the UDF does. It declared a cursor. It selected some rows into a thing. And then it had all this weird logic to figure out if the streak was consecutive or if we had to restart the streak and, like, hold on to the highest current streak and then return that streak.
I’m just going to take this one quick moment here to say that if you are the type of person who has to declare cursors to do things, please always make sure that you at least include local in your cursor definition.
There are a lot of issues with global cursors, especially if you have two people try to declare them simultaneously, because you can’t.
They will clash and they will error out. So please at least, if you’re going to use cursors, declare them as local. That’s my spiel there. So there are, like, a bunch of things that I want to show you today. But the thing that I want to start with is actually how big of an impact this distinct keyword has in this particular UDF.
Now, I just want to make sure that we have no other indexes in this index created. And I have three testing queries over in this window. The three testing queries are, well, really the same query over and over again.
So again, I’ve got lined up for you today to look at a cursor version of this scalar UDF, a non-cursor version of the scalar UDF, and then an inline version of the UDF. And I think it’s kind of important to see the progression there, because not every scalar UDF has to have a cursor in it in order for it to be horrible.
So without the distinct keyword, right? We have the index now. We have this thing ready to go.
So this thing takes a real, real long time. Like a disappointingly long time. When we look at how long, it’s about 15 seconds of time this thing takes to run.
And this is absolutely zero buenos, as the kids say. And this was sort of the first thing. Well, not the first thing.
The first thing that I did when I opened up the cursor was just, like, hang my head. Because, you know, who would put a cursor on a UDF and expect something good to happen? You’d have to be one of the more foolhardy individuals that I’ve ever met in my life.
So that takes 15, well, let’s see, I guess 14 seconds. But if I move the right way, you can see 14 way down here somewhere, maybe. Oh, it’s on the other way.
No, there it was. Hang on. Where did you go? Where’s the 14 seconds? Next to the six. There we go. 14 seconds, right? Obviously not good.
We don’t like not good things at Darling Data. We have a strong HR policy against good things, against not good things. So let’s just recreate this function with the distinct keyword in there.
And let’s rerun this. And it’ll be about three times faster with the distinct keyword in there. It should take about four or five seconds, right?
So we cut 10 seconds off this thing just by adding distinct into the cursor. So if you’re afraid of making big code changes, sometimes a little distinct goes a long way. Don’t tell anyone I said that.
Usually I make fun of people who are just like distinct, distinct, union, distinct, distinct, union, union, union, distinct. Because, you know, they’re crazy. And they don’t probably just screwed up joins or didn’t use not exist or not exist when they should have. But I digress.
So you really do. So that was that, right? And obviously, you know, we are SQL Server data professionals. And the first thing we do whenever we see a cursor is we stand on our hind legs and we ring a bell and we say, have you tried thinking in sets?
And then we get a little treat. We get to be in the in crowd when we say, have you tried thinking? Have you thought about writing a set-based solution? Okay.
The thing is, and this is a big thing, is that, like, from a, let’s just call it programming perspective, because I don’t know what else to call it. Writing a set-based thing is kind of hard sometimes.
Like, I don’t know a lot of people who can bang out, like, a true gaps in islands solution, like, first try flawlessly without, like, a half a day of tinkering, depending on, like, how many weird, like, edge cases and outliers you might have to deal with. So this is the set-based solution, right, where we have to use window functions like lead, and we have to, like, calculate date diffs, and we have to use sum with, like, a real windowing function sum, not just, like, hey, sum column, like, legit sum with, like, an over clause.
And we have to remember to use the rows between unbounded proceeding and current row, because if you use range in here, your life is going to be nothing but pain. Everyone who loves you will leave you alone, right? You don’t want to use range.
And then we have to do all this stuff, right? So this is the inline version of the function, right? And, of course, we can have, let’s just move that one over there where it makes a little bit more sense. This is the scalar UDF version written using set-based code, where we would declare a variable up here, and we do all this stuff.
Now, the funny thing about this set-based solution is that when we get to thinking in sets, when we think really hard about our sets, really focus our brains on sets, we mess up scalar UDF endlining. So the whole CTE crowd out there who’s like, yeah, CTE, readable, ha, got your readable query here.
They are, they are, we’re going to be disappointed. If we had used derived tables in there, we could have avoided this unpleasant scenario. But with all the CTE, if I just, let’s just get an estimated plan for this.
We look at the properties here. We are going to see that our T-SQL UDF function, not parallelizable, reason shows up because CTE make, break scalar UDF endlining, make scalar UDF endlining not work.
Okay. So the cool thing is, though, is that if you’ve got cursor code, like there might be something you can tweak in there to make it less awful.
Not great, but less awful. There are cursor options you might tinker with. You might throw a distinct down there. Might do all, try all sorts of tricks.
But if you’ve already got sort of set-based code like this, it’s very easy to strip away the things that make this function not inlineable, right, and write it as an inline function.
Now, just keep this in mind, like, in your head when we’re looking at this code. The only, like, this is the, this is a scalar UDF. The only different, the only real, like, actual code body differences are in the scalar UDF, we declare this variable, we write CTE, and then we return this variable.
The inline version of this function, well, it’s, obviously it skips over declaring a variable, and obviously it skips over returning a variable, but it does the same thing otherwise, right?
Like, we don’t declare a variable up here, and we don’t set that variable equal to the final result here, and then return that variable, but the code is exactly the same otherwise.
Like, all those CTE are exactly the same. I changed nothing except, like, just those things in the code, and of course I told SQL Server that I’m returning a table rather than an integer over here. All right, so that’s it.
That’s all that’s different. All right, so let’s come over to our, our answer street testing window. We’re going to give these things a real thrill ride, and let’s run all three, and we’ve got query plans turned on, so we can see what the query plans did.
All right, this and this and this. So all three run, and sort of as expected, as is wont to happen when one trifles with scalar UDFs, we have, up here, we have the cursor version, which runs for, again, about four seconds, and what’s, again, something really nice about modern, execution plan analysis, is that we get operator time, so we can see that the clustered index scan of the user’s table, which took 174 milliseconds, was pretty quick, even though SQL Server’s like, oh, we need an index to make this fast, 85%, or almost 86%, we’ll improve the query by.
The thing is that that index is on the user’s table, the user’s table has absolutely nothing to do with this compute scale R, the compute scale R, if we subtract the 174 milliseconds from here, just runs for about four seconds even.
Right? Close enough. There might be like a 50 millisecond difference or so. So, obviously, like, we did better in here without the cursor.
Right? That’s fairly obvious. The cursor UDF took about 4.2 seconds, and the non-cursor UDF took about nine seconds, even not being able to produce a parallel plan anywhere along the way, yada, yada, yada, yada, yada.
We still did a lot better. We spread this query up by 4x. In some cases, that might be good enough. All right?
So, let’s compare that with the inline scalar UDF. Sorry, the inline table valued function version of the query, which finishes in 164 milliseconds. And I know what you’re thinking.
It’s an unfair comparison, Eric. You should drop clean buffers and run it at max.1 so it’s fair with the other query. Whatever other nonsense people bark at me when they’re, like, mad that I did something faster than them. I don’t know.
It’s the thing people have. So, yeah, this does have the somewhat unfair advantage. This does get a parallel execution plan. This does finish, I don’t know, 800 milliseconds faster, which is, you know, another good, depending on what you care about.
It’s a good improvement. One other thing you might notice looking at the query plans is that these two have very small query plans over here. These just show us hitting the user’s table.
This one actually shows us going to the post table. If we follow this trail long enough, we’ll see that we actually do touch the post table way over here, which we don’t see when we look at the scalar UDF versions.
And, of course, if we were to get the estimated plans for the UDF versions, we would see the UDF query plans in here and in here. Right?
And this query plan for the, you know, the set-based UDF is very close to the query plan for the inline table valued function. It’s just hidden all the way inside, buried in the UDF. Now, if you’ve watched my videos before, you might have heard me explain why.
It’s because if you’ve actually listened to this video closely, you might understand why. It’s because every time we pass a row through a scalar UDF, we have to run that UDF.
So, for this query where, let’s see, let’s just run this one real quick since it’s easy to, since it’s pretty quick to run. This returns 613 rows. If we were to get an actual execution plan for every iteration of the function, we would have returned 613 execution plans back to SSMS.
SSMS would have fallen over and died in its 32-bit misery. So, we’re probably thankful that we don’t get that. We just get a compute scalar that says, don’t worry, I took care of it.
Right? Okay. So, fair enough. All good there. Now, to close this video out, to my grand finale here, what I want to tell you is that either scalar UDF inlining, the feature, assuming that you have right functions that are eligible to be inlined, if you’re on SQL Server 2019, Enterprise or Standard Edition, I don’t know about Web Edition, because who cares?
You’re not serious. It’s like if you use Hyper-V in production, you’re just not serious. He’s like, you must be joking. So, if we have scalar UDFs that can be inlined automatically, or if we go through the great mental strain, the tremendous difficulty, peril, of rewriting scalar UDFs as inline UDFs, sometimes we may find that our query slows down quite a bit.
And the reason why is because if we get the estimated plan for this one, what you might see in your query plans are eager index spools.
Now, I’m not going to sit here and make you wait for this to run, because an eager index spools coming off the post table is fairly disastrous. It usually runs somewhere around 45 seconds to a minute.
So, what the scalar UDF inlining or rewriting scalar UDFs as inline table-added functions can often expose as really bad indexing or just insufficient indexes to help your queries.
So, if you rewrite a query that has a scalar UDF in it, you rewrite the scalar UDF and you find that the rewritten query suddenly slows way, way, way down, make sure you get that actual execution plan.
Make sure that you are on the lookout for eager index spools and make sure that you create indexes that adequately satisfy the needs of your scalar UDF so that SQL Server doesn’t haul off and create a whole index for you on the fly every single time.
It’s not a good time. Not a good time at all. You don’t want that. And, of course, that won’t happen with this version of the UDF because this UDF takes one row and goes and executes it because this one takes all the rows and comes up with the query plan and inlines it.
SQL Server thinks, well, I don’t want to do that nested loops join that many times. That’s madness. Absolute madness. I’m not going to… I need an index. You want me to do that.
So just be careful there and be prepared to create indexes if you run into that scenario. Of course, I have lots of videos about eager index spools on this channel, so if you search my channel for eager index or spool, probably just search for spool.
That’d be good enough because you’ll learn about other types of spools too. Might as well increase the entire surface area of that smooth brain of yours, get some good wrinkles in there. Right?
Half the battle and all that. So anyway, this was a small portion of the client work that I did this week. got a real bad UDF, rewrote it, and just because I had something to sort of work off of, I figured, why not give you three examples over here and show you some of the downfalls of cursors in UDFs, UDFs in general, and something that you might run into, an eager index spool.
If you rewrite a UDF as an inline UDF, and performance, for some reason, gets worse. So you have all of, you are equipped with all of the knowledge you need to go from point A to whatever your end point is.
So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you are better prepared to do your SQL Server performance tuning job because I care about that.
If you like this video, I do adore a thumbs up. I do. I do. I sometimes like comments depending on, depending on what’s in them.
Sometimes they’re good. Sometimes they leave, leave a bit to be desired. Sometimes they hurt my feelings, my deep, deep feelings. And of course, if you like this sort of SQL Server content about performance tuning, you can subscribe to my channel so that every time I unleash these nuggets before you, you are notified promptly and you can see them before anyone else.
You can be the first person to see them. Wouldn’t that be great if you were the first person to ever see them? Like viewer number one every time? Surely you’d win some sort of prize if you could prove that sort of thing.
Anyway, thank you for watching. I’m going to turn off all these really hot lights now because I feel a patina beginning to form and I do not like feeling patined.
So, thank you. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
I’m going to be totally open and honest with you, dear reader: I’ve been experimenting with… AI.
See, I’m just a lonely independent consultant, and sometimes it’s just nice to have someone to talk to. It’s also kind of fun to take a query idea you have, and ask “someone” else to write it to see what they’d come up with.
ChatGPT (for reference, 4 and 4o) does a rather okay job sometimes. In fact, when I ask it to write a query, it usually comes up with a query that looks a lot like the ones that I have to fix when I’m working with clients.
If I poke and prod it enough about the things that it has done wrongly, it will agree with me and do things the right way, eventually. That is an improvement over your average T-SQL developer.
Your average T-SQL developer will spend a terrible amount of time trying to figure out ways to write queries incorrectly, even when you show them the right way to do something, often under the assumption that they’ve found the one time it’s okay to do it wrong.
For this post, I came up with a query idea, wrote a query that did what I wanted, and then asked the AI to write its own version.
It came pretty close in general, and even added in a little touch that I liked and hadn’t thought of.
Duplicate Post Finder
Here’s the query I wrote, combined with the nice touch that ChatGPT added.
WITH
DuplicateTitles AS
(
SELECT
Title,
EarliestPostId = MIN(p.Id),
FirstPostDate = MIN(p.CreationDate),
LastPostDate = MAX(p.CreationDate),
DuplicatePostIds =
STRING_AGG
(CONVERT(varchar(MAX), p.Id), ', ')
WITHIN GROUP
(ORDER BY p.Id),
TotalDupeScore = SUM(p.Score),
DuplicateCount = COUNT_BIG(*) - 1
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
GROUP BY
p.Title
HAVING
COUNT_BIG(*) > 1
)
SELECT
dt.Title,
dt.FirstPostDate,
dt.LastPostDate,
dt.DuplicatePostIds,
dt.DuplicateCount,
TotalDupeScore =
dt.TotalDupeScore - p.Score
FROM DuplicateTitles dt
JOIN dbo.Posts p
ON dt.EarliestPostId = p.Id
AND p.PostTypeId = 1
ORDER BY
dt.DuplicateCount DESC,
TotalDupeScore DESC;
If you’re wondering what the nice touch is, it’s the - 1 in DuplicateCount = COUNT_BIG(*) - 1, and I totally didn’t think of doing that, even though it makes total sense.
So, good job there.
Let’s Talk About Tuning
To start, I added this index. Some of these columns could definitely be moved to the includes, but I wanted to see how having as many of the aggregation columns in the key of the index would help with sorting that data.
Those datums? These datas? I think one of those is right, probably.
CREATE INDEX
p
ON dbo.Posts
(PostTypeId, Title, CreationDate, Score)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
It leads with PostTypeId, since that’s the only column we’re filtering on to find questions, which are the only things that can have titles.
But SQL Server’s cost-based optimizer makes a very odd choice here. Let’s look at that there query plan.
two filters? in my query plan?
There’s one expected Filter in the query plan, for the COUNT_BIG(*) > 1 predicate, which makes absolute sense. We don’t know what the count will be ahead of time, so we have to calculate and filter it on the fly.
The one that is entirely unexpected is for PostTypeId = 1, because WE HAVE AN INDEX THAT LEADS WITH POSTTYPEID.
My first thought was that that, since we’re doing this: (CONVERT(varchar(MAX), p.Id), ', '), that the compute scalar right before the filter was preventing the predicate on PostTypeId from being pushed into an index seek.
Keep in mind that this is quite often necessary when using STRING_AGG, because the implementation is pretty half-assed even by Microsoft standards. And unfortunately, the summer intern who worked on it has since moved on to be a Senior Vice President elsewhere in the organization.
At first I experimented with using smaller byte lengths in the convert. And yeah, somewhere in the 500-600 range, the plan would change to an index seek. But this wasn’t reliable. Different stats samplings and compatibility levels would leave me with different plans (switching between a seek and a scan). The only thing that worked reliably is using a FORCESEEK hint to override the optimizer’s mishandling.
This changes the plan to something quite agreeable, that no longer takes 12 seconds.
STRING_AGG? more like STRING_GAG! 🥁 🤡
So why the decision to use the first plan, au naturale, instead of the plan that took me forcing things to seek?
12 second plan: 706 query bucks
4 second plan: 8,549 query bucks
The faster plan was estimated to cost nearly 10x the query bucks to execute. Go figure.
For anyone who needed a reminder:
High cost doesn’t mean slow
Low cost doesn’t mean fast
All costs are estimates, with no bearing on the reality of query execution
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.
Profiling Query Performance In SQL Server With Extended Events The Easy Way
Thanks for watching!
Video Summary
In this video, I delve into using my free, open-source store procedure, SP_human_events, for profiling stored procedures in SQL Server. This tool is incredibly useful when you’re working on performance tuning and need a detailed view of what’s happening within complex or poorly understood stored procedures. By setting up an extended event that captures query-level performance information, we can monitor specific session IDs or individual stored procedures to identify bottlenecks without the overhead of Query Store. I walk through examples of how to set up SP_human_events to capture and analyze execution plans, parameter values, and other critical metrics from a single window in SQL Server Management Studio (SSMS). This approach helps pinpoint exactly where performance issues lie, making it easier to apply targeted optimizations.
Full Transcript
Erik Darling here with Darling Data. And in today’s video, we’re going to talk about how you can use my free, open source, bug-free store procedure, SP underscore human events, to profile store procedures. Now, I use this all the time when I’m working with clients. And the main way that I use it is to set up a new user, an extended event that captures as much performance information as I can about something running from a single window in SQL Server Management Studio. So, this is very useful if you have stored procedures that, you know, either do a lot of tiny little things or are just very long, and you’re not really sure where to start your performance tuning, because you’re not really sure what the worst of the worst of the worst of the worst is. Now, you can get some idea about this stuff from Query Store.
Now, you could also use my other store procedure, SP underscore Quickie Store, to search for a specific store procedure in a database. And you could look at the top 10 queries ordered by average CPU and probably do a pretty good job of figuring out where to start in there. But, you know, with Query Store and, you know, with the unfortunateness that is the plan cache, the thing that you may often run into is that you will see one of these, you know, cached plans, which is the equivalent of an estimated plan, right?
There’s no actual execution runtime statistics stored in them. And it might be a little hard to figure out exactly which part of a BigQuery plan is slower. It might be tough to figure out exactly, like, which part you might want to start focusing on. And this will help you do that. So, over in this window, I have a few different examples of how you can set up SP underscore human events to do that.
So, the top one is the one that we’re going to be using today, because we’re just going to be watching a specific session ID. That’s this window over here. We’re going to use the query event type, because this one will get all the query level performance information that we could possibly care about. So, we’re going to only get queries that run for 500 milliseconds or longer. And this is the session ID that we’re going to focus on, 107.
That should be the one that we’re using in this window, but I’ll double check that before we kick things off. And we want to use this keep alive thing, because what the keep alive thing does is it sets up an extended event that SP human events won’t automatically tear down. So, like, one other way of running SP human events is to give it a number of seconds you want it to run for.
And what it’ll do is it’ll start an extended event session, like, grab whatever event type information you decide you want to capture in here. And then at the end of that sampled seconds period, it will, like, parse out all the information from the event and then kill off the session so that it doesn’t keep running and running. Why the keep alive thing is useful is because you can just right click on it, save, watch live data, and then, like, run your store procedure and watch data as it comes in.
So, there are a couple other examples down here. And these two just show you how to collect stuff from one specific store procedure. Right. So, these are a little bit different because they don’t use session ID. We’re just using object name and we’re going to say we just want to get stuff from this store procedure. And if you’re feeling a little scared of, you know, let’s just say that you’re casting a wide net with what you want to collect, one thing that you can skip over is collecting actual execution plans by using the skip plans parameter.
So, this will get you all sorts of other information, you know, parameter value, statement level, CPU duration, all the other stuff, just without the actual execution plans. So, like, maybe this might just be good enough for you to, like, grab an example query execution of something that ran for a long time, rerun it in SSMS and get query plans from it that way. Or use the other, use the other, use the, use SP human events to focus in on a single session, just like recreating that stuff.
So, I do try to make it easy to, to use all these things. You know, there is a, there is a help parameter that lets you figure out exactly which parameters and which valid, which parameters are available and what valid arguments for them are. So, let’s just double check this. We are indeed using session ID 107 here. So, what we are going to do is we are going to execute this block and I don’t know why there is so much white space over there.
That is quite strange looking. And then we are going to go into management and extended events and we should probably refresh this. And now we have this keeper underscore human events query, right? So, it is prefixed with keeper because it is going to stay alive.
And of course, human events because it is SP human events. And then we have underscore query because we are capturing query events. Now, you probably don’t want to have multiples of these running on the same server because, you know, stuff might get a little wonky.
But then what we are going to do is just right click on this and select watch live data. So, we will do that. Now we have this wonderful video, window here where nothing is happening yet.
But if we come over to this window and we execute our stored procedure, we don’t have to have query plans turned on here. We are just going to run this. And as this thing executes, we are going to watch this window.
Because eventually, something will show up in here. Like this is still running. So, we don’t have any, nothing has come in yet. So, some query is running for a good long time. Laptop is starting to make some noise.
And now we have a couple rows in here. And now we have a couple more rows in here. Now, if you are feeling real scared of like, you know, maybe SQL Server, like over collecting your right over to observer overhead, you can stop the extended event from collecting data once it is finished.
But here we have a timestamp, which is oh so very useful. And then we have some of the events that fired in here. So, here’s a query plan for a slow thing.
And you can see the full query plan here. And we can zoom in and, you know, see, oh wow, this took a real long time. It took 12 seconds to insert into this table variable.
Table variables, boo, hiss. And, you know, we had a, this thing took 7 seconds to do a seek. And this thing took 4 seconds to do a scan. And oh boy, the whole thing is just a mess.
Now, if it were me doing my professional query tuning song and dance with this, I would, first thing that I would probably point out is that, you know, you’re inserting into a table variable. And table variables have all sorts of downsides in SQL Server.
Like, you’re not allowed to generate a parallel execution plan when you insert into a table variable. Or when you modify a table variable generally. And if you’re on SQL Server 2022 or various Azure nonsenses, you’ll get this warning.
If you’re on older versions of SQL Server, you’ll just say non-parallel plan reason. Could not generate valid parallel plan. And then we would talk about how table variables don’t get column level statistics, histograms associated with them.
And, you know, they’re a little bit of a black box to SQL Server. And, like, even in newer versions and with recompile hints, we can get, like, the table cardinality. Like, the number of rows that are actually in the table variable.
You still don’t have any, like, good histogram statistic information about the values in the columns like you get with temp tables. So, we’ve got some information here. And one thing that you can do, which I usually end up doing, especially for the longer stored procedures, where there’s a whole lot more stuff in them, is I’ll usually start right-clicking here and hitting Show Column in Table, so that I can see stuff like CPU time and I can see stuff like granted memory and I can see, you know what, this, the post execution plan one isn’t my, doesn’t have all my favorite stuff.
But you can also grab the statement text. You can grab the overall duration. Like, you can grab all sorts of, like, good information about what’s going on in here.
And like I said before, like, if you, even if you skip getting execution plans, there are a couple of the events that show, that are used as part of the event session definition that will show you the parameter values that got passed into the store procedure. So, this can all be very useful stuff to compare.
Now, the, what’s frustrating is that the CPU and duration is in microseconds and my brain does not compute or comprehend microseconds very well. So, like, I just, like, whenever I’m looking at this stuff, I always have to make mental notes. Like, like, go, like, look at the, look at the actual, like, look at the actual execution plan.
This was 12.135 seconds. And so, I know that any numbers that are about this length are going to be, like, over 10 seconds, right? Like, like, like, just, that starts at the 12 and there’s 1, 2, 3, 4, like, 7, 8, 57 digits afterwards.
And I know in my head that this is what, you know, took the bulk of the execution time in here because the full statement took about 15 and a half seconds. So, you know, this is where, like, the query tuning magic sort of kicks in. And let’s kill this thing off.
Let’s say, stop that and, you know, come back over here. Now, this is a lot, again, this is a lot more valuable when you have store procedures that do a lot of different things. Maybe even call sub-store procedures. You know, there’s a lot of, like, tiny little statements in them.
And the reason why this is so useful is because if you have store procedures that do, like, a bajillion things, right? Like, a lot of, like, you know, declaring variables, assigning variables things. And you turn on actual execution plans in SSMS, you’re going to flood that thing and have just a bad old time scrolling through a billion tiny little different execution plans trying to figure out where in the batch the slow stuff happened.
So, you know, just to do a little query tuning song and dance stuff. Let’s say, you know, we looked at all this stuff and we said, ah, table variables. And we shook our fists and we got, you know, gathered up the angry villagers, rounded up a constabulary.
We did everything we could. We, you know, united our forces. And we were like, you know what?
We’re going to do the query tuning professional thing. And we’re going to replace at signs with pound signs or hash signs depending on where in the world you live. And let’s just say that we wanted to change this query to use temp tables instead of table variables.
And just to make life a little bit easier because there’s only two statements in this one. Let’s run this. And now we’ll see that this didn’t take 15 seconds.
We had two quick. We have our queries now that finish very quickly. This one takes 2.2 seconds. Yay. And this one takes 352 milliseconds.
So, you know, the temp table wins again. The old temp table versus table variable foot race. And just to preemptively answer any questions, no, a CTE wouldn’t be better here. You stop it.
Stop it with the CTE. They’re not, they’re not your friend. So, again, this is how you can use SP human events to monitor. You could even say, you could use it to profile the activity of a single spid.
And that’s what we did here. And we got valuable information and insight into what our query was doing, why it was taking a long time. And then we knew exactly what to do to fix it.
So, if you find yourself in your job as a, whatever you do, having to tune SQL Server queries, this is a great way to capture the performance details of store procedures that do a whole lot of different crazy things so that you can figure out exactly where you should start tuning them. Granted, on this one, I started with a very low query duration. That’s a very low ceiling for me.
If I’m looking at something for the first time, I’ll probably set this higher, like one second or five seconds or ten seconds, depending on, you know, like what I know about the store procedure. Generally, if someone tells me that something runs for a half hour and I see there’s like, you know, like, get some information about it and I see that like the worst running queries run for like over a minute, I might even set that query duration thing to like 30 seconds so that I focus on the big stuff first. And I don’t get like all squirrel brained about, oh, this thing takes like five seconds.
I should also fix that even though I know that like getting like improving something that runs for like 30 seconds by, you know, five seconds or like 30 minutes by five seconds isn’t going to make anyone happy. I want to focus on the big stuff first. So this is a few different ways you can do that.
You can get this again for totally free from my GitHub repo. The link to the GitHub repo will be in the in the video description. And you can you can you can start your fun there.
So that’s that’s good, too. All sorts of interesting stuff in there. And you can use SP human events for several different different things that you might care about in SQL Server, Blank, Compiles, Recompiles, things like that.
So there’s all sorts of good stuff you can do in there. And yeah, this is the most common way I use it. I hope I hope you I hope I hope you find it as useful as I have found it. So thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that you’ll go use this store procedure that was a quite a monumental chunk of effort to get get working and correct and all that other good stuff. God, extended events are hard.
That’s why I made this thing. But anyway, if you like this video, there are you have options for showing me your your mass approval. There’s a thumbs up button, which is good.
There’s a comment area where you can leave nice comments, no mean comments. Please, I’m a fragile human being. And if you like this sort of stuff generally, subscribe to the channel. I want to say that at this point, if you subscribe to my channel, you will be one of nearly 3,448 people who have also subscribed.
So the next person will be 3,448. Imagine that. Quite a quite an island of misfit toys.
Mass dear. Anyway, thank you for watching. It’s hot under these lights. And I’m going to go drink some water or I fall over. Thank you.
We’ll catch it. Now not to close the house or I go dorm if you have to. Guess what just?
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.
Plan Cache Pollution From Temporary Objects In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into an interesting aspect of SQL Server stored procedures involving temp tables. Specifically, I explore how creating a temp table in one stored procedure and using it within another can lead to unexpected behavior in the plan cache and query store. By walking through a detailed example with two stored procedures—one that creates a temp table and calls another that uses it—I demonstrate how this setup results in multiple compilations and recompiles, even when using the `KEEPFIXEDPLAN` hint. I also highlight the differences between traditional plan caching and Query Store, showing that while the optional spid issue affects the plan cache, it does not impact Query Store, leading to a cleaner execution history. This video aims to provide insights for database administrators and developers who need to optimize their stored procedures and understand how SQL Server manages query plans in complex scenarios.
Full Transcript
Erik Darling here with Darling Data Enterprise Edition. That’d be nice, right? Everyone should be Erik Darling for 15 minutes just to experience how bizarre it is. Have to talk about these things. Today’s video is actually sort of a reader mailbag comment. It wasn’t really a question. But it was a comment that I identified with because it’s something that I’ve run into. And it was, I’ve actually had two questions about it because there’ll be a link to the blog post in the show notes. And a while back, I sort of co-authored a blog post with Mr. Joe Obish about how this certain use of temp tables in stored procedures, like when you have a stored procedures, like when you have a stored procedure that creates a temp table and then calls another stored procedure where that temp table also gets used, not like you can share temp tables between stored procedures, how that can cause weird plant cache pollution. And there was also another sort of like reader comment slash concern about if that would affect query store. And we’re going to look at that today. So, I’ve already got my index created. I’ve already successfully used a stack overflow database. And these are the two stored procedures in question. Now, we’re going to look at these in one slightly different way towards the end of the video. But for now, this is good enough. Good enough for government work, as they say. So, in this stored procedure, this is the inner stored procedure, because this is the stored procedure that gets executed inside of an outer stored procedure. And this may look familiar because I use this in a recent video to do some other stuff. But in this one, we just select from a temp table. I’m just going to use a couple different variations on this query, one with no hint whatsoever, and the other with the option, keep fixed plan hint. Now, this is the outer procedure, where we’re going to create a temp table, we’re going to insert data into that temp table, and then execute the inner stored procedure to finish things off. We’re going to look at this from two different, two different, well, actually, like four different ways. We’re going to look at this from the point of view of compiles, recompiles, and of course, how it affects the plan cache. So we’re going to look at the plan cache information in here. And so what I’m going to do is to start things off, I’m going to make sure that query store and the plan cache have been cleared out. We don’t need this just yet, we’re going to do this towards, well, after we do some other things, just got to make sure that, make sure that, make sure that I have my semicolons in there. Can’t go a day without semicolons. And then we’re going to explore a blog post from 2019 written by the most beautiful man at Microsoft, Joe Sack, about reduced compilations for workloads using temporary tables, where some stuff happens. We’re going to click on the link in a little bit, don’t worry.
So what I need to do in order to start proving that I know anything about SQL Server to you is fire off a couple of different instances of my store procedure, along with these funny loopy things. So much like in the last video where I talked about how recompile, keep plan, keep fixed plan, and other things of that nature are compiled and recompiled and plan get reused in different ways amongst them. This is just a loop that goes through, executes this door procedure, increments some stuff, and eventually finishes and returns some results. And we just want to get a good, healthy number of executions. And for this particular demo, I’m executing this from three different windows, because what I want to do is get three different spids, three different session IDs, all using this stuff together.
All right, that’s the goal here, because that’s where we see stuff start to get way out wiggy weird. Now, this is going to be different from if we had just done all the work within a single store procedure, because that spid is actually going to get cached along with the execution plans. So if we come over to look at the compile situation for this store procedure, we’ll see a total of three compiles.
And it’s a little bit easier to just click on these things. So this is the store, this is the statement that does not have a query hint on it. And you’ll see this one looks a little bit different, because this one actually has some compile CPU stuff alongside it.
This one also shows three compiles, but there’s like no compile time or anything associated with it, all this line here. And this is going to be the one that has that keep fixed plan hint. Now, what the recompile situation looks like is a little bit different.
So because statistics changed on the temp table, this statement without that keep fixed plan hint recompiled 123 times. So we have 123 recompiles plus three compiles, right? Keep that straight in your head a little bit.
And let’s come over here and let’s look at the plan cache. What we’re going to see is something pretty close to what we saw last time, except a little bit different. It’s a teeny tiny bit different.
Because all those different spids were executing the store procedure at the same time from different connections. And SQL Server was caching those temp tables. If we look over here, we’re going to see three different spids show up in this optional spid attribute.
Not throw up in. That would be gross and smelly and like a New York subway. I mean, it could be either the New York subway, the sandwich chain or New York subway, the mode of transportation.
Either one, you get what you get. So if we look at really this statement here, right? This one in the middle.
Oops. Scroll bars. Just mutiny. Mutiny today across all technologies. Scroll bars included. We have 126 executions of the get average score by year outer, which essentially just creates a temp table and does the insert.
Right? And so there’s 126 of those. And that number adds up.
If you remember, if you come back to these windows, we have the 123 recompiles and the 3 compiles. Right? So that adds up to 126.
So every time this statement ran across those three spids, it either compiled an initial plan or recompiled. Right? So we had the initial three runs where it was like compile a plan and then 123 where it was like recompile.
Pretty crazy. Now, I guess the point that I’m making with this is that even though we get good plan reuse across the three statements that have the keep fixed plan hint. That’s these three right here.
I wonder if we just make this a little bit bigger. These three down the bottom have that keep fixed plan hint. So they don’t recompile constantly, but they do initially all compile a different execution plan. Right?
Because this optional spid value makes new plan cache entries. And if we look at these spids, 68, 79, 71, that’s going to match up with what we have down at the bottom for session IDs. I’m trying to figure out a good way to get.
There’s 68 right there. You can see it right next to the shoulder. If we do it, if we zoom in on this one, come over here. Where are you, baby?
Where are you? Why are you running away from me? There’s spid 79. And this should be spid 81. If we zoom in correctly over here. Sorry, 71.
Right? So we see that even with the keep fixed plan hint, we do get that optional spid cache. And we do get an initial plan compilation, but we still get the good plan reuse after that. Right?
So even with keep fixed plan, we don’t get the same, well, I guess we do get the same behavior in that we don’t constantly recompile after we compile a plan. But the optional spid cached along with that does create three different plan cache entries. This does not happen in Query Store, though.
And if we look at entries in Query Store for the get average score by year inner procedure, and only that. All right, if we look at this, we’re going to see, come over here a little bit, we’re going to see just 126 executions each. So by the time SQL Server gets to sticking stuff in Query Store, all the optional spid stuff is pulled out.
So the optional spid issue with the plan cache doesn’t transfer to Query Store. Query Store just shows 126 executions of each. Of course, you know, the one with the keep fixed plan hint is going to have just the three compiles.
And the one that doesn’t have the keep fixed plan hint is going to show the three compiles plus 126 recompiles. Sorry, 123 recompiles to add up to 126. But just looking at this, right, like we just get the, we just see the 126 total executions in there.
Where this changes just a tiny, itty bitty little bit. And we should probably, probably click on this post to, oh, Microsoft Edge, what’s new? I don’t know.
Just look at what’s new in Chrome. You’ll see about the same thing. So here we are. Again, the most beautiful man at Microsoft, Joe Sack. Back in 2019, which is a blog post about one of the early CTP releases of SQL Server 2019, 2.3. And in his pattern, the temp table is created here, but then the insert is done here.
Now, well, this does help with the recompiles. You still have to compile a different entry. So if I were to move the insert statement from the outer procedure and put that in the inner procedure, we would still see that insert with the optional spid.
We would see three entries for that. And I suppose I can just go and show you that so you don’t call me a liar because, you know, for some reason a lot of people think I just make stuff up, which, you know, I wish that I had the moxie to just make stuff up about SQL Server like so many other people seem to do in their various posts and other things, just make things up entirely or paste things in from chat GPT.
And it’s all just sad and lonely. All right. So we’re going to rerun this.
And what I should probably do just to make absolutely positively extra extra sure is reset query store and the plan cache. And again, we’re going to do the same thing where we go, but up, but up. And then we’re going to run these three.
And we’re just going to twiddle our thumbs until this finishes. Now, I do think that, you know, SQL Server 2019 did help with the recompile issue, but the optional spid thing does still contribute to the, like, extra plans being compiled thing, which isn’t great because the plan cache is such, like, a terrible, noisy, ephemeral place anyway that stuff like this can really add up.
And stuff like this can really make the plan cache a pretty bad place to look for performance problems. There are certain performance problems. I mean, not even performance problems. There are certain, let’s just call them workload oddities, that will only show up in the plan cache because you can’t, this stuff doesn’t affect query store.
Right? Like, by the time you get to query store, the option, like, see, query store is like optional spid. Who?
What? Just get, get, go away, go away from me. Why are you here? I don’t need an optional spid. Plans don’t get used from here. Get, get, get it away from me. So if we look at this, we have the, well, now we have 126 total compiles of the, the hintless plan. And we have something a little bit interesting in here, right, where now we have an additional statement has entered the fray.
And we have the three compiles here, right? So the, the, the, the insert statement didn’t recompile, but it never recompiled. We just see the text for it in this now because we moved it into the inner procedure.
So we still have the three compiles of the insert because if we go and look at the, we go and look at the plan cache now, that insert statement is going to be included in the, what do you call it? The inner store procedure.
So these three lines with the insert into filtered posts, these all get, I mean, again, they still get the three separate entries for the three separate spids, but they do get good plan reuse within that. So there’s 42 executions across all these, whereas these have, you know, 42 executions, but 43 plan generation numbers. Because we do this as a new plan almost every single time with the stats changes.
And if we come over here and we look at, let’s just narrow it down to this chunk with the, sorry, the inserts and the select statement. So we’re just like this span of like six rows. You know, we’re still going to see the optional spids now cached with the insert query now too.
This only happens with queries from the inner store procedure, not with the outer store procedure. So in a weird way, it might even be better. It might be better if we kept this the original way where the insert is done in the outer procedure, because then you only get the one plan cache for it.
You don’t get three separate plans cached for it with the optional spid put in there, right? So depending on how, like, how big of an issue this might be for you, you might want to consider moving your inserts into whatever outer store procedure calls there are, and moving your temp table stuff into just the queries from, like, not the inserts into the inner store procedure.
Now, this is one of those funny things where, like, I’ve long believed that query store should take over responsibility from the plan cache in many ways. Because in a lot of ways, it’s doing that more and more. Because in query store, you can force execution plans.
In 2022, you can apply query hints. Like, there’s all sorts of, like, feedback mechanisms within query store that don’t exist in the plan cache. And I really think that the query store should take over more responsibility from the plan cache, because the plan cache is just such an awful, again, very just unpredictable place.
It almost seems like a retrograde way of doing things now that we have query store enabled, you know, by default with SQL Server 2022. Now, query store does need help in a few different places. You know, the GUI is a disaster.
And a lot of the cleanup stuff gets real screwy. Like, with a few different clients now, I’ve had to run the SP flush query store to disk procedure on, like, an hourly schedule, so that query store doesn’t fill up because cleanup only happens, like, once a day or something.
And, like, it just gets too big and no new plans get in. It goes into read-write or it goes into an error state. There’s a lot of stuff with query store that needs help before it can really take the championship belt from the plan cache with these things.
But I think with the amount of responsibility that query store has now for directing query execution, both from the plan forcing, adding hints to queries, like, different intelligent query processing feedback mechanisms, that really we’re at the point now where query store should be taking more and more ownership of things that the plan cache used to take care of.
When you add into that, the query store is much better at managing historical data, and you have a much better track record, and you can see query regressions and, you know, all sorts of other stuff, there’s almost no sense in relying on the plan cache for much anymore, at least from what I can tell because the plan cache is just full of spiders and ghosts and ghouls and gremlins that I just don’t, I don’t like messing with it.
I avoid looking at the plan cache as much as possible when I have something better like query store or a third-party monitoring tool that I can rely on rather than having to, like, run it and just be like, oh, well, I mean, 99% of the plans were created in the last hour.
Do you care about, like, did anything bad happen in the last hour? Do you care about any, were there any performances in the last hour? No.
Okay, well, we’re out of luck. Like, the plan cache is just so often useless that I just kind of stopped looking at it. Unless I need to explore sort of weird issues like the optional SPID thing, and, like, why, you know, different queries might end up with different execution plans and how temp tables get used and shared and all that other stuff.
So there are times when it’s, you know, you do need to get some information there, but even getting that information is hard unless you’re constantly monitoring it because there’s so much churn and flush with the plan cache. So, anyway, if I had to summarize this video, it is that, well, it is an interesting and novel method of sharing data between store procedures to use temp tables to do that.
It can certainly have some interesting side effects on the plan cache. As much as possible, I would say that you should keep temp table creation population and, like, you know, select logic contained within one store procedure as you can.
If you have to, like, share temporary table data from one store procedure to another, just be a little bit careful about how you do that because the more logic you put inside the inner store procedure and the more concurrency there is with that store procedure, the more plans you’re going to see popping up and doing that stuff.
Of course, like we talked about, that, you know, doesn’t really affect Query Store because by the time everything gets to Query Store, everything is, like, the optional spit stuff is all parsed out anyway.
It doesn’t matter. So, anyway, a fun Monday video. Got to, it’s a good one for me because I got to share some stuff. I got to do some demos.
I got to rant a little bit about Query Store and the plan cache. And really, I don’t find those things fulfilling. It’s just, it’s nice to get them off my chest because who else would listen? I can’t, I can’t tell my wife about this.
I can’t, like, you know, head downstairs and be like, honey, you wouldn’t believe the plan cache. You wouldn’t believe what the plan cache does. You share temp tables between store procedures. You’d immediately zone out.
Like, yeah, okay. Go right to the phone on that. So, anyway, thank you for watching. I hope you enjoyed something. I hope you learned something.
Enjoyed yourselves? Learned something? If you like this video, there’s a thumbs up button that’s the nice thing to push for me. So I feel better.
And I also like getting comments. Topical comments. Thought-provoking comments. Good questions about what we’re talking about. If you like this sort of SQL Server content, you can subscribe to the channel.
Almost 3,500 people have done that so far, which, you know, maybe if I did a little bit more ASMR, my numbers would be better. But from what I can tell from previous recordings where there were perhaps some audio issues where there was a little accidental body noise ASMR, y’all don’t want to hear that.
So, you know, probably going to avoid that. So, anyway, thank you for watching. And like and subscribe. And temp tables.
Yeah. Temp tables.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
Or at least at some point, back when I gave all my precious blog blood somewhere else, I used to quite enjoy writing the release notes for the First Responder Kit. It was fun, and there were a lot of contributors to credit.
This most recent release had a note in it that got me taking a stroll down memory lane.
Deprecating sp_BlitzInMemoryOLTP, sp_BlitzQueryStore, and sp_AllNightLog
sp_BlitzQueryStore was originally written by Erik Darling when he worked here. He’s moved on to start his own excellent company, plus his own sp_QuickieStore. You should be using that instead.
sp_BlitzInMemoryOLTP was always kinda distributed as a courtesy – the real home for it is in KTaranov’s Github repository, and you can still find it there. It hasn’t been updated in over 6 years, and I’ve never seen anyone using it, so I’m removing it to streamline support issues.
sp_AllNightLog was a ton of fun when we built it several years ago, but it’s consistently had a problem. Companies start using it, then decide they want to build something even more ambitious, typically a C# service with robust error handling and scheduling. sp_AllNightLog isn’t the kind of thing I want to encourage beginners to use – it’s complex.
I didn’t have much to do with sp_BlitzInMemoryOLTP. I’m still not entirely sure what it does. All I know is that In-Memory was the hottest frog in the pan for exactly 14.9 minutes.
But I have some quite fond memories of building sp_BlitzQueryStore, and sp_AllNightLog.
I didn’t write every bit of code in either one, but I definitely started work on and them pitched in quite a bit on both. Other contributors deserve whatever credit they’re publicly willing to take.
If you’ll permit a fella with a lot more grey on his face than there was when these two procedures were first F5 birthed into the world to reminisce a bit, I would like to eulogize them here.
sp_BlitzQueryStore
I had been working on sp_BlitzCache just about full time for a couple years, since Jeremiah (who wrote it originally) had decided to embark on a professorial career. When Query Store got announced, I knew I wanted to write something for it.
After all, this seemed like a no-brainer for folks on SQL Server 2016 to adopt. I just had no concept of what I wanted to do, until one day…
I had just gotten off the phone with the worst credit card company in the world, because someone had purchased ONE-HUNDRED $99 Play Station gift cards from a Russian IP address with a .ru email, and they told me that I would need to fill out 100 dispute PDFs to dispute each charge separately.
I forget where I was walking home from, but I was in the company Slack, and I had a message from BrentO asking how I wanted to approach it, and I felt like I had to make something good up on the spot. It turned out to be: I want to find all of the worst metric spikes, and grab the queries that ran during them. So it would look for the highest CPU, reads, writes, memory, tempdb, etc. and look for the queries responsible for them. And since we have all this groovy historical data, I wanted to show which queries were sensitive to parameter sensitivity by looking for wild swings in those metrics.
In theory, this was a great idea. In practice, those queries were god awful slow. It wasn’t all my fault, of course; I can’t take full credit. I see a lot of scripts (including queries from the SSMS GUI) that hit Query Store which are equally as God awful slow.
Perhaps ironically, some of the absolute slowest points in any Query Store query are the ones that hit the “in memory” tables.
that is seven minutes, thank you for asking
At any rate, SQL Server 2016 adoption was fairly slow, and Query Store adoption was even slower. It was even hard to recommend turning it on at first because of all the bugs and issues that were cropping up and getting fixed in CUs (and even then, Service Packs). SQL Server 2017 didn’t help things at all, and I was out on my own in the world by the time SQL Server 2019 got released.
So poor ol’ sp_BlitzQueryStore languished a bit. Of course, as I added checks and gizmos to sp_BlitzCache, I’d also add them to sp_BlitzQueryStore, but… It just wasn’t the same every day utility belt tool for me.
When I sort of lost faith in the whole thing was sometime in 2018 when I tweaked a query in sp_BlitzQueryStore to try to speed things up, and it made my local SQL instance stack dump, and I had to manually restart it. If that happened with a client, hoo boy!
But here’s to you, sp_BlitzQueryStore! It’s how I first started learning the Query Store DMVs, how they related, and what data was in them.
You’re like that long-term relationship that ends before you meet the person you end up marrying.
sp_AllNightLog
This made me feel cool, because:
I’d always loved Log Shipping (still hate AGs)
It was my first “programming” stored procedure
Let me clarify point 2 a bit, because I’m not one of those “SQL isn’t coding” people. Most things that I write only work with data. This worked with REAL LIVE FILES. Writing them. Copying them. Restoring them. Across two different servers. Where neither one knew the other existed.
Wild. WILD!
I’m not sure if I’m allowed to say the name of the company that wanted it, but they were based in Chicago, so I ended up flying out there a couple times to work on it along with Brent.
That kind of stuff had never happened to me before, and has only happened a couple times since.
I learned some hard lessons from this one:
If you fat-finger an invalid date value in an output parameter, you’ll end up with a REALLY HARD BUG TO FIND
If you don’t add a WAITFOR to looping code that’s constantly looking for new databases, new backups to take, and new backups to restore, you can really drive one CPU crazy
It was also when I learned that you can’t add triggers to “system” tables, like restorehistory, in the dbo schema, in msdb. If we could have done that, a few things would have been way easier.
Of course, my fondest memory of this one was when it finally worked. I remember hitting a bazillion errors and issues and debugging stuff for ages. And then one magical day, the Agent jobs started up, and it was all green.
I was far from alone in working on it; I don’t want it to sound like THIS ONE’S ALL ME. There were a group of 3-4 people who put in work writing and testing things.
That was kind of the nicest thing about it — real collaboration with real people in real life — not just pushing changes around the internet and asking people to test them.
The Departed
While it is a bit sad to see them go, I totally understand why they had to. It’s difficult to be responsible for large code repos that you don’t use a lot, and have become unfamiliar with because you don’t use them regularly.
I’m a bit surprised that sp_BlitzBackups didn’t also end up in the deprecation pile. It hasn’t had an issue opened since 2019, or any commits aside from version bumps. But maybe it’s just that well-written! If that ever does get deprecated, I have exactly one funny memory of the writing process, and it probably won’t get a blog post. I can spare you the drama of “it runs really slow when there are hundreds of databases” and “how slow?” and “like a minute” and “why do you need to run it every 15 seconds anyway?”.
Perhaps the most surprising thing about the years since 2016 is that not a single third party monitoring tool has embraced Query Store data in their performance diagnostics. Hopefully someday someone tells them about it, I guess?
But hey, that’s enough about that! Go get the most recent release of the First Responder Kit because it has a bunch of new and improved in it. Enjoy your shinies while they last.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
The Difference Between Read Committed And Read Committed Snapshot Isolation In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the differences between read-committed isolation and read-committed snapshot isolation (RCSI) in SQL Server, specifically addressing how RCSI mitigates some of the issues that can arise under the pessimistic isolation level. I demonstrate these concepts using a simple example with tables named consultants and clients from my CRAP database, showing how read queries behave differently when using RCSI compared to traditional read-committed mode. By walking through the process step-by-step, I explain why optimistic isolation levels like RCSI can be more aligned with developer expectations in many scenarios, while also highlighting potential trade-offs and considerations for different workloads.
Full Transcript
Erik Darling here with Darling Data. And spring is all around us here in the Northern Hemisphere, but for me more specifically, spring is all in my nose. So if I sound a little weird today, that’s why. And spring being in my nose is not a Deadpool-esque drug reference, it is an allusion to my allergies, which is alliteration. So last week I went on vacation, but before I did, that was a rhyme too. Wow, I’m nailing it today. Last week I went on vacation, but before I did, I recorded a video about different read phenomena that can happen under read-committed, the pessimistic isolation level that can make query results look weird. And in that video, I made several sweeping proclamations that this sort of thing wouldn’t happen with an optimistic isolation level like read-committed snapshot isolation.
Then of course, while I’m away, exploring business opportunities across Europe, I get questions in my email like, How would it be different? Why would that be different? Please tell me. So I’m recording a video to answer all of those questions today.
So the first thing that we’re going to do is we’re going to alter the CRAP database. I do not distribute the CRAP database. It is not an open source project. If you would like the CRAP database, you are free to create a database called that, and you are free to fill it with whatever you’d like, all the joy in life, your hopes, your dreams, whatever they may be. And then we’re going to redo a couple steps from the last video where we get rid of any tables that might exist that might cause strange things that would make the demo not work.
So I aim to be idempotent. It’s a tough word sometimes. And so what we’re going to do is create a table called consultants.
And even though there is only one consultant in there, it’s called consultants because I think naming tables as plural is kind of the right thing to do. You have consultants in a table. Each row is a consultant. Many consultants are plural.
So for now, there’s just one of me. And we’re going to stick old Erik Darling in there. And then we’re going to create a table called clients just like last time. And just like last time, the clients table will have a lovely primary key and will feature a magnificent foreign key that references the consultant ID in the consultants table.
Good stuff there. All right. Cool. So let’s insert a couple rows into clients because we have Erik Darling, the consultant at Darling Data, has many clients.
Right. So it makes sense to call that clients, even though there’s only two here. I promise there’s been more in a few. And then if we look at the data that we currently have in clients, then well, sorry, consultants and clients joined together, everything looks good.
Right. Consultant ID, first name, last name, invoice ID. The consultant ID again, a bit repetitive, but that’s what you get with select star. And of course, the invoice amount, which is an amount that I would love to invoice in American dollars to someone someday. Perhaps if the government is looking for some SQL Server help, we could talk.
Print some more money for me so that whatever number that is, is worthless. Worth five dollars in real life. So over here and let’s take this.
Let’s stick this over here. I think, oh, look, I already did. I’m so, I’m so smart. I see ahead. I see into the future. I see all things. All right.
So that returns right results. Now, in the last video, what happened was I said, begin tran and didn’t update. And then over here in this window, I ran this query and this query got blocked. This query got blocked until I committed the transaction in the other window and then it returned inconsistent results.
Since read committed snapshot isolation is now working with the version store, we are getting a snapshot of this data from prior to the update happening. So we still see all the right stuff in here because if you look over what this update is doing, we are incrementing that invoice amount by $1. I got a dollar tip on that huge invoice.
Someone was just like, yeah, Eric, darling, you’re worth that extra buck before we overflow the big value for SQL Server. Top you off. So in the last video, this query got blocked and this query, it doesn’t.
This query runs and returns the values as they existed prior to the update running. And the same thing will happen if I run this update, right, and I changed my last name to Darling Data because Erik Darling married to the data game, right? So we run this.
This will still not be blocked, but it still won’t reflect the change within that transaction, right? That’s still out there in the open. Nothing going on there. If I run this query from within the transaction, well, then I can see all the changes that happened because this query is working within the transaction that made the changes.
And then finally, if I commit this transaction, let’s make sure we’re extra committed to that transaction. And we come over here. Now this query will finally see the changes.
So the difference, of course, is that your read queries don’t get blocked, but your read queries might be seeing older versions of how data existed before changes started happening to it. So that can be great for some people. That can be great for 90 something percent of the people who have a workload in SQL Server because it’s great for most people who have workloads on other database platforms that made better choices of default isolation levels like Oracle, Postgres, and probably DB2 if anyone can find DB2 out there.
So that’s how they’re different. And we can repeat the same thing with the other query that I showed you where it looked like a unique constraint had been violated in SQL Server. Where it looked like we had duplicate values in unique constraint.
In this case, we won’t see any weird results. So we’ll run these first three updates right here. One, two, three.
And we’ll just refresh your soggy memories. There is a unique constraint on Butthead. And so if we were to try to insert a fourth row, it would fail because we would violate that unique constraint. And that was sort of the gist of the last demo where I was like, hey, if enough changes happen and things swap around, your query results could make it look like that column returned non-unique results.
And that would be confusing and awful and terrifying. You would question SQL Server and you’d call up Microsoft and be like, Microsoft, SQL Server has a memory leak. And it’s broken.
You better catch it for whatever kids do these days when they make prank calls. So let’s grab this query just to prove out our point once again. Let’s paste that in there.
And if we run this select, we get exactly what we should. Because no changes have happened with the table. We get Beavis incorrectly saying huh and Butthead incorrectly saying other things. Well, I guess the one at the bottom is right.
But the first two, absolutely wrong. Non-canonical Beavis and Butthead laughs. All of Flutter here in this demo. But now, let’s begin trend and update. And just like last time, we are not going to see any blocking here.
But we’re going to see the data the same way that it was before the update started. And if we run a couple more updates, we are still going to not be blocked. But we’re still not going to see any changes.
Now, if I were committing these changes along the way, of course, we would see them over here. But because these changes haven’t been committed yet, we’re still seeing that snapshot of the data from before the changes. And just like in the prior demo, if I select from this, and here we can see the changes, right?
Then if I commit and then extra commit that transaction over here, those changes will finally show over here. But the important thing is that this select will never look like it returned a violation of our unique constraint on the Butthead column.
So, when I talk about workloads that would benefit from RCSI, this is a big one. Not get, not your read queries, not getting blocked, and returning correct results from prior to modification starting, is usually what people want.
Using no lock hints, being able to see transactions in flight is not good, right? And seeing dirty data, it’s dirty reads. The concern here, of course, is reading stale data, right?
Because if your select queries do require getting blocked, and then seeing changes after the blocking, read committed might be the right isolation level for you.
But with the huge caveats that came with stuff in the last video, about how if there are multiple queries in a transaction that make changes, if your query got blocked after like, like let’s say that there are 10 of them, if your query got blocked on like the fifth one, and then five more things happened, your query would have seen like five things that changed, five things that didn’t change, and then five things that did change, and that can be really screwy for results too.
So, again, no isolation level is generally 100,000 million percent perfect for every workload. However, I do find that the way optimistic isolation levels is far closer to developer expectations than the read committed reality that they’re faced with, and of course the no lock or read uncommitted shortcuts that they take when blocking happens, and they’re like, why God, why have you forsaken me here, letting my queries get blocked like this.
So, that’s the difference between read committed, the pessimistic isolation level, and read committed snapshot isolation, the optimistic isolation level. I hope that this makes things more clear, and I hope that this helps you with anything that you might want to test in your own environment to make sure that if you are going to start using an optimistic isolation level, that you use the right one.
Because remember you have snapshot isolation over here, which requires queries to ask for it, and you have read committed snapshot isolation over here, which affects all of the read queries that come into your database. So, if you find that there are queries that would operate incorrectly under read committed snapshot isolation, you could choose snapshot isolation and have certain queries that don’t require that to opt in to using an optimistic isolation level, or you could add read committed lock hints to queries that do require not reading from a version of the data, so that those queries would obey the locking semantics of read committed.
But, just remember, read committed leaves a lot to be desired as far as guarantees go. The guarantees for read committed, the pessimistic isolation level, are very, very weak. If you truly have queries that need to operate off of the freshest data without having, like, changes and other things happening all around them mess them up, you might be looking at needing to use repeatable read or serializable as an isolation level for absolute correctness.
Because under concurrency, read committed the pessimistic isolation level wipes out pretty hard a lot of the times. Does not, well, I would say it doesn’t live up to expectations, but the reality is, it’s really just behaving as it’s documented and your expectations were wrong.
Read committed is just kind of a crappy isolation level. Even in the crap database, it’s a crappy isolation level. So, before I need to blow my nose or sneeze or anything else that would be untoward to do on camera, I’m gonna end this video.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you missed me terribly while I was away exploring business opportunities in Europe. If you like this video, the thumbs up button is a great way to show that.
I also like comments that say, Yay, good job, Eric. And I also like subscribers, because the more subscribers I have, the more people I reach in teaching people how to use SQL Server correctly, which is nice, because very few people do that.
And I’d like to see more of it, so that one day I can do something else with my time. I don’t know. Start a channel about how to behave properly in a bar.
How to not annoy bouncers. I don’t know. How to not annoy bartenders. There’s lots of things. Lots of things that I could do. Lots of things I could do with my time.
But here I am, talking about SQL Server. So, that’s enough of that. Again, thank you for watching, and I will see you in another video, another time, another place, another you, another me.
Who knows? Goodbye. Goodbye. . .
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
I often see clients using forced plans or plan guides (yes, even still, to this day) to deal with various SQL Server performance problems with plans changing.
There’s usually an execution plan or two floating around that seems to be a good general idea for a given query, and a couple weird high-end and low-end outliers for very specific populations of values.
This is especially common in third party vendor environments where code and/or index changes may not be allowed without the okay from the high priest of tech support who only answers questions when their celestial craft passes near Earth every 27 years.
Of course, forced plans and plan guides can both fail. You may also run into a “morally equivalent plan” in Query Store that looks quite morally ambiguous.
The fix we came up with was to track down the compile values for that nice middle ground plan, and use OPTIMIZE FOR to push that plan shape into reliably reality.
Territory
Here’s a close enough approximation to what we did, with a good-enough demo. Trying to get a more realistic one was hard without a much more complicated schema, which the Stack Overflow is not.
An index!
CREATE INDEX
p
ON dbo.Posts
(OwnerUserId)
WITH
(SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);
And a procedure!
CREATE OR ALTER PROCEDURE
dbo.OptimizeForStuff
(
@ParentId integer = NULL,
@PostTypeId integer = NULL,
@OwnerUserId integer = NULL
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
AND (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
AND (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
ORDER BY
p.Score DESC,
p.Id DESC;
END;
All good so far, even if it does have an air of laziness.
Darwin
The problem was that when the query executed something like this:
Here’s what we did (again, round about) to make sure we got the generally good plan across the board, without failures!
CREATE OR ALTER PROCEDURE
dbo.OptimizeForStuff
(
@ParentId integer = NULL,
@PostTypeId integer = NULL,
@OwnerUserId integer = NULL
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
SELECT TOP (1000)
p.*
FROM dbo.Posts AS p
WHERE (p.ParentId = @ParentId OR @ParentId IS NULL)
AND (p.PostTypeId = @PostTypeId OR @PostTypeId IS NULL)
AND (p.OwnerUserId = @OwnerUserId OR @OwnerUserId IS NULL)
ORDER BY
p.Score DESC,
p.Id DESC
OPTION
(
OPTIMIZE FOR
(
@OwnerUserId = 22656,
@ParentId = 0,
@PostTypeId = 2
)
);
END;
Which gets us the original fast plan that I showed you, plus faster plans for all the other executions.
In all cases, the plan is generally better and faster, and sharing the plan across (though imperfect for the outliers) tamped down the extreme performance issues that were there before with attempts at forced plans.
Posit Hell
While I’m no great fan of OPTIMIZE FOR UNKNOWN, using a specific value can act like a less faulty version of plan forcing.
You shouldn’t pull this out every time, because it is a bit of duct tape to keep a sinking ship above water, but in oddball cases, it can be a quick and rather painless fix.
At some point, better solutions should be explored and implemented, but emergencies don’t generally allow for the greatest care to be taken
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.
