Category: Plan Cache

Why Microsoft Should Get Rid Of SQL Server’s Plan Cache And Use Query Store Instead

Posted on by Erik Darling

Used To Love Her

I used to think the plan cache was so cool.

  • You can find queries that aren’t good there
  • Plans are full of details (and XML)
  • Supporting DMVs give you extra insights about resource usage

But most of the time now, I’m totally frustrated with it.

It clears out a lot, plans aren’t there for some queries, and the plans that are there can be very misleading.

Can you really tell someone what their worst performing queries are when everything in there is from the last 15 minutes?

No.

Why I Like Query Store Better

Plans end up there for more definite amounts of time, even if you stick a recompile hint on them.

Plus, you get a whole lot more information about what went on with all the different query metrics. As far as a source of “what happened and when”, it’s much more reliable. It’s almost like having a monitoring tool in SQL Server.

Do you trust your plan cache to tell you what was slow more than a couple days ago?

One really big bonus is that it’s portable, and it’s per-database. You can backup or clone a database so someone else can easily look at it somewhere else.

On top of that, it’s more usable: there are built in reports, a graphical interface, settings you can tweak, and plans you can force with a couple clicks.

You could solve a lot of frustrations by moving things here.

Microsoft Has Work To Do, Though

There have been a number of issues, and unanswered user requests for Query Store over the years, and not all of the associated DMVs have full parity.

Querying the data also needs to be made less prone to performance issues. And, living in the future, it’s a much more sensible location for the last known actual plan.

A nice to have would be predicate normalization for identical and morally equivalent queries, with totals and averages for resource usage, so you could group things together. Especially for morally equivalent queries, this would allow plan forcing where the queries get different ids.

Some monitoring tools have this in their interface, and the number of times I’ve been able to track down outliers within a group of executions to solve a problem is, well, at least memorable to me.

I wish more people had monitoring tools.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

What’s The Point Of Estimated Plans In SQL Server, Anyway?

Posted on by Erik Darling

Further From Truth

Estimated plans can be a bit confounding, huh?

Whether you see one by hitting CTRL+L, looking in the plan cache, or looking in Query Store, the song remains the same.

The query is slow: sometimes? always? usually? rarely?

But where? But why?

Estimated plans can hide a lot of work from you. They’re sort of like government contracts, where the optimizer is the government: it picks the cheapest plan, but then…

(Okay, maybe it picks a plan because a friend or relative or big donor was behind it. Sometimes it seems that way, with the nonsense you end up with.)

Of course once execution is under way, you run into all sorts of unexpected costs: Everything is much more expensive, and takes a whole lot longer than projected.

Glad Ways

From a practical perspective, estimated plans can be useful to get a general idea of the plan shape that the optimizer is coming up with.

Despite all the lying estimated plans can do, you may be able to spot some obvious anti-patterns.

Of more utility is when the query is particularly long running, or when the query will modify underlying data.

When I’m tuning modification queries, there’s usually some pain involved when you need to reset things.

For example:

  • Most people don’t want to wait 5-10 minutes just to see if a plan changed after making changes
  • Deleting data that doesn’t exist
  • Updates that don’t change anything
  • Not being able to insert duplicate data a second time

…Are all hindrances to getting things done.

And so you need to restore or roll things back another way (and boy howdy has SQL Server 2019 spoiled me for rollbacks).

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

How Trivial Plans Can Make Identifying Queries Confusing In SQL Server

Posted on by Erik Darling

End Of The Line

There are a lot of words you can use to describe Extended Events. I’ll skip over my list to keep the blog family friendly.

In this post, we’re going to look at how two event can show the same query having a different plan handle.

I stumbled on this while working on sp_HumanEvents, when one of my precious sessions suddenly STOPPED RETURNING RESULTS.

Very helpful.

Let’s look at why.

A Plan Here, A Plan There

When the optimizer gets its hands on a query, it has some early choices, where it might choose to keep a plan at the trivial optimization level, and it may choose simple parameterization in order to make things more re-usable.

Apparently this causes some differences when it comes to hashing plans.

Here’s an example query:

SELECT COUNT(*) FROM dbo.Votes WHERE BountyAmount = 500 AND UserId = 22565;

Looking at the execution plan for it, we know a couple things.

SQL Server Query Plan
Goodbye to you.

It didn’t stay trivial because it went parallel, and the optimizer thought that simple parameterization was a good idea. Even though we passed in literals, they’re replaced by parameters in the execution plan text.

What’s the big deal?

Well, nothing, unless you want to correlate information about a single query from multiple Extended Events.

Check It Out

Let’s run sp_HumanEvents and get debug information — because the regular results won’t return anything for this query right now (I have fixed this issue, don’t worry).

EXEC sp_HumanEvents @event_type = N'query', @debug = 1, @seconds_sample = 5;

In another window, we’ll run our query.

SELECT COUNT(*) FROM dbo.Votes WHERE BountyAmount = 500 AND UserId = 22565;

What do we get back? The debug parameter will spit out data in temp tables, and print out dynamic SQL commands for troubleshooting.

SQL Server Query Results
Two Events, One Query

Eagle-eyed readers will notice that the plan handles in each event for the same query are different, but query hash and plan hash are identical.

Because of this, I’ve had to change the way I get plan handles, so I only take the one that comes from post execution showplan, because…

There’s Another Way

Which plan_handle ends up in your plan cache? Not the one from sql_statement_completed.

We can see that by running sp_HumanEvents to capture wait stats, which will also try to correlate waits to the queries that caused them.

EXEC sp_HumanEvents @event_type = N'waits', @debug = 1, @seconds_sample = 5;

I’m grabbing the debug here too so we can see what ends up in the temp tables along the way.

SQL Server Query Results
COOL YEAH

We can grab information about which queries caused waits, and the plan handle here is the same one that we saw in the post execution showplan event.

Yay?

In the Actual Results Plus from sp_HumanEvents, we can see the simple parameterized version of the query ended up in the plan cache.

SQL Server Trivial Plans
Goodie Bagg

There are two lines here because we had waits on two different wait stats, but the point is more that we can see simple parameterization at work here, but not in the events that collected information about executed queries.

Frustration Point

The problem would have been more obvious if the parameterized text showed up in the post execution showplan event, and not the version with literal values. It would have been easier to figure out why the plan handles were different, anyway.

So what’s the solution? Now, sp_HumanEvents will only get the plan handle from the post execution showplan event, in case you want to go and do anything with it later.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

How Functions Can Make Performance Tuning SQL Server Queries Harder

Posted on by Erik Darling

Sensational

I’ve posted quite a bit about how cached plans can be misleading.

I’m gonna switch that up and talk about how an actual plan can be misleading, too.

In plans that include calling a muti-statement table valued function, no operator logs the time spent in the function.

Here’s an example:

SELECT TOP (100)
     p.Id AS [Post Link],
     vs.up,
     vs.down
FROM dbo.VoteStats() AS vs --The function
JOIN dbo.Posts AS p
    ON vs.postid = p.Id
WHERE vs.down > vs.up_multiplier
AND   p.CommunityOwnedDate IS NULL
AND   p.ClosedDate IS NULL
ORDER BY vs.up DESC

When I run the query, it drags on for 30-ish seconds, but the plan says that it only ran for about 2.7 seconds.

SQL Server Query Plan
As we proceed

But there it is in Query Time Stats! 29 seconds. What gives?

SQL Server Query Times From Execution Plan
Hi there!

Estimations

If we look at the estimated plan for the function, we can see quite a thick arrow pointing to the table variable we populate for our results.

SQL Server Query Plan
Meatballs

That process is all part of the query, but it doesn’t show up in any of the operators. It really should.

More specifically, I think it should show up right here.

SQL Server Query Plan

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Signs Your SQL Server Execution Plan Is Lying To You: Spills and Memory Grants

Posted on by Erik Darling

Milky

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.