We have an application that uses two RDS nodes [Write-Only + Read Replica]. The read replica does not autoscale in any way. Do you see any advantage in this architecture? To me it just looks like wasted resources, since the UPDATE/INSERT/DELETE will have to be executed twice now.
Can you give me a quick summary of the changes made to Query Store in SQL Server 2022?
Hi Erik, Could you record a video where you explain when ‘LEFT JOIN – WHERE IS NULL’ is preferrable from ‘NOT EXISTS’, if there are such cases ?
Our index templates for devs to use default indexes as Desc order. Are there any performance issues you’ve run into with this for single column indexes? I know an index can be read both ways, so curious what else it might impact.
We have 4 SSDs in a RAID10 setup for our database server. We recently replaced the SSDs and the data centre are asking if we want the cache to be enabled or if it was turned off for a reason. Is caching recommended for SSDs in a RAID10 setup? A read only cache?
In this video, I’m Erik Darling from Darling Data, and we’re diving into another exciting Office Hours episode where the community gets to ask me their burning SQL Server performance tuning questions. Whether you have queries about optimizing your database setup or just want some general advice on life and everything in between, these episodes are packed with valuable insights. I cover a range of topics from the practical aspects of SQL Server performance tuning—like when auto-scaling isn’t necessary for RDS nodes—to more nuanced areas such as the changes made to Query Store in SQL Server 2022. I also delve into some technical queries, explaining scenarios where `LEFT JOIN WHERE IS NULL` might be preferable over `NOT EXISTS`, and discussing the implications of indexed templates designed with default sorting columns. Whether you’re a seasoned DBA or just starting out, there’s something for everyone in this episode.
Full Transcript
Erik Darling here with Darling Data, and I am just pleased as punch because we are about to do another episode of Office Hours! Yay! These are the episodes where I answer five user submitted questions that you, the user, submits. Typically about SQL Server performance tuning, but you are free to ask me personal questions about life, love, weightlifting, I don’t know, eating, drinking, sleep hygiene, general hygiene, etiquette, manners, whatever, whatever you need to know about because I’m, I’m a man of the world, baby! I know all sorts of stuff. If you would like to answer, ask questions that I answer on Office Hours, if you start answering questions, we’re in trouble. You have this wonderful, beautiful link right here in the most kindest of blues. That link is provided to you in the description. below the topic of video description. Below the topic of video description, there is also a link where you can sign up for a membership to say thanks for doing all this stuff that you do here on YouTube for SQL Server people. Join 60 or so other people who have said yes to the dress. If you like this content, but you don’t particularly like dresses, you can also like, comment, and subscribe. Other ways to help my channel become the top SQL Server channel on YouTube. All wonderful things that you can do. If you need help with SQL Server, if you just, if you’re staring at this thing and it’s just not getting any faster by you staring at it, you can hire me. I am a consultant and I do this stuff for a lot of people all the time. Health checks, performance analysis, hands-on tuning of your servers, queries, and indexes, dealing with SQL Server performance emergencies, and of course, training your developers so that you have fewer SQL Server performance emergencies in general, which is usually a great way to improve sleep hygiene. We’re talking about that. If you would like to get some training from me, you can do that as well. Go to that link, plug in that discount code, and you can get 75% off whatever you buy. If you buy the everything bundle, you can get that for about $150, and that lasts for the rest of your life.
So, live a long time. Happily, healthily, all that stuff. Upcoming events. This slide, all of a sudden, got very, very busy. There is a lot going on in the SQL Server data community at the moment. We have SQL Saturday, New York City, May 10th of 2025. We have Redgate on tour in New York City, August 18th through 20th, in Dallas from September 15th to 17th, and in Amsterdam from October 1st to 3rd. And, of course, we have the venerable Pass Data. Well, I suppose I should say Pass Data Community Summit, but I don’t want to spell community wrong and mess anything up.
Taking place in Seattle, November 17th to 21st. I should probably double check that, make sure. Let’s see. Our internet’s a little slow here. Let’s see. Pass Data Summit, 2025. Pass Data Community Summit. Oh, that’s a wrap for Community Summit. Hey, there we go. Yep, 17th to 21st. I was right. Cool. All right. Let’s go answer some questions now.
What’s that? I’m dead. No. Yeah, it’s so cold. All right. Let’s answer these questions here. We have an application that uses two RDS nodes, one write-only and one read replica. Well, that makes sense. Well, that depends on what you call a waste of resources.
Well, that depends on what you call a waste of resources. I don’t think you’re offloading reads if you’re keen on that. So I don’t think it’s a waste depending on how you’re using it. I don’t really see what auto scaling has to do with it unless you have a primary on like one set of hardware and a read replica on some diminutive set of hardware.
But that’s between you and whatever buttons you clicked in Amazon. But no, I don’t think it’s a waste. There are all sorts of good uses for replicas that don’t involve auto scaling. Bit of an odd bird there. All right. Can you give me a quick summary of the changes made to Query Store and SQL Server 2022?
Gosh, we are going into reading the documentation mode. All right. So let’s see here. Query Store is on by default. That’s a nice one. Query Store is on by default. There are all sorts of additional configuration options that you can set to decide which queries end up in Query Store.
It got like, what is it, optimized plan forcing or whatever that feature is called. That one’s kind of neat. You get Query Store hints, right? You can add hints. You can manually add hints to queries in Query Store. It got tied into, I think, a couple other features.
I forget if the parameter sensitive plan one needs queries to… It feels like it doesn’t. Maybe it does. I don’t know. But things like cardinality estimation feedback, memory grant feedback, those rely on Query Store. We’re supposed to get like the thing where it’s available on second, like AG second, availability group secondaries.
But that’s still not in… That’s like not safe for production, so says Microsoft, three years later. So, I don’t know. Got me on that one.
But yeah, that’s sort of the important stuff that I can think of off the top of my head. Let’s see. Hi, Eric. Could you record a video where you explain when left join where is null is preferable from not exist, if there are such cases? Well, I would if I ran across such a case.
For the most part, the cases I run into, not exist just works a whole heck of a lot better. So, if I happen to come across a case where the left join, where something is null pattern works out better, I will surely record a video for it because that will be a magical outlier to what I generally witness out in the wild. All right.
Let’s see here. This is probably a weird one. Okay. Are indexed templates for devs to use defaults? Okay.
So, you have indexed templates for your developers that presumably someone created where you default to sorting columns in descending order. Are there any performance issues you’ve run into with this for single column indexes? I know an index can be read both ways, so curious what it might impact.
Well, yeah, sure. I mean, lots of stuff. But it really depends on what your queries are doing.
Queries can be read both ways. However, backward scans are not eligible for parallelism. So, that’s one thing.
If you are using a lot of windowing functions where you need to sort things in descending order, this might be very helpful. If you’re not, it might not be. If a lot of your queries are asking for columns in descending order, this could be useful.
Like in the order by, that could be useful. But, geez, for someone who has made a template for developers, you sure seem unaware of the repercussions of your choices in designing these templates. Or whomever designed these templates could maybe answer the question as to why it was designed that way.
And shed some light on exactly their decision-making process for that choice. So, yeah, there’s all sorts of stuff that it can affect. But does it affect?
I don’t know. You and I would have to look at your queries together. It sounds like a fun consulting engagement. All right. We have four SSDs in a RAID 10 setup for our database server. We recently replaced the SSDs in the data center.
We are asking if we want the cache to be enabled or if it was turned off for a reason. Is caching recommended for SSDs in a RAID 10 setup, a read-only cache? So, here’s my take on it.
Is I don’t want… I generally don’t want any caching on my disks. Generally, I want my caching done with memory.
I know this isn’t going to directly answer your question. But really, your reads should be as non-reliant on disk in any disk settings as possible. You should strive to keep the set of data that you most often care about in the buffer pool because going to disk is always going to be slow and painful, regardless of any caching settings you have on there.
But the only thing that cache settings do is inflate crystal disk mark benchmarks. So, you know, sure, it might be nice to have some moderate cache of, like, hot data for reads on there, but your goal should not be to read anything from disk anyway.
You didn’t ask about right caches, but that’s a whole prickly world of strange things anyway. So, yeah, I don’t have a big strong opinion on this because my opinion gears more towards, hey, let’s not go to disk.
Let’s have it in memory so we don’t have to deal with it as much. You might be in a special situation where maybe you’re on standard edition and your buffer pool is limited to 128 gigs.
Or maybe you’re on enterprise edition, but you have, you know, 70 terabytes of data and you can’t possibly get enough memory to deal with that. That’s when you should be looking into stuff like compression, page compression, maybe even columnstore, maybe even using a handy script to clean up your indexes, like sp index cleanup, which you can get at my GitHub repo.
So there’s all sorts of things that I would want to get through before I start tinkering with disk cache settings because once you get, like, I feel like if you get to the point where you’re tinkering with disk stuff, you have sort of failed your workload in a different way.
Like there are many things within SQL Server that you, and, you know, with memory that you could be doing that would provide you much, much more benefit than worrying about this. This is not a finish line setting at all by any stretch of the imagination.
So work on some of the other more fundamental stuff first. All right. So that’s five questions, right? One, two, three, four, five.
So says the row numbers. The row numbers never lie. So we’re going to put this one in the bucket. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in whatever the next video is. Whatever just explodes out of my brain and onto the screen.
All right. Anyway. All right. Thank you for watching. 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.
In this video, I present a riddle involving two SQL Server queries in SQL Server Management Studio (SSMS) to challenge your analytical skills and deepen your understanding of query optimization. The first query includes the `owner user ID` column from the `post` table with a hash join hint, while the second query omits this column, leading to an optimizer error despite no apparent reason for it. If you enjoy riddles or are interested in SQL Server performance tuning, I invite you to sign up for a membership and help me solve this intriguing puzzle. Your support will ensure more riddle content in future videos! For those who prefer straightforward solutions, feel free to leave a comment expressing your desire for no more riddles—though that might just make the riddles even more tempting.
Full Transcript
Erik Darling here with Darling Data, and in today’s video I have a riddle for you. So if you enjoy riddles, stick around. If you don’t enjoy riddles, I’m giving plenty of warning to avoid having to be riddled by anything. But today I’m going to present a riddle, and tomorrow I’m going to do my best to answer the riddle to your satisfaction. Hopefully you will not react as terribly as the troll under the bridge, and you will not react as terribly as the troll under the bridge. I’m going to eat one of my toes. It hurts to stand. If you would like, if you like riddles, boy, you can show me how much you like riddles by signing up for a membership. So the more people who sign up for a membership after this video, the more riddle content you will get. I will riddle my butt off. If you hate riddles, you can also leave a comment that says, hey, I would sign up for a membership if you never tell another one. The link to do that is down in the video description. If you are just sort of nonplussed by riddles, I hope that’s the right word for that. You can like, you can comment, you can subscribe, or if, I don’t know, whatever you feel like doing. If you want to ask me a question during office hours, I was going to go somewhere with that, and then I decided against it.
If you would like to ask me a question for my office hours episode, that is the link. It will not be blue in the video description, so don’t get freaked out if the colors aren’t the same. If you have your own query plan riddles, if you just have so many riddles that you can’t deal with them all, and you would like some help with that, I am available for hire. You can literally pay me money to solve your SQL Server performance problems.
Health checks, performance analysis, hands-on tuning, dealing with performance emergencies, and training your developers so that they don’t get their toes eaten by the troll under the query plan riddle bridge, whatever. I do all that, and as always, my rates, they’re reasonable. If you would like some also reasonably priced stuff from me, I have training, about 24 hours of it, a little bit more than that, depending on if you watch it at double speed.
If you watch it at regular speed, it’s about 25, 26 hours. But, you can get all that for about 150 US bucks with the discount code right there. Also, it will not be blue in the video description, but the link will be there for you.
Upcoming events, boy, oh boy, oh boy. Spending a lot of time in New York City, aren’t I? SQL Saturday, New York City, coming up on May the 10th, fast approaching, just a couple few weeks away. Get your seats while you can.
There is a performance tuning pre-con on May the 9th by Andreas Valter. And we are all very excited about all of this. We are putting on our finest New York City attire to attend this event. So, please dress appropriately.
And the fine folks at Redgate are bringing Pass on Tour. We will be mini-passing in New York City, August 18th to 20th. The most beautiful time of year to be in New York City.
Then we’ll be in Dallas, September 15th to 17th. Again, beautiful time of year to be in Dallas. And then Amsterdam. Wow, I mean, just beautiful times of year all around. October 1st to 3rd.
And all that’s going to be great. And you should buy tickets and come and hang out with me. Imagine seeing Erik Darling in Amsterdam. Be amazing, right? Who knows?
Who knows what hijinks we could get in together. And then, of course, the big event, Pass Data Community Summit, taking place in Seattle, November 17th to 21st. We will be having a grand old time in beautiful Seattle on the edge of fall.
If you look out across the soundy area, there’s just wonderful foliage everywhere. Highly recommend it. But with that out of the way, let’s riddle ourselves a little bit.
Let’s go over to SQL Server Management Studio. And I’m going to show you two queries. All right? Two queries. We got two of them.
Otherwise, there would be no riddle. And the first query, well, actually, let’s just say there is one difference. Shut up, tooltips. There is one difference between these two queries.
And the first query, the column owner user ID from the post table, that’s P. That’s P up there, is present in the select list. And we have a hash join hint down here.
All right? Obviously, the hash join hint is integral to the riddle. And in the second query, the only difference is that the owner user ID column has been removed. We have quoted that out.
So if we run this first query. Wait, wait, wait. Give this a few seconds. This thing doesn’t have any very special indexes to help it. It takes about four seconds.
But it runs and returns results that are correct. And we get a query plan from it. You can see all this stuff. SSMS is hiding our missing index request a little bit. That’s not so important to the riddle, though.
But we run and we get a valid plan and everything is okay. Now, if I try to run this second query with owner user ID removed, what happens? You get an error.
Not an Eric. We get an error. Though I am frequently confused with such things. The error that we get is that the query processor cannot produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using set force plan.
Well, why is that? Why would owner user ID being emitted from the select list make this an invalid query? Now, if we don’t highlight the hash join hint, if we just highlight this part of the query and we get an estimated plan, this is what it looks like.
We don’t have hash joins at all. We have a nested loops join. We have a nested loops join where SQL Server is saying we don’t have a join predicate, which is a little bit silly because if we highlight, or rather, if we bring up this tooltip and we see that we have a predicate here for where p.owner user ID equals 22656.
And if we highlight this, we will see that we have a predicate where user ID in the comments table equals 22656. So technically, the predicate has been resolved and we don’t need a predicate at the nested loops join. But we also have now a nested loops join going to the users table.
So there are no hash joins in this query. Your job is to figure out why. Your job is to figure out which part of the query makes this query optimizer error occur and try to come up with a reasonable explanation for it. Should you choose to accept this mission, of course.
If you don’t, I understand. You have better things to do. Waiting for the answer. So anyway, this is where I’m going to leave things.
These two queries and, of course, a link to the demo database will be available in a GitHub GIST or GIST. I don’t know if it’s a GitHub GIST, GitHub GIST, GitHub GIST, I don’t know, something like that. It’s hard to tell how they wanted that pronounced.
I’m not going to get into the GIF versus GIF debate on this one. It’s weird to say a GitHub GIST. Anyway, I promise I’m dead sober for these.
That’s the worst. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope that you enjoyed this riddle and I hope that you will try to answer this riddle. If not, then stick around for tomorrow’s video in which I will provide as in-depth an answer to this as possible.
Anyway, thank you for watching. Where’s the button? There’s the button.
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.
Does The FORMAT Function Still Suck For Performance in SQL Server?
Video Summary
In this video, I delve into the performance implications of using SQL Server’s FORMAT function, particularly when dealing with large datasets. I compare its execution against a simpler alternative—using CONVERT—and highlight significant differences in query plans and runtime. By running two queries side-by-side—one utilizing FORMAT and the other employing CONVERT—I demonstrate that while both yield similar results for smaller row counts, the FORMAT function can significantly slow down operations on larger datasets, making it less ideal for high-throughput environments. I also discuss scenarios where FORMAT offers advantages, such as custom date formatting requirements, but emphasize the importance of considering performance when working with extensive data volumes.
Full Transcript
Erik Darling here with Darling Data. And today’s short video, so short in fact that I’m not going to do the full intro slides because they would actually be longer than what I have to say about what’s in the video. We’re going to ask and answer a rather simple question. Does the format function still suck? And by still suck, I don’t mean like, does it do something bad? No, it does. Does some actually kind of neat things that you can’t do very easily otherwise? But it can still be pretty bad for performance, especially if you need to put a lot of rows through it, which is unfortunate because, you know, I use it in a lot of my procedures to very conveniently format numbers and percentages and other stuff, but I do it over a relatively small number of rows, tens or hundreds or, I mean, maybe at the high end, like a few thousand. And it just doesn’t really cause much of an issue there. So there is, there is a real need to like, like specify that format really does like the penalty for it really is when you start to get up to bigger and bigger row counts. That’s not to say that it’s free for smaller row counts. It would certainly be slower than other alternatives, but it is, you’re just not going to see like big difference.
I think if you’re like doing like a select top five or something, right? Like you need to format something, use the format function five rows is not going to cause a meaningful difference. You know, if you’re using this in an OLTP environment where absolute throughput is crucial, I might, I might give it a, you know, a second consideration to not to get it out of there because you could be, you could be, you know, adding just enough overhead to every single thing to like mash on the system a little harder, but in general, it wouldn’t be the first thing that I go for. So without going through everything else, let’s go over to Management Studio. And I’m going to show you as soon as that highlighting comes the hell down. So, two queries, two queries. They both use, come on, wake up, zoom it. Almost. Are you there? There you are. There’s a pretty lady. I’m going to use my patented method of forcing SQL Server to fully process a result set.
By fully process a result set. I mean, do all the work in here without actually producing any results. By not producing any results, I mean by filtering to where row number equals zero. SQL Server isn’t smart enough to know that no row number can start with zero. So it does all this work and then throws it all away, filters it all out. So I’ve got this query up here that does this. And you can see that I’m using the format function not to do anything so crazy. I am just formatting this date as year, month, day, right? So in other words, basically like the equivalent of saying convert date, get date, which coincidentally is what I’m doing down here. Convert date, get date.
So these would produce equivalent results, right? Converting creation date to a date. I guess if you want to get really picky, I would have to say like convert and barcar 10 or something around this, but it’s just not going to make a big difference for what I’m going to show you next. The second part of this would be running these, but you know, like I said, aiming to keep this video rather short. So I ran these ahead of time. So you didn’t have to sit there and wait on all sorts of spinny things. And you’ll see that the top query plan, which runs through about 24 and a half million rows in total, that takes about nine seconds.
The bottom query plan, which does the exact same thing, takes about three seconds. So let’s stop here and let’s look at where the differences in this query plan really are. I have to remember which way to turn so that this happens correctly.
You’ll notice in the top query, my hand is conveniently cut off. That scan of the clustered index takes about 800 milliseconds. This one here, a little bit longer, right?
But that’s not because of the function. The function isn’t like in the where clause. It’s just in the select list. If you look up there, we have a sort that takes 2.1 seconds. And that sort’s pretty close, right? 2.104 to 2.181.
Those are little CPU timing differences, maybe. Then we have a window aggregate, a lovely batch mode window aggregate. Oh, dear. Well, you sure do love me a window aggregate.
25 milliseconds, 19 milliseconds. You know, it’s tiny little timing differences. But then we get to our friend the compute scalar. This is where things heat up a little bit.
The compute scalar for the format function. Takes 6.1 seconds. 6.1 seconds. The compute scalar for the convert function.
Takes 23 milliseconds. Then we have a little filter over here. This is where we filter out to row number equals zero. That’s why, this is why the row count goes suddenly from 24.5 million rows to zero rows in both of these.
All right. You can see that pretty steep drop off there. It’s a pretty big cliff. Boop.
Boosh. But all the time is spent in the compute scalar. And if you go to the properties of the compute scalar. So if you’re looking at an execution plan and you’re using the format function. And you notice that there’s a compute scalar that takes us a strangely long amount of time.
You might want to just verify that. So one easy way to do that is to go to the defined values. You’ll see the list of expressions that the compute scalar is responsible for computing.
And if we zoom in up here, we will see the function name for this one is format. And we’ll see all the stuff that format is doing in there. And that’s where the 6 seconds goes.
The, sorry, the compute scalar down here. Same deal, except this one is going to show us convert. And we’re going to see the work that the convert does in here. Where did the convert go?
There, there, there she’s hiding. So if you’re using the convert function over a large number of rows, think about like a big ETL processor, data import thing.
I would strongly urge you to use something different. Use convert with a style that gets you the formatting you want rather than using convert. Convert is very convenient and very easy to use.
And it’s actually flexible to, it’s a little bit easier to do some stuff with format than it is to do with convert. But convert has a lot less penalty and overhead to it from a performance perspective. Just as an example, I have one client who needs to present dates with like a four digit year, two digit month, two digit day, but then only with hours and minutes, like no seconds or milliseconds.
And format does that really easily. That’s a very easy specification in format. I haven’t really seen a way to do that with convert.
You can get convert pretty close and then use like, like, use like a substring or like left or right or whatever to cut off the minutes and seconds and milliseconds if you need to from using convert. But there’s no like, just automatic like style to do that. So just be careful out there.
If you are using format with like big data processing, loading, like modifications, stuff like that. You might want to take a second look at your query, your actual execution plans and see if you have any big time spent in compute scale hours because you might be able to make significant improvements by, by using convert instead. Nor did I said convert and not cast.
We do not use cast in the Darling data household. We use convert in the Darling data household. Cast is icky, except try cast because Microsofty reasons. But we’ll talk about that in another video.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video, which will be about something equally as useful. I promise.
All right. 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.
In this video, I introduce a new stored procedure called SP_PerfCheck that I’ve been working on, silently and slowly but surely. This procedure is designed to quickly assess important performance-related settings and issues on your SQL Server, whether it’s an on-premises installation, a managed instance, Azure SQL DB, or Amazon RDS. The goal is to provide a straightforward overview of key performance metrics without delving into other areas like backups or database integrity checks, which can be complex due to the evolving landscape of SQL Server features and cloud environments. SP_PerfCheck focuses on performance configurations and settings, offering a useful set of information that can help you quickly identify potential issues and optimize your server’s performance.
Full Transcript
Erik Darling here with Darling Data, and in today’s Darling video, we are going to introduce a brand new member to the family. No, not a new employee, not a new baby. Are you out of your minds? I am damn near 45 years old. What business do I have procreating? This is a new store procedure that I’ve been working on, silently, slowly, but surely, called SP underscore PerfCheck. The goal of this is to quickly look at important performance-related settings and issues on your server, your SQL Server, sort of regardless of where it runs. So this is compatible with on-prem SQL Server, managed instance, Azure SQL DB, and Amazon RDS. Everything else is just a VM in the cloud, right? And I can already hear what you’re saying. Well, why do a lot of things? Why do all this stuff in your own procedure? Why not just do stuff with SP Blitz? Well, SP Blitz is like an overall sort of health checky thing. I don’t, in this procedure, I don’t spend any time looking at backups, particularly backups, because like the backup story gets harder and harder to tell with like AGs, like people taking backups on secondaries, the cloud where who knows what. MSDB is not like a great source of truth for a lot of things. I’m not going in, making sure you’re running DBCC CheckDB. I’m not looking at like torn page and check some page verification stuff. I’m just focusing on performance metrics and performance configuration stuff. That’s it. That’s the whole point. I don’t really do anything else in there. I get in, list out a whole bunch of important stuff and try to get you the right information. Now, this is largely only compatible with SQL Server 2016 and up.
There’s a lot of stuff that on 2014, it just gets real annoying or before 2016, it just gets real annoying to figure out. So I left that out of the batch. Given that SQL Server 2019 just hit the end of mainstream support, going back prior to 2016 seems rather wasteful. So we’re not going to do that. So let’s take a quick look at a couple of things. First, over on my website, there is a URL for SP PerfCheck. Now, this doesn’t go through a lot of the stuff that some of the other URLs do, because what I wanted to have here was a largely accurate, maybe not completely cut out list of all of the things that it looks for. So if we zoom in a little bit here, we’ll see the commands and stuff that it looks for. Some of this stuff, if you’re used to running SP Blitz will be familiar to you.
Like I did try to make it so that if you’re used to seeing performance-related metrics there, you will also see them here. But I also go and look at a lot of other stuff. So settings, storage performance, database configuration stuff, query store stuff, what do you call them, database scope configurations, things like that. And I do some looking at weight stats and, I don’t know, this section is for like real weird stuff. So I put it kind of on its own.
But if you scroll down through all this, you will get some more information about all of like, like there are URLs in the procedure that will get you to all of this stuff in here. As for the store procedure itself, of course, it starts off with the usual ASCII art that I handcraft with nothing but concentration and my deep affection for you. And then, of course, you know, fun fun ensues after that.
And over here, a couple of things that are important to note. One of them is if you want to get help with this procedure, there is a help parameter because I put a help parameter in all of my procedures so that you can get help so that you don’t have to email me to ask me what a parameter is. So there’s not a lot of parameters for this one just yet because this is sort of in the newish stages of development.
Doing a lot of just sort of like finding my sea legs with it, figuring out exactly what I want to do and check. Figuring out like sort of getting a good idea like what’s in scope for the results. If you need, if you run into a problem with this and you feel like the problem might be dynamic SQL related, there is a debug mode which will print out all of the stuff that will return results for everything in like the temp tables and stuff.
And then, of course, in the messages tab, you will see all of the dynamic SQL printed out. That includes for the like the overall stuff and for the database by database stuff. As for actual results, as for like what the stuff that you will see in here, it is what I think is a pretty useful set of stuff.
Up at the top is sort of a summary of important things that like things that are good for you to know about the server. So that’s this section up here where I just like highlight all the normal stuff. You know, well, this is of course the version of the store procedure, not SQL Server.
You know, and this is like, you know, having stuff like this is really useful, especially if, you know, you are making like a spreadsheet of, you know, servers or you’re a consultant and you want to take a quick screenshot that gives you like a nice like overview of stuff in the server. Right. Like this current server configuration, all sorts of good things in there for you to dig into and, you know, have have a screenshot of so that, you know, the state of the server when you looked at it.
And then down below are the results where you will see all of the stuff that was picked up by by SP perf check. And of course, this URL column over here will bring you to the correct spot on the page for whatever finding we have in here. And this server is in relatively good shape.
I leave something slightly out of tune. So when I run this stuff, I have like a little built in unit test for it. But there was a lot of time spent configuring and unconfiguring things to make sure that the various checks work. So if you are in the market for a store procedure that gives you a good performance overview of settings and bad stuff going on and like there’s stuff about weight stats in here that doesn’t, that hasn’t popped up on this because I haven’t been doing a lot of like crazy performancey stuff on here where wait that like meet the weight stats, thresholds, but trust me there’s good stuff in there.
So if you are looking for a performance check of a SQL Server that you are looking at, whether it’s Azure, managed instance, on-prem, VM, RDS, whatever, this thing should have you pretty well covered. So if you have any questions, comments, or concerns about the code involved here, you can either go to code.erikdarling.com. That’ll bring you to my GitHub repo where you can open issues, ask questions, suggest improvements to things.
Or you can go to my website and contact me. There’s a contact form on my website where I will say for support, please go to GitHub. Because that’s where I do my script support.
I don’t tend to do script support via email because it is very hard to track issues and stuff via email. I have a difficult enough time sensibly maintaining a nicely organized GitHub repo with the fury that I work, the furious pace that I work at. So anyway, thank you for watching.
I hope you enjoyed yourselves. I was going to say I hope you learned something, but you’re probably going to just learn about this script existing. So that’s a plus, right? That’s something to learn.
And I hope that you find this utility script useful. You know, again, these are not tools that I make for the community. These are tools that I make for myself that make my job easier, that I choose to share with the community.
But I do want other people to use them and I do want to get feedback on them. I do want to improve things so that they are more useful for everybody, myself included. There is a benefit for all of us here.
We all get better with your help, with our help. Something like that. Anyway, thank you for watching.
Go get this thing from code.erikdarling.com. It goes right to my GitHub repo. Boom, boom, boom, boom, boom. All of a sudden, there you are.
Professional SQL Server Performance Consultant. All you need, a few stored procedures. A couple books. About 15 years of forgetting everything else important and stuffing your brain full of database stuff.
Minor, minor things. Anyway, I’m going to go now. 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.
Many of our Tables have 15 plus guid s from other tables, but not declared as Foreign Key. Plus Foreigne Keys. When I add a Index on for every Foreign Key plus Indexes for declared foreign keys we end up with tables with 30 plus indexes for fks only. We don’t have write performance issues. would you index every fk for every possible join?
Do you ever see big wins just from changing compatibility level? What specific features cause these big wins?
When you hit performance issues that seem like actual bugs in SQL Server itself, have you ever had success engaging with Microsoft directly on getting improvements, or is it so much effort that if you can get by with your own approach, just don’t bother?
Hi Erik, our company is in the early-mid stages of migrating to Azure Managed Instance from on-prem SQL Server. Could you summarize some of the main issues/complaints you and/or your clients have with it?
Long time viewer. I know all of the theory behind the supremacy of RCSI, but I have 90 days to convince 25 people to enable it. Any advice just about winning those arguments?
In this video, I delve into some of the common issues and questions that arise when working with SQL Server, particularly focusing on foreign keys and indexes. I also explore the nuances of changing compatibility levels and discuss whether it can lead to significant performance improvements. Additionally, I address the topic of engaging directly with Microsoft for bug fixes or improvements, emphasizing the importance of reporting legitimate bugs even if they might not be addressed immediately. The video then shifts to a discussion on migrating from on-prem SQL Server to Azure managed instances, highlighting potential challenges and performance considerations that users should be aware of during such migrations. If you have any questions related to these topics or other SQL Server issues, feel free to submit them through the magic Google form linked in the video description, and I’ll do my best to answer them in future episodes!
Full Transcript
Erik Darling here with DarlingNada. And there is something magical in the air, isn’t there? There always is. We do office hours in which I answer five entire questions submitted by you fine folks into my magic Google form. If you want to ask a question that gets answered here, you can go to this link. This link is down in the video description. about here. And you can click on it and submit your own question. While you’re there, you might want to consider clicking on the other link to sign up for a membership and join the 60 or so other folks who, out of the kindness of their hearts, support this channel monetarily so that I can keep bringing you all this delightful ad-free, commercial-free, absolutely no weird shilling for anything SQL Server content. If you would like to subscribe to our channel, please like to subscribe to our channel. If you’d like to support the channel in other ways, of course, you can like, you can comment, you can subscribe. We are treading perilously close to the, if I’m remembering correctly here, we can check the digits. We are perilously close to the 6,500 subscriber mark, of course. I remember it was not too, not so long ago, I was very excited about getting close to 5,000 and boy, now we’re getting close to 1,500 more than that. So, I guess, we’re on the way, we’re on the road to 10,000. We’re more than halfway there. It’s a good feeling. I don’t know. If you would like some consulting help with SQL Server, I am, of course, the best in the world outside of one house on New Zealand at doing these things. If you need health checks, performance analysis, hands-on tuning, responding to performance emergencies, or training your developers so that you have fewer performance emergencies, well, golly and gosh, you couldn’t spend your money better than on this fella here because my rates are reasonable. If you would like to get some SQL Server training, perhaps, you’re in dire desperate need of learning some performance tuning stuff, well, all of mine, again, down in the video description, you will find the link to do all of this stuff. You can get all of my training for about 150 USD.
And that lasts the rest of your life. So, it’s a good deal. It’s a good deal. Upcoming events, SQL Saturday, New York City, taking place May the 10th of 2025. Wonderful, crime-free, crime-free, Manhattan at the Microsoft offices, right across the street from Port Authority. So, you can take in a bus or a train and you can make some friends at the bus station. It’s a great time. I promise.
With that out of the way, though, let’s answer these gosh darn questions, shall we? All right. So, let’s start with this first one. Actually, let’s narrow this out a little bit so that when we zoom in, we get the whole thing on the screen. There we go. All right. That’s nice and fitting, isn’t it?
Many of our tables… Let’s make sure we read this whole thing. Don’t want to skip a word here. Many of our tables have 15 plus GUID S from other tables. Not really what I would want to inherit from anything, but hey, you know.
At least they’re GUIDs. At least they’re not like VARCAR 36 or NVARCAR 36, right? Because GUIDs, they’re nice. They take up 16 bytes of space. They sort kind of funny, but, you know, it’s really hard to run out of them.
They’re quite unique. But they’re not declared as foreign key. Oh, okay. So, these other tables… These columns are definitely foreign keys. The other tables are not declared as foreign keys. It’s an interesting choice.
Plus, you have foreign keys. Okay. It’s a little unclear if, like, they’re… Like, if you have a GUID column and then an integer column and they both foreign key to the other table, but… Details, details.
You know, there’s only so much you can type. When I add an index on for every foreign key plus indexes for declared foreign keys… So, like, the undeclared foreign keys and the declared foreign keys, all right. We end up with tables with 30 plus indexes for foreign keys only.
We don’t have right performance issues. Would you index every foreign key for every possible join? Well, yes and no. So, while I probably would do that, my indexing strategy, aside from…
So, like, think of foreign keys as sort of, like, database infrastructure. They’re, like, highways and bridges and underpasses and stuff. So that, like, you know, your people can find…
Like, your queries can find their way around your data and stuff like that. But, like, you have, like… And those are good things to support with indexes. But, like, my general indexing strategy is not just, like…
Because what it sounds like to me is you have 30 single key indexes on these tables, which is not a very good index design strategy. My index design strategy is to create indexes that not only support these foreign keys doing stuff, but that also, like, support, like, the grander goals of other queries.
Because it’s, like, in my experience, it’s fairly rare that you would be, like, you know, like, select… Like, let’s say, like, for a foreign key, right? Obviously, that’s a join between two tables.
So, like, you know, let’s say you’re selecting from table A, which has a foreign key to table B. You’re joining to table B. You’re probably selecting more than just the foreign key column or columns up here. And you’re probably joining on, like, the foreign key columns.
But then you might have, like, a where clause or group by or order by or other stuff that is good to index for involved in these queries. So, my indexing strategy is to, like, not only index to help this sort of database infrastructure like you have, but also to index for, like, the grander query design.
You might find that over time, you end up creating multi-key indexes, or as they say in fancy academic literature, compound indexes that have multiple columns. And when you have those, plus what you’re talking about, you have a lot of, like, sort of superset, subset, duplicative indexes that you probably don’t need.
So, as long as you have another index that leads with the foreign key column, then you’re fine. You don’t need, also, single key indexes to support the foreign keys. You already have an index that leads with the foreign key column, so you can figure that out.
But this is a good plug for my new store procedure, SP index cleanup. I realize you said you don’t have write through, write problems. Like, your write queries are still fine.
That’s great. But you may still have an opportunity to clean indexes up that lead, like, if you have these truly, like, 30 single key indexes plus other indexes that, like, also lead with the same columns, you can probably start pruning off those single key indexes because you just don’t need both.
All right. So, let’s move on to the next question here. Do you ever see big wins just from changing compatibility level?
What specific features cause these big wins? So, let’s preface this a little bit by saying that changing any compatibility level in SQL Server is perilous. It is somewhat less perilous if you are going from, like, 120, which is SQL Server 2014, which is when the new cardinality estimator got tied into a higher compat level, and a higher compat level.
If you are on a lower compat level, like 120 through, or, sorry, 110 through, like, whatever is officially supported at the low end these days, then it is more perilous. So, let’s put this into two buckets.
One, if you’re coming from compat level 110 or lower and you want to go to a higher compatibility level, make sure you use the database scoped configuration option, database scoped configuration option, force legacy cardinality estimation. That out of the way, like, cardinality estimation and query and database compatibility level are two separate things, right?
So, like, while going to a higher compat level will, like, also force you to a higher or different cardinality estimator, you can still force the older cardinality estimator and get the higher compat level. In some cases, I find, like, where I have to, like, people who have been using, like, compat level 130 switch to, like, 150, but do better with the legacy cardinality estimator.
But that’s not really what you’re asking. You’re asking if there’s any specific features in the higher compat levels that are big performance wins. And the two that come immediately to mind are batch mode on rowstore and UDF inlining. While neither one is, like, a panacea or a bromide or whatever, like, old-fashioned word people had for something like cure-all, like, they certainly do, like, add helpful things to workloads that keep you from having to do a lot of work.
So, for, like, batch mode on rowstore, it can save you a lot of, like, you know, like, creating temp tables with columnstore indexes on them and doing, like, the left join on one equals zero thing or creating empty filtered non-clustered columnstore indexes on tables to get batch mode happening in your rowstore queries.
And UDF inlining can potentially save you a lot of, like, scalar-valued function rewriting because, like, you don’t have to rewrite them as inline table-valued functions. Not, like, of course, like, neither of these features is perfect and probably never will be because they’re very difficult query optimization paths.
But, like, batch mode on rowstore, like, relies on a bunch of, like, heuristics about the tables and the type of query you’re running that may not pick up on every single query that would benefit from batch mode on rowstore. May also put batch mode on rowstore on queries that don’t really benefit from it and might hurt a little bit.
UDF inlining doesn’t apply to every single scalar UDF that has ever been written. There are a lot of, like, limitations on it, a lot of things that will just prevent it from happening where you would still have to manually rewrite functions.
But those are probably the two biggest ones as far as, like, compatibility-level specific feature unlocks that, like, I’ve seen be beneficial to workloads. All right.
Do-do-do. When you hit performance issues that seem like actual bugs in SQL Server itself, have you ever had success engaging with Microsoft directly on getting improvements? So, this is funny.
So, you’re talking about bugs, but then you mention improvements. Bugs get fixes. Bugs do not get improvements. You don’t improve bugs, you fix bugs. Or is it so much effort that you can get by with your own approach, just don’t bother?
So, no. You should always report bugs to Microsoft. Microsoft should be aware of its bugs. The one sort of flaw in the bug reporting process is that if you open an issue on user voice and you are not a, and you don’t have, like, a repro or, you know, you’re not, like, you can’t give them any, like, you can’t, like, you can’t give them an easy way to, like, show them what the bug is and, like, how to reproduce it and, like, so they can go and fix it, you’re going to have a hard time getting traction on it.
So, you would, like, probably require a Microsoft support case, and we’re going to talk a little bit about Microsoft support when we hit that next question, but that can be a real difficult process as well. The other thing with, like, bugs and stuff, reporting bugs and stuff for SQL Server is that, like, the user voice thing that they use, like, people have to vote on stuff.
Voting on stuff makes a lot of sense when you’re dealing with, like, features or when you’re dealing with, like, you know, improvements to the product, like you mentioned up there. It makes a lot of sense to file issues for features and improvements, but they’re a popularity contest.
Like, if you’re not fairly well-known or your issue doesn’t catch the eye of someone who is fairly well-known who can promote it, you’re not going to get a lot of votes. But, like, for the bugs, like, you know, like, that’s just a much tougher one.
But you should always file bug reports with Microsoft when you find legitimate bugs. Legitimate bugs are not just, I don’t like the way this works. That’s, you know, feature improvements and whatever.
But always report the bugs because, you know, Microsoft should know about them. Just don’t be surprised if they don’t get fixed immediately or at all, ever, because there are lots of bugs that have not been addressed.
So is it worth the effort? Well, I think that it’s generally worth the effort to log the bug because who knows if someone else might find it and say, oh, I’m not crazy.
So maybe if, you know, do it for yourself. Do it to maybe help Microsoft fix flaws in their product, but also do it for the general public who might come across your issue when they hit something similar and say, oh, this is a bug.
Oh, I hate this question. Hi, Eric. Hi.
How are you? Our company is in the early to mid stages of migrating to Azure managed instance from on-prem SQL Server. Could you summarize some of the main issues complaints you and or your clients have with it? Well, my clients’ complaints.
I’m going to leave my complaints out of this because if I tell you what my complaints are, then it just sounds like I’m ragging on managed instance. So I’m going to tell you what my clients complain about who use managed instance is, and I’ll let you decide whether these apply to you.
So let’s start a little ways back before managed instance was a thing. I was around, perhaps, you know, cutting myself open and showing you how many rings are inside. I was around for the great virtualization epic in computing, which means like when virtualization was the big craze before moving to the cloud was the big craze, you know, everyone was gung-ho on virtualization because they wanted things to be more manageable.
They wanted consolidation. They wanted to save money. All the stuff that people say about the cloud now. Guess what?
The early days of virtualization. Now, if you, like, let’s say that you had a physical SQL Server and you were very happy with performance. If you moved to a virtualized SQL Server that was, let’s just say, identical spec-wise, like I realize it’s not going to be absolute identical, but let’s just say it was identical spec-wise to the physical machine you had, you would typically see a 20% to 30% slowdown.
If you were very unhappy with, like, the performance of your physical SQL Server and you moved to a better VM, well, a better spec’d VM, you would probably see improvements.
A very, very similar thing happens with the cloud, specifically managed instance, Azure SQL DB, to some extent, and is on RDS, where if you are currently happy with the performance of your on-prem boxes, you’re going to need to spec up your virtual, like, your instance type in Azure by 20% to 30% to see commensurate performance.
If you are planning on moving to a general-purpose box in managed instance because it costs way less than business critical, you might need to even go higher. The reason for that is because the general-purpose instances store all of your SQL data in Azure blob storage.
You will notice that when you go to disk to read data in managed instance, you are making an HTTP request. The wait type actually has HTTP in it. If that ain’t a punch in the narbles, I don’t know what is.
So be prepared to oversize your managed instance general-purpose box or pay to the back teeth for a business critical box to compensate for all sorts of strange design choices. Another giant complaint that my clients have with platform-as-a-service in Azure is that the support for it is incredibly expensive.
When I say multiple tens of thousands, I mean 50,000 to 60,000 a year to hundreds of thousands a year and sometimes higher for support that gets nothing done. They cannot reliably support you there. You end up with third-party support engineers who say, hey, can I have logs in a repro?
You give them logs in a repro. You maybe go back and forth a little bit. They give you some humdrum advice about rebuilding indexes and partitioning your temp tables. Then you don’t hear anything for a while.
Then three months later, they shuffle you off to someone else and you start from scratch. It’s a terrible experience and you pay so much money for it. It’s not good.
It’s not good at all. So the big problems that you’re going to have, one, unless you way oversize your boxes, which costs a lot of money, or you go to business critical, which costs a ton of money, your performance is not going to be equitable between on-prem and managed instance.
And the support you get is going to be god-awful. So avoid that if you can. I would much more highly recommend Amazon RDS over managed instance.
Microsoft managed to botch a managed platform for its own database. Amazon did a much better job with it. So let’s move on here.
Longtime viewer. Oh, thank you. Won’t make any jokes there. I know all of the theory behind the supremacy of RCSI, but I have 90 days to convince 25 people to enable it. Any advice just about winning those arguments?
What arguments do you need to win? It’s hard for me to tell you how to win an argument when I don’t know what the argument is. You know, my basic outline of enabling RCSI is something like, do you have a lot of blocking problems with readers and writers?
Or deadlocking problems with readers and writers? If so, you can most likely solve the vast majority of them using RCSI. Do you already have no-lock hints sprinkled everywhere around your queries?
If you already have no-lock hints, there is absolutely no downside to switching over to RCSI. RCSI is actually better than no-lock when it comes to returning results. Right?
Because no-lock allows dirty reads. RCSI does not. Stuff like that. Sometimes you need to get a demo database where you enable RCSI and do stuff. If people are worried about TempDB, they’re living in like 2012.
You know, newer versions of SQL servers do a fairly good job of setting up TempDB for success for you. It creates extra data files. The trace flags 1117 and 1118 are enabled by default.
So you don’t have to worry about, like, black magic stuff when it comes to TempDB setup. There’s really not a lot to… Like, for most people, the switch to RCSI is not a very rocky one.
There are very few situations and there are very few code patterns where RCSI is a problem. If they are really, really worried about RCSI and you have reasonable control over the workload, you might have some luck winning people over by enabling snapshot isolation and going through and only allowing certain queries that everyone deems are, you know, safe to add the snapshot…
To add the set transaction isolation level snapshot to them so that you can start showing people just exactly how things work and behave. Granted, there are some, you know, there are some differences between snapshot isolation and read committed snapshot isolation. But generally, the snapshot isolation, like, having, like, a layover in snapshot isolation is a pretty good route to getting read committed snapshot isolation enabled.
So if you would like to supply me with some of the specific arguments you’re getting, then I’d be happy to address those more specifically. But in general, that’s sort of… Those are sort of my talking points on things.
Anyway, that brings us to the end of the fifth question. Again, I thank you all for submitting them. I thank all future submitters for submitting them.
And, of course, all past submitters for having submitted them. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in other videos and other office hours and… I don’t know.
Lots of stuff to… Lots of questions to answer. Lots of interested folks out there. So, anyway, that’s good for me. 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.
In this video, I introduce a new application developed by my friend Josh Darnell to help monitor blocking on SQL servers. This tool uses SP WhoisActive to notify users immediately when blocking occurs, providing a more proactive approach compared to relying solely on SQL alerts or other methods. If you’re already a member of my YouTube channel and have purchased any of my training materials, you can download this application now and start monitoring your SQL servers for blocking issues. The app allows you to add the servers you care about and receive notifications directly when blocking happens. I also walk through the setup process and demonstrate how it works in real-time, showing off its features such as detailed blocking chains and query plans.
Full Transcript
Erik Darling here with Darling Data. And in today’s video, we’re going to talk about kind of a cool new add-on application that I wanted to develop to help people out in the world monitor blocking on their SQL servers. A lot of my clients have requested something like this because what happens is they’re not sitting there watching their SQL servers all the time. They just want to know when blocking is happening, but they don’t want to like sit there and rely on like SQL alerts and other stuff like that. So I had a friend of mine, Josh Darnell, develop this application that runs SP WhoisActive that like will tell you when blocking occurs. And the whole idea of it is that you get a more immediate notification about blocking than if you are, than like, you know, almost any other way. So if you’ve signed up for a YouTube channel membership, or if you paid for a membership or if you paid for a membership or if you paid for my training right now, you can go download and use the application right now. You can add in the servers that you care about and get notified when blocking occurs. So in my videos, when I talk about like, like signing up for a membership and the link down in the video description, that’s what you do and you should see a members only post with a link to download this. Also in my videos, when I talk about if you buy my training, you can get all of it for with that discount.
Again, link in the link in the video description for about 150 USD for life. You can also go download this right now as part of the training. If you if you’re looking to access it via the training, it’s going to be under the locking and blocking course is going to be a little zip way down at the bottom that says block monitor. That’s what you download in order to get it. Right now it is Windows only. And it only supports Windows and SQL authentication. There’s we’ll talk about roadmats roadmats stuff in a minute. But of course, two people who this wouldn’t have been possible without Josh Darnell, web developer extraordinaire who did the actual application work on this. You can find him at Josh the coder dot com. And of course, my friend Adam mechanic who gave me permission to use SP who is active in this is like a scripted thing. And of course, you can find SP who is active the script itself at who is active dot com. There are some again roadmap items that are on the list that just aren’t aren’t aren’t in there yet. More filtering based on what who is active allows you to filter on some per connection filtering stuff. Saving off historical blocking right now if you close out the application, you lose the blocking stuff that was in there before but working on that. And then more connection options for cloud stuff. I think like intro or whatever.
I don’t have an exact timeline on this because you know Josh is a busy fella and he works on this as he can. So this stuff will get in there. It’s just not like an immediate timeline thing. But let’s go kind of look at the application and see what it looks like and what it does. So we of course have a little about screen that tells you a little bit about the application. We have the global connection settings. So minimum block duration and minimum block session count and then the manage connections window where you manage your connections. Of course, you need the server name and if you want Windows authentication, leave that checked. If you don’t want Windows authentication, just uncheck that so you can enter in a username and password there. But then once once once that’s all in there, you should like and you get blocking. You should see a little notification pop up. It looks like this down here. This little window will pop up in the corner that says new blocking has occurred. And in the application itself, you’ll see a line that looks like this. Now, if you click on this, you’ll get more information, right? So if you double click on this, you will see the blocking chain that happened on the server. And you’ll see stuff that looks again like SP who is active output with the duration and the SQL text and the SQL command and all that other good stuff. And even if you scroll across, you will be able to save off the query plan that you will see the query plan. So all of the query plan that got captured. So all of the stuff that you would expect to see, like when SP who is active runs and you find blocking, this will give you in the application. So if you’re on the fence about, you know, channel stuff or training stuff, maybe this will convince you to finally become a loving, caring, darling, data, darling, data, darling, the four D’s quadruple D crew. Anyway, thank you for watching. Happy lock monitoring. And, you know, let me know what you think, how it works. And I don’t know. Maybe we can troubleshoot these blocks together someday. All right. Goodbye.
Bye.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.
How I Used Claude Code To Refactor My Stored Procedures
Video Summary
In this video, I dive into my recent experience using Cloud Code from Anthropic as part of my work on Darling Data. Typically, my videos focus on T-SQL, query performance, and database optimization—topics that are near and dear to the hearts of SQL Server enthusiasts. However, in today’s episode, I explore a different aspect by sharing how Cloud Code helped (and sometimes hindered) me with tasks like refactoring stored procedures and generating README files for my GitHub repository. While some aspects were handled seamlessly, particularly with formatting code and creating consistent READMEs, other areas required more manual intervention. For instance, when it came to refining the SP Health Parser, Cloud Code was adept at condensing repetitive checks into templates, making the code cleaner and more maintainable. However, there were challenges in certain sections of Quickie Store where the end of the stored procedure was a maintainability nightmare, requiring extensive debugging and sanity-checking post-refactoring. Overall, this experience provides valuable insights into both the strengths and limitations of AI-assisted development tools in real-world scenarios.
Full Transcript
Erik dishwaskeldarling here with Darling Data. And in today’s video, we’re going to talk about something a little bit different than what I normally talk about in my videos. Usually we’re talking about T-SQL, query performance, indexes, you know, best practices, stuff like that. In today’s video, I want to talk about something that I was doing kind of over the last couple of weeks, where I was using a new offering from the Anthropic Cloud people. called Cloud Code. It’s actually still a beta thing, to refactor and some other stuff with the store procedures that I offer for free to, like, analyze SQL Server performance. Now, the tasks that I wanted it to do were things like, like, refactor big chunks of code, especially very redundant stuff. You know, create, like, readme files, like stuff that I’m really bad at doing, like the admin work of the GitHub repo. Like, making sure that everything has readme files and they’re up to date. And then also, like, over the last couple weeks, I was working on a brand new store procedure called SP Index Cleanup. This is where Cloud Code was somewhat less successful. It was good. It was good for some things in the development of it, but not very not, very much not good at other things. So I want to talk through all that. So I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that. I’m going to talk about that.
You know, the first thing I tried was this an application called Cursor and that thing just choked and died on my code immediately. Like, like it couldn’t under like just didn’t get any of what I was trying to do. And worse was like the few things that like it did pick up on. I was like, cool, can you fix this? And it was like, I can’t find the file. And I’m like, but you just read from the file. Like you, you, you see the code, like you just printed the code up for me. And it was just like, yeah, but I, I can’t find the file to write to it. And I was like, huh, that is that normal. And, and, and Cursor was like, no, I think I have a bug. And I was like, oh, this is, this is a strange self-awareness for a computer program, but okay, we’re just gonna, just gonna move on from here. So I tried out Claude Codes. I was very interested in it. And I’m going to talk about kind of what it did, where it was successful and where it wasn’t. If you would like to support this channel, and I want to bring up right now, Claude Code is not free. You know, like there are a lot of LLMs that you can have basic conversations with for free. It doesn’t cost you any money. Claude Code requires like an API, like API access and credits. And there were a few times me sitting there working with Claude Code where I was just like, oh, my credit card has been charged. What was that? Oh, another 50 bucks to Anthropic.
Because you have to keep refilling credits to keep using it. If you run out of credits while you’re doing something, Claude’s like, give me my $2, right? So if you want to support my Claude Addiction, excuse me, if you want to support my Claude Addiction, there’s a link down in the video description for you to do that. If you spent all your money on Claude Code credits as well, you can like, you can comment, you can subscribe. And if you want to ask me a question privately that I answer publicly during my Office Hours episodes, you can do all that. If Claude Code is not quite good enough to take the place of all of your DBAs and developers, and you still need some help with SQL Server, you can still hire me for not for Claude credits, but you can pay me real money to do any of this stuff, health checks, performance analysis, hands on server query index tuning, dealing with SQL Server performance emergencies, and of course, battering your developers and deep frying them in SQL Server knowledge until there are fewer performance emergencies for you generally, which is always a good thing.
If you would just like to get some training from me, I have about 24 hours of it. It’s all SQL Server performance tuning training, and you can get that for about $150 once you stick that discount code onto the pile. Upcoming events, the calendar has finally filled out, and there’s a lot to talk about. SQL Saturday New York City 2025 is taking place May the 10th. Redgate is going on tour with some mini-pass events in New York City, Dallas, and Amsterdam.
Those will be in August, September, and October. All the dates are right over there. And of course, Past Data Summit is taking place November 17th to 21st in Seattle, Washington. I guess that one was always out in the air, but, you know, tis the season to start making sure everyone knows these dates, has them in their calendars, and buys the appropriate tickets.
So, with that out of the way, let’s talk about Claude Code. I think I’m going to start over here, because this one’s a little bit easier. This is the…
So, like, when you start working with Claude Code, like in a GitHub repo or something, or wherever you work with code, one thing that you can do is feed it a bunch of, like, rules to follow. So, like, when Claude first started, like, spitting out code and doing stuff, the formatting was a disaster.
So, I was like, we got to work on this. So, one of the first things I did was create this Claude.md file in my GitHub repo, so that Claude knows, like, how I want my queries to look. So, I gave it all my very particular rules about comments and naming conventions and query structure and, like, join order and all this other stuff.
And, like, how to format things so that when it spits code out to me, I don’t have to spend another, like, hour reformatting everything that it gives me. So, there’s this whole long file in here where, like, you know, it’ll, like, it’ll, like, read through this at the beginning of a session. Or if I’m like, hey, you’re messing up, check this file again, and it will, like, reformat stuff the way I like it.
It gets pretty close most of the time. It’s, you know, still not perfect. There’s still stuff that I have to, like, adjust a little bit because I’m very particular about things.
But, in general, it’s pretty good. So, after… So, with that in there, the other task that I set it to with my GitHub repo, again, the admin work that I stink at was, like, making sure that all the readme files were, like, consistent and up-to-date.
And, like, everything had one because there were a bunch of folders in here where there was nothing. Some of them still don’t have them, and that’s okay. They’re ones that I didn’t want them for.
But they’re, like, the main store procedures and stuff and, like, install all, like, all of these things have readmes now so that you can, like, figure out, like, what the procedure does. There’s, like, you know, some examples of how to run it, things like that.
So, everything now has a nice consistent readme file. And Cloud Code was great at that. Cloud Code is actually much better at, like, just spitting out good markdown than I am. One thing I was really happy with was, like, when I did the human events one, I was able to tell it, like, hey, like, there are two store procedures in here that people should care about.
Like, let’s branch them at the top so that people can get right to the one they care about. So, like, it just, like, was able to do this stuff very easily and quickly. And it added a picture in there for me.
And I was very happy with this part of it. Like, this was great. The store procedure stuff was a little bit different. Let’s start off talking about where it was mostly successful. And then we can talk about where stuff was maybe not as great.
So, like, one of the first things that I had it do in SP Health Parser, when I first wrote SP Health Parser, I was, like, really just trying to get it to a point where it worked. And it did what I wanted.
And the results that it returned were, like, you know, sensible. Like, maybe not perfect, but, like, it got the results out. I could read them. Like, they were correct. Stuff like that.
But, you know, this thing reads from the system health extended event, which is all XML, parses all the XML out, and does a lot of stuff. And, like, along the way doing that, I didn’t, like, I was more focused on making sure that I just had everything. Like, just please, God, just work.
Like, then I was on, like, making sure that the code was, like, reusable, maintainable, like, not insane. Like, just adding length to the code where it wasn’t necessary. So, there was a lot of stuff in there I was doing that was very repetitive that could be condensed down.
So, one of the first things I asked Claude to do was, like, hey, like, I do this check over and over and over again to look at this one function that reads from the extended event file called sys.fnxe file target read file. It rolls right off the tongue because in SQL Server 2017, they added this column called timestamp underscore utc. So, like, it’s a lot easier to filter on that than it is to, like, parse the XML and filter on the event date in there, which I still have to do for, like, the 2016 and earlier stuff.
But there was, like, this branch in the code for, like, hey, if we do this, then we do this. Right? But it was, like, the whole query written out.
So, one thing I wanted to do was, like, template some of the dynamic SQL so that I could just, like, based on what I find here, like, replace things and just do the right, like, run the right query, like, over and over again. So, that was one of the first things I had to do. And it was pretty good at this.
So, like, and one thing that’s cool here is that in, like, Azure, well, I don’t know about Azure SQL DB because Azure SQL DB doesn’t have the system health extended event. Again, Azure SQL DB is stupid. It’s, like, running SQL Server in safe mode.
You don’t have access to a lot of stuff that you would need to do, like, good analysis on a regular SQL Server. Microsoft’s doing it for you. Anyway.
But, like, managed instance where you can do this stuff. And, like, on-prem SQL Server, there’s a bug report about the timestamp UTC column. It’s fixed in, like, the newer versions of SQL Server because, like, in the bug report it says this will be fixed in the next major release.
So, that’s not, like, NDA 2025 stuff. Don’t yell at me about that. But, like, prior to that, you had to, like, convert the column to a date time 2 before you could filter on it.
I’ll show you what the query looks like in a minute. But, like, I wanted to make that less annoying. So, I had to create this timestamp UTC mode local variable, which is a tiny int, not a bit.
And, basically, what I do is it starts at zero. If we’re on SQL Server 2017 or up, then we add one, right, because that column exists. But we have to use the convert where clause to do anything with it.
And then, excuse me, if the product version is greater than SQL Server 2022 or we’re using managed instance, then we add another one to that, right? So, if this number is, like, zero, then we use the old, like, event date from the XML filtering. If it’s one, then we use the 2017.
And if it’s greater than one, then we use the column without the convert on it. So, that was, like, one of the first things I had to do. It was pretty good at that.
Like, everything generally worked out. And then, the other stuff that I had to do was create templates for Dynamic SQL where I could easily do some, apply this stuff. So, the templates all look something like this, where we have, like, the insert list and, like, object name stuff here.
And then, we have the time filter that I want to use here. And then, the cross apply that I want to use here because that changes, too, depending on how the query ends up. There’s this thing, but this isn’t really what I want to show you.
This is more in line with what I want to show you here where, depending on how the timestamp UTC mode thing works or looks, we do different stuff, right? So, this will do this version of the cross apply for 2017 plus. We do this version of the cross apply.
And then, if it’s, like, a version where this isn’t fixed, oops, it’s a version where this isn’t fixed. Then, we do the convert date time offset 7 before we do the filtering. And then, if it’s the version where it’s fixed, then we don’t need to worry about that.
And then, you know, like, you run the replace on stuff in here, and then you have this set up to run over and over and over again. So, once you get into, like, the actual checks, this is the logging to table stuff. We don’t need to look at this again.
We need to get down a little bit lower to where the queries start running. So, what it does now is rather than, like, rather than that query with the cross apply that we just looked at being written out over and over and over again for every different event, this thing runs a little bit differently now. We fire up a cursor.
We work off a table variable that has everything in it that I want to loop over. I think that’s just up here. Yeah.
So, this is the collection table variable that I use. So, this figures out exactly, like, which, like, area to check in, the place that I get it from, and the temp table that it goes to, right? It’s in, like, the column that I need to select from and insert and everything.
So, all this data is here now. And now, when I loop over stuff down in this cursor, I just, like, generate that insert. I run a replace on this collection SQL local variable to replace the object name and the temp table I’m inserting to and the insert list for that table.
And then I just, like, that just runs in a loop and does the insert over and over again. And, of course, like, with debug and stuff, I can print that out. And then within the cursor, I just, like, update this to say which things are done, which is important because some of these collection areas work off the same thing, right?
So, like, SP server diagnostics component result, you’ll see that a few times in here. I don’t need to run this once for each one of those. All I have to do is collect this once and then parse the XML out from that.
So, it’s very useful to just have, like, hit that once. So, I don’t need to hit this over and over and over again. So, it was very good with helping me sort of condense this down.
Like, again, not perfect. There were things that I had to, like, fix and sanity check and make sure it still worked afterwards. But, like, as far as just, like, setting up, like, the bones of things, it was very good at that.
The other thing that I had to do was in Quickie Store. So, in Quickie Store, the end of the store procedure was a nightmare, like a maintainability nightmare. The output, and it was purely for, like, the output of Quickie Store, right?
So, there were, there’s two options. There’s expert mode, which returns a bunch of extra columns in the main result set and additional tables. And then there’s format output.
And format output, like, you have big numbers. It puts commas into the numbers for you. By default, expert mode is set to zero, but format output is set to one because, like, I don’t want to have to remember to turn format to one every time I run this, which is what I do.
So, I had it create two things for me to drive the final result. They’re both table variables. And this is actually a good use for table variables, right?
So, I actually had it use table variables for something table variables are good for. And what I, like, and this took a few iterations. I don’t want to pretend like it just spit this thing out perfect on the first go. Like, there was definitely stuff that needed tweaks.
But, like, I could type in the tweaks that I needed it to make, and it would make the right adjustments. The really cool thing, too, is that, like, it wasn’t just returning code to the screen that I had to go and, like, paste into the procedure and do stuff with. It was, like, actively editing the files that were in my GitHub repo.
So, I could just, like, you know, hit submit to GitHub and, like, push stuff around. It was, it’s a little tricky right now because Cloud Code doesn’t run on Windows. At least, I couldn’t figure out how to get it to run on Windows.
I don’t think it does. But, so, like, I, like, like, sitting at my Mac over here where I have the Cloud Code terminal window running. I’m going to, like, type stuff there, push it up to GitHub, pull it down on GitHub on Windows, then run the store procedure and, like, make sure that stuff works. So, there’s a little bit of, like, crosstalk back and forth right now.
This can be a little bit awkward if you’re, if you don’t have, like, a Mac to, or something else to use this on. But I figured it was worth it for the experiment. So, it creates these table variables.
And, like, it was, like, kind of amazing at, like, like, analyzing the select list, figuring out which temp tables things came from, which columns were expressions, and, like, which conditions those, like, those columns should show up under. And, like, making a table variable to store all this stuff. So, like, all of these things have, like, like, a format pattern.
And, like, if they need one, so, for, like, down here, like, all of these, all these columns in here have the option to format them. And, no, that’s the last execution time. I’m looking for, like, duration.
So, like, when I run the format function, we use that n0, right, just to put commas into, like, with no decimal point. That’s a n0 means zero decimal places. If you can do, like, n1 or n2 or n5 or whatever if you want decimal places.
n0 is just, like, a number formatted with commas that, or whatever local thing you use. In France, it’s spaces, I found out. But, yeah, you can, like, it just put all this information in there for me.
And, like, at first, like, it was just numbering these sequentially. And I was, like, hey, if I need to insert stuff in between, can you put this in, like, can you use a different numbering scheme so that, like, if I need to put stuff in, here or reorder this, it’s not a nightmare of me having to renumber everything.
So, and it was pretty good at this. One thing that needed help was, like, some of the new columns weren’t perfect. But, like, it was able to get that once we started, like, once I started, like, you know, telling it, hey, this isn’t going to work under these conditions. We need a column to, like, direct it to this.
Then, like, we need another, like, we need to, like, let, like, another level in the case expression to, you know, make sure that it, like, does these things right. So, like, one example of those would be, like, the hash totals for physical IO reads. This was one where we had to put, like, this thing in different than the other ones.
Right. So, that’s kind of fun there. But, yeah, anyway, like, it was very good with that.
And then the other thing that I had to do was, like, use a table variable to sort of deal with all the different parameters. So, the parameters that get passed in, like, indicate different temp tables get used and created and, like, stuff like that. So, this was all very useful here.
And then, like, there was a bunch of, like, sort of very repetitive code where, like, for all of these things, like, all of, like, all of the checks to include these things or, like, alternately exclude these things were all, like, very much written out very explicitly. Like, if this, this, if this, if this, if this, if this, then this. Right.
Like, I was just able to, like, have cod template all this stuff for me. So, like, one thing that was good in there was, like, let’s get down to, actually, let’s see. Let’s just do this.
So, the cursor that runs over this part does a bunch of stuff in here. And then, like, there’s just neat logic in here, right, where it’s, like, insert into, like, I could have done, like, the replace thing. But, I don’t know.
This, this, this just, this was just fine for this. Like, insert into the temp table. Insert the column list. Then run this. And then down here, like, like, even, like, made, like, the temp table part dynamic. So, if I need to include stuff, then I use include.
Otherwise, I stick the ignore on the temp table name. It was just very successful at doing all this. And then, way down at the bottom, this was where stuff got more interesting, I think. Was it dynamically builds up the column list based on this, right?
So, like, the column SQL is, like, if format output is zero, then we don’t do anything to it. If format output equals one, then when the format pattern is not null, we put wrap stuff in the format function. And, like, you know, based on this crazy where clause for stuff, then we do certain things in here.
And then, like, in here, we just insert the column list, right? So, like, we just tack the column list onto the SQL and then, like, do the rest of the from clause down here. I was able to do something fairly similar with the expert mode output, but I did not go as deep as that with it.
This, I was just like, you know what, we’re just going to do, like, because, like, with the format stuff in these queries specifically, it’s weird because, like, the columns are sort of interspersed. So, like, it didn’t make as much sense to do the table variable thing.
So, in here, it’s just, like, if format output equals one, then we, like, put this in there. Otherwise, you put this in there, and then we have this other column, and then this other column. And, like, you know, so, like, there was just a lot of, like, templating stuff that made sense, like, locally for this that didn’t make sense for the other parts.
Now, this was all the stuff that it was pretty good at. But in SP index cleanup, kind of what I tried to do with it was be like, hey, like, I need to, like, this is, like, the goal of my query. Like, what would I, like, how should I structure this to, like, give me what I want, right?
Like, what’s a good way to do this? And one of the first things I came up with was this insane cursor that just, like, never turned correct results. So, I was, like, like, I got to scrap this.
I’m going to, like, just write, like, very, like, procedural, like, like, rule check updates. And then there were, like, a lot of things that I had to fix to, like, make sure that the next update worked. Claude was a little less good at this.
It was helpful, but, like, like, it required a lot of me, like, like, looking at things, analyzing things, doing stuff. Where Claude did become helpful in this part was, like, like I said, like, coming up with, like, unit test indexes to make sure that all the rules that I cared about were being adhered to. And then one of the neat things that you can do, or rather that I did here, was I was, like, okay, like, based on the indexes you gave me.
One other thing that I had at Spitout, too, was, like, one thing that SP Index Cleanup does is, like, it ignores unused indexes for deduplication. So, like, they’re unused. We’re just going to disable them.
So, one thing that I had to do was, like, spit out queries that forced, like, a select count from the table using a specific index, right? And so, like, if there was a filtered index, it would put a where clause in there that matched the filter expression. So, like, it did a lot of cool things here.
But, like, one thing that I had, one thing I did was, like, I would take the final results of the query, like, hey, these are the actions that SP Index Cleanup is now saying I should take based on these indexes. Like, here’s all the data that I have. We could, like, paste in the table data.
Like, do the results match up with what I want? And, like, it was, like, a few times it was, like, no, no. Like, this is messed up. Like, one thing that I caught early on was, like, one thing I wanted SP Index Cleanup to do was differentiate between a column sort order and the key of the index. So, like, there was an index on reputation ascending and an index on reputation descending.
And one of the first bugs I caught using that method was that it was still saying that, like, those were duplicates when they were, like, I don’t want them marked as duplicates for safety reasons. So, it was able to actually caught that in the results output and was, like, I don’t think this rule is being followed. Like, we should fix that.
So, like, there were some, like, good parts to it. Like, some of, like, the, like, larger strategic things, it was less, it would, like, kind of go off the rails with. But there was a lot of stuff where, like, if I was able to give it very specific instructions and, like, very targeted, like, I need to do this specific thing, it was really good. Another thing that was cool with it, and this will be the last thing I say because this video is getting kind of long at this point.
One thing that was kind of cool with it is that since it’s all working in my GitHub repository, I could actually have it take, like, various things that I do in other store procedures and integrate them quickly into the new store procedure. So, like, there are things that I do in SP Index Cleanup that are repetitive to other store procedures that I do, like, to get all databases, include databases, exclude databases. And it was very easy to be, like, hey, I use this, I want to apply this pattern to this store procedure, take the stuff that I do in this store procedure and put it into this new one.
And it could just, like, go look at, like, Quickie Store or HealthParser or, like, whatever else, bring, and, like, bring in the portions of the code that I cared about. Like, it was able to, like, follow the get all databases equals one part, like, and see the, like, where that got triggered and, like, just bring all the code over. For me, that would have been hours of, like, split window pane pasting back and forth.
So, it was, like, there are some very cool things you can do with it. Writing code from scratch, maybe not just yet. It wasn’t a great experience with that, but, like, the refactoring and the tweaking the refactoring and the, like, applying patterns from one store procedure to another, that was all very good.
So, if you have, if you’re interested in cloud code and seeing what it can do for you in these sort of situations, like, you know, like, even just, like, hey, find where there’s redundancy in this code. Like, it was pretty good at identifying that stuff. But, you know, there are some times, like, you still have, like, you still have to babysit this stuff a lot and be, like, and, like, sanity check it a lot because some of the stuff it comes back with is bonkers.
Right? Like, like, it was still giving me, like, in, like, invalid columns that don’t exist in, like, select lists and stuff. And I was, like, but you can see everything.
So, you know, at this point, like, like, it was able to do some impressive things. But, again, you have to give it very specific, very targeted, like, you have to, like, basically tell it exactly what to do before it does it, like, correctly and to the spec you want. And it really helps to have, like, a list of rules that it has to follow for formatting so that it doesn’t just spit ugly-ass queries out at you.
But a lot of good, a lot of good. I think it did save me a lot of time. And it actually made it possible to do this stuff.
Otherwise, I probably wouldn’t have done some of this stuff. So, like, it was a very helpful sort of, like, companion in this adventure. But, again, there is a lot of hand-holding here still.
Not the end of the world. Like, not like you shouldn’t do it. But, you know, just be prepared to, like, you know, you’re actually teaching this thing how to do something right kind of deal. And it has a short memory and it’ll forget how to do something right the next time.
So, you do have to be careful. But, overall, pretty good time. Pretty fun experiment. It got a lot of good work done that I’m happy with. So, you know, I can’t, I can’t, I’d probably give it, like, a 7 out of 10 as far as, like, you know, nailing it type stuff.
But I think, you know, like, again, this is still in beta. So, is it, that gets better and, you know, stuff improves and I’m sure this will get better. One thing that is kind of annoying is, like, how long it takes to do some stuff.
And how many times you have to, like, hit okay for it to do something. Like, there are some times when it would spin for, like, five minutes to produce, like, five lines of code. Which I didn’t understand.
I have no idea what you’re doing. Other times, like, you would ask it to do a specific thing and it would be like, well, let me look at 50 other things. And you’re like, no, no, no, no, no. You’re not wasting time on that. I gave you this one thing to do. You don’t need to check every other store procedure and everything else.
And then there are things like, you know, like, there are some things where you can be like, don’t ask again for this session. But there are some commands where you have to, like, keep coming back and hitting okay. So, there are times when I’d be like, oh, I’m going to hit okay and go over here and do some stuff.
And then I’d look back and, like, just realize it was just sitting there waiting for me to hit okay again to do something. So, you know, again, not end of the world type stuff. Just, like, you know, kind of like, I don’t want to keep context switching to tell you it’s okay to do this.
Just do it. Like, you can’t say something like, just do this all unprompted. Like, don’t ask me any more questions.
Because it’ll say, okay, and then still ask you questions. So, you know, it is junior developer-y in a lot of ways. But it can be a very productive junior developer. So, I would suggest trying it out.
I don’t get anything from Claude Code or Anthropic or anything like that for telling you that you should probably try it out. But, you know, it’s a good time. I had fun.
Sort of. I didn’t sleep much the last couple weeks. Just getting back into a normal sleep schedule now, kind of. Because there were a lot of late nights working on SP Index cleanup. So, yeah.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in another video. After I have cut my breath. 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.
In this video, I’m Erik Darling from Darling Data, and we’re diving into some exciting updates to SP Pressure Detector. This improvement has been a long-requested feature by many of you, and after putting in the effort, it’s now available for everyone to use. The main enhancement allows logging results directly to tables, giving you detailed insights into when pressures occurred and what was happening at those times. With this capability, you can analyze historical data more effectively, making it easier to identify trends and issues within your SQL Server environment. Whether you’re a data professional or just want to support the work I do, there are multiple ways to contribute—financially through donations, engaging with my content by subscribing and asking questions during Office Hours episodes, or hiring me for consulting services to help optimize your SQL Server performance. Additionally, if you’re interested in training, I offer 75% off a comprehensive course that lasts a lifetime, providing valuable insights into SQL Server performance tuning.
Full Transcript
Erik Darling here with Darling Data. In this video, we’re going to talk about recent improvements to SP Pressure Detector. And this is an improvement that many people have asked me about and requested for a long time. And, you know, it was a lot of work, but well worth it because, you know, I love and care about you and I want you to have an easier time dealing with SQL Server stuff. So, the improvement, big improvement made to SP Pressure Detector, like a couple of the other store procedures we’ve talked about so far, is that we can log the results to tables. So, now you can figure, you can log all this stuff to tables and figure out exactly when your pressures happened and what was going on and how awful everything was.
If you like the work that I do with SQL Server and you like the stuff that I put up on YouTube, you can support it with money by clicking on it. I’ll see you in the link down in the video description. If you have no money for whatever reason, you’re a data professional with zero dollars, not even four dollars a month for a lowly consultant like myself. You can like, you can comment, you can subscribe and you can ask questions for free that I will answer on my Office Hours episodes.
If you have money, if you have like money money, and you want to hire me to consult and make your SQL Server, you know, less awful, I can do that. And I do all of these things, health checks, performance analysis, hands-on tuning of all varieties, dealing with SQL Server performance emergencies, and training your developers so that you have fewer SQL Server performance emergencies. You can also get training from me, 24, 25 or so hours of it, 75% off, that means about 150 US dollars.
And that lasts for life, that is no subscription required. So again, link to do all that stuff down in the video description. SQL Saturday, New York City, May 10th, 2025. Wow. Beautiful. Springtime in Times Square. What a great time to be alive in an office building for a Saturday. You can always just like, say you’re going and, I don’t know, go to a Mets game or something.
But with that out of the way, let’s talk about SP pressure detector stuff. Now, let me just make sure that everything from the last video in here is cleaned up. It’s not. Okay, let’s get rid of that. And so the main, again, the main improvement with SP pressure detector, like a couple of the other procedures, is the ability to log things to a table and retain that data for only as long as you care about it.
Much like the other procedures, there is some stuff that I do to prepare the results to do it. If you decide to log to a table, then I will validate things. I will make sure that all of the tables are created. I will make sure that whatever schema you want to put things in lives there. And then I will, I don’t know, create all the tables that we need.
And then down here where the queries start running, I will actually put stuff into those tables. I promise it is absolutely fantastic what happens. It is beyond compare how amazing what happens is. So let’s just go look at this thing in action, because that’s where, that’s what everyone cares about anyway, right?
So again, all my procedures have the help parameter. It gives a handy script introduction. Sometimes even some examples of how to run it. Not in this one, though. And then, of course, I say that on the one that doesn’t.
And then a handy reference to what all the parameters do. So this is all very good stuff in here. I’m going to run this with debug on and hopefully we won’t hit any errors.
It happened successfully. Now, the debug output, because we have that enable that’s going to return all sorts of stuff to us about what ended up where and all sorts of other things. But over the messages tab, we will see where we created all our tables.
We applied the retention policy. That should probably be seven days. I’ll fix that later.
And then you’ll see all of the pressure detector stuff that gets logged, right? So coming back over here, if we look at where or like rather what tables get created, it is all of the, it is all the results that you would expect to see from pressure detector. So wait stats, excuse me, file metrics, perfmon, memory counters, CPU information, queries consuming memory, queries consuming CPU, and any CPU, high CPU events.
So all sorts of stuff ends up in there. Just to give you a brief look at what that ends up looking like. If we hit refresh, we will have all these new tables.
And the wait stats table is usually the one that gets the most action. So if we just right click and say select top 1000 rows, we will get like all of the, basically the table of information that you would see when you run pressure detector, just in stored in a table. So you can, you know, again, you can put this in, well, there’s, I guess there’s two tables for sample time and collection time, but whatever.
These things happen. I don’t mess up the regular results, I think, if I did too much there, but we don’t have to talk about that. Dope.
So you get all this stuff back. And again, you can stick this into an Excel file. You can query it with DuckDB. You can do whatever you want. And you will be able to figure out what your highs and lows for SQL Server are. And that’s across all of these things, obviously, like anything that SP pressure detector would have logged when you hit F5 would end up in these tables over here.
So hopefully you find this very useful. Hopefully you will find this a beneficial improvement to the much beloved SP pressure detector. And you will be able to start figuring out your SQL Server problems a bit easier with this data.
These data’s, these datum. Anyway, that’s about it for this one. Not too much there.
The next thing we’re going to talk about is QuickieStore. So as always, I hope you enjoyed yourselves. I hope you learned something. I hope that you will use these scripts and take advantage of the table logging that these scripts now offer. And again, if you, to get this code, it’s all of my GitHub repo.
The link is in the video description. That’s also where you go to ask questions, get support, file bug reports, request improvements, yada, yada. Don’t send me emails.
I will just tell you to go to GitHub. So that’s, that’s how I roll. 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.
In this video, I wanted to share an exciting improvement I made to SP QuickieStore, a tool designed for analyzing SQL Server Query Store data. While working on my GitHub repo and experimenting with Cloud Code, which was mostly successful but occasionally hit some snags, I decided to focus on enhancing the utility of SP QuickieStore. Specifically, I introduced a feature that aggregates metrics by query hash, providing a more comprehensive view of how often queries are executed and what their overall impact is. This change is particularly beneficial for those dealing with ad hoc queries that generate different execution plans but share the same query hash, offering insights beyond just the top 10 queries displayed in Query Store. If you’re interested in supporting my work or learning more about SQL Server performance tuning, you can find all relevant links and information in the video description below.
Full Transcript
Erik Darling here with Darling Data, and this is going to be a sort of short video. So I’ve been doing a lot of work on my GitHub repo yesterday. Yesterday, these like past two weeks, I’ve slept none whatsoever.
And like part of what I’ve been doing is like general repo improvements like readmes and code refactoring. QuickieStore was the beneficiary of a very large code refactoring to get rid of a lot of stuff. And as part of it, I wanted to experiment with Cloud Code, which was like 70% good.
I tried Cursor before that because everyone was talking about Cursor, but Cursor just completely like broke down on me. But I’m going to do a video about like the improvements that I made using that. It’s not going to be like a live video of me talking through that stuff because like honestly, it’s really boring like watching me type stuff into a command prompt and then watching like the thing spin and charge me 50 bucks and spin and charge me 50 bucks to do stuff.
But like I think the results of it for some things is pretty cool. The results for other stuff was way more mixed. So I’m going to talk through that in a different video.
But for now we’re going to talk about some improvements that I made to SP QuickieStore and the results. And this is actually a very, very useful change for people who have a lot of sort of ad hoc queries that hit SQL Server. So what I do in this one, and I do need to preface this and say that this is like this can be wrong because of a bug in SQL Server.
And it’s not just a bug with QueryStore. It’s a bug that goes back to plan guides. Paul White recently blogged about it where when you like create a plan guide and or like you force a plan in QueryStore, something in the SQL Server code swaps query hash and query plan hash.
So this is subject to like weird bugs that are not my fault. So if you see weird results in here, it’s not me. It’s Microsoft.
Blame them. Have them fix it. Call them up and yell at them. Get the pitchforks out. Whatever. But this is very useful for people who have lots of sort of ad hoc queries that hit SQL Server because what it does is it groups it like aggregates metrics for each query that would be in your results anyway. And like adds a column that shows you like like like like the totals by query hash, not just for like the query ID and plan ID combination that you’re looking at.
So that’s very, very useful for some people where you’ll see like way different numbers than you would see just in the regular results. Be like what this query ran once who cares. Like no, you can find find out this query ran like 3000 times and it got the same query hash and just got different query plans.
So neat stuff there. But before we get into that, let’s talk about this channel and you and me and birds and bees and butterflies and what’s that poem from Boogie Nights? Something about the slide or something.
It’s a slide, I think. Water slide. Anyway, if you like this channel and you like my content and you like my SQL Server stuff, you can sign up for a number ship for as little as few as $4 a month. That’s like a shot of New York espresso.
Link to do that is down in the video description. And I just want to preface most of this by saying almost everything that you are like is a actionable link is going to be in the video description. If you don’t have $4 per month to give to a handsome, charismatic fellow like me, you can like, you can comment, you can subscribe.
And you can ask me questions for my Office Hours episodes at that link, which is again down yonder. If you need help with SQL Server, if you find yourself struggling with performance or other issues, I’m available as a consultant to do all this stuff. And as always, my rates, they are reasonable.
If you would like some very reasonably priced training, you can get all of mine for about 150 US dollars. That’s with that 75% off code. The link again down in the video description for you to get all that fun stuff with.
SQL Saturday, New York City, 2025, May the 10th in Times Square at the Microsoft offices. Come, I don’t know, write something in the bathroom stall, I guess. I don’t know what you people do in bathrooms.
But with that out of the way, let’s talk about SP Quickie Store. There’s not a ton to show in here. Just really this one thing that I want to point out. But it’s a big enough improvement for me to want to talk about it like a lot because it’s something that a lot of people are going to find useful.
Like with all of my store procedures, there is a help parameter. And that help parameter will give you all sorts of information about what the store procedure does, document things within the store procedure. Like these are weight stat categories for query store.
But down here a bit, this thing will let me see. If this thing will scroll in the right pane and not just be a pane, you will find this include query hash totals thing somewhere. Where are you hiding?
There we go. There we go. Include query hash totals. So one fun thing about SSMS is that now copies the column header with everything. So that’s nice. But I just want to show you real quick kind of what this does in the code.
And we just need to get past a few things in here. This should be it. So what this does is when you say I want to include query hash totals, this thing will look at all your query store data for the queries that you get in your table of queries, like the top 10 queries that you would have gotten in here anyway.
My initial implementation of this was okay, but left something to be desired. So at first I was just doing like the query hash total for what was like in the results. And like that’s okay as long as there’s like duplicate queries, duplicate query hashes in the results.
This goes way back and finds everything. Aggregates like executions, duration, CPU time, reads, writes, memory, rows, everything across like all of your query store data, but only for the plans that end up in there. Otherwise this thing would be hell on earth to run query stores incredibly slow and painful to query.
So that’s what this thing does. And then in the end results, you will see the include query hash totals columns. What that looks like for my query store thing right now at this very moment in time is this.
So one thing that I, one thing that I change in the results when you say include query hash totals is to include the query hash column. I don’t, there’s at least a couple instances of there being duplicate query hash stuff in here. So we have that going for us, but if we come over and we look at the results a little bit further over where we start getting to like the places where you will start seeing these count executions by query hash type columns.
You’ll see that they are different from the columns in some other places. So this one found another query hash there. This one found two more.
This one found another one. This one found three more. This one found two more. So we like, you can already see where like something like aggregating things by query hash gives us way more information about like how many times a query did something. This also plays into like comparing like the total for some of these columns where the results are different.
Like this one here that goes from two to five, like the total duration in milliseconds for the two executions that we have in here is about 11 seconds. That’s about five and a half seconds a pop. But the total duration for this thing is 22 seconds, right?
So those extra three executions did way more. So I don’t do like the averages and stuff with also the total duration by query hash involved. I think just showing like the totals like with all the query hash stuff aggregated is enough.
But this column will repeat for all of the useful metrics in here. So it’ll do it for CPU. It’ll do it for reads, even though reads are a stupid meme and you shouldn’t pay attention to them.
It’ll do it for writes and physical reads, which are not a stupid meme. You should pay attention to those. So basically everything over here will end up with a total by query hash column that will complement other columns in here.
So that’s very, very useful for people who have lots of ad hoc queries that generate different query plans or have generated different query plans with the same query hash and whatever, because you get a much better idea of just how much these things are happening outside of just the results that you see in the top 10 by whatever metric you choose to order by in QueryStore. So, again, quick video just to give you a heads up on this new thing.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that you will turn on QueryStore and use sbquickiestore to navigate it. And again, you can get this code at my GitHub repo.
It’s code.erikdarling.com. That link will be in the video description as well. And again, that’s where I do all my support and stuff from. So if you have questions, you hit bugs, you have other problems, then file the stuff there. Don’t email me because I’ll just tell you to go to GitHub anyway.
So deal with it. Anyway, thank you for watching. Now, I’m learning to smile and smile and see you. I hope that that houffLife is happening. I hope that 노 amplifier해� dessus time, cause you might get things presente. I think, Maroon conditions on Internet says… Like a community number one or whatever.
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.
My company (using SQL Server 2019 Standard) has an algorithm that keys addresses into a varchar(40) has a cross-reference database that assigns an identity property to each new value, allowing us to post the numeric in our datasets. Production has to search their generated string keys in this database’s table to get the integer key in return. We have ensured proper string data typing on lookups and have unique compressed indexes on the string values. What would your next tuning step be if this was not getting the performance you needed?
What are your thoughts on a proc with heavy dynamic SQL used to provide for flexibility – to include extra tables to join, table variables of IDs, and usually resulting in multiple query plans? Is there a best practice to handle this other than “create separate procs” (and stop using table variables)?
What options do we have for optimizing queries against tables that store JSON data in SQL Server? There are often queries that update the JSON in place or depend on some JSON value for filtering. Would indexing a computed column make sense over an indexed view? At what point do we start trying to design a normalized table structure to store this data?
Filtered index over IS (NOT) NULL – good, bad, or ugly?
how does RCSI/Sanpshot isolation work when crossing databases, if either the calling DB or target DB does not have it enabled?
In this video, I dive into a variety of SQL Server-related questions during my Office Hours session, covering topics like optimizing queries against JSON-stored tables, parameter sniffing and execution plans, the use of table variables in stored procedures, and snapshot isolation across databases. I share insights on how to effectively troubleshoot performance issues, discuss best practices for dynamic SQL and procedure design, and provide advice on when it might be better to normalize data storage rather than using JSON or XML columns directly. Whether you’re a seasoned DBA or just starting out, there’s plenty of valuable information here to help improve your SQL Server skills.
Full Transcript
Erik Darling here. Guess what? If you were feeling a strange vibration in your soul, if you woke up this morning and you thought, wow, today’s going to be a good day, it must be because it’s time for Office Hours. My favorite days, favorite moments in my life answering these questions. If you would like to ask a question for Office Hours, you can do so at this lovely link right here. It’s available in the video description, I promise. I make things easy for you. The other thing that I make easy for you is if you would like to join this channel as a paying member to say thank you for the extraordinary amount of work and effort that I put into these things. PowerPoint’s free. Microsoft, have you seen Microsoft subscription charges? They wanted to charge me an extra 30, some 40, 40 bucks a year to add Copilot to things. And then they had the audacity to put an asterisk and say Copilot included. And like, of course it costs 40 more dollars. So here’s a big tip for you. Here’s a money saving tip for you. If you got an email from Microsoft that was like, we’re going to start charging you more. If you go to cancel and you like click through all the yes, I’m out buttons, you can actually sign back up for like the Office 365 Classic that doesn’t have any of the AI stuff for the normal price. So screw you Microsoft. Pigs. Anyway, since I just saved you 40 bucks a year, if you want to contribute 40 bucks a year to me, you can sign up for a membership. Anyway, if you are just broke because Microsoft took all your money, Microsoft bullied you in the cafeteria. You have zero dollars left. You can do other things that support the channel. You can like, you can comment, you can subscribe. And I don’t know, I think that’s about it. I’d say you can give me a hug, but you know, I don’t need any social diseases at this point in my life. If you need help with your SQL Server, if you think, gosh, it’s time for young, good looking consultant to come fix all my problems for me, because Copilot ain’t doing it. I am available for all of these wonderful SQL server activities. And as always, my rates are reasonable. If you want some training on SQL Server, if you are just like, man, I have all this room in my brain, what am I going to do with it? You can get all of my performance tuning content for 75% off that is about 150 US dollars. And you get that for your entire the entirety of your life. So you know, do your don’t die.
Before you finish watching anyway. If you would like to catch up with me live and in person, I will be at SQL Saturday, New York City on May the 10th. And there will be a performance tuning pre-con that I will be, I will be in the room for, given by Anderias of Autor on May the 9th. And we together will give you cookies. And it’ll be nice. But with that out of the way, let’s answer some office hours questions over here. We got some we got some long ones today. Boy, howdy. Let’s see here. My company using SQL Server 2019 standard. I feel like we need like a montage cut on this one has an algorithm. It’s a fancy word for query that keys addresses into a VARCAR 40.
Okay, as a cross reference database that assigns an identity property to each new value. Okay, getting there, allowing us to post the numeric in our data sets. Sounds good. Production has to search their generated string keys in this database’s table to get the energy key column. We have ensured proper string data typing on lookups and have unique compressed indexes on the string values. What would be your next tuning step? What would your next tuning step be if this was not getting the performance you needed?
Well, I mean, there’s a lot of information in there. What there isn’t in there is an example of the query or an example query plan for any of that stuff. There are many ways for you to share this stuff with me. There’s a wonderful website called dbfiddle.uk. My friend Jack runs it and you can put example queries in there. You can use sample data. You can do all sorts of stuff. If you want to share query plans, there are a number of ways to do that.
Paste the plan.com, anything that you can paste the XML into, and I can answer questions like this better. But with most things performance, the question comes down to what is the server telling you and what is the query telling you? It’s difficult to say if this is a performance problem specific to the query and process that you have in place.
Like it could be a server level, like something is messed up in a muck, or it could be at the query level. And in which case, the things like the execution plan will tell you where you are spending the majority of your time. Since you are on SQL Server 2019, you have this wonderful facility where when you get the actual execution plan, it will tell you where you actually spend time in your various operators.
And then you can look and you can say, gosh, I spend a lot of time here. How can I make this better? So that’s where I would start. I don’t know where to go based on this word problem, but I don’t know exactly what I would do based on this word problem.
But I know exactly what I would look at to figure out where things are slowing down on me. You know, it could be that you need to watch the server and all this stuff is happening. And you might see that the server is overloaded in some way, either from a CPU or memory or locking perspective.
Perhaps there is some clashing between you generating these FARCAR 40s and identities and other stuff, and you trying to read from those tables. I don’t know.
But I do know where I would look. And hopefully now you know where to look too. All right. What are your thoughts on a PROC?
Procedure with heavy dynamic SQL used to provide for flexibility to include extra tables to join, table variables of IDs, and usually resulting in multiple query plans. Is there a best practice to handle this other than create separate procedures and stop using table variables?
Well, table variables really shouldn’t result in separate plans since table variables have a recompile rate of exactly zero. You should be reusing execution plans if your queries are properly parameterized and, you know, you send in two of the same query.
It should be happening. There may be something going on with table variable deferred compilation. If you’re on SQL Server 2019 plus and you’re using compat level 150 or better, you might need to look at that. But in general, I have no problem with this.
I don’t even have a problem with there being multiple execution plans for things. The plan cache has been babied and treated with kid gloves for years, and it has done nothing but abuse and mistreat us, clearing out frequently, not giving us all the information we need.
So I don’t really see a need to be kinder to the plan cache. So screw you, plan cache. If you are overloading query store because these queries are particularly busy, that might be a slightly different matter.
But in general, I don’t have a problem with this, and managing plan reuse generally comes down to proper parameterization. And, you know, if you have this, you know, people are just constantly generating queries that need to do different stuff because you are obviously, like, joining to different tables on some occasions.
You might be selecting different columns. You might have completely different where or join clauses based on this stuff. You might even throw in some exists or not exists just for, you know, happy fun time giggles. It’s one of those things where, like, I don’t mind heavy dynamic SQL because heavy dynamic SQL has been very, very good to me over the years.
And when you need to provide this type of flexibility to end users, there is no guarantee that separate procedures would get you anything more than properly parameterized dynamic SQL executed with SP execute SQL would get you.
It essentially gets you the equivalent behavior of a store procedure because SP execute SQL is a store procedure. And that store procedure is executing a query, and you will get plan reuse on that query so long as everything lines up appropriately.
So it may not be a solvable problem for you if the number of queries that you are generating is indeed vast. All right. And here’s, oh, God, another long one.
All right. What options do we have for optimizing queries against tables that store JSON in SQL Server? There are often queries that update the JSON in place. Oh, oh, come on.
Or depend on some JSON value for filter. No. When indexing a computed column makes sense over an indexed view, at what point do we start trying to design a normalized table to store this data now? There are two good times to design tables to normalize JSON or XML or any other type of blob-ish data.
When you first design the application and today. When you have to do, like, blobs like that, ideally in any database, are retrieve only. You just get them out and you return them somewhere where they have to be returned to.
You should, if you need to parse them apart, you should do that in the place that you return them to. Doing this stuff in your database is just a crappy idea. Don’t do it to yourself.
It hurts. It’s painful. I’ve seen way too many people try to do this, fail miserably. And by the time they realize how miserably they have failed, it is far too late to re-architect the number of things that would need to be re-architected in order to solve the problem. And you are stuck.
Well, XML is a particularly awful beast. But JSON, Microsoft smartened up a little bit. But the really bad part about XML is that, like, the XPath stuff isn’t valid as a computed column unless you put it in a scalar UDF. And as you all know, if you put scalar UDFs in a computed column, even if you index them, life is hell.
So the JSON stuff is a little bit more forgiving with that because you can create computed columns based on JSON and you can index them. And you can, like, do that without the presence of a scalar UDF. So that would be one way of doing it if you have, like, a minimal amount of data inside of there that you need to, like, filter on.
That doesn’t help you with the modifying of the JSON. In fact, it would probably hurt you with the modifying of the JSON because now you have to maintain the separate computed column and index structure when you modify the JSON. So if you want my personal opinion, just don’t do this, period.
Parse out your JSON, store it in a table, don’t update it directly, don’t filter on it directly in a query. It’s just bad news for performance. Let’s see here.
Ah, blessedly short one. Filtered index over is not null. Good, bad, or ugly. Perfectly fine in my estimation. Just make sure that when you create your filtered index, at bare minimum, the column in your filter expression is included somewhere in the index. It could be a key column.
It could be an included column. In my experience, the include column is just fine for it because you are probably going to be, it’s probably going to, like, with no other predicates in place, it’s just going to be a scan of the index anyway because SQL Server knows that all of the data in the index matches your requirement.
The nice part about is null and is not null is you can’t really, like, they don’t have the usual problem with, like, bit columns where you could be searching with a parameter. Is null and is not null, pretty much have to write those out as literals every time, so you don’t have to deal with that as much. So just a fine thing to do.
The only caveat’s there. Again, make sure that the filter, the column that is in your filter expression is somewhere in the index definition. And, you know, what do you call it?
There was one other one. Make sure that it is a useful filter. If your filter expression gets you, like, doesn’t eliminate, like, half of the table data or better, it might not be worth it in the long run. Like, if you still have, like, 75, 80, 90% of the table, like, covered by whatever filtered index, what are you really getting out of it?
Not much. Not much at all. And finally, question number five.
You can tell it’s question number five because there is a five right here. So this will be the last one today. How does RCSI and snapshot isolation work when crossing databases? If either the calling DB or target DB does not have it enabled?
Well, if you’re in the calling database and you try to set the isolation level to snapshot and snapshot is not enabled, you’re just going to get an error. So you can’t do, you can’t go from, like, here to here with that. I believe that’s also reciprocal.
I don’t think that you can start a transaction using snapshot isolation if you are crossing boundaries into a database that doesn’t have it enabled. RCSI is somewhat more forgiving because you don’t actually have to set anything for that. So the behavior that you should see is that regardless of where the query starts, when you start reading data from a database where it’s enabled, you should see the row versioning stuff.
But, like, for any database where it’s not enabled, your modification queries aren’t generating row versions so they can’t use it. Right? Like, it’s just impossible.
Like, there’s no row versions for them to read so they can’t possibly use that isolation level. So, good for us. We have answered all five questions, hopefully to everyone’s satisfaction. I don’t think there’s much more to say here.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I guess I’m going to try to cue up five more to answer. We’ll see how it goes.
Anyway. Cool. Thank you for watching. 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.