Erik Darling, founder of Darling Data, has created these fantastic stored procedures to query SQL Server more efficiently to get health, log, or performance information. I will go through them here regarding using them in Azure SQL database since I don’t have any SQL Servers I manage anymore.
Available Stored Procedures
He has a handful of them, which you can install all at once using a script here:
- sp_HealthParser delves into the system health extended event to provide detailed information about various aspects of SQL Server performance, including queries, waits, I/O issues, CPU, memory, blocking, deadlocking, and system health.
- sp_LogHunter aims to simplify searching through SQL Server error logs, presenting results chronologically.
- sp_HumanEvents, a stored procedure for Extended Events, is especially useful for troubleshooting slow stored procedures.
- sp_HumanEventsBlockViewer focuses on detailed insights into blocking problems.
- sp_PressureDetector identifies resource pressure on SQL Server
- sp_QuickieStore is designed to improve the Query Store GUI by providing flexible parameters to filter query results.
More details about each are provided by Erik here.
I used this script to get all the stored procs into Azure.
sp_QuickieStore
If you ask me, I will start with sp_QuickieStore because it’s the most exciting part of these stored procedures when working in Azure SQL. It is super solid, like everything Erik puts out, and great because it’s very useful in SQL Server and Azure SQL DB. You can install any of these stored procs separately or do them all at once. If you want just QuickieStore, you can get that here. In there, Erik provides samples to work from on how to exec sp_QuickieStore, as well. He has many videos here, if you want to see him explain sp_QuickieStore more, He also has text-based if that’s more your thing vs video.
I chose this to write about first because 1) it’s great in Azure, and 2) I keep getting asked at work for worst-performing queries in Query Store and want an easier way to query Query Store. For more information about Query Store in general and to enable it, visit this link from Microsoft. Visit these links for information on configuration and recommended settings. Using the sp_QuickieStore proc is much nicer than using the Query Store GUI in SSMS!
And especially since I can’t find a way to use Query Store in Azure Data Studio (except for this widget that I haven’t tried), these stored procs will be very helpful. If you want to use Query Store in SSMS, there’s some GUI stuff in there.
Getting Started with sp_QuickieStore
I started with getting help:
EXEC sp_QuickieStore @help = 1; I tend to take the approach of – if all else fails, read the instructions- but this time, I decided to read them first, especially since I wanted to help explain it to you all.
sp_QuickieStore Help
@help will help you (imagine that – what amazing naming of a parameter! 
) understand the parameters and accepted values. Some of the useful ones to me are (with the details from @help):
- @sort_order – cpu, logical reads, physical reads, writes, duration, memory, tempdb, executions, recent – with favorites being cpu, logical reads, duration, and executions.
- @top – the number of queries you want to pull back – a positive integer between 1 and 9,223,372,036,854,775,807
- @start_date – the begin date of your search, will be converted to UTC internally – January 1, 1753, through December 31, 9999
- @end_date – the begin date of your search, will be converted to UTC internally- January 1, 1753, through December 31, 9999
- @execution_count – a positive integer between 1 and 9,223,372,036,854,775,807
- @duration_ms – a positive integer between 1 and 9,223,372,036,854,775,807
There’s lots more, but they aren’t as useful to me. Run the help on the stored proc to see what else might be helpful to you. If you are in SQL Server or Managed Instance @database_name or @get_all_databases will be helpful to you, but as I’m in Azure SQL DB, I don’t need to specify that since I have to put the stored proc in each Azure SQL DB I want to run it against.
Some others I might tinker with in the future are:
- @execution_type_desc – regular, aborted, exception
- @procedure_schema – a valid schema in your database
- @procedure_name – a valid programmable object in your database
- @query_text_search – a string; leading and trailing wildcards will be added if missing
- @wait_filter – cpu, lock, latch, buffer latch, buffer io, log io, network io, parallelism, memory
- @query_type – ad hoc, adhoc, proc, procedure, whatever
- @expert_mode – returns additional columns and results – 0 or 1
- @format_output – returns numbers formatted with commas – 0 or 1
- @workdays – use this to filter out weekends and after-hours queries – 0 or 1
- @work_start – a time like 8am, 9am or something
- @work_end – a time like 5pm, 6pm or something
sp_QuickieStore in Action
As an example of running sp_QuickieStore:
EXEC sp_QuickieStore
@sort_order = 'memory',
@top = 10; And some results in ADS. There are a lot of columns to review, so make sure to scroll over to the right in your results to see it all.
sp_PressureDetector
Lots of good documentation here on it. In his words: “Is your client/server relationship on the rocks? Are queries timing out, dragging along, or causing CPU fans to spin out of control?”
Seems to work well in Azure SQL db, too.
sp_HumanEvents
Another one that looks like it can be used in Azure SQL DB. Right now, I’m not ready to dig into that more as I really need to use sp_QuickieStore. Looks like this could be useful to someone else out there, though. Check out the examples here and the description here.
And because you can’t see the current extended events in Azure Data Studio GUI, you must query to see if they are there. This one in the screenshot below is there because I ran that sp_HumanEvents above. Now, it will collect data for you to query with sp_HumanEvents.
If you want to drop the session in Azure Data Studio later, you must also use a script. I’m not sure if there is functionality in sp_HumanEvents to get rid of the event after or not, but here’s a script in case you need it.
DROP EVENT SESSION keeper_HumanEvents_query ON DATABASE;sp_HumanEventsBlockViewer, sp_HealthParser, and sp_LogHunter Don’t Work for Varying Reasons
sp_HumanEventsBlockView
It looks like this one is out because you can’t set these things in Azure SQL DB.
sp_configure doesn’t exist in Azure SQL DB. There are Database Scoped Configurations in Azure SQL DB, though, but no ability to set advanced options or blocked process threshold, so it looks like this one is out.
sp_LogHunter
Also, it doesn’t seem usable in Azure since there is no way to be a sysadmin like in SQL Server.
sp_HealthParser
Also, not usable in Azure SQL. As Erik says, “This won’t work in Azure because it’s horrible.”
All in all, still some great stuff in here for Azure SQL DB. You can’t use it all like in SQL Server, but don’t let that discourage you from trying out the procs that do work. sp_QuickieStore is especially wonderful!
The post Darling Data SQL Server Stored Procs in Azure SQL Database appeared first on sqlkitty.