SQL Server Community Tools: How I Use sp_BlitzCache

Posted on by Erik Darling

Planning To Fail

Back when the plan cache was the only thing you could look at to find slow queries, sp_BlitzCache was a goldmine.

I spent hundreds of hours writing checks for new and different things, adding features, and beating my head against XML queries.

Aside from some of the Blitz procedures that I wrote originally from scratch (BlitzWho, BlitzLock, BlitzQueryStore) I probably spent the most time with sp_BlitzCache.

I don’t say that to take credit away from anyone else who contributed to the procedures — I’m thankful to anyone willing to work on my terrible code — but birthing those procedures was a hell of a process.

And a nerve-racking one. I know how many people use and depend on these procedures.

Getting things right the first time is no easy task.

These days, I spend way more time looking at Query Store data, because half the time I go looking at the plan cache it’s hardly more than a few hours old.

Big Train

The main patterns I dig into when I look at the plan cache are:

  • average CPU
  • unused memory grant
  • frequent executions
EXEC sp_BlitzCache
    @SortOrder = 'avg cpu';

EXEC sp_BlitzCache
    @SortOrder = 'unused memory grant';

EXEC sp_BlitzCache
    @SortOrder = 'xpm';

Assuming that you find things in here that are pertinent to the workload as a whole, and that you can tune in a meaningful way, you’ll get a wealth of great details about what lurks in the query plan XML.

Seriously. You almost don’t even have to look at the plan sometimes because of what gets called out in the warnings for sp_BlitzCache.

There’s an amazing amount of stuff that it will yell at you about.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts