sp_QuickieStore: Find Your Worst Performing Queries During Working Hours

sp_QuickieStore: Find Your Worst Performing Queries During Working Hours

---

Thanks for watching!

Video Summary

In this video, I dive into a recent update to SP Quickie store, focusing on adding functionality to filter query execution data based on working days and hours. This feature allows you to narrow down your workload analysis to times when users are actively engaged in their work, helping you identify and address the queries that truly impact end-user experience. I walk through the changes made to the stored procedure, explaining how new parameters like `work_days`, `work_start`, and `work_end` enable more precise filtering. By setting these parameters according to your local time settings (using a 12-hour clock for American convenience), you can effectively filter out noise from off-hours data, focusing on queries that users actually care about. This video is perfect if you’re looking to refine your query performance analysis and make targeted improvements based on user behavior during working hours.

Full Transcript

Erik Darling here with Darling Data. I don’t quite know why my face looks so red in this recording, but who knows? Maybe I’m turning into the devil. Hard to tell sometimes. Anyway, this is my first, well, one of these videos of 2024. I took a little break from doing a lot of stuff like this. Kind of after a past summit, I just needed a little chill time, and holidays came around, and I don’t know. Got busy with stuff, traveling around, doing things. And I did record a pretty cool hour-long video on my YouTube channel with Mr. Randy Pavlo of OtterTune and General Database Geniusness. That was fun. But in this video, I wanted to talk about a recent addition to SP underscore quickie store, quickie store, quickie store, quickie store, quickie store. That gives you the ability to filter your workload to just working days and hours. So this is what the query execution would look like, but there’s not a lot of fun in that, is there? Not a lot of, like, just, look, there it is, go do it. So what I want to do is go over to my GitHub repo. And apparently it’s just when the screen in front of me is really bright white, I turn really red. This is a little bit calmed down now. This is a little bit nicer. What I want to do is just kind of walk through the change itself so you can see exactly kind of like what’s going on in it. All right. So the first thing that I did was I added some new parameters to the store procedure. They are, if you weren’t paying attention when the video started, started, started. They are got a little aggressive on me, work days, work start and work end. Now, work days is a bit. And this tells quickie store whether or not to follow the code path to calculate all these things. And then we have work start, which is a string. And work end, which is also a string. And in those strings, what I’m expecting you to put in is using a 12 hour clock. Not a 24 hour clock. A lot of my constituency is Americans. And many Americans are not used to 24 hour clock stuff. And just because of vast American superiority, we are going to stick with the 12 hour clock. We speak the best English. We format the best dates. We make the best cheeseburgers. I don’t know. We do a lot lot of things just really well, including sticking to 12 hour clock. We have the best measurements, hands down. So we’re going to stick to the 24 hour clock thing as well. So part of what we need to make this thing work is a temp table called AM PM. And that’s going to hold our the time that we need to figure out where we’re going to nuzzle our queries between. We’re going to cuddle up to our queries.

And then we need to do a couple other things just as like little placeholders to keep track of important stuff along the way. So I’m going to explain these as we go through. But DF is going to hold date first information. So we make sure that we are starting depending on local date settings. So if you’re in Italy, you have different like date first day of the week settings than people who live in places that aren’t Italy or something like that.

And then we need to, there we go. So that’s where we set date first to the local date first setting. So we hopefully get the right days of the week. It’ll either be like one to five or two to six or something, depending on where you live in the world and what your local language settings are and all that stuff. And then another thing that we need to do, aside from populate the temp table with interesting information about times and all that good stuff, is to make sure that we are dealing with the right hours according to UTC time.

So what query store does is it stores all of its date data in UTC time. So we need to bump any numbers that we care about up to make sure that we are hitting UTC time. Now, internally, of course, we need to use a 24 hour clock because the time that gets stored in there is 24 hour time. But I like to make it as easy as possible on all my American friends if they want to get the right times in there.

So by default, I’m going to, if you don’t put in anything, I’m going to work with nine to five. And if you start, if you leave either end of the equation open, I’m either going to add or subtract eight hours to your workday to give you some semblance of a reasonable set of data. And then, of course, we need to flip our work start and work end stuff to respect UTC time.

So this will add, like, whatever, however many hours we need to add to get the start and end time to make sense UTC time. That’s what we’re going to do. And then if you have done all this stuff, then we add, if your date first setting is one, then we will look for where your date numbers of the week are between one and six.

And if your date first setting is seven, then we will look for where your date first stuff is between two and six. And then we will also keep saying we, I, very much I, I will, or SP Quickie Store will. I actually won’t do anything.

I’m not going to come on your server and start filtering things and doing weird stuff. But then we’ll look for the work start and work end int. And, of course, the work start and work end int translation happens.

Oh, gosh, where is that? Right about here where you set whatever string information you put in to the store procedure, like 9 a.m., 5 p.m., whatever information you put in there, we translate that to integer numbers so that we can do the filtering without things getting all stupid.

And what that will get you at the end of your long, hard, exhausting day is, oh, look at that. Let’s switch that over to a database that has Query Store enabled on it. And what that will get you is a set of data that all happens.

Because if you look at this last execution time column, all the queries in here will have occurred on a weekday between 9 a.m. and 5 p.m. And the reason that I wanted to do this is because something that I say quite a bit, but I don’t know. I felt like when I said it so much that I actually started reflecting on my own stuff is that anyone can find a slow query.

If you look at any script in the world that looks at Query Store, Query Store included, the plan cache, any one of those things, they just order by some metric that gives you some query that does the most of something. And you can certainly, like, for a lot of them set, like, boundaries for, like, I want something that happened between Monday and Wednesday or Tuesday and Thursday or whatever, right?

But you can get a lot of noise in there, right? There’s a lot of stuff that happens, like, after hours, early morning, you know, whatever that you don’t care about that doesn’t affect end users. We want to find queries that end users complain about, that affect end users working, doing what they need to do.

We want to find those painful queries so that we can start tuning those and we can make a bigger, better difference. Otherwise, like, by default, Query Store, I mean, this seemed like a reasonable starting point. By default, what Query Store would do was just go back seven days, find the top 10 queries that use the most CPU, and then, you know, you could do other filtering and stuff from there to find different things within that.

But, you know, it just, there wasn’t, like, for me, when I was, like, working with clients trying to find stuff to tune, it’d always be like, oh, look at this thing, it uses 10 million CPU things. But, you know, they’d be like, ah, man, it runs at 3 a.m.

What are we going to do with that? Nothing much. So, a new tool in your arsenal to find your worst performing queries that end users, might be really happy if you fixed, is to use Quickie Store and to look for work days, right?

So, we set work days to one. Reset our work start and work end to times when users are in there doing stuff, meaningful, important work that users do, right?

Clicking stuff, waiting for things to finish. And then, that’ll allow you to find your worst performing queries when people will say thank you for fixing them. Because often, there’s a really big difference between the stuff you want to tune and the stuff that end users will be happy if you tune.

Now, you might find some store procedure that has every anti-pattern in the book, but who cares? No one cares but you. Anyway, thank you for watching.

I hope you learned something. I hope you enjoy yourselves. If this is the type of SQL Server content that you enjoy, feel free to give the video a like and feel free to give my channel a subscribe.

Now that the holidays are over and I’m a little bit back to normal, I’ll be recording a lot more, so I have that to look forward to. And anyway, thank you for watching and I will see you in the next video.

Sometime, maybe today. Who knows? Feeling crazy.

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.