Some Updates to sp_QuickieStore and sp_PressureDetector

In Trunks


I’ve made a few changes and improvements to these scripts. Here’s the changelog:

sp_PressureDetector:

  • Improved introduction
  • Added help section
  • Changed column order to put DOP information next to thread usage information
  • Added reserved threads to the CPU overview (2016+)

sp_QuickieStore:

  • Added a filter to the “maintenance” section to screen out automatic statistics updates (SELECT StatMan…)

A new function!

You can grab updated versions and get support over on GitHub.

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 on 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.

sp_QuickieStore: Searching For Wait Stats

Money Money


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 on 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.

Updates to sp_QuickieStore, sp_PressureDetector, and sp_HumanEvents

A Thing Of Things


sp_QuickieStore:

  • Added the ability to search query store wait stats for queries that generated a large amount of a specific wait category.
  • Noticed a couple TOP queries were missing DESC for the ordering
  • Increased length of all_plan_ids column to max after someone said they hit a truncation error with a length of 1000
  • Updated documentation to reflect more specific version support: 2016 SP2+, SQL Server 2017 CU3+, 2019+, and Probably Azure

sp_PressureDetector:

  • Added a parameter to skip getting execution plans when server is really hammered
  • Added database name to memory query
  • Added a missing isolation level set
  • Increased decimal length for servers with larger amounts of memory

sp_HumanEvents:

  • Updated proc to format queries to suit current formatting memes I enjoy
  • Organized the proc a little bit better, putting all the declared variables and temp table creation at the beginning
  • Fixed some inevitable bugs that come with formatting and reorganizing really long stored procedures
  • Cleaned up error handling
  • Added a check for the signed query hash action; I found the proc failed on some earlier builds of 2014 and 2012

So uh, go get those. Use’em. Have fun.

Do a SQL.

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 on 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.

sp_QuickieStore Improved Performance Troubleshooting

Things Are Getting Better



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 on 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.

sp_QuickieStore Debugging

Centipede



Thanks for watching!

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 on 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.

sp_QuickieStore Passing Lists

Checking It Twice



Thanks for watching!

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 on 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.

sp_QuickieStore Performance Trouble Shooting

Four Hours



Thanks for watching!

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 on 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.

sp_QuickieStore Error Handling

Red Text



Thanks for watching!

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 on 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.

sp_QuickieStore: Safe Dynamic SQL

Headboard



Thanks for watching!

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 on 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.

Introducing sp_QuickieStore: What To Do If You Hit A Problem

No One’s Perfect


I’ve been working on scripts like this for long enough to know that I don’t know everything that might happen out there in your kooky-krazy environments.

The beauty of open source is that it’s really easy to let me know, or jump in and get to fixing things on your own.

To help you figure out where things are getting messy, I’ve added a few parameters to make troubleshooting easier.

Help


The first place to look for information or details is the help parameter.

EXEC dbo.sp_QuickieStore
    @help = 1;

You’ll get information about parameters, results, shortcomings, and licensing.

Of course, if you’re hitting an error whether it’s red text or logical, you probably need a bit more than this.

Debug


If you want to see the queries that the dynamic SQL builds and executes, you can use the debug mode.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @debug = 1;

What debug mode returns:

  • Dynamic SQL statements, and statement length in the Messages tab
  • Supplied parameter values to the procedure
  • Declared parameter values
  • All temp table contents with table name identifiers
  • Replacement selects for empty tables with table name identifiers

This should help you figure out what’s going wrong and when. If you find something, open an issue on GitHub to tell me about it.

Performance


If you find this proc is regularly running slowly for you, you can use run this to return query plans and see where the trouble’s at.

EXEC dbo.sp_QuickieStore
    @database_name = 'StackOverflow2013',
    @troubleshoot_performance = 1;

This will SET STATISTICS XML ON; for queries that touch Query Store views, where we typically will have performance issues, along with a table of queries and runtimes.

slow moving target

Once data is in temp tables, things are fast because they’re small. Before that, you’re at the whim and fancy of the elements.

If you come across something that you think I can fix (and isn’t just poor Query Store schema design/indexing), open an issue on GitHub to tell me about it.

Wrapper’s Delight


This week we covered all the major aspects and features of sp_QuickieStore. I’ll do some video code reviews to close out the series for now.

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 on 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.