SQL Server Community Tools: How To Filter What sp_QuickieStore Shows You

Posted on by Erik Darling

Wide Open

I try not to make too many assumptions about what you might want to see. The only real restrictions out of the box with sp_QuickieStore are:

  • It only looks at one database at a time
  • It only shows you the top 10 sorted by average cpu
  • It only shows you the pas 24 hours of data

These were design decisions made in order to help sp_QuickieStore live up to its name.

But of course, you can tinker with these things with the following parameters:

  •     @database_name: the name of the database you want to look at query store in
  •     @sort_order: the runtime metric you want to prioritize results by: cpu, logical reads, physical reads, writes, duration, memory, tempdb, executions
  •     @top: the number of queries you want to pull back
  •     @start_date: the begin date of your search
  •     @end_date: the end date of your search    
  •     @execution_count: the minimum number of executions a query must have                 
  •     @duration_ms: the minimum duration a query must have                    
  •     @wait_filter: wait category to search for;  cpu, lock, latch, buffer latch, buffer io, log io, network io, parallelism, memory

These can be useful things to tweak based on your situation.

Details, Details

You can do some really cool stuff with these to narrow search results to things you care about. I’m gonna highlight those here, even if they may seem obvious.

  •     @database_name: some databases are more important than others
  •     @sort_order: if your server has a particular bottleneck, it can be useful to find queries using the most of that bottleneck
  •     @top: sometimes there’s red meat beyond the top 10, like when you’re looking at high execution counts
  •     @start_date: know when you had a problem? start here.
  •     @end_date: know when the problem stopped? stop here.
  •     @execution_count: you may not want to see queries with low execution counts, because they might just run once at night
  •     @duration_ms: low duration queries may not be tunable, and you may not want to see them
  •     @wait_filter: does a particular wait stat stick out on your server? Find the queries responsible for it!

I tried to give you plenty of options to focus in on high-level things that can help lead you to queries that are causing you problems.

You can also zoom in to specific queries using a few different searchables, and we’ll talk about that tomorrow.

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