Tag: Query Tuning

Do Best

---

Video Summary

In this video, I delve into some common issues in query writing and indexing that can significantly impact performance. Starting off with a few personal updates, I recently got a new haircut and am experimenting with different socks—though you won’t see them in the video, I’m trying to figure out which level of subscription is best for revealing my sock choices! Moving on from the fun stuff, I address a query scenario where using `ROW_NUMBER()` in conjunction with joins can lead to performance bottlenecks. By examining how SQL Server handles this situation without indexes and then creating an appropriate index, we uncover some interesting insights into parallel execution plans and the importance of proper indexing for windowing functions. This video is inspired by a real-world issue I faced recently where a query was running extremely slow, taking up to five minutes before optimization. Through better syntax and strategic indexing, we see significant improvements in both performance and execution plan efficiency.

Full Transcript

Erik Darling here with Darling Data. And I just want to give you a couple personal updates on me. One is that I got a haircut. Feeling good about the haircut. Two is I’m wearing a new pair of socks. You can’t see that. I haven’t quite decided which subscriber level I want to start revealing my socks at, but we’ll figure that out eventually. From a technical perspective, well, a few things. Got a rather brusk comment the other day. Someone saying that they could hear me breathing and they didn’t like that I cleared my throat and that I was full of myself. So, I’ve taken some steps to remedy all three of those things. One, as a fellow misophonia sufferer, I understand the… as well.

So. it, as sortwa- not because I believe in juice cleanses but just because I am trying to be less full of myself and so I am replacing myself with various cold-pressed juices that’s going fairly well I am very very much not as full of myself after a few days of that but in this video what I’d like to talk about is a common I think well it’s a mix of it’s a mix of problems all right it’s sort of a query writing problem and then when you once you write the query correctly you might even uncover an indexing problem and the problem we’re going to talk about is related to when you need to do a join that involves a row number or a windowing function that you’re filtering on you can see in my query here I have row number generating some some numbers over rows and then I’m filtering on that row number outside of the the join so if we have no indexes on the table and I’m pretty sure that I already dropped indexes but we can validate that there if we get rid of all the indexes on the table now this is the index I’m gonna create and I’ll talk to you about why of course because I care about you and I care about you learning for free what we have here when we do when we run this query I’m gonna try not to scroll down any further and give away too much about the the rewrite but we’ll stick with this for now when we run this query it’s gonna take about two and a half seconds at least it did before it might be might be a little bit longer oh I was so close I was so very close 2.553 I was almost on the nose there and I mean that’s not horrible but I’ve written I’ve engineered this query a bit to not be so horrible so that I can record a short video and not sit here for a half hour waiting for a query to run this video was inspired by the fact that I actually just fixed a query like this today it was running for four and a half five minutes so it can get kind of gnarly out there anyway so what this query does is it goes parallel which is understandable because this query has quite a bit of work to do when multiple CPU threads and cores working on this thing all at all at once it’s probably a good idea and SQL Server asks for an index to support this query it says an index would be very useful here I want an index on post type ID and score and it wants us to include owner user ID which is I guess like a an okay junior DBA index it doesn’t take into account several things I mean well it is focused on on the where clause that we have inside of the join where we have an equality predicate on post type ID and an inequality predicate range predicate on score it doesn’t really take into account the fact that we need to partition by owner user ID order by score here order by score here that we need to join on owner user ID here so well it would help the where clause wouldn’t be very useful for any other part of the query this is another another big reason why over the years I’ve grown to really just trust missing indexes don’t think I don’t think they’re often worth prescribing to people outside of rather narrow circumstances where there’s sort of just blood everywhere and you need to you need to like there’s just no nonclustered indexes on any tables and you need a starting point and you can you can tune queries more individually from from there on out but uh so that’s not it’s not a very good missing index request but it will at least get you somewhere right wouldn’t wouldn’t get you all you know across the finish line but it would get you at least it would at least get your sneakers on so you could you could make you could make the run a better way of writing this query is usually to use uh the apply syntax in this case we have a left join up top so we’re going to use outer apply here to make sure that our query is semantically correct which is a I hear it’s important people want accurate results at least sometimes mountain no lock hence I see makes me wonder why people are like oh well I don’t think the results are right like well were they ever good question but I’m not going to run this as is because if we run this as is it’s going to take a long time and the reason it’s going to take a long time is well if you’ve been paying attention to my youtube channel or my blog for any uh any period of time you may have seen me post or talk about eager index spools and we’re going to hit one of those here the reason why we’re going to hit one of those here is because when we use the outer apply syntax sql server is going to choose a nested loops join to uh execute this query uh where is above we used a hash join and nested loops join because we take a row and we go do some work well if we don’t have a good index we’re going to end up scanning that post table a whole lot so sql server I want you to pay close attention up here because there is no green text along here like there was in the query above saying hey we could use an index sql server just creates one for you and if we look at what happens we talk about this a little bit now I know I have another video on eager index pools somewhere somewhere along my channel but you know I’m just going to talk through it a little bit because I don’t want to make you go jumping around from video to video searching for things I don’t know it doesn’t seem like fun to me so uh there there are a few fundamental issues that I have with eager index pools one is that in the context of a parallel plan they are absolute liars uh they are built on a single thread you can read from them uh multi-threaded but uh reading all of the data out of the post table will happen on a single thread that’s like a 17 million row table building a 17 million row index on the fly is not fun especially because uh spools in general which are built over in temp db don’t really have any of the optimizations that creating temp table selecting data into inserting data into creating indexes in temp db have uh they are uh built a row at a time which is also another rather unfortunate scenario uh for the eager index pool um if you find that a weight on your server called exec sync e-x-e-c-s-y-n-c all one word is pretty high you should start going through query plans looking for large eager index pools small ones probably won’t make that big of a difference if the table is like you know 10 000 100 000 500 000 rows you probably won’t have too much of an issue building uh an eager index pool in a plan and i mean if you have to build it multiple times that’s a different story but um usually it’s when tables tend to get on the larger side i think 17 million rows qualifies at least in this case for being on the larger side uh the bigger the table is the more painful building the spool gets um and of course sql server doesn’t offer you a missing index request to say hey i’d like to stop building this index every time the query runs i’m just going to build it every time it runs and i’m going to make fun of you behind your back so this is how uh using better syntax can uh sort of uh unveil indexing issues uh in in the uh in the in the database and uh if we hover over the eager index pool we can of course see the definition of the eager index pool that’s equal the the definition of the index rather the sql server creates uh the output list is irrelevant in this case because the score column is represented here already so we don’t need to care too much about that uh what we do need to do is take into uh mentally take into account that we are seeking on three columns owner user id post type id and score all right so for most eager index pools you want to take the seek predicates and create an index with a key on the seek predicate columns and then if there are any columns that are in the output list that are not already represented in the key of the index and we’d want to add those to the included columns cool we got that we’re good there so again i’m not going to run that because it would take a while to run but i want to i want you to keep in mind that when we run this query i’m just going to take again about two and a half seconds 2.6 this time shocking uh when we run this query we get a fully parallel execution plan we scan the clustered index and we filter to where the row number equals one here then we come out here and we sort our data here we also have another sort inside the inner part of the nested loops join and because we don’t have an index that helps us with the uh with the row number function again that’s owner user id partition by and score descending ordered by uh we have to we have to sort that data for all of the results that come out of the post table here right that that that sort is going to ask for 490 megs of memory to to run and do that so let’s create our index and let’s see how the plans for these change now the index i’m creating leads with owner user id and it’s going to lead with owner user id because i’m tailoring this index to the apply syntax if i were trying to tailor this syntax this index to the left join syntax i would probably put post type id score first and then owner user id because we can’t do the filtering for this query until we hit the join with the apply syntax and we when we get the nested loops join we can get uh the apply nested loops version of a nested loops join and push the owner user id column into the inner side of the nested loops where it acts as a predicate which i’ll of course show you when we get there eventually so let’s run these two queries now and we’re going to see how the execution plans for these change now i know we only looked at the estimated plan for the apply syntax before but again i don’t i didn’t want to sit there for you know a couple minutes waiting for that to run first thing i want you to notice is that sql server uh asks for a different index on the well actually that’s for the same index on the post table uh because it still wants the filtering in here first because it has to join on this later but again you know owner user id is a join column i don’t understand quite understand why we wouldn’t want that to be um we wouldn’t want that to be uh in the key of the index so this plan is single threaded now uh and this is what happens a lot when you’re indexing for queries especially that use windowing functions in the real world is something that i see happen all the time you create a better index and even though this query is has a cost of sorry an estimated cost of 108.731 query bucks a sql server does not choose a parallel execution plan for it perhaps sql server is catching on to the fact that parallel merge joins were a mistake and because it chooses a merge join here we don’t need we would we would need to uh we we sorry we run the risk of uh getting all sorts of uh intra-parallel query uh uh thread dependencies and deadlocks because of potential ordering but um one thing that is kind of interesting about this query plan is most of so uh most of the time um you know you like the merge join of course expects sorted input and since we have owner user id sorted we can do the merge join without a uh without without a sort operator in the plan and double that with the fact that you know uh the row number function is kind of putting owner user keeping owner user id in order we don’t need to sort the data any any further to support the merge join it’s a little interesting that uh sql server did not join a did not choose a hash join here or it did not and that it did not choose a nested loops join here considering we have a pretty prime index to support a nested loops join and we would have uh at least a pretty good scenario for a merge join we could if this plan went parallel we used a bitmap we could probably do a little bit more work there or a little bit less work there especially because we end up you know it’s a merge join we have to scan this whole index anyway all right we have to do an index scan of the nonclustered index i’m going to frame that up because i’m kind of standing in the way of it all right and this is why sql server wants the missing index request on these two columns again here because it has to do this filtering first and then way over at the merge join is when we can finally evaluate if all right a little bit of screen lag there’s where we can finally evaluate if owner user id equals the id column in the users table so the the second query the outer apply query is in a slightly different shape uh rather than taking 4.2 seconds this takes about a little under 500 milliseconds you zoom in on that hello screen lag 483 milliseconds this does get a parallel plan now i could have nerfed this to get a max stop one plan or a single threaded plan but i chose not to because i want you to see what i encounter a lot of the times in query tuning and that is that with a like you know better indexing in place oftentimes the the query pattern above that i showed you with the left join to the the select from the post table then joining to the user’s table outside and also filtering to the row number function outside uh is slower right a lot of the times because sql server will choose a serial execution plan for that now part of the beauty of using the apply is that we don’t do one big scan of the post table we take a pretty small set of rows from the users table right we take about 13 000 rows from the users table we go into our nested loops join and for each iteration of the nested loops join we seek into uh the index that we created on the post table which you know that’s not bad for the amount of rows that we have to go and do go and like find over there and go do stuff with uh we generate our window function and of course over here we filter out our window function at the end which is good enough i think bring the query from you know uh the the original form with no indexes to support it to uh the the form where we do have an index to support the query and then uh writing the correct syntax we made it we made enough of a dent in this query i think to to say hey i think i think i think we’re in good enough shape here there’s not really a whole lot else you could do now keep another reason why um over time i’ve grown to distrust the misinformation that are that is missing index requests is now sql server for this query is saying oh we need an index on the users table on reputation and display name well i guess we guess we could shave 76 milliseconds off this thing maybe i don’t think that’s really i don’t think that’s really what i would call a victory at least i wouldn’t i wouldn’t i wouldn’t i would never go to someone who hired me and say hey we’re down 76 milliseconds here’s the invoice doesn’t seem doesn’t seem like a good plan to me anyway uh to kind of recap what we talked about here because we are getting up around the 20 minute mark and i must my mustache is a little bit itchy uh when you’re writing queries where you’re going to use a windowing function to uh determine the top you know one thing for something we could you know again if i was working on this for a long time i might experiment with writing the query as a top one uh or something like that it all kind of depends a little bit might even might even try like a max query here right get the see how that works so it kind of depends on the data and the indexes a little bit might also depend on you know if i’m even able to create indexes maybe i maybe i’d want to do just like a big aggregate where i get batch mode or something but when you’re writing queries that need to find the this specifically where you’re looking for the row number you’re filtering to the row number and uh you need it to be fast generally the better i the better prescription for writing this is to use the apply syntax to encourage the optimizer to use a nested loops join and also to have a decent supporting index for what the query is uh well in this case the join columns the filtering columns and of course the the windowing function columns are all very important things to consider um one pretty big deal that uh that i find is uh if you don’t have an index that perfectly supports the windowing function and you end up like we saw originally with with a sort on the inner side of the nested loops to put the data in order for the windowing function uh that one big sort on the inner side is typically a whole lot more painful than doing one sort per owner user id on the inside right because you you you would you would you would only need to sort data that you found for each owner user id rather than sorting the entire table that you’ve that you found after filtering out these predicates by owner user id so i forgot what i was saying must be the juice cleanse cleaned out my brains apparently i don’t know what that says about where my brains are anyway uh thank you for watching if you liked this video even in the tiniest bit like an iota if you like this video uh please also hit the thumbs up button to like it uh if you like me my haircut you want to see my new socks uh or you appreciate my uh advanced noise gate technology or you no longer have to know that i am a biologically viable uh entity you can subscribe to my channel for more noise-free sql server content uh we hit 21 minutes on this which i did not intend to do so i’m going to uh i don’t know maybe i’ll go faint anyway uh 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.

Headline News

---

For this most recent T-SQL Tuesday, I challenged bloggers (using the term challenge weakly here) to think of the last time code they saw made them feel a feeling.

I wasn’t terribly specific about what kind of feelings were in play, and so I kind of expected some negative ones to creep in. Most of the results were overwhelmingly positive.

This challenge made me realize that code, like people, comes in all shapes and sizes. And that code, like music, has quite a wide audience. Some folks get down with the Bieber, and others need a full symphony to locate their jollies.

I’m not judging here, just making a casual observation. Just don’t wear a t-shirt of the language to the conference, and we’re still cool.

Anyway, on the roundup!

Comment Section

---

I’m curating these from the comment section of my post, in order. Here at Darling Data, we strive for fairness in all things.

• Rob Farley sparks one up for windowing functions
• Todd Kleinhans puts ChatGPT in the Blender and comes out with a Cube
• Guy Glantser scores trigrams from Pablo Blanco
• Kevin Chant enjoys the touch and feel of cotton, the Fabric of our lives
• Brent Ozar expresses his never ending adoration for XML
• Hugo Kornelis schools us on the importance of penmanship
• Steve Jones splits everything but his pants
• Kendra Little explores an identity crisis
• Damien Jones and his Amiga hit the Pong
• Isaac Dagny-Heath sees the light on star schemas
• Deborah Melkin takes us on a journey across the emotional spectrum
• Shane O’Neil rides the Kubler-Ross ouroboros
• Kenneth Fisher gets into sp_QuickieStore (I swear I didn’t bribe him)
• Rod Edwards gets by with a little help from row goals

Extended Viewing

---

If you wrote a post that didn’t ping back to me, or you didn’t leave a comment with the link, please let me know so I can add it here.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Busy Bee

---

I’ve been spending a lot of time lately working on my free scripts. And, I know, I’ve been recording videos about them, but some folks out there like to read things.

In this post, I’m going to talk about a couple cool changes to sp_HumanEventsBlockViewer, a procedure I wrote to analyze the blocked process report via Extended Events, and wish I had given a snazzier name to.

You see, when I wrote it, I pictured it as a utility script for sp_HumanEvents, which will set up the blocked process report and an extended event. But it turns out I use it a lot more on its own.

Go figure.

Well, you live and your learn. Sometimes.

Plans!

---

It used to be that there was a column in the output with SQL handles pulled from the blocked process report, and you could take those SQL handles and run Your Favorite Plan Cache Script to (maybe) find the plans for those queries.

That’s clunky, as a wise man once said. Now, there’s an additional result set with all of the available cached plans related to the blocked/blocking queries.

It will look something like this, and there are many related query execution metrics also returned. It just doesn’t make a good screenshot to capture them all.

Priorities!

---

The findings results section used to only be sorted by the check ID. Through the magic of window functions and aggregates, I’m now also sorting the results by which database/objects/whatever had the highest amount of blocking.

It should look something like this:

The code that handles this is pretty cool, and it’s not something I’ve seen many people do. It’s an aggregate inside of a windowing function, that does something like this:

SELECT TOP (10)
    p.OwnerUserId,
    n = ROW_NUMBER() OVER
        (
            ORDER BY 
                COUNT_BIG(*) DESC
        )
FROM dbo.Posts AS p
GROUP BY
    p.OwnerUserId;

In a normal query, you could just sort by COUNT_BIG(*) DESC to order results, but when you’re putting results into a table to return later, the sorting won’t be preserved.

Assigning a row number to the aggregates means I can sort by check number, and then the row number within each check, to put the worst offenders up at the top.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Busy Bee

---

I’ve been spending a lot of time lately working on my free scripts. And, I know, I’ve been recording videos about them, but some folks out there like to read things.

In this post, I’ll be talking about some additions and changes to sp_PressureDetector, my script to quickly detect server bottlenecks like CPU, memory, disk, locking, and more. Well, maybe not more. I think that’s all of them.

Disk Metrics

---

I added  high-level disk metrics similar to what’s available in other popular scripts to mine. Why? Sometimes it’s worth looking at, to prove you should add more memory to a server so you’re less reliant on disk.

Especially in the cloud, where everything is an absolute hellscape of garbage performance that’s really expensive.

By default, I’ll show you results where either read or write latency is over 100ms, but you can change that with the following parameter:

EXEC dbo.sp_PressureDetector
    @minimum_disk_latency_ms = 5;

Results may vary. Mine look like this.

CPU Time

---

This only works for SQL Server Enterprise Edition right now, because it uses a DMV related to Resource Governor.

In the wait stats output, you’ll see how many hours of CPU time queries have consumed since server startup. I know, someone could clear out the Resource Governor stuff, but I’m willing to embrace that as an incredible rarity.

I’m also aware of the fact that I could get similar information from sys.dm_os_schedulers, but that’s only available in SQL Server 2016+, and I sometimes have to support older versions.

On the fence a bit about doing some checks, but right now it’s like…

• Are we on Enterprise Edition? Use the Resource Governor thing
• Are we on Standard Edition? Is it 2016 or better? Use the other thing
• If not, then what?

I wrote a similar bit of code into sp_BlitzFirst, and the fallback is to sum all the CPU time from queries in the plan cache, but that’s awfully iffy. Most plan caches I see, all the plans are less than 24 hours old.

If I figure something else out, I’ll work on it, but for now I’m sticking with this.

New Columns

---

Down in the CPU details section, there are some new columns that detail things like

These are useful, especially during THREADPOOL demos, ha ha ha.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

Busy Bee

---

I’ve been spending a lot of time lately working on my free scripts. And, I know, I’ve been recording videos about them, but some folks out there like to read things.

Why haven’t I been writing lately? I haven’t felt like it. I’ve been enjoying getting my video recording set up worked out, even though one anonymous user hates that I clear my throat sometimes.

I can assure you, anonymous user, it would be far more unpleasant to listen to me talk with a bunch of allergy in my face. Tis the season, and all that.

Anyway, the next few posts are going to detail what I’ve been working on. This one is about sp_QuickieStore, which is my stored procedure to get and search through Query Store data.

All Of’Em

---

The first thing on the list that I want to talk about is the ability to cycle through all databases that have Query Store enabled.

If you have a lot of databases with it turned on, it can be a real hassle to go through them all looking for doodads to diddle.

Now you can just do this:

EXEC sp_QuickieStore 
    @get_all_databases = 1;

And through the magic of cursors, it’ll get all your worst queries in one go.

AdProc

---

The next thing is the ability to filter to a specific type of query, either ad hoc, or owned by a module.

Why? Well, sometimes I work on vendor systems where the user queries are submitted via ORM-type things, and more complicated background/overnight tasks are owned by stored procedures.

I also work on some systems where folks write stored procedures to touch vendor tables, and they want to focus on those because they can’t touch the vendor code.

For that, we can do this:

/*ad hoc*/
EXEC sp_QuickieStore 
    @query_type = 'a';

/*module*/
EXEC sp_QuickieStore 
    @query_type = 'literally any other letter in the alphabet';

I know this looks silly, but there’s no great way to differentiate what kind of module owns the code for non-ad hoc queries. View? Function? Procedure? Whatever.

If you care about only ad hoc queries, put an ‘a’ in there. If you care about code owned by modules, put anything else in there. That’s all it’s checking for, anyway.

Time Light Zone

---

This was a tough one to do, and it’s something that not even the Query Store GUI does correctly when searching through data.

/*time zone*/
EXEC sp_QuickieStore 
    @timezone = 'Eastern Standard Time';

/*time zone*/
EXEC sp_QuickieStore 
    @timezone = 'Eastern Standard Time',
    @start_date = '20230707 09:00 -04:00';

The first command will show you first and last execution times in whatever valid time zone you choose, which can be used to override the default behavior of displaying them in your server’s local time.

That’s cool and all, but now when you search through Query Store data based on start or end dates, I’ll convert your search to UTC time, which Query Store stores data in.

In the background, I find the difference in minutes between your local time and UTC, and manipulate your start and end dates to match.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.

sp_HumanEventsBlockViewer: Now With 100% More Query Plans And Prioritized Results!

---

Video Summary

In this video, I discuss some recent updates to SP Human Events Block Viewer, a utility script that helps analyze blocking issues in SQL Server by examining the blocked process report XML data from extended events. I’ve made two significant changes: one involves adding a second result window that queries the plan cache for execution plans related to the blocking processes, providing more context and insights into the queries involved. The other change prioritizes the output based on which objects or indexes have experienced the most blocking activity, helping DBAs focus their efforts where they are needed most. These updates enhance the usability of SP Human Events Block Viewer and its sister procedure, SP Blitz Lock, making it easier to troubleshoot and resolve blocking issues in SQL Server environments.

Full Transcript

Erik Darling here with Darling Data, rated by several prominent publications, Winesniffer Magazine, Beargut Magazine, and well there are some others, but they all got together and voted and they rated me the most capable SQL Server consultancy in the entire world. Even Atlantis is part of that. I don’t know how computers work underwater, but there’s a strong possibility that if humans had stayed underwater, we would not have nearly as many DNS problems as we do today. This is just a short video on this kind of grotesque Sunday to talk about some changes that I made to SP Human Events Block Viewer.

Now, this started off as a utility script that I could use to look at the blocked process report XML data from the extended event that SP Human Events would set up to get information about blocks, but I extended the usability of this procedure a while back to look at any extended event session that has blocked process report XML in it, because, you know, because, you know, because, you know, human events is great and it catches blocking, but, you know, it just uses the ring buffer and oftentimes, you know, not a lot of data stays in the ring buffer at once.

So it worked really well in the context of the human events procedure, which would log stuff from the ring buffer off to a table. But, you know, in the in the longer term sort of, you know, gathering blocking data with clients, you know, you kind of you don’t often want to set up things of that complexity. You just want to have an extended event that captures the block process report and you want to read data from and get information from it.

Now, the there are two changes here. And the cool thing is that since SP since a while back when I did a rewrite of SP Blitz Lock, SP Human Events and SP Blitz Lock share nearly the same code. So SP Blitz Lock is over in the first responder kit. And that looks at the deadlock extended event stuff. And thankfully, the deadlock extended event stuff in the block process report extended event stuff is pretty darn close XML wise.

So they share nearly the same code. Just, you know, one looks at deadlocks, the other one looks at blocking. But of course, blocking begets deadlocks. So our blocking begets deadlocking, I think would probably be the better way of saying that. So we’ve got two for the price of one almost anyway, the two changes that are in SP Human Events block viewer and that I also have in a pull request out for SP Blitz Lock are, I think, good changes.

Because of the way that the procedures used to work, where they’re like, you know, so the first change is around SQL handles. So identifying plans that were involved in the blocking. It used to be that both SP Human Events and SP Blitz Lock would show you the SQL handles. But then it was up to you to go use whatever plan cache mining script you care about to go out to the plan cache and look for the execution plans of the queries that were involved in blocking or deadlocking.

What I did was I added a second result window to both of those store procedures that will go out to the plan cache and look for SQL handles that were involved in the blocking and bring back some metrics about them. Now, it doesn’t use query store because I don’t know if query store is on. I didn’t want to add a bunch of complicated checks and, you know, all that other stuff. It just seemed like a lot of work. So I’m not doing that. But I am looking at the plan cache.

So what I did was I added a section of code that goes out to the plan cache and looks for execution plans for any of the queries that were involved in and well for for block viewer for blocking for SP Blitz Lock, it’ll look for anything involved in deadlocks. Now, if you’ve been watching my videos for any period of time, you’ll know that I have slowly fallen out of love with the plan cache over time. And I think the reason for that is at least somewhat obvious here, because not every plan is going to be in the plan cache when you go and look.

If your plan cache is unstable, which a lot of servers that I look at, the plan cache is somewhat unstable. If you go and look, you might not find a very a lot of historical data in there. Often the plan cache is less than 24 hours old.

But I figured it would be better to do this and make a good effort at finding execution plans for people than it would be to have them try to make the next logical step in the in the in the result output and go look for query plans of things that were involved. And for this procedure blocking for SP Blitz Lock deadlocking. So that’s the first change and you can see the separate results here and available plans.

And I think this illustrates pretty well why I am not totally in love with the plan cache. We only have blocking for a couple of things in here and we have a whole bunch more blocking granted some of these blocks were like many months and days ago. So I don’t expect that to be in there, but I just want to show you this to sort of level set, like make sure that expectations are right, that not everything that is in the blocking the block process report is going to have a plan available for it.

But I want to make a good effort to go and find that stuff for you and show it to you. So, you know, for this for this for the available plans that we have here, we can see this most recent blocking thing up here. And this is the query that was involved in the blocking.

And this is the query plan for it. You know, granted, you could probably do something with this knowledge where, you know, you have the predicate on a, you have a seat predicate. And then we have, well, it’s kind of funny how modification queries show up in here with this type of execution plan, because there’s a predicate on the column that we’re updating, which is not technically a predicate, because we’re not actually searching on it.

We’re just looking for where where ID equals something, but because we’re updating the age column, some reason it shows up as a predicate here. So we get the execution plan back. And then I also grab as many execution metrics as I can from the plan cache. So, you know, you’ll get when the plan was created, the last execution time, the count, you know, worker time, elapsed time, stuff like this, you know, so like all the sort of standard stuff that you would expect to get from a query going out and looking at looking for execution plans and query metrics.

So it’s a pretty good amount of stuff, pretty good amount of information in there. That’s the first change. The second change is that I started ranking or I started prioritizing the output by which things had the most blocking associated with them. So before the only thing that I was ordering by was check ID. I added another column to the findings table, which is a sort order, which when I do the inserts into the findings table, I order them by which thing had the most stuff going on.

And then on the way out, like I have a sorting column specifically for that. And on the way out, I sort also by I check check ID and then that column. So when you go look at the results now, you’ll see in the findings column, which should probably expand a little bit so that comes a little bit more obvious what I’m doing here. We come over here, we can see that Stack Overflow has been involved in 37 blocking sessions, which ranks above temp DB, which had two blocking sessions.

And then for more specific checks like which objects, indexes, stuff like that have had more blocking associated with them. You can see 14, 8, 5, 3, 3, 2, 1, 1. And then throughout all the results, you know, like 35, 2, 26, 1, 2, 1, you know, 38. So like all this stuff is ranked, all this stuff is sorted in here by which, you know, things had the most blocking associated with them.

I, like I said, since Blitzlock shares nearly the same code base as this, I also added that change in a pull request out to SP Blitzlock. You should see that in the next first responder kit update whenever that is, who knows? Brent’s busy playing the slots, you might not get around to pushing that pull request through for a while, who knows?

But everything in here is ranked. So you see like even like the timing in here is ranked by which things had the most time associated with it. So you can kind of get a sense of where you should concentrate your efforts by like, you know, which database tables, indexes, stuff like that had the most blocking events and blocking time associated with them.

So hopefully two good changes that will make your life easier when dealing with blocking and deadlocking issues. This is all available in the main branch of my GitHub repo for human events block viewer. Again, for SP Blitzlock, there’s a pull request out for it.

You can go hunt down the that branch over there and look for it if you want to sort of, you know, beta test a little bit that version. But other than that, that’s about it. But anyway, two changes that I’m really happy with.

This kind of, this work was kind of a part of a weird like code frenzy that I went on with like this and SP Quickie store with the time zone stuff. I don’t think, I don’t think anything changed in SP pressure detector along with these two. But, you know, again, stuff that I’m really happy with, stuff that makes my consulting job easier and that I want, I want, you know, to pass along to make your life easier troubleshooting SQL Server.

Because who knows, someday you’ll say, you know what, that Erik Darling, he sure does a lot of good work. We should, we should hire him just to say thank you. We shouldn’t actually like have him do anything.

We’ll just pay him money, say thank you for all the hard work, and then go about your job with, with great aplomb or something. Don’t worry, I’m not shaking you down. If I were shaking you down, I’d show up at your job, or your house, wherever, or I guess that’s both for a lot of people.

Anyway, it’s Sunday, I got stuff to do. I just wanted to put this out there so that you would be more aware of changes to this store procedure. Happy hunting for blocking, happy, have a lot of fun resolving blocking.

Getting, getting your server in tip-top shape. Remember, read committed snapshot isolation level is the key to resolving most blocking issues. So you should, when possible, use that for your SQL Server databases because it takes care of the most idiotic blocking scenarios that exist in databases when read queries and write queries can block and deadlock with each other.

So really, I can’t, I can’t recommend making that change more than I already do. If you’re on SQL Server 2019 and up, accelerated database recovery is a wonderfully complimentary settings change to use alongside read committed snapshot isolation because you take the, you take tempdb out of the equation.

You use the persistent version store per database to read row versioning information from, which is great. It’s really, really smart move on Microsoft’s part. So you can take all the, you know, the, the worries about tempdb contention.

If you have a lot of databases that you would want to use RCSI, you can take that out of the picture and have them use that information instead. Anyway, I’m going to get going now. I’m going to get off my soapbox chit chatting about this stuff, but enjoy the rest of your hopefully long weekend.

Uh, it’s July 4th coming up for my American friends. We’re going to celebrate our independence from our British friends. I guess it wasn’t always that friendly, but, you know, we’ve got, got a special relationship these days.

Anyway, uh, thank you for watching. I hope you learned something. I hope you enjoyed the free scripts.

I hope you use them. I hope they are useful to you. Uh, and, uh, I think that’s about it. All right. We’re at lucky number 13 minutes now. So I’m going to, I’m going to cut this here.

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.

sp_QuickieStore: Time Zones, Time Zones, Time Zones

---

Video Summary

In this video, I delve into the intricacies of searching Query Store data accurately in SQL Server. After receiving feedback from the Darling Data community, particularly a valued member named Rick, I realized that query store data is stored in UTC but isn’t being searched correctly due to time zone issues. This led me to develop SP_QuickieStore, which not only displays all query store dates in your local time but also allows you to search through query store data using the correct UTC time. The process involved a lot of trial and error, especially with understanding how SQL Server handles date and time functions, as well as dealing with the limitations of older SQL Server versions. Despite these challenges, I hope this script makes it easier for everyone to work with Query Store data accurately.

Full Transcript

Erik Darling here with Darling Data. And I’m a bit of a mess. It’s been a week. A lot going on. Busy, busy, busy. Getting a lot of things ready. Getting a lot of work done. And working on my free open source scripts that I provide to you, the SQL community writ large. I’m not sure what that means. I saw it in a book recently and I really liked the way it sounded writ large. But one thing that I’ve been putting a lot of work into recently is SP underscore QuickieStore because it was brought to my attention by a valued member, the Darling Data community, a fellow named Rick, that I was not, that query store data is stored in UTC. But we were not searching in UTC values. And I know that probably sounds like a big gigantic deal, like a big oversight. But the query store GUI also doesn’t do that correctly. I profiled every single query from all of the views that you can run in query store. That’s the high variance, regress, all those things. And I’ve been doing that for a lot of time.

And this is what they all look like. I mean, this isn’t exactly what they all look like, but they all do roundabout the same thing where you see this where clause written completely backwards, where first execution time is not greater than the end time. And the last execution time is not less than the start time. But the parameter values that get passed in there are never converted to UTC for accurate searching. And, you know, I saw that every time I looked at what query store was running. And I thought, well, maybe something magical happens. Maybe this is the right way to do things because there’s not a lot out there about the right way to search query store. It’s not even talked about a whole lot. I think only Aaron Stellato has a couple blog posts about query store dates being stored in UTC. But as I was working on quickie store to do two things. One is display all of the query store dates to you in your local time. And two is to search through query store in UTC time.

What it became really hard to do was figure out a way for you to pass in dates and times without the offset and still search accurately. Now, if you’re just looking for a whole day of data, it might matter a little bit less. All right. If you were just searching for this whole span of data, it might not matter a little bit that the UTC offset is not precise. It might, depending on how far away, like the further away you get from UTC, obviously, the more it would matter. But, you know, if I am finding everything for the full day of June 27th, it’s a little bit less critical that, you know, I have everything to within UTC perfect time.

Now, where things get difficult is when, like I was saying, when you don’t pass in an offset, it becomes harder to search accurately. Now, sysdateTime, the function does not return with an offset, a time zone offset. So if I run this query and I look at the results, I’m going to keep this up here, we have the start date, which is, for me, right now, like accurate.

Right? That’s like the actual time, but SQL Server is inferring, it is implicitly casting my current time with UTC time. I can guarantee you that this time is correct for me here. You can see down in the, actually, you can’t see, it’s a little bit under my armpit, but right about there, it says that it’s 8 p.m.

And we ran that at 16.59, which is just about 8 p.m. So SQL Server is taking 8 p.m., thinking that it’s UTC time. And then when I say, let’s go back to Pacific Standard Time, I mean, we can go back seven hours from now, but that’s not right, because Pacific is four hours from Eastern, it’s seven hours from UTC, so that’s wrong.

And then when we, if we try to cast it back to UTC, it just goes back to my time, Eastern time. So that is a really difficult problem to solve, and I haven’t quite figured out a way to do that. Now, it all changes when you use sysDateTimeOffset, because this returns with the correct date time offset for where I am.

So my SQL Server is in Pacific time, so this is correct here. And when I go and search, and when I go and cast this at the time zone UTC, now the reason my SQL Server is in Pacific time is because I installed Windows on a VM, I didn’t really pay much attention, and then bingo, bango, many years later, I found out it was in Pacific time, so that was fun.

I guess I don’t care about my VMs all that much, but slightly embarrassing. But anyway, so my date time offset is here, right, this is correct from UTC, and when I cast that to UTC, I do get the correct date time where it is officially a little bit past tomorrow.

So it’s 1 a.m. UTC, which is 7 hours from Pacific and 4 hours from Eastern, and you can tell, again, by the little clock under my armpit where it says 8 o’clock and 4 hours, it’ll be, I actually know it’s 5 because of the daylight savings, but, you know.

These date functions aren’t daylight savings aware, either. So the picture gets even foggier as we go through. And I know, this is all very boring and confusing, and it doesn’t give you a lot of faith in SQL Server or Microsoft or the summer interns who write these date functions.

Now, SQL Server 2022 does introduce a couple things that would make life easier, but, of course, ain’t no one in the world using SQL Server 2022 in a widespread enough way for me to only write scripts that work with SQL Server 2022.

So here I am writing scripts that have to work for folks using Query Store going back to SQL Server 2016, which, you know, I’d rather be able to help them than not, but, you know, even if you were on 2019, this wouldn’t help.

These functions are so new that the SSMS parser does not recognize them. We get these wonderful little red squiggly lines here where SQL Server, or rather SSMS says, I don’t know who you are.

But if we had these available, then we could maybe do something to figure out which time zone people are in easily and use that to correct dates passed in without the time part. But even that would get really challenging for a number of reasons that, trust me, I’d bang my head off and I couldn’t quite figure out.

Now, older versions of SQL Server, you can read the registry with XP Reg Read, and you can also get Pacific Standard Time back. However, the last thing I want to have to deal with is explaining to someone why I need to read the registry in order for them to query Query Store data.

So this is out. There’s some XP Reg Read stuff that I wrote into the Blitz scripts that I often have to not use when working with clients because they’re like, get out of my registry.

I’m like, I’m just reading it. And they’re like, no. So I found myself in a tough situation. But in order to make it as easy on you as possible, you can at least use the DateTimeOffset function here to figure out what your DateTimeOffset is.

So when you want to search accurately through Query Store data, which SP underscore Quickie Store does, because in my where clauses, I cast my start date at UTC time so that my searches are accurate. So anytime that there’s a start date parameter in the Dynamic SQL, it’s cast a time zone UTC.

That’s up here. That’s up here, too, where you can see UTC all over the place. And, well, there’s this one up here, but that just goes back.

This one just uses sys UTC date time to go back and search there. So, you know, that’s about that. Maybe I should use sys date time offset for that.

I haven’t quite decided yet. We’ll figure it out. But I think sticking with UTC is the smart thing to do there. Anyway, when you want to search through Query Store to look for stuff, this is what you have to do.

Put in the date, you put in the time, and you put in the offset, which you can find out, again, by running this and looking at what the sys date time offset is. So if I search through Query Store using this, I get nothing on purpose. And I want you to see that I get nothing because I am searching accurately.

And when I expand my search by two minutes, I get back a whole bunch of stuff. And since I’m showing the, using the time zone parameter here, we’ll display times in the current time zone. You can see that information if you go over here.

We have the first execution time in your time zone. All right. That’s helpful. All right.

We can see that that is indeed the correct Pacific time offset for what I’m looking for. And we see the, well, I search on the last execution time. So that’s going to be this column.

But you can see all of that that happened at 5.04 falls into the window of 5 to 5.05 that I was looking for. I also show you the UTC times just in case you ever would need them. I, that was first suggested to me by the lovely and talented Sean Ghilardi.

And I said, hey, man, that’s weird. And then as I was troubleshooting this and I, and I was trying to really figure out the UTC thing, I was like, oh, yeah, I’m just going to show. So, so, so thank you.

Thank you, Sean Ghilardi for, for pointing that out that I should do that because it ended up being a very valuable troubleshooting step for me. And I decided to keep it in there just in case it might ever be a valuable troubleshooting step for you too. Cool.

So, the time zone parameter in SP Quickie Store does validate the time zone if, when you use it, if you don’t, if you leave it blank, it will default to UTC. It seems reasonable to me since everything is stored in UTC. But if you try to use a time zone that does not exist in the DMV sys.timezoneinfo, then I will yell at you and say, please check sys.timezoneinfo for a valid list.

Cool. So, if you ever need to learn about how to use any of the stored procedures that I write, you can use the help parameter.

The help parameter will describe all of the parameters that you can pass in and their various uses. I’ve shown this in other videos before and for some reason this mouse will not grab onto this thing where I want it to. So, we’re just going to have to deal with a little bit of a scroll bar because I don’t feel like making you sit here and watching me wrestle with trying to grab onto this thing and shrink it.

It is just not doing it. Oh, there it is. Nope, not happening.

So, trying to make it as easy on you to troubleshoot this stuff as possible. And also this helpful note to use the sys.timezoneinfo DMV if you need to figure out what to pass in for a time zone above. I would I could do more to automate the timezone stuff but oops not Tim zone sister time zone info if you if you look at what’s in here there like I was like I told with the idea of being like okay well I could look up the sys date time offset of someone’s local time and I could go with that right but there are there are a lot of date time offsets that have the same value minus seven for a bunch of stuff minus an eight and nine for a bunch of stuff and then like you think about crossing day boundaries think about you know people who are on half hours and stuff and it just became incredibly difficult and really just all too much to bear but anyway spquickiestore now does what the query store DMVs do not do when it lets you search the correct UTC time for queries in query store it also display it also allows you to display times to you in the time zone of your choosing as long as that time zone exists in the sys.timezoneinfo DMV and uh well don’t we all feel like more complete people now at least I do anyway um it is nearly 8 15 p.m.

here scenic New York uh I’m gonna go watch baseball uh I wanted to get this video out because I would have just kept me up last night and uh you know I don’t like when things keep me up and when things keep me up I assume they’ve kept you up at some point maybe not to the same degree or maybe I just care too much it’s something I’ve frequently been accused of uh anyway uh thank you for watching hope you learned something hope you enjoyed yourselves if you decide that this video is worthy of your respect then give me the old thumbs up if you decide that you want to learn more about how arduous sql server is uh you can subscribe to my channel and uh of course you can get all of my open source scripts over on github that’s about it 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.

Extended Eventually

---

SQL Server has had the fn_xe_file_target_read_file function for a while, but starting with SQL Server 2017, a column called timestamp_utc was added to the output.

Somewhat generally, it would be easier to filter event data out using this column… if it worked correctly. The alternative is to interrogate the underlying extended event XML timestamp data.

That’s… not fun.

But if you write your query like this:

SELECT
    xml.wait_info
FROM
(
SELECT
    wait_info =
        TRY_CAST(fx.event_data AS xml)
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx
WHERE fx.object_name = N'wait_info'
AND   fx.timestamp_utc >= DATEADD(DAY, -1, SYSUTCDATETIME())
) AS xml
CROSS APPLY xml.wait_info.nodes('/event') AS e(x);

It will return no rows. Not ever. But at least the predicate is pushed down to a reasonable place: when you touch the file.

But if you write your query like this, it’ll work, at a cost:

SELECT
    xml.wait_info
FROM
(
SELECT
    wait_info =
        TRY_CAST(fx.event_data AS xml)
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx
WHERE fx.object_name = N'wait_info'
AND   CONVERT(datetime2(7), fx.timestamp_utc) >= DATEADD(DAY, -1, SYSUTCDATETIME())
) AS xml
CROSS APPLY xml.wait_info.nodes('/event') AS e(x);

The cost is an additional filter operator in the plan, which causes the entire file to be read and then filtered.

For large on-disk XML storage files, that can be really painful to deal with. It’s especially ugly because there’s no parallel read for these files.

There’s an open issue about this here that you should go and upvote.

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.

What SQL Server Parameter Sniffing Looks Like In sp_WhoIsActive

---

Video Summary

In this video, I delve into the often-overlooked `sp_whoisactive` stored procedure to explore parameter sniffing issues in real-time on a SQL Server. Using SQL Query Stress, I run through a demo that highlights how parameter sniffing can manifest by comparing query performance and execution plans under different conditions. By leveraging `sp_whoisactive`, we focus on the average milliseconds column to identify potential parameter sensitivity problems, where queries might perform well most of the time but occasionally take much longer. This video is perfect for anyone looking to catch these elusive issues early before they become major bottlenecks in their SQL Server environment.

Full Transcript

Erik Darling here with the unstoppable Darling data. And we’re going to use an often overlooked parameter for SP who is active to look at what parameter sniffing can look like while it’s happening to your SQL Server. And we’re going to actually recycle a demo from last time, just like I promised. And I’m going to be using SQL query stress to do a thing. The thing that SQL query stress is going to run is, well, it’s going to look like the last demo that I did on what parameter sniffing looks like in query store. If you are watching this video, that would be the one that I’m referencing where we use query store to identify queries that might be parameter sensitive. Wew call so WA are using. Just back Andcr ow. move on. So what of Las огрwh here and code.

here of mystic call. we are going to use the small query plan for whenever the second part of the sys date time call modulus by five equals zero then we’re going to go and use the big one so what we’re going to see is most queries finishing pretty quickly than every five seconds a bunch of queries taking like four five plus seconds there might be some more if we can introduce decent temp db contention in there we’ll see what happens we’ll see we’ll see what we catch we don’t know yet i do all these demos without practicing so you can imagine how good i am when i actually do practice but anyway oh wait that’s the that’s the old demo we don’t need that anymore you can see now where i embarrassingly copied and pasted my code so if we run sp who is active right now nothing is running nothing is running there are no rabbits up my sleeve and now if i run sql query stress and we look at what happens here we’re going to see a column that a lot of folks often miss and that’s this column here with the avg at the end this is how long the query runs for on average so every once in a while when we see the average milliseconds still low but the actual seconds that something is taking be pretty high that’s a pretty good sign that we are hitting a parameter sensitivity problem right this has got seven seconds there on average it’s 100 163 milliseconds and so that’s kind of what it looks like when things start getting weird we can go look at the execution plan the execution plan is going to look like again classic parameter sniffing and what we see here is that uh this query was expected to let’s zoom in on this sql server was expecting 4756 rows which with the uh other with the other parameter is not is just kind of about right but when we use the further back parameter is just about wrong and so that’s why we have this high number of rows here now sometimes if we get lucky and we look in the uh plan we look at the uh plan details we’re using actual parameters and not local variables because local variables mess this up but if we look at the parameter values here we can see exactly what it was compiled with which is useful because how do we know how can we test parameter sensitivity unless we have a starting point and the good starting point here would be to you know test our query in a parameterized sort of way with um should be all done now test the query in a parameterized sort of way like this again this is the good parameterized parameterized dynamic sql now granted it being sensitive to parameter sniffing is not good but you know there are various ways of fixing that but what we would want to do is kind of going back to the the demo from the previous run previous video and we don’t have to go 50 times to do this uh we would want to test let’s just make sure everything is every all things are equal here dbcc free proc cache we’re going to get rid of everything and we’re going to run this with the parameter compile value that we found in the plan cache all right let’s let’s turn on query plans for this one it won’t won’t hurt us since we’re not running it 50 million times we run that and get the execution plan and again sql server’s guess here was pretty gosh darn good for a range predicate right we got back we we expected 4756 rows we got back oh just about 300 200 and some odd more maybe actually that is pretty close to 300 isn’t it i guess it is you know about 300 rows extra 300 more rows than estimated uh cardinality estimated not just not just a guesstimate there and then if we are intrepid query tuners we could go and look at what happens if we use that same execution plan but with a value that gets more data in this case just like last time we’re going to go back to 2011 12 30 from 2013 12 30 and if we run this we’re going to see a query plan that looks fairly close to what we saw when we ran who was active where we got let’s see uh 1808340 we got 1.8 million rows back rather than the 5 000 or so rows back that we got on the other query and of course ending up in a loop and all that stuff when you have way way more rows than would make sense to loop over can be rather caustic on the cpu so we have the serial execution plan not not for serial just uh you know serial because sql server came up with the execution plan based on a parameter that was expected to return a low number of rows and it did but now that plan doesn’t make a whole heck of a lot of sense when we have to return way more rows and that isn’t that something isn’t that ain’t that parameter sniffing so anyway uh that is what parameter sniffing will look like when you uh use sp who is active to look at what is currently running on your server again the two parameters that i typically use to figure this stuff out one is to get average time because i need to know if what i’m seeing is what normally happens when the query runs right so like you know you see get average time the average time is normally really low but now all of a sudden you’re seeing the average time like the average run time like the the actual run time way higher than the average time well that’s a sign that something has something has run amok with your query now does it have to be parameter sniffing not necessarily your server could be under an unduly high load the query could be blocked it could be uh some other sort of resource contention going on all right it could be all sorts of things that aren’t parameter sniffing but if you if you use this and you are and you think that it might be parameter sniffing you are curious about a potential parameter sniffing problem on your server well that’s when you know using uh sp underscore quickie store to look at query store data again that expert mode parameter we talked about the video before this one was a good way to figure out what was going on there especially sql server 2017 and up will collect high level query weight stats and it could be really useful to look at those to see if maybe there were some weights that you wouldn’t expect this query to hit like maybe a bunch of lock weights or something so that’s typically what i do when someone says hey eric it’s an emergency we need to pay you to fix our sql server because that’s what you do for a living and i say okay what’s wrong and they say i think it’s parameter sniffing this is one of the very first things i do so i come in make sure that the remote dac is enabled all right the dedicated administrator connection very important for these things run sp who is active get average time see if there’s any big discrepancies between the current run time and the average run time columns and then we go from there all right we have to choose our own adventure we have to figure look at weight stats you have to look at you know overall server load remember all good old sp pressure detector it’s a good one for that dig into all sorts of fun things anyway it’s hot in here and my hair and makeup crew wasn’t available today so i’m a little little shiny i hope that doesn’t offend you too much i hope that you uh you can deal looking at my shiny countenance and uh i don’t know i’m gonna go towel myself off and then i’m gonna record another video about about sp who is active and how you can tell the difference between queries that are doing work and queries that are stuck all right cool thank you for watching hope you enjoyed yourselves i hope you learned something i hope that you will uh find it in your lungs to like this video and subscribe to my channel so i can hit the 3000 subscriber mark sometime before the 50th president of the united states states is elected that’d be that’d be cool i’m still doing sql server by then who knows maybe this will still be useful to some maybe these fossils will still be useful anyway uh thank you for watching you’re cool and good looking young if i wasn’t married i would

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.