Search Results for “sp_blitz”

Index tuning and query hinting give us one plan that works well enough for some situations, but what if we really need two different plans? For example, we’ve been dodging around the problem of two different input parameters that really require two different approaches: maybe it’s small data versus big data, maybe it’s two different…

So far, we’ve added indexes, broken up queries into sections to encourage SQL Server to use those indexes, and even added query-level hints, all in an effort to get one execution plan that works well enough for most scenarios. But what if you can’t? Recompile hints are so compelling because they get a brand new…

On day 1, we implemented monitoring to start collecting plans having parameter sniffing issues. To keep things simple, we kept the compatibility level at 2017. Now let’s start exploring the features that SQL Server 2019 brings to the table – in spirit, they’re supposed to help SQL Server adapt to varying amounts of data moving…

The method of logging sp_BlitzFirst/Cache/Who to table will probably give you everything you need to troubleshoot the majority of parameter sniffing situations. However, sometimes you need to gather even more data, and you’re willing to pay a performance price to get it. Query Store is a built-in feature that can track dramatically more of the…

In order to detect, troubleshoot, and prevent parameter sniffing issues, we’re going to need: Metrics about how queries are performing Different versions of the query plans that caused those metrics To collect them, we’re going to go from the lowest-overhead, least-data solutions first, and then gradually progress to the collection methods that have the highest…

There are 3 specialized causes for parameter sniffing: unparameterized queries flooding the plan cache, memory pressure due to query workspace grants, and plans aging out of the cache. Demo Script Transact-SQL /* Fundamentals of Parameter Sniffing What Triggers Parameter Sniffing Emergencies, Part 2 v1.2 – 2020-08-04 https://www.brentozar.com/go/snifffund This demo requires: * SQL Server 2016 or…

In another module in this class, we explain normal ranges of CXPACKET, CXCONSUMER, and LATCH_EX that you’ll see during a typical workload. We how to mitigate “normal” parallelism waits with changes to Cost Threshold for Parallelism and MAXDOP. However, I say that just by changing those two settings alone, you may still run into abnormal…

The phones are ringing, the users are screaming, and your manager is dropping four-letter words. The server’s performance has tanked, and you’re the one on call. Start up a workload, find the query who suddenly got a terrible plan, and do the needful. Part 1: Setting Up the Lab Part 2: Your Mission Part 3:…

Remember the things we discussed in the last module? Restarting the service, rebuilding indexes, updating stats, and freeing the entire plan cache? Yeah, don’t do that. You solve the parameter sniffing emergency for one query, but you cause a parameter sniffing emergency for the rest of ’em – because now all the other queries are suddenly getting…

Some things get mistaken for parameter sniffing issues when they’re unrelated. For example, when you run the same query in two different environments and get two different plans, it might not have anything to do with the parameters at all: the data distribution might be different in the two environments, or they might have different…

The biggest problem with parameter sniffing isn’t that SQL Server can produce different plans. The real problem is that the plans change unexpectedly, seemingly out of nowhere, with. no advanced warning. However, armed with an understanding of what triggers plans to be evicted from the cache, you can start to predict when these emergencies will…

We’ll start by defining what parameter sniffing is: a query that builds a dramatically different plan depending on what parameters are first used to compile it. We’ll begin with a query that uses literal values, show two different parameters that produce different plans, and show what happens when it’s put into a stored procedure. Demo…