Category: sp_QuickieStore

Updates to sp_QuickieStore

Posted on by Erik Darling

Just Visiting

I made some updates to sp_QuickieStore recently, which you can download here.

The main updates to the code are to:

  • Add support for SQL Server 2022 views
  • Add the ability to search by query hash, plan hash, and SQL handle

The SQL Server 2022 stuff isn’t important just yet, but… Hey, maybe someday.

The new search functionality is really important though, at least for how I use sp_QuickieStore much of the time. Often, you’ll find hashes and handles in other parts of the database:

  • Plan cache
  • Deadlock XML
  • Blocked process report
  • Query plans

There’s still no good way for you to search Query Store by anything. Not plan or query id, not query text, not object names. Nothing.

Don’t worry, I’m here for you.

Some other minor updates were to:

  • Improve the help section
  • Improve code comments throughout
  • Remove the filter to only show successful executions (sometimes you need to find queries that timed out or something)
  • If you filter on any hash or handle, I’ll display that in the final output so they’re easy to identify
  • Replace TRY_CONVERT with TRY_CAST, which throws errors in fewer circumstances

That’s about all the stuff you need to know about. Aside from that, all my changes were slight logical errors or plumbing to implement the new features.

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.

Updates To sp_HumanEvents, sp_QuickieStore, And More!

Posted on by Erik Darling

For Free!

Here’s some stuff I’ve been working on lately:

sp_HumanEvents:

  • Added the recompile_cause column to the output of the recompile event
  • Added the wait_resource column to the blocking event output
  • Fixed a bug around table creation
  • MsSQLServerDBA fixed an issue where GO statements were preventing deploying with DBA Tools
  • Remove a recompile hint that was leading to high tempdb allocations (more on that tomorrow)

sp_QuickieStore:

  • Fixed a bug where date filtering was messing up query results

Enjoy, and as always, please let me know if there’s anything I can do to improve the scripts usability, or any issues you encounter while using them.

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.

Some Updates to sp_QuickieStore and sp_PressureDetector

Posted on by Erik Darling

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

Posted on by Erik Darling

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

Posted on by Erik Darling

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

Posted on by Erik Darling

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

Posted on by Erik Darling

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

Posted on by Erik Darling

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

Posted on by Erik Darling

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

Posted on by Erik Darling

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