I absolutely adore the SQLBits conference, and it was the Biggest Bummer In Bummsville last year when I had family travel plans that overlapped with the conference dates.
So this year, I’m back with a mach 5 vengeance, and ready to get back to the most highingest-flyingest conference in the world.
If you’re interested, and you wanna throw your hat in the ring, here are the links for general and full training day submissions:
To learn more about speaking at the event, click here.
To learn more about attending the event, click here.
I’ll have all my fingers and toes crossed until November 14th when the training day schedule is announced.
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.
A Little About The CXSYNC_PORT Wait In SQL Server 2022
Video Summary
In this video, I delve into the fascinating world of parallelism in SQL Server queries and introduce a crucial aspect that can significantly impact query performance: CXConsumer weights. Parallel execution plans are designed to distribute workloads across multiple threads for faster processing, but sometimes, these threads don’t utilize resources evenly, leading to inefficient use of parallelism. By examining CXConsumer and CXPacket weights, you can identify when queries might be underperforming due to skewed thread distribution. I walk through a practical example using the `parallel, serial` view to demonstrate how to analyze execution plans for signs of inefficiency, including looking at query time stats and wait types like CX sync port. Additionally, I highlight important metrics such as DOP (Degree of Parallelism) and thread usage, which can provide valuable insights into whether your parallel queries are truly benefiting from multiple threads or if they’re just wasting resources.
Full Transcript
Erik Darling here with Darling Data, of course, still to this very day, always and forever, faithfully yours. In this video, we’re going to talk about, well, we’ve got a couple things to cover. It’s an admittedly interesting subject that has to do with parallelism and which weights you can use to indicate that a parallel query may have had inefficient usage of parallel threads. Now, what that means is you might see some queries that use like .threads to process rows, right? You have the coordinator thread zero, then you have some number of rows, whatever your .threading is, it will have rows given to them to do work. Sometimes, the distribution of rows across those threads is nice and tidy and near perfect. Other times, it’s very skewed towards a single thread, or just like, you know, one thread has like 90% of the rows and the other threads have like a smattering of rows. There are all sorts of different weird reasons this can happen. But the important thing is that you need to be able to figure out if it’s happening on your server.
Now, a while back, Microsoft introduced a weight called CXConsumer to split up CXPacket versus, well, to split up how parallelism is measured within a query. So like, while a consumer thread is waiting on parallel threads, you get CXConsumer, while parallel threads are doing stuff, you get CXPacket. Now, because some guy who works at Salesforce was like, CXConsumer is totally benign, a lot of scripts that you might find out there on the internet will automatically screen out CXConsumer.
I won’t do that to you because it can be really important. But SQL Server, well, I guess the really bad part about that is they get screened out of actual execution plans. So like, you’ll see CXPacket weights, but you won’t see the CXConsumer weights in there. And the CXConsumer weights in there can really help you figure out why or really be an indicator of why your parallel query was quite slow. The kind of good news is that SQL Server 2022 has inherited a weight stat that both Azure Managed Instance and Azure SQL DB use.
Thankfully, no guy who works at Salesforce is around to tell you that this weight is negligible and benign and that you don’t have to worry about it. So, so that you can, you can find in your query plans when parallel might be inefficient. So what I’m going to do is run this first part of the script and this first part of the script, well, it’s going to be a little over, a little, little, little bit more than I would care to normally capture.
I don’t usually want the query plans for the, those temp table inserts, but in this case, we’re just going to live with it because it makes, makes more sense to kind of do it all in one shot. So, so right now we’re selecting data from this view called parallel, but serial, which you can probably infer from the name is a query that generates a parallel execution plan. But the, the, the, the rows all end up on a single thread.
So in effect, it’s actually just a serial execution plan. So let’s dig a little bit into what we have here. We have an execution plan right here.
And if we zoom in a little bit on the execution plan, we can see all this lovely stuff. See all this lovely data, there’s all these lovely parallel operators. And we might be wondering, well, why did this, why did this parallel query take 24 seconds?
The reason, if we dig in a little bit more closely, now we’re going to do some advanced stuff here, right? We’re going to, well, actually zoom it, zoom it kind of, zoom it, zoom it kind of jinx me on that one. So this is how, this is how many rows our query is dealing with.
The problem is that when we look at the properties over here, and this is a little, little wonky at the moment. Let me fix that for you. There we go.
Let me go and slide this on over. Very professional presentation over here. You can see that all the rows end up on this thread one. So it’s not very good.
And if we carry on, we actually go and look at this one. See that all the rows end up, still end up on thread one here as well. So that problem persists throughout the query plan.
And part of it is the lack of a repartition streams operation anywhere here. We have a distribute streams operation, which attempted to divide rows evenly out to different threads. But the round robin partitioning type kind of messed that up a little bit.
Anyway. So digging into what went on here. If we look at the properties and we look at, well, there’s two important things. One of them is the query time stats.
So one thing that you want to be on the lookout, if you have a parallel execution plan that runs for a while and you’re like, what the hell took so long? What were all those threads doing? A very important thing to look at is in the query time stats section of the property.
If you go to the select and you get the properties there, pretty much do what I just did in the video. You’ll see this. CPU time and elapsed time are just about even.
And that’s not good because the whole point of a parallel execution plan is that you give more CPUs work so that you spend less time on the wall clock. Right. You have multiple CPUs.
I’ll do a bunch of work. Assemble that work later. Those threads all do an equal amount of work. Like let’s say your query runs at DOP 8. Your query should be eight times faster than wall clock time, assuming that parallelism scales linearly. It may not always, but you know, neighborhood of, you should see some improvement there.
If we go into the wait stats, and I’m going to bring this back out. If we go into the wait stats for this query, we’ll see a whole bunch of CX sync port. This is that new wait type that I was talking about.
It’s in SQL Server 2022, Azure Managed Instance, and Azure SQL DB. And this can kind of help you figure out when queries have skewed parallelism. Now, something that eagle-eyed watchers might notice is that the wait time on this wait is nearly even with CPU and elapsed time for the query itself.
So this is a pretty good indicator that the whole time this query was running, the 23 or so seconds this thing ran for, most of the time we were waiting on parallel thread stuff. And then if we dig, again, dig a little bit further into the execution plan, let’s get rid of this. We can look at almost anything that shows where rows end up.
We can look at this index seek operator, and we can look at the actual number of rows, and that will show us all the rows ending up on a single time. Right there. We could look at any one of the lines that shows where rows were distributed among threads and see it there.
Remember, initially, we looked at this line and this line. You would also see that if you look at this key lookup, where all of the rows, just like before, ended up on a single thread. And sort of likewise in the query plan, those sections where the rows are terribly skewed.
Oops, didn’t need that tooltip. Thanks anyway, though, tooltip. Very useful otherwise.
Those rows are where things in the query plan really did accumulate the most time. So basically all 23 seconds of the runtime for the plan was just in this chunk right here. So again, always be looking at your actual execution plans.
Always be paying attention to operator times. And if you have a parallel plan that you think is running very slowly, it’s always worth investigating if one or a couple of the threads ended up with the majority of the work, and you had a bunch of threads waiting around, sitting there doing nothing.
Now, another sort of interesting point in here is if we look at the thread stat for this query, we will see that this query was allowed to have three active branches. So three parallel branches were allowed to be running simultaneously.
We reserved 24 threads for this whole query to run, but we only used 17 of them. So at certain points, what’s that? Four, well, seven threads.
Remember, because it’s DOP8. At some point, seven threads are just like, I got nothing to do. Good. Not doing much over here.
So that’s another good indicator that perhaps your parallel query did not do the work that you thought it did. Now, what I have to do is over here, I have to figure out why I have a bunch of negative numbers in here and why I have a bunch of duplicates in here, because all I did was insert rows into this table for my SPID and then join that temp table over here.
I think I found a bug in SQL Server where parallel queries don’t clear out when you open new windows and you use the sys.dm exec session waitstats view. Something is a muck in there.
So that’s fun. It’s a lot of fun, actually. Really, really happy to find these things. One last thing that I will point out is that a lot of this information is available to you in the plan cache and query store. So if you go digging around the plan cache or query store, you might see times when you have a parallel query.
Last DOP 8, last elapsed time, 23 seconds. Last worker time, 23 seconds, where things are quite even despite a high DOP, and that could be a pretty big warning to you. There’s also these.
Well, I mean, we already looked at the DOP column, but let’s give it a pink hug anyway, where the last reserved threads 24, last used thread 17. So it kind of brings you that information that we saw in the actual execution plan. And, of course, that is our query here.
So morale of the story, you will not see this in cache plans. Cache plans don’t save the row stuff for you, right? You don’t have all that cool information about which rows ended up on which threads in cache plans.
So that’s the plan cache, query store. Estimated plans won’t show you that, but we can go digging because that’s what we are. We are query plan archaeologists.
We can go digging more into actual execution plans and figure this stuff out. So anyway, I hope you learned something. I hope you enjoyed yourselves.
What was the other thing? If you like this video at all, please consider throwing it a thumbs up. If you enjoy this sort of hard-hitting investigative SQL Server content, then feel free to like and subscribe to my channel.
Like the video, subscribe to the channel, in that order. And you will get helpful notifications every time I post this sort of hard-hitting investigative SQL Server content. Anyway, thank you for watching.
I will see you in another video some other time.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into an intriguing issue that caught my attention recently—specifically, how sort operators spill in row mode versus batch mode plans within SQL Server. This isn’t something you can easily fix; it’s more of a Microsoft problem they need to address. The video explores two queries with different memory grant hints, highlighting the surprising performance difference between sorts that spill and those that don’t. I also touch on some practical tips, like using Ctrl+R in SQL Server Management Studio to toggle result set visibility, which can be quite handy for keeping your workspace organized. Additionally, I discuss how batch mode sort spills can significantly impact query performance, making it crucial to always examine actual operator timings rather than relying solely on percentage-based metrics.
Full Transcript
Erik Darling here with Darling Data, coming to you on this crisp, clean, beautiful Friday. And in this video, I wanted to talk about something that I came across sort of recently, and there’s an issue open with Microsoft about it. It’s not something you can really do anything about. It’s an issue they have to fix. It has to do with when sort operators spill in in row mode versus batch mode plans. So I didn’t mean to spoil things there too much for you, but come on, grab the thing, do the thing, do it right. So there are two interesting things about this demo when we look at the row mode execution plans. First one is that when you look at how operators time leading up to this, you can see that we spent a little under a second, a little under a second, well, not exactly five seconds because repartition streams is weird. And then about 11, well, actually, let’s call it 12, 11.8. That’s, that’s close enough to 12 for me. About 12 seconds spilling out to disk. And then the entire query finishes in just about 12 and a half seconds. Now, when you compare that to a query down here, it doesn’t spill at all.
We still hit, we hit about 20 seconds here. Now this is weird, right? This is a strange circumstance. There’s a query that spills to disk that takes almost half as long as a query that doesn’t spill at all. It gets, in fact, if you look at the queries, I’m just going to get rid of that screen real quick. If you, if you use SQL Server Management Studio, and you ever get annoyed that, uh, like the results set is in the way, like the results set is in the way, just hit Ctrl and R. It’ll bring the results set window down and you can, you can just hit Ctrl and R again to bring it back up. It’s pretty, pretty useful, at least for me anyway. So, uh, this first query is hinted to have a max grant percent of one. And this second query is hinted to get 100% of the memory grant it wants. Now, the reason why the first query finishes so much faster is because I have really good I.O. hiding underneath my laptop at some Lenovo, Opel, SSD deal. Uh, and I.O. there is really fast. So when the sort starts writing out to disk, it can do, it can do two things. It can write to disk in small chunks and it can, it can build the sort in small chunks.
And doing a whole bunch of small things is sometimes a lot faster than doing one big thing. Um, the first time that I ever was, uh, sort of introduced to that within the context of databases was not with a loop or a cursor. It was, uh, an Adam mechanic session on cross apply where he was using cross apply and there was a row number thing inside it. And you still had to sort data, but you only had to sort data for a specific thing inside the cross apply rather than doing one big sort and bringing the whole thing over along.
So in this case, it is faster. I know this is going to sound crazy to you. It is faster for a sort to spill to disk and build the sort up in tiny chunks reading from disk than it is to do the entire sort in memory. Now you are probably not as lucky as I am. You are probably using some sand catastrophe where, uh, I mean, the disks might be good, but you know, the, the storage network from the, the server to the, to the disks might not be great. Uh, you might be on cloud storage, which is, uh, uh, pathologically wretched and the prices that you get charged for it should, there should be, there should be an SEC violation or something attached to those.
Uh, but where things get even more interesting is with batch mode. Now you might think, wow, batch mode super fast, right? Cause it’s good for all those big, crazy data warehousey, uh, insane aggregation queries.
Those like, like, like, like far edge of the world workloads where, you know, you have to aggregate the Kajagoogoo billion rows. But it turns out batch mode sort spills. Uh, so let’s take a look at these two queries, right?
Now, in row mode, the batch mode, I’m sorry, in row mode, the sort that spilled was wicked fast. And the sort that didn’t spill, well, not awful, was still 20 seconds. It was still almost twice as slow as the sort that spilled.
With the batch mode plan, have a gander at this. This sort spilled for a full four minutes and 11 seconds. That’s, that’s wall clock time.
That’s time on, that’s time out of your life. That’s time out of whoever ran this query, which is me. It’s time out of my life. And then the, the sort that didn’t spill only took about 3.2 seconds. So there are a couple messages here.
One, uh, sort spills in row mode might not be the end of the world for query performance. Remember to always get those actual execution plans and always look at operator times. If you are the sort of foolish person who looks at percentages, God help you.
You’re, you’re living in a far distant past of irrelevant query metrics. Sorry to say it. Second message is, if, uh, you are the type of futuristic forward-looking person who has embraced batch mode, either via fancy tricks, like, like empty filtered columnstore indexes or empty columnstore objects that you do funny little joins to, or you’re on, uh, 2019 and up and you’re using batch mode on rowstore, you might have to be very, very careful when you see batch mode spills.
Now, this, you should still always, of course, be getting the actual execution plan and looking at the actual operator timings on these things. Uh, there’s a very big difference in what’s actually, what’s actually good and bad in this query, percentage-wise, when you, when you look at, look at the actual operator times.
Uh, there’s something else profound and prolific that I was gonna say. Uh, so yeah, this is a, this is a Microsoft problem. Uh, this is something that, uh, they’re going to have to address.
Uh, the reason why the batch mode sort spills, uh, take as long as they do, apparently has to do with, uh, well, a couple issues. One is the, the way that, uh, some, uh, string data, deep data is stored in batch mode or dealt with during the sort, something like that. And the other is, uh, to do with, uh, how the, the batch mode operation, uh, reads the sort from disk, which is one big write, one big read, which apparently is, is not good for performance, I hear.
So, uh, there’s that. Well, anyway, uh, I’ve got other videos to record. And, uh, since I switched over to Camtasia and I’ve got this, this crazy partner app to Camtasia called Audiate.
Uh, after I, after I stop recording this, I get to go, uh, edit the audio portion of this so that you don’t have to hear any of my biology or anything else, which you probably don’t want to hear, which I don’t even want to hear. So, anyway, if you enjoyed this video, in the least, there was a slightest bit of joy or interest brought to you by this video, give me the old, that old thumbs up.
Uh, if you like this sort of SQL Server content, uh, you could pretty please subscribe to my channel. It’s a great channel. It’s a great channel.
Especially now that I have reliable recording software. Even if I have to do more work for you on the back end, it’s, it’s a reliable product that I get to, I get to share with you. Uh, I hope you enjoyed yourselves.
I hope you learned something. I hope you laughed. I hope you loved. I hope you lived. I hope you don’t buy home decorations from Pier 1. Uh, I don’t know.
Every once in a while they actually have some decent Halloween stuff though. I think we got some real spooky Halloween stuff in years past that I think we still, I think, didn’t break, which is amazing for Halloween stuff. Usually, on November 1st, that stuff just starts to like crumble.
It’s got a timer on it. Anyway, thank you for watching and, uh, we got another one to record. Actually, maybe, maybe one or two to record after this. We’ll see, we’ll see how motivated I am.
It is after all, Friday. Alright. Uh, I’m gonna very smoothly and suavely hit the stop button here. I’m not even looking at it.
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.
Sometimes people will ask me penetrating questions like “why does SQL Server choose a bad execution plan?” or “why is this query sometimes slow?”
Like many things in databases, it’s an endless spiral of multiverses (and turtles) in which many choose your own adventure games are played and, well, sometimes you get eaten by a Grue.
In this post, I’m going to talk at a high level about potential reasons for both.
I can’t possibly go in depth on both in a single blog post, but I thought it would be fun to talk about all the things that could possibly cause your Unfortunate Circumstances™️.
At any rate, I hope you enjoy it. And maybe learn something. And maybe feel bad about blaming SQL Server for things that are all your fault.
Getting There
A lot of assumptions are gonna get made here, because a lot of things can stand between you and your query getting to SQL Server.
We’re talking networks, security, Active Directory, the cloud. Who knows? Not me. I stick to what happens inside the server.
Just know that there are a lot of moving parts between your application server and your database server.
I’ve seen logins take 30+ seconds to process. Not on purpose, mind you. I stick to what happens inside the server.
But hey, maybe we can just say this all worked quickly, and now we can talk about the stuff I stick to.
Inside the server.
Upon Arrival
There are even more things that have to happen now, even after you log in.
Your connection has to pass some checks and do a bit of routing, and then at a high level, your query will either:
Re-use a cached plan if one exists
Start building a query plan
This is when SQL Server’s query processor comes along and goes through the various phases of figuring things out:
Parse
Bind
Expand views
OPTIMIZE!
Come up with an executable plan
Apply memory grant and degree of parallelism
There’s one step in that process that seem important. The actual optimization portion. In there, you have:
Simplification
Cardinality estimation
Join ordering (depends a bit on which search phase you make it to: 0, 1, 2
Plan exploration phases
Rules, Smoky
During plan exploration is when the optimizer starts applying various rules to your query.
If you want to see the names of all the crazy rules that get may get applied, look in here:
SELECT
deqts.*
FROM sys.dm_exec_query_transformation_stats AS deqts
ORDER BY
deqts.name;
In SQL Server 2022, there are about 440 of them. It’s a wonder anything gets done around here.
This process has to happen quickly. So when you throw your crazy-town queries at SQL Server, it may not figure out the single best possible plan ever.
If your queries are parameterized, this brand spankin’ new plan will be created for those parameters.
At this point, it would be good to remind you that your query hasn’t even started executing yet.
It’s just been waiting to get going.
Once it has an executable plan, that happens.
Inside the server.
Ready Set Go
Once your query gets on its horse, you’re subject to many potential holdups, both physical and logical. It may even have to wait for memory to compile an execution plan at times.
Memory to compile a query plan (RESOURCE_SEMAPHORE_QUERY_COMPILE)
Read pages from disk into memory (PAGEIOLATCH_SH, PAGEIOLATCH_EX)
Writing to the transaction log (WRITELOG)
Memory for a memory grant (RESOURCE_SEMAPHORE)
CPU threads to run on (THREADPOOL)
CPU attention while other queries are busy (SOS_SCHEDULER_YIELD)
Spilling to disk (IO_COMPLETION, SLEEP_TASK)
Building spools (EXECSYNC — parallel plans only)
Blocking (LCK_X)
Latching (LATCH_XX and PAGELATCH_XX)
Parallel threads to process (All those CX waits)
Not enough memory? Wait. Not enough CPU? Wait. Need to read data? Wait. Need to write data? Wait. Blocked? Wait.
And that doesn’t even count all the work your query might have to do while it’s running and not waiting on anything.
The next time you have a gripe about a slow query, try to keep all this stuff in mind.
There’s a lot going on.
Inside the server.
Since You Want To Know
A great way to see all the stuff your queries are waiting on all at once is to run sp_PressureDetector.
Inside the server.
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.
SQL Server doesn’t do much to help you figure out what’s wrong with it. Sure, it makes a lot of information available, but logging, decoding, and assembling all that information is hard work.
And it sure doesn’t help that a lot of it is stored in XML. Even when there are graphical options for displaying it, they end up hiding a lot of the really useful information.
It also has a lot of optional things that you have to turn on, and all of these things require you to monitor and alert on them.
In this post, I’m going to talk about why you should turn on Query Store, the Blocked Process Report, and capture deadlocks, and link you to free tools to analyze them.
This is all stuff that I help clients with as well, along with actually helping them fix the problems, but if you’re feeling keen on getting to it yourself, here’s what you should do.
Query Store
This thing is great for most SQL Servers out there. It’s like the plan cache, except it won’t keep disappearing on you.
To get it up and running, just run this command:
ALTER DATABASE
[YourDatabase]
SET QUERY_STORE
(
OPERATION_MODE = READ_WRITE,
MAX_STORAGE_SIZE_MB = 1024,
QUERY_CAPTURE_MODE = AUTO
);
After it’s turned on, you can check in on it in two ways:
Just running this will go back up to seven days and give you the top 10 queries by average CPU time. In other words, it’s the stuff that uses the most CPU whenever it runs.
If you find stuff you don’t care about, just copy and paste the contents of the query_id column, and do this to ignore them:
The next thing you’ll wanna do is start logging which queries are blocking each other. You never know when this will ratchet up and become a real big problem.
The number of times I hear “we had a bad blocking problem” but no one even has a screenshot of it hanging out would boggle the very depths of your mind.
To avoid that, you need to:
Enable the blocked process report
Enable something to collect it
First, do this:
EXEC sys.sp_configure
N'show advanced options',
1;
RECONFIGURE;
GO
EXEC sys.sp_configure
N'blocked process threshold',
5; --Seconds
RECONFIGURE;
GO
This will get SQL Server capturing any blocking events that go on longer than five seconds. This is about the level where people start hitting problems, but you can set it to a higher number if you feel like it.
Next, you’ll want to set up an Extended Event to house the data from it:
CREATE EVENT SESSION
blocked_process_report
ON SERVER
ADD EVENT
sqlserver.blocked_process_report
ADD TARGET
package0.event_file
(
SET filename = N'bpr'
)
WITH
(
MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION
blocked_process_report
ON SERVER
STATE = START;
GO
If you’re using Azure SQL DB, you’ll need to tweak this to use it successfully. It’s a lot harder to successfully set up and access event files there, and the ring buffer is far less valuable because it doesn’t hold onto data very long.
The easiest way to get at the data in there is to use my stored procedure sp_HumanEventsBlockViewer.
It’s as simple as pointing it at the event session you created, but there are a whole bunch of other parameters to filter data in or out.
The final thing you should do is set up something to collect deadlocks in the long term.
To do that, Extended Events is your friend all over again.
CREATE EVENT SESSION
deadlock
ON SERVER
ADD EVENT
sqlserver.xml_deadlock_report
ADD TARGET
package0.event_file
(
SET filename = N'deadlock'
)
WITH
(
MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION
deadlock
ON SERVER
STATE = START;
GO
While this stored procedure isn’t in my GitHub repo, it’s one that I do a heck of a lot of work on. The easiest way to parse everything out is with sp_BlitzLock.
It’s as simple as pointing it at your extended event session.
Having these three things set up and running will make your life a lot easier the next time you face a performance emergency with your SQL Server.
Sure, there are a lot of other things that could go wrong, but these are the most common areas I see clients having problems in.
If you need to do a post mortem on an issue, you might want to look at:
sp_LogHunter: Dig through your error logs for high severity issues
sp_HealthParser: Dig through the system health extended event for a wide variety of issues
And as always, if you need help working through these kinds of problems, hit the link below to get some consulting help from me.
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.
A Little About Select Queries That Cause Writes In SQL Server
Video Summary
In this video, I revisit an old topic with a fresh perspective and improved production quality. Erik Darling from Darling Data is back to discuss why select queries that cause writes in SQL Server are worth tuning, even though they appear to be simple read operations. I walk through a specific example using the Casey Kasem query, highlighting how eager index spools can lead to significant write operations despite the query being primarily a `SELECT`. By analyzing the query plan and leveraging tooltips for index pool information, I demonstrate how to identify and optimize such queries effectively. This video aims to provide valuable insights for database administrators looking to improve query performance by focusing on often-overlooked modification activities within select statements.
Full Transcript
Erik Darling here, apparently dying. I don’t know where that old man came from. Erik Darling here with Darling Data, recording another video with Camtasia, apparently, because the last one worked really well. I got to work on the camera focus a little bit. I noticed that I was a little blurry when I got in close and, well, you know, I didn’t feel great about being a little bit blurry. I’ll work ever diligently to be less blurry for you in the future. Since that went well, what I’m going to do is go back in time a little bit. I’m going to re-watch all my old videos where people were like, hey, the audio dropped out. And I’m going to try to re-record those. I’m not going to try to. I’m definitely going to re-record those. And, I don’t know, get twice the bang for the buck, I guess. And hopefully become less blurry. In the future. So, because this one was more recent. And, when I re-watched the video, quite frankly, I was appalled at how much of the audio dropped out. You know, you spot check this stuff, but you can’t catch everything.
So, I’m going to re-record these and start with this one. So, this is a redux, a redo, redo-dux, redo-dux, of why I care about select queries that cause writes in SQL Server. Now, when you insert, update, or delete, or select into, or merge data, you at least stand the chance of doing some writes. Like, you might affect zero rows and not do any. And, you know, I guess modification queries that don’t cause writes would be something interesting to talk about. But, no.
What I’m much more interested in, when I am tuning queries, when I am looking for queries to tune, is looking for queries that have written a lot of data that are only select queries. And, the reason why is because that often means that the queries had to spill a fair amount of data. There’s either a sort or a hash that spilled a good amount of data. Or, there was a spool in the query plan. Either a table spool or an index spool. And, you know, spools will show up in modification queries, oftentimes for Halloween protection.
But, you know, seeing them in select queries often, at least to me, says, there’s something we could fix here. There’s some room for improvement. Especially when there is an eager index spool. So, I have this query here. This select top 40, Casey Kasem query, where I have thoroughly untuned, detuned the query to the point where SQL Server says, You need an index so badly, I’m just going to create it for you. I’m not going to tell you about it because I don’t want to upset you.
But, here’s an index. And, when I run this query, and this query is just a simple thing that I put together to look at a few limited portions of the plan cache on my SQL Server. I don’t suggest you try to rewrite this query, use this specific query. You probably have your favorite plan cache query in the world. It might be, yes, people, it’s cash. I don’t know. Who knows?
I’ve never written my own plan cache query from scratch because I’ve grown to hate the plan cache over the years. I find it very unreliable. I often find that the plans that I need aren’t in there, either because the plan, the cache got cleared out, or there was a recompile, or, you know, what was the other good one that I had?
Oh, yeah. Maybe some knucklehead turned on an optimized for ad hoc workloads and ended up, we just only had a plan stub in the plan cache in place of a query that we could actually fix and tune, which is great, too. I love when that happens.
Good job, optimized for ad hoc workloaders. You really, really did a solid there. Thanks for that best practice. I wish there was a setting called optimized for no-lock workloads, and we could really make a good video. But anyway, pretty simple plan cache query.
It gives me just a little bit of information here, like the database name and the last execution time, which was today, recently, how many times the query executed, the total worker time, the total elapsed time, but more importantly, this column right here, the total logical rights.
Now, I included the memory grant stuff in here, because originally I was like, oh, I’ll show them what, like, spills, too, but I got a little bit lazy. And also, cached query plans don’t show spill operators, so I don’t know.
I just kind of passed on. I said I’ll save that for another video so I can make even more content. Just kidding. I’m not going to do that.
But we get the query text back, which we can see is the Casey Kasem query that I wrote before to grab the top 40 rows from the user’s table, and then we have the query plan. Now, in this query plan is one of my least favorite things to see in a query plan.
Actually, kind of like if you watch the NOLOC video, eager index pools are a lot like NOLOC hints. I love when I see them. I love when I see lots of them, and I love when I see them coming off of big tables, because I know that I can have an immediate profound impact on some queries without having to do any thinking whatsoever.
It’s just automatic at this point, which is great, because it’s one of those things that once you learn it, once you start applying it, you start to see what a huge difference tracking these things down and looking at them makes. Sometimes if I’m looking at weight stats on a server, and I see a lot of exec sync weights, E-X-E-C-S-Y-N-C, exec sync, if I see a lot of those, especially if there’s a long average duration for those weights, hoo-wee!
I know that I’m in for a lot of fun, because I know that there are going to be a lot of parallel query plans with eager index pools in them, and I’m going to be able to tune and tweet those, and act like I’m doing a whole lot of work when I’m really just goofing off making videos.
Shh! Don’t tell anyone! I don’t think… I’ve never been sued for theft of services yet, and hopefully I can avoid that for the rest of my life.
Just kidding. One of my mentors, Kendra Little, used to have a joke about how she would look busy while she was drawing cartoons and just tell her boss that code was compiling or something, and it reminded me of that.
So this query caused a lot of writes, because we had to write all… let’s see how many rows this is. Let’s do a little count here. I want to say that 8042010…
So we had to write 8 million rows into an index up in tempdb, an indexed object up in tempdb. You can think of the eager index pool like a temp table with a clustered index on it. Close enough. And so we had to index this query on the fly, and that’s why we did so many writes for this query, this simple select query.
Now, of course, if you watched other videos on my channel, and hopefully ones where the audio worked, I don’t want to say any names here, then you’ll know how to fix these. You look at the tooltip for the index pool, the eager index pool.
You can consider these seek predicates to be the key of the index you need to create. And you can think of the output list as the included columns that you need to create. In general, going back to our query, which is a little…
Oops, I went to the wrong demo window. Oh, I’m failing you all miserably. If we go back to the query that we ran, in the query plan, if we went by that, we only would have had the date column as an included column. But since we’re ordering by date, it makes a lot more sense to have the key of the index on the badges table beyond user ID, and then date, and then only include name.
The reason for that is because this cross-apply will most likely get applied as a nested loops join. And since we will have an equality predicate for each user ID, it’s more helpful for the order by part to have the date column in the key of the index, so we have the date in order for each user ID that we seek to, each equality predicate that we seek to.
So, if you’re like me, and you do a whole lot of digging through SQL Server, and you want a new thing to look for, when you are trying to find queries to tune to impress people, make money, get dates, whatever it is, whatever fame, fortune, and glory you seek in the world, look for select queries that cause a bunch of rights.
Now, there might be reasons, if it’s a small number of rights, it might be like a stats update, sticking a plan in query store, it might be, there are a couple other reasons out there. But if there’s a small number of rights, it might not be worth your time.
But when you see that number of rights start to really creep up to, you know, be like a larger number, like a six, seven digit number, maybe not a five, probably like a six, seven digit number, then that’s probably when I’d start paying attention and when I’d start want to looking at, looking deeply at that query plan to see whether it’s spills or spools, because you could be, you could really save a lot of performance problems by fixing these sorts of issues.
So anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. Again, because it’s the second time I recorded this because Streamlabs stabbed me in the back on the first one bad. And hopefully Camtasia doesn’t do the same thing. If you like this sort of content, especially with full audio, and hopefully crisp video, I tuned things a little bit. It’s a little hard to tell because my video preview is tiny here and I can’t tell exactly how bright and shiny I am.
Let’s see, what did I cover? Like the video, subscribe to the channel. Thank you for watching. And then I will see you in another video relatively soon. I may have even spoiled the contents of another video when I clicked on the wrong tab in SSMS. So, oh, don’t tell anyone.
I don’t want to get sued for stealing my own demo. I would do that too. I’m litigious. Anyway.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into why NOLOCK is my favorite query hint in SQL Server, presenting it as a tongue-in-cheek exploration of its prevalence and implications. I discuss how seeing NOLOCK hints everywhere often indicates problematic code that requires significant tuning and training to fix, leading to increased consulting opportunities. The video also touches on the benefits of enabling read-committed snapshot isolation and accelerated database recovery, which allow for cleaner queries without NOLOCK hints, ultimately making applications more robust and bug-free.
Full Transcript
Erik Darling here with Darling Data, recording this video with Camtasia because I tried recording it with Streamlabs again and shockingly did not go well. I listened to the whole video and about three quarters of the audio managed to drop out. So we’re going to stop doing that unless I can find some really incredible way of fixing that issue. And in this video, where the intent is a rather tongue-in-cheek video, is to talk about why NOLOCK is my favorite query hint in all of SQL Server. Now, the first reason is because I am a consultant. In other words, I get paid to fix problems. And NOLOCK is a big problem. Generally, the more NOLOCK hints that I see when I look at code. I can abide a few of them. But generally, the more NOLOCK hints that I see when I look at code, the more money I know that I’m going to make. I know that I’m going to make more money because the developers are going to need a lot more training. No one who knows what they’re doing puts NOLOCK hints everywhere. The code is going to need a lot of tuning because no code that runs quickly needs NOLOCK hints everywhere.
The indexes are going to need a lot of fixing because especially for modification queries, if they were finishing quickly, if they had good indexes to find the data that they were after, and they don’t need to modify a bajillion indexes in order to complete their work, well, they’d run pretty quickly too. Now, granted, there are certainly exceptions, like if you’re using the help hit known as merge, or if you’re for some reason trying to modify a kajillion rows at once, that could certainly hamper things a bit, but in general, most normal workloads, merge is an abnormal part of a workload, need my help for that. And I think probably the thing that gets me the most excited though is that I know when I see NOLOCK hints everywhere that there are going to be serious bugs in the software because you’re going to be reading dirty, crappy data from dirty, crappy tables.
Okay, the tables aren’t dirty and crappy, but the reads are. And when that happens, you’re going to read bad data and that bad data is going to end up in results and possibly used to do other things with NOLOCK hints that are also based on bad data. And there’s just going to be a lot of bad data floating around. And the more bad data floating around there is, the more chance of there being bugs in results and logic and all this, all the other good stuff that makes an application run well. I think the only thing that makes me also see cash registers floating in the sky is, wear an entity framework only shop. Because when I hear that, oh boy, start looking at private jets.
Another reason that I love NOLOCK is because no one actually knows what it does. Even though when I point it out and I say, hey, developers, how come there’s NOLOCK hints everywhere? Someone will stand up with all the confidence in the world, all the confidence in the known universe, the Padasha Emperor of Confidence. And they will say, so my query doesn’t take any locks. And I stare at the camera for a while and I say, aha, well, bad news. It’s not quite it.
You know, NOLOCK queries will still take that schema stability lock out. But really what the NOLOCK hint does is the same thing as the read uncommitted isolation level. And that is read bad data that is in the middle of being changed. Now, you can see the same rows with the same values twice, depending on what columns you’re selecting.
You could likewise see the same rows with different values twice, depending on what columns and rows you’re selecting. And you could also miss rows entirely with it. Now, granted, I do grant you this, that you can run into a lot of that with the read-committed pessimistic isolation level, which I find really not far off in its despicableness from the read uncommitted or NOLOCK isolation level.
But, yeah, it really does make things crappy. And, you know, in highly transactional systems, that can really cause all sorts of bugs, problems, issues. And best of all, one of my absolute favorite things in the world to do is turn on read-committed snapshot isolation.
And the reason I love doing that is because I get to turn it on. And in 2019, I also get to turn on accelerated database recovery. So I use the local version store rather than 10DB, which is phenomenal.
Love accelerated database recovery. Probably one of the best features that’s landed in SQL Server in I don’t know how long. But the best part is I get to turn that on and hang around and I get to delete all those NOLOCK hints and all those set transaction isolation level read uncommitted and I get paid to do it.
And I could keep doing that forever and ever while I look at private jets. And probably the third and final reason why I love NOLOCK hint is because I get to keep talking about it. I get to keep talking about it and writing about it and producing blog content about it and video content about it.
Along with, like, well, I mean, there probably used to be hundreds of other bloggers. These days, they all work at Microsoft and don’t do anything with themselves. Aside from work at Microsoft, it might be down to, like, a dozen or maybe two dozen bloggers that can still write and record videos and make content about how crappy NOLOCK is.
And a lot of them are consultants. And most of the time, no one listens to you until they’re paying you. So as a consultant who makes a lot of blog and video content and who gets paid to fix these things, I love the NOLOCK hint.
It is the gift that keeps on giving. It would be like if you opened up a bar slash bail bond shop across the street from a courthouse, people would just walk by and golf clap at your prowess in securing a prime location for your two businesses.
That is what NOLOCK, having NOLOCK and SQL Server is like. It is phenomenal. It is amazing.
I want to thank Microsoft. I want to thank whatever, like, original database standards committee said we need a way to give people bad data for some reason. I don’t know why.
But, yeah, it is miraculous that no matter how much blood and ink is spilled and dedicated to telling people and teaching people about why NOLOCK in, like, 95% of cases is a bad idea and not what you want to use and not what you want to be doing, people still will tell you that it’s a best practice and, like, their company’s coding guidelines or, like, you’re not allowed to have a query without it. But I’ve worked with some clients where with NOLOCK was a keyboard shortcut, pasting it in.
It’s truly a thing. It’s truly a mystery. Like, you’ll see it in, like, modification queries.
It is truly astounding and miraculous. And, again, I could fix that all day long because it’s really easy for me to fix that. And you won’t need NOLOCK hints anymore.
And you’ll wonder what you ever did without me. Just kidding. I don’t think. I’m not sure my wife even does that. She might wonder how she ever thought that she knew what true annoyance was without me.
Anyway, thank you for watching my inaugural video with Camtasia. I hope you learned something. I hope you enjoyed yourselves.
I hope the tongue-in-cheekness of this video came through. If you like the video, please give it a thumbs up. If you like amusing, fun SQL Server content, subscribe to my channel so that you get all the bling, bling, bling notifications whenever I drop one of these amazing videos. And, yeah, I think that’ll just about do it.
Thank you again for watching. And I will see you in another video real soon. Depending on how long it takes me to render and upload this video and make sure that all the audio and video is okay in it.
Well, it could be sooner than later. We’ll have to wait and see, won’t we? Anyway, thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
I was helping a client with an issue recently where they wanted to allow certain admin users to override changes currently in a table, but not allow anyone else to make changes.
The thing is, users had to be allowed to make other changes to the table, so it wasn’t something that could be handled easily with security features.
The example I’m going to show here is simplified a bit to get the code across to you, so keep that in mind.
Now, at first I was thinking about using some fancier features that SQL Server has to offer:
CONTEXT_INFO
sp_getapplock
But after talking over with the Most Valuable Kiwi, those ideas quickly lost their luster. They would both require additional handling in case of errors, and sp_getapplock could have issues under concurrency.
What turned out to be the best idea was actually the simplest: create a temp table with a very random name (in this case, running SELECT NEWID() and removing the dashes from the result) and having the trigger look for that to decide whether or not it should allow the update.
See below for the demo code.
Thanks for reading!
USE Crap;
GO
/*Out ye devil*/
DROP TABLE IF EXISTS
dbo.the_table;
/*Okay you can come back*/
CREATE TABLE
dbo.the_table
(
id int PRIMARY KEY IDENTITY,
dt datetime NOT NULL DEFAULT SYSDATETIME()
);
GO
/*One row is fine.*/
INSERT
dbo.the_table
(
dt
)
VALUES
(
DEFAULT
);
GO
/*Check me out*/
SELECT
tt.*
FROM dbo.the_table AS tt;
GO
/*
The trigger, and why:
Other options like SESSION_CONTEXT and sp_getapplock
require a whole bunch of other handling and can be weird
with connection pooling and under high concurrency
* Clearing out session context when done
* Error handling for sp_get applock to release locks
* A whole list of other stuff!
*/
CREATE OR ALTER TRIGGER
dbo.the_trigger
ON dbo.the_table
INSTEAD OF UPDATE
AS
BEGIN
IF
(
ROWCOUNT_BIG() = 0
OR OBJECT_ID(N'tempdb..#B7E66DC66D9C4C4182ECCF583D126677') IS NULL
)
BEGIN
RAISERROR('Only admins can update that column!', 11, 1) WITH NOWAIT;
RETURN;
END;
ELSE
BEGIN
SET NOCOUNT ON;
IF UPDATE(dt)
BEGIN
UPDATE t
SET t.dt = i.dt
FROM dbo.the_table AS t
JOIN Inserted AS i
ON i.id = t.id;
END
END;
END;
GO
/*
Create a #temp table for the trigger to pick up on
*/
CREATE OR ALTER PROCEDURE
dbo.the_admin_procedure
(
@id int,
@dt datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
CREATE TABLE
#B7E66DC66D9C4C4182ECCF583D126677
(
the_crumb bit
);
UPDATE t
SET t.dt = @dt
FROM dbo.the_table AS t
WHERE t.id = @id;
END;
GO
/*
Non-admin users don't get the temp table
*/
CREATE OR ALTER PROCEDURE
dbo.the_normal_procedure
(
@id int,
@dt datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
UPDATE t
SET t.dt = @dt
FROM dbo.the_table AS t
WHERE t.id = @id;
END;
GO
/*
In real life, you might be able to figure it out based on login names
*/
CREATE OR ALTER PROCEDURE
dbo.the_real_procedure
(
@id int,
@dt datetime
)
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
IF SUSER_SNAME() = 'sister_minnie'
BEGIN
CREATE TABLE
#B7E66DC66D9C4C4182ECCF583D126677
(
the_crumb bit
);
END;
UPDATE t
SET t.dt = @dt
FROM dbo.the_table AS t
WHERE t.id = @id;
END;
GO
/*
Show and tell
*/
EXEC dbo.the_normal_procedure
@id = 1,
@dt = '19000101';
EXEC dbo.the_admin_procedure
@id = 1,
@dt = '99991231';
SELECT
tt.*
FROM dbo.the_table AS tt;
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
In this video, I delve into an interesting problem I faced while working on a client project, where the goal was to conditionally trigger updates based on who called the stored procedure—specifically, distinguishing between regular users and administrators. To solve this, I explored various methods such as session context and SP get app lock but ultimately settled on using a simple temp table breadcrumb technique within the stored procedures themselves. This approach proved effective in managing the complexity of error handling and concurrency issues, ensuring that only authorized users could perform certain updates. By sharing this solution, I hope to offer a straightforward method for similar scenarios, avoiding the pitfalls associated with more complex security implementations.
Full Transcript
Erik Darling here with Darling Data. I started to record this video and then realized that my chair was in the frame and so I restarted it because I am a consummate professional. Nothing if not professional. Because I am a true professional, I do have to give some friends of mine credit for helping to talk me down off some rather than a professional. I am a realist ledger when I first started working on this problem. Now, the overall goal of the problem I was trying to solve was a client wanted a trigger to fire or not fire depending on which store procedure or more specifically in this case which user called a store procedure to update some stuff. Now, the whole thing was that like there was some admin functionality where the admins were allowed to do something that regular users weren’t. So, close enough approximation to the issue. So, let’s look at how I did that. Some problems that I hit along the way and how the thing got solved. So, I am just going to create a simple table here called the table and I am probably going to get some guff from someone about how it is not a very creative name and I usually give things creative names like your mom and your dad and your aunt Debbie and all sorts of other stuff that are highly professional, consummate professional.
Perfectly cromulent professional as my friend Drew might say. So, we have one row in this table when we go and look at it here. Alright, that’s today’s date and even date time close enough if you’re watching this. Now, I’m going to talk through the trigger a little bit up front. Because that makes sense to do doesn’t it? Talk through the trigger up front. And why I chose to do this rather than other options. So, when I first started thinking about this, like I had seen people use session context effectively for a lot of things. I’ve also seen products use session context quite poorly for a lot of things like an aware clause. So, like a real bad, real level security type deal. And I also thought about SP get app lock. I have a rather old video about SP get app lock that might still be worth watching but the quality just isn’t as superb and stupendous as it is in this video.
So, maybe I’ll re-record that one too. Maybe I really have my work cut out for me with all this free stuff that I just like giving away to people. So, I thought about session context and I thought about SP get app lock but there were a lot of complexities around that especially with handling errors like if the code failed, would SP get app lock still hold something? Would session context still be set to something? And like resetting like resetting session context or getting rid of SP get app lock and like a try catch block or something?
And it turns out that the oldest method of doing this is the simplest method and that is to leave a breadcrumb or create a breadcrumb temp table with inside of a store procedure. It doesn’t have to do anything and have SQL Server, have the trigger inside of SQL Server make some decision based on whether that temp table exists or not. So, what I did here or what I’m going to show you here is a close enough approximation of the trigger.
Now, if you’re watching closely at home, you’re going to notice that I have two checks here and if either of these checks is true, this error gets raised. And like, well, I do strongly suggest you have this at the beginning of all your triggers and you bail out on the trigger without a raise error here. I’m just doing both in one shot to be petty.
I don’t want overly long code distracting you in the video. I realize that it looks a little strange there, which might be distracting on its own. But just know I’m aware of it.
It’s intentional because I’m lazy. So, what this will do is if the row count is zero or if the temp table is not visible to the trigger, which it won’t be if it doesn’t get created, but it will be if it does get created in the store procedure, we’ll get this.
Only admins can update that column, which will look pretty funny for a row count of zero. And then if we pass that check and if we pass the update to the column that we care about check, then we will perform this update here. All right.
Fairly clear, I think, anyway. So, let’s make sure. So, I actually, when I was running through this demo at some point in the near past and I forgot to create the trigger. And then when I was like, big reveal, it was like, wait, nothing happened.
So, remember to create the trigger, Eric. I should put a note inside here. Remember to create the trigger. And so, we have, for me, again, kind of easier to do this rather than have like separate tabs where I call things and do things with separate users and blah, blah, blah.
I just have two copies of the store procedure. They both do about the same thing, except the admin procedure creates the breadcrumb temp table so that we’re allowed to do the update. And the not admin procedure does not create the temp table so that the update does not occur.
Remember, that’s an instead of update trigger up there. At least it should be. Otherwise, anyone can do the update.
If it’s after update, you can roll it back. That’s stupid. Just don’t do it. In real life, the procedure was sort of like this, where like we, like there was a username check and some permissions checks. And if those things pass, then the store procedure would be allowed to do the update and move forward.
Otherwise, it would not. So, this was closer to the real version. Granted, in real life, it was a lot cooler.
You know, like this is sort of like a Hawaiian t-shirt. It’s just a great idea just sort of presented poorly. So, keep in mind, this is really cool.
Just the demo isn’t all that cool. But it works. And if you ever have to do something like this, it’s a really cool way to do it without catching yourself under, you know, weird stuff with concurrency, error handling, resetting session context, things like that. So, what happens now, if I run the normal procedure, we get an error that says, what?
Only admins can update that column. But if we use the admin procedure, it completes successfully. And if we select from the table, we can see that our time, our date in the column is not 1900-0101 like the normal procedure would have made it.
But really, what I’m told is the end of time by SQL Server. So, there we have that. Now, if you ever have to do something like this, I do strongly suggest you use the temp table breadcrumb method.
Because it is the simplest one to set up, get going, all that other stuff. Coming back to the temp table name, all this is, I just did select new ID and took the dashes out. So, you can be, like, mostly, at least until, like, the end of time date, that you’re not going to have another temp table randomly get created with that in there.
You know, it’s a pretty good chance of that. Anyway, I like it. Worked for me.
Worked for my client. Everyone went home happy. It’s like, it’s a good night. It was like clown night at the goth bar. Everyone had a great time.
No one was disappointed. Everyone went home happy. So, anyway. I think it’s pretty cool. That’s why I decided to share it. If you think it’s pretty cool, or you think I’m pretty cool, or pretty, or cool, either one.
Like, any one of those will do. You should subscribe to my channel. If you like the, if you think the video is pretty cool, or pretty cool, you can like the video.
Which makes me feel good. I always like seeing the views and the likes pop up with a fairly even ratio. Otherwise, I wonder, who watched this and didn’t like it?
Who, who, who didn’t enjoy this content? Scoundrel. Couldn’t be bothered in their day to hit the thumb button for me. I’m going to come find you.
Anyway. Thank you for watching. Hope you enjoyed yourselves. I hope you learned something. And I hope that you will continue to watch these really high quality videos. It helps if you turn the quality up in YouTube.
If you’re watching this early, you’ve got to wait for like the full rendering to go through. But you can do it. I have faith in you. I know that you probably wait for the video to get high quality on other video sites that you might have opened in Incognito tabs.
So, I think you can do that for me too. Anyway. I will see you in another video very shortly. Because I’m on a roll today, baby.
Especially, as I got, I got my groove back. All right. All right. That’s enough.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
When you’re designing and testing SQL Server stored procedures (or views or functions or queries), you need to do the same thing.
While most of it isn’t a security concern, though it may be if you’re using Row Level Security, Dynamic Data Masking, or Encrypted Columns, you should try executing it as other users to make sure access is correct.
When I’m writing stored procedures for myself or for clients, here’s what I do.
Try To Reveal Bad Parameter Sniffing
Sometimes it’s easier than others, but here’s what you should try:
For equality predicates, run some count queries against those columns to find data skew
For range predicates (like dates) try small and large ranges to see if the plan changes
Try passing in NULL or blank values, especially for any string parameters
When you do this, grab and compare the execution plans. If you get crazy different plans, see what happens when you don’t recompile, and plans get shared across executions.
If performance is bad, think about these things:
Can you improve indexing?
Do you need a temp table?
How many CTEs do you need to throw in the garbage?
Should you use dynamic SQL to generate different plans?
Maybe a recompile hint would be good for a specific query?
This is at the top of my list for new code, because I don’t want it to get called in a weird way and go haywire. That’s not what people pay consultants for.
Speaking Of Dynamic SQL
If you’re not sure if yours is safe from SQL injection, now is the time to find out.
Especially for long-ish string parameters, try passing in some nefarious commands. In general, what you don’t want to see is dynamic SQL like this:
DECLARE
@s nvarchar(MAX) = N'',
@d nvarchar(40) = N'Jon Skeet';
SELECT
@s += N'
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
WHERE u.DisplayName = ''' + @d + N''';'
EXEC sys.sp_executesql
@s;
This is unsafe dynamic SQL, because it accepts user input and concatenates it into a string.
There are safe ways to accept user input, as long as either:
The user input dictates a static string to append to the dynamic SQL
The user input is parameterized within the dynamic SQL
Something like this is an example of taking user input and having it dictate a static string:
DECLARE
@s nvarchar(MAX) = N'',
@t nvarchar(40) = N'Votes';
IF @t = N'Users'
BEGIN
SELECT
@s += N'
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u;'
END;
IF @t = N'Votes'
BEGIN
SELECT
@s += N'
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v;'
END;
EXEC sys.sp_executesql
@s;
But this is a case where you should see what happens when you pass a lizard in a beer glass.
And of course, parameterized dynamic SQL looks like this:
DECLARE
@s nvarchar(MAX) = N'',
@d nvarchar(40) = N'Jon Skeet';
SELECT
@s += N'
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
WHERE u.DisplayName = @d;'
EXEC sys.sp_executesql
@s,
N'@d nvarchar(40)',
@d;
Run It From The Application
In SQL Server, there are these things called ANSI settings, and they can really screw with performance and execution plans.
Even if you’re not using indexed views, computed columns, or filtered indexes, you may see oddball things if you’re testing in SSMS and running code from somewhere else.
This is what SSMS uses, and what SQL Server needs to effectively use those indexed views, computed columns, and filtered indexes.
+-------------------------+----------------+
| SET options | Required value |
+-------------------------+----------------+
| ANSI_NULLS | ON |
| ANSI_PADDING | ON |
| ANSI_WARNINGS 1 | ON |
| ARITHABORT | ON |
| CONCAT_NULL_YIELDS_NULL | ON |
| NUMERIC_ROUNDABORT | OFF |
| QUOTED_IDENTIFIER | ON |
+-------------------------+----------------+
You’ll need to check your application(s) to see what they’re using, and make adjustments where necessary.
Bonus points if you’re using the Python or JDBC drivers, and you turn off those pesky implicit transactions.
This Is A Good Start
You may have other things to test, like if you’re offloading reads in an Availability Group, using security features, or other logical constructs.
If your code has a lot of variable assignment in it, you may want to override the lookups with static values to see what happens if some out of band values (especially NULLs) come around.
I do this a lot in the SQL Server Troubleshooting stored procedures that I write to make sure things go down the right path and the right things happen.
You should too.
No one expects the unexpected.
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.