SQL Server Community Tools: How To Dig Deeper With Expert Mode With sp_QuickieStore

Posted on by Erik Darling

Perfect Crime

As much as I’d love to think that the normal set of results in sp_QuickieStore is sufficient, sometimes you need a little bit more to figure out what’s going on.

That’s where Expert Mode comes in. Or, as I lovingly call it, @expert_mode.

Quality engineering, there.

Most normal people don’t like a flood of information all at once. That’s why I tend to write shorter blog posts, and I write short sentences in small paragraphs.

In case you were wondering.

More Better

To summon @expert_mode all you have to do is ask nicely.

EXEC sp_QuickieStore
    @expert_mode = 1;

What you get back is stuff that wouldn’t be useful when you’re just trying to find some queries to tune, but might be really useful when you’re trying to dig deeper into why a specific query was slow.

  • Compilation Statistics: Here you get stuff like how many times, how long, how much memory, and other details around plan compilation.
  • Resource Statistics: This data comes from the plan cache and is largely for additional memory grant details that aren’t available in Query Store, like the actual grant, and not just what was used.
  • Query Store Wait Stats By Query: Up top, you get the three most prolific waits that a query was hit with; down here you get all of them ordered from highest to lowest
  • Query Store Wait Stats Total: At the database level, all of the wait stats that queries have generated
  • Query Store Options: How you set up Query Store, because sometimes you might wanna tweak those

Like I said, you won’t always need that stuff, but it can be useful at times in some scenarios.

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