SQL Server Performance Office Hours Episode 8
| Hello friend, How many Hello friend, are there techniques to compare data returned from a sproc when making perf changes to make sure you are not messing up the results? I currently run the old sproc and the updated sproc and copy the results into a text diff app, but was wondering if there was a better way. |
| Hi Erik, What is the feature of mssql that you would like to see customers or dba use more ? |
| What are your top three books on performance tuning that you would enthusiastically recommend to someone about to embark on this journey? |
| Hi Erik, in AdventureWorks I have these two statements: select top 5 ProductID pid into #p from Production.Product; select * from Production.Product where ProductID in (select ProductID from #p); As you can see, the subquery is wrong, but SSMS doesn’t highlight the error, the query runs basically unfiltered. Do you know the reason? |
| Hi Erik, please do NOT consider my previous question. Thanks. I’m tired…. |
To ask your questions, head over here.
Video Summary
In this video, I dive into some SQL Server performance tuning tips and tricks during a lively Office Hours session. We tackle questions ranging from comparing data return between old and new stored procedures to improving code formatting for clarity and efficiency. Whether you’re looking to optimize your queries or just want to know more about the features of Microsoft SQL Server that could benefit your work, there’s plenty to learn here. I also share some of my top recommendations for books on performance tuning, providing a resource list for anyone embarking on this journey. So if you have any questions or need help with SQL Server, consider joining our next Office Hours session or exploring the resources available on my website, erikdarling.com.
Full Transcript
Erik Darling here with Darling Data, and I don’t know if you can get that pungent aroma in your nose. What is that delightful smell? That is the smell of Office Hours. Yeah. Alright. If you like this content, maybe you want to ask a question on this here Office Hours thing. I hesitate to call it a podcast because, God forbid, another person in tech with a podcast. Wow. No, not doing that. It’s not a thing. You can support this channel by signing up for a membership. You can like, you can comment, you can subscribe. All of the useful links to do these things are available in the video description. Gosh darn it. Look a little bit down below the handsome face. If you need help with SQL Server, maybe you watch these things and you think, gosh, that Erik Darling sure does know his SQL Server stuff. Well, you can hire me to do all sorts of SQL Server things for you. And as always, my rates are reasonable. If you want some training, I have it. I’ve got it. I would love for you to have it. I would love for you to have it so much that I give you this gigantic discount, 75% off. That brings it down to, what, $150 US dollars. You get that for the rest of your life.
Again, video description. What a nice place to be. We still have SQL Saturday, 2025. That is not the 2025th SQL Saturday. It is the year that it is taking place. The date that it is taking place is May the 10th with a performance tuning pre-con by Andreas Walter on May the 9th. And you can go to both. And you can see me at both. And I don’t know, maybe we can go smoke cigarettes outside together. With that out of the way, let’s go party here. Let’s have some office hours fun. And I’m going to have to shrink this down a little bit. It’s not really friendly to the… Oh, dear. What happened there? Oh, boy. All right. Well, you know, nothing’s perfect. Anyway, let’s get to this first question here. If Zoomit will decide to be my friend. Well, that word wrap cut off a little bit, but we’ll deal with it. Hello, friend. How many hello, friend? How many times can you type hello, friend?
Are there techniques to compare data return from a sprock? Godly. Just call it a store procedure. Why do you have sprock? Sounds ugly in the mouth. Sprockets. When making perf changes to make sure you’re not messing up the results. I currently run the old store procedure and then the updated store procedure and copy the results into a text diff app. There are easier ways of doing this, depending on the nature of the results.
If… Okay. So, one, if you have a store procedure that returns multiple result sets, we should probably talk. It’s not really a great thing. You know, like my analysis procedures do, but they’re not being consumed by an application. So, let’s hope and pray that your store procedures are just returning a single result set.
You could either use a global temp table and like keep it, like even like I understand, like you’ve made the performance changes. You’ve, you’ve been like, you’ve figured out this new version is faster than this old version. Great. What I would use is either a global temp table or a real table in the database, write the data out to it, and then use some variety of SQL, of the lovely SQL standard intersect and accept operators to see if the results are equivalent.
One thing to keep in mind is that depending on the nature of the query plan, the results may be correct, just in different orders. So, if the order of the results matters to you, you may, like if you have like an ID column or some sort of sorting element, you, you might need to make sure that, you know, like if you’re comparing the results, like one-to-one that you have that included in the query. But if you’re using intersect and accept, which are fantastic because they handle nulls.
So, if you’ve got nulls in your results and you’re like, oh gosh, how do I compare all these nulls? Intersect and accept are your friends for that. Let’s go and let’s answer the next question.
Hi, Eric. Hi. Hello. How are you? What is the feature of MS SQL? Miss SQL. Wow. Hello, Miss SQL.
Pretty lady you are. That you would like to see customers or DBA use more? Well, there are a number of things that I have in mind here. I wish that, well, it’s funny, right?
Because there’s stuff that I wish were closer to being the default in SQL Server than currently exists today. Just for example, I firmly believe that since 2005, the default for any new databases should have been the recommitted snapshot isolation level. So, I do think that, well, you know, starting with SQL Server 2022, query store is on by default.
So, that’s a good step forward. But, you know, we’ll see how this 2022, 2025 adoption goes. 20, you know, still, 2019 still leading the pack as far as I can tell.
So, those are two right off the bat. I also, like, I really wish that, so, the system health extended event does capture deadlocks. And it does capture a very limited amount of blocked process report stuff.
You can expose both of those using my store procedure, SP Health Parser, which will go in and get as much information out of those two things as you can get. But I wish that there were more dedicated stuff for the blocked process report and the deadlock XML report. In fact, I wish the blocked process report and the deadlock XML report were wrapped up into Query Store because they’re pretty important things for query performance.
Right? Like, it would just be nice if it were all centralized in one location where you could go and say, oh, this is where performance was awful. So, those are a few things that I wish people would use more.
You know, it does help to create specific extended events to capture the blocked process and XML deadlock report because it is much easier to access them via events specific to those things being captured than it is to go through all the system health extended event stuff. Because the system health extended event logs a ton of other stuff.
And, like, sometimes you lose information or it’s, like, just takes forever to parse through things and get to it all. So, those are a few things there that I think would be useful for people. Another book question.
What are your top three books on performance tuning that you would enthusiastically recommend to someone about to embark on this journey? The good news is for you that I just recorded a video. And coinciding with recording that video on books that I like, SQL Server books that I like, there is now a dedicated page on my website.
So, if you go to ericdarling, that’s eric with a K, erikdarling.com slash books, you will see a list of books that I enthusiastically recommend for SQL Server people. And, uh, this is sort of a twofer, but, you know, um, I do five at a time and these just happen to, these just happen to make the five question cutoff. Uh, so, uh, here we have our beleaguered friend bemoaning their, their exhaustion.
I, I, you know what, friend, I, I hear you. I’m, I’m tired all the time. I, I, worn out.
Uh, please do not consider my previous question. Thanks, I’m tired. Well, uh, we’re going to answer this question anyway because this, this question, like, speaks directly to a lot of things that I’ve talked about here, uh, both in the past and present and probably will again in the future. And that is around code formatting and proper aliasing and all that other good stuff.
They’re saying that they have two statements where they select the top five product ID. Notice that product ID is aliased as pid here without an as. Nuh, nuh, nuh, nuh, nuh.
Always put an ad. Like, like, first off, like, table aliases should be as. Column aliases, you know, it’s a lot more clear when you say pid equals something than pid as. Like, like, you have to read too far over this way to get to the as or too far down.
It helps to have the column names all lined up. And then, uh, the second thing that they have is select star from production dot product where product ID and select product ID from P. Well, there’s no product ID column in P because you name product ID pid in P.
So what’s, what you’re, what you’re not doing down here is aliasing your, your pound sign temp table. And so SQL Server is resolving this product ID column to the product ID out here. And, and that’s where things are going awry.
So please, for the love of God, write your queries clearly. Alias things properly. The sooner this, the, the, the better you are at writing your queries, the less confusing this stuff becomes. You can, you can, you can become a smarter person overnight just by taking the time to be a bit more verbose in your T-SQL.
Stop taking shortcuts. Stop leaving words out. Stop putting things in funny places.
Stop putting commas over here. Commas belong at the end. Uh, column aliases belong as column, as a column name equals expression. Stuff like that.
And, you know, when you do, when you do things like this, when we are sloppy with our code in these ways, we hit all sorts of strange, unexpected bugs. This can, this does not just go for, um, like the, the, the mere writing of the query. This goes, this goes to all sorts of things.
A number of strange performance, uh, oddities that I have encountered in, in silent, in bugs that have been silent for years that, um, have cropped up just because people were lazy with things like this. Or, uh, lazy in, in using the correct data types for things, uh, is, is pretty big. So, uh, when you write your queries, make sure, make sure, you know, you and SQL Server are able to understand them.
And make sure, like, when you write your queries, think if, if the dumbest person, person in the world came and looked at this query, would they be able to figure out what it’s doing? If Erik Darling came and looked at this query, would he be able to understand what it’s doing? What would Erik Darling say about this query if he saw it?
Well, these are questions that you should ask. Anyway, me and Bats, I’m going to sign off. Uh, I do have some more office hours questions to get through, but since I, I do the five at a time because Bats loses patience with me, we’re going to, we’re going to, we’re going to stick those in a new video. But, uh, thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. I hope that you will format your queries properly. And I will see you in the next video. 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.