Updates To sp_QuickieStore, sp_HumanEventsBlockViewer, and sp_PressureDetector

Chasing Perfection

---

We here at Darling Data strive to get things right the first time, but sometimes late nights and tired eyes conspire against us.

The nice thing about using these on a wide variety of SQL Servers in various states of disrepair is that bugs get spotted and sorted pretty quickly.

You can download all of the main SQL Server troubleshooting procedures I use in one convenient file.

Here’s a breakdown of changes you can find in the most recent releases!

sp_QuickieStore

---

Here’s what got fixed and added in this round of changes:

• Fixed a big where execution count and other metrics were being underreported
• Fixed a bug when checking for AG databases would throw an error in Azure SQLDB and Managed Instance (Thanks to AbhinavTiwariDBA for reporting)
• Added the IGNORE_DUP_KEY option to a couple temp table primary keys that could potentially see duplicate values when certain parameter combinations are used (Thanks to ReeceGoding for reporting)
• Added support for displaying plan XML when plans have > 128 nested nodes of XML in them (you can’t open them directly, but you can save and reopen them as graphical plans)
• Added underscores to the “quotable” search characters, so they can be properly escaped

So now we don’t have to worry about any of that stuff. How nice. How nice for us.

sp_PressureDetector

---

Here’s what got fixed and added in this round of changes:

• Fixed an issue in the disk metrics diffing where some data types weren’t explicit
• Fixed a bunch of math issues in the disk diff, too (it turns out I was missing a useful column, doh!)
• Fixed a bug in the “low memory” XML where I had left a test value in the final query
• Added information about memory grant caps from Resource Governor (with a small hack for Standard Edition)

Turns out I’m not great at math, and sometimes I need to think a wee bit harder. Not at 4am, though.

sp_HumanEventsBlockViewer

---

Here’s what got fixed and added in this round of changes:

• Added a check for COMPILE locks to the analysis output
• Added a check for APPLICATION locks to the analysis output
• Improved the help section to give blocked process report and extended event commands
• Improved indexing for the blocking tree code recursive CTE
• Moved contentious object name resolution to an update after the initial insert

The final one was done because when there’s a lot of data in the blocked process report, this query could end up being pretty slow. Why, you might ask? Because calling OBJECT_ID() in the query forces a serial plan.

Fun stuff.

Issues and Contributions

---

If you’d like to report an issue, request or contribute a feature, or ask a question about any of these procedures, please use my GitHub repo. Specifically, check out the contributing guide.

As happy as I am to get emails about things, it makes support difficult for the one-man party that I am. Personal support costs money. Public support is free. Take your pick, there!

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.