Darling Data: SQL Server Troubleshooting Scripts

Navigatory

Scripts:
- sp_PressureDetector: Quickly detect CPU and memory pressure
- sp_PerfCheck: Comprehensive SQL Server performance health check
- sp_HumanEvents: Use Extended Events to track down various query performance issues
- sp_HumanEventsBlockViewer: Analyze the blocked process report
- sp_QuickieStore: The fastest and most configurable way to navigate Query Store data
- sp_QuickieCache: High-impact query detection from the plan cache using Pareto analysis
- sp_QueryReproBuilder: Generate executable reproduction scripts from Query Store data
- sp_HealthParser: Pull all the performance-related data from the system health Extended Event
- sp_LogHunter: Get all of the worst stuff out of your error log
- sp_IndexCleanup: Identify unused and duplicate indexes
- sp_QueryStoreCleanup: Remove duplicate and noisy queries from Query Store

Installing

To install every procedure in the list above except sp_QueryStoreCleanup, run Install-All/DarlingData.sql. To install one procedure, run the .sql file in its folder. sp_QueryStoreCleanup is not in the bundle because it removes queries from Query Store. Install it from its own folder.

Utility scripts

These folders each have their own README:

Clear Token Perm: Monitor and clear the security token cache
Export-SqlResults: Run a query and export every result set to CSV
Helper Views: Views and functions for diagnosing performance issues
Ola Stats Only Job: Agent job for nightly statistics updates with Ola Hallengren's solution
ProtectSession: Keep a session alive when it is blocked on lock waits
sp_WhoIsActive Logging: Log sp_WhoIsActive output to tables
String Functions: Functions that extract or remove characters from strings
TestBackupPerformance: Test backup settings to find the fastest combination

Who are these scripts for?

You need to troubleshoot performance problems with SQL Server, and you need to do it now.

You don't have time to track down a bunch of DMVs, figure out Extended Events, wrestle with terrible SSMS interfaces, or learn XML.

These scripts aren't a replacement for a mature monitoring tool, but they do a good job of capturing important issues and reporting on existing diagnostic data

Support

Right now, all support and Q&A is handled on GitHub. Please be patient; it's just me over here answering questions, fixing bugs, and adding new features.

As far as compatibility goes, they're only guaranted to work on Microsoft-supported SQL Server versions.

Older versions are either missing too much information, or simply aren't compatible (Hello, Extended Events. Hello, Query Store) with the intent of the script.

If you have questions about performance tuning, or SQL Server in general, you'll wanna hit a Q&A site:

Back to top

Pressure Detector

Is your client/server relationship on the rocks? Are queries timing out, dragging along, or causing CPU fans to spin out of control?

All you need to do is hit F5 to get information about:

Wait stats since startup
Database file size, stall, and activity
tempdb configuration details
Memory consumers
Low memory indicators
Memory configuration and allocation
Current query memory grants, along with other execution details
CPU configuration and retained utilization details
Thread count and current usage
Any current THREADPOOL waits (best observed with the DAC)
Currently executing queries, along with other execution details
Blocking chains with lead blockers (@troubleshoot_blocking = 1)
Optional logging to permanent tables (@log_to_table = 1)

For a video walkthrough of the script and the results, head over here.

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@what_to_check	varchar	areas to check for pressure	"all", "cpu", and "memory"	all
@skip_queries	bit	if you want to skip looking at running queries	0 or 1	0
@skip_plan_xml	bit	if you want to skip getting plan XML	0 or 1	0
@minimum_disk_latency_ms	smallint	low bound for reporting disk latency	a reasonable number of milliseconds for disk latency	100
@cpu_utilization_threshold	smallint	low bound for reporting high cpu utilization	a reasonable cpu utilization percentage	50
@skip_waits	bit	skips waits when you do not need them on every run	0 or 1	0
@skip_perfmon	bit	skips perfmon counters when you do not need them on every run	0 or 1	0
@sample_seconds	tinyint	take a sample of your server's metrics	a valid tinyint: 0-255	0
@log_to_table	bit	enable logging to permanent tables instead of returning results. Ignores @what_to_check, @sample_seconds and the @skip_* parameters, and logs all CPU utilization (treats @cpu_utilization_threshold as 0)	0 or 1	0
@log_database_name	sysname	database to store logging tables	valid database name	NULL (current database)
@log_schema_name	sysname	schema to store logging tables	valid schema name	NULL (dbo)
@log_table_name_prefix	sysname	prefix for all logging tables	any valid identifier, 112 characters or fewer	'PressureDetector'
@log_retention_days	integer	Number of days to keep logs, 0 = keep indefinitely	integer	30
@troubleshoot_blocking	bit	show blocking chains instead of pressure analysis	0 or 1	0
@help	bit	how you got here	0 or 1	0
@debug	bit	prints dynamic sql, displays parameter and variable values, and table contents	0 or 1	0
@version	varchar	OUTPUT; for support	none	none; OUTPUT
@version_date	datetime	OUTPUT; for support	none	none; OUTPUT

Back to top

Human Events

Extended Events are hard. You don't know which ones to use, when to use them, or how to get useful information out of them.

This procedure is designed to make them easier for you, by creating event sessions to help you troubleshoot common scenarios:

Blocking: blocked process report
Query performance: query execution metrics and actual execution plans
Compiles: catch query compilations
Recompiles: catch query recompilations
Wait Stats: server wait stats, broken down by query and database

The default behavior is to run a session for a set period of time to capture information, but you can also set sessions up to log data to permanent tables.

Requires SQL Server 2012 SP4 or newer (the query_hash_signed action must exist). The blocking event needs the blocked process threshold option set.

For execution examples, run it with @help = 1.

If you set up sessions to capture long term data, you'll need an agent job set up to poll them. You can find an example of that here: Examples

Misuse of this procedure can harm performance. Be very careful about introducing observer overhead, especially when gathering query plans. Be even more careful when setting up permanent sessions!

More resources:

For a video walkthrough of the procedure, code, etc. there's a YouTube playlist here.
For a text-based adventure, head to my site here.

Current valid parameter details:

parameter	name	description	valid_inputs	defaults
@event_type	sysname	used to pick which session you want to run	"blocking", "query", "waits", "recompiles", "compiles" and certain variations on those words	"query"
@query_duration_ms	decimal	(>=) used to set a minimum query duration (milliseconds) to collect data for; 0 removes the floor and collects every duration	a decimal; fractional milliseconds allowed (e.g. 0.5 = 500 microseconds), 0 captures all durations	500 (ms)
@query_sort_order	nvarchar	when you use the "query" event, lets you choose which metrics to sort results by	"cpu", "reads", "writes", "duration", "memory", or "spills" (any of which you can prefix with "avg" to sort by averages, e.g. "avg cpu"), or "event_time"	"cpu"
@skip_plans	bit	when you use the "query" event, lets you skip collecting actual execution plans	1 or 0	0
@keep_prepare_rpc	bit	when you use the "query" event, adds a result set of the prepared-statement RPC calls (sp_prepare, sp_prepexec, sp_execute, sp_unprepare, anything matching sp_cursor%, and sp_describe_undeclared_parameters) that are normally filtered out. Lower @query_duration_ms (to 0 or a fractional value) to get rows back. Shows whether a driver is preparing and reusing statements, e.g. pyodbc's fast_executemany	1 or 0	0
@blocking_duration_ms	integer	(>=) used to set a minimum blocking duration to collect data for	an integer	500 (ms)
@wait_type	nvarchar	(inclusive) filter to only specific wait types	a single wait type, or a CSV list of wait types	"all", which uses a list of "interesting" waits
@wait_duration_ms	integer	(>=) used to set a minimum time per wait to collect data for	an integer	10 (ms)
@client_app_name	sysname	(inclusive) filter to only specific app names	a stringy thing	intentionally left blank
@client_hostname	sysname	(inclusive) filter to only specific host names	a stringy thing	intentionally left blank
@database_name	sysname	(inclusive) filter to only specific databases	a stringy thing	intentionally left blank
@session_id	nvarchar	(inclusive) filter to only a specific session id, or a sample of session ids	an integer, or "sample" to sample a workload	intentionally left blank
@sample_divisor	integer	the divisor for session ids when sampling a workload, e.g. SPID % 5	an integer	5
@username	sysname	(inclusive) filter to only a specific user	a stringy thing	intentionally left blank
@object_name	sysname	(inclusive) to only filter to a specific object name. With the blocking event, also pass @database_name	a stringy thing	intentionally left blank
@object_schema	sysname	(inclusive) the schema of the object you want to filter to; only needed with blocking events	a stringy thing	dbo
@requested_memory_mb	integer	(>=) the memory grant a query must ask for to have data collected	an integer	0
@seconds_sample	tinyint	the duration in seconds to run the event session for	an integer	10
@gimme_danger	bit	used to override default duration minimums for wait events, including zero-duration waits. only use if you're okay with potentially adding a lot of observer overhead on your system, or for testing purposes.	1 or 0	0
@target_output	sysname	output target for extended events	"ring_buffer" or "event_file" (event_file not available for Azure SQL DB or Managed Instance)	"ring_buffer"
@keep_alive	bit	creates a permanent session, either to watch live or log to a table from	1 or 0	0
@custom_name	sysname	if you want to custom name a permanent session	a stringy thing	intentionally left blank
@output_database_name	sysname	the database you want to log data to	a valid database name	intentionally left blank
@output_schema_name	sysname	the schema you want to log data to	a valid schema	dbo
@delete_retention_days	integer	how many days of logged data you want to keep	a POSITIVE integer	3 (days)
@cleanup	bit	deletes all sessions, tables, and views. requires output database and schema.	1 or 0	0
@max_memory_kb	bigint	set a max ring buffer size to log data to	an integer	102400
@version	varchar	to make sure you have the most recent bits	none, output	none, output
@version_date	datetime	to make sure you have the most recent bits	none, output	none, output
@debug	bit	use to print out dynamic SQL	1 or 0	0
@help	bit	well you're here so you figured this one out	1 or 0	0

Back to top

Human Events Block Viewer

This was originally a companion script to analyze the blocked process report Extended Event created by sp_HumanEvents, but has since turned into its own monster.

It will work on any Extended Event that captures the blocked process report. If you need to set that up, run these two pieces of code.

Enable the blocked process report:

EXEC sys.sp_configure
    N'show advanced options',
    1;
RECONFIGURE;
GO

EXEC sys.sp_configure
    N'blocked process threshold',
    5; --Seconds
RECONFIGURE;
GO

Set up the Extended Event:

CREATE EVENT SESSION 
    blocked_process_report
ON SERVER
    ADD EVENT 
        sqlserver.blocked_process_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'bpr'
    )
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);

ALTER EVENT SESSION
    blocked_process_report
ON SERVER 
    STATE = START;

The setup session above does not work in Azure SQL Database as written. There, create it ON DATABASE instead of ON SERVER, with a ring_buffer target.

Once it has data collected, you can analyze it using this command:

EXECUTE dbo.sp_HumanEventsBlockViewer
    @session_name = N'blocked_process_report';

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@session_name	sysname	name of the extended event session to pull from	extended event session name capturing sqlserver.blocked_process_report, system_health also works	keeper_HumanEvents_blocking
@target_type	sysname	target type of the extended event session (ring_buffer, event_file) or 'table' to read from a table. NULL detects it from the session	event_file or ring_buffer or table	NULL
@start_date	datetime2	filter by date	a reasonable date	NULL; will shortcut to last 7 days
@end_date	datetime2	filter by date	a reasonable date	NULL
@database_name	sysname	filter by database name	a database name. A name not on this server gives a warning and matches only reports recorded under that name	NULL
@object_name	sysname	filter by table name	a schema-prefixed table name	NULL
@target_database	sysname	database containing the table with BPR data	a valid database name	NULL
@target_schema	sysname	schema of the table containing blocked process report data	a schema in the target database	NULL
@target_table	sysname	table containing blocked process report data	a table in the target schema	NULL
@target_column	sysname	column containing blocked process report XML	an XML column containing blocked process report data	NULL
@timestamp_column	sysname	column containing UTC timestamp for filtering (optional). MUST be stored in UTC — @start_date and @end_date are shifted to UTC internally to match the XML @timestamp attribute, and the same UTC-shifted values are used for this column filter. A column in local time will be filtered against the wrong window.	a datetime / datetime2 / datetimeoffset column storing UTC timestamps	NULL
@log_to_table	bit	enable logging to permanent tables instead of returning results	0 or 1	0
@log_database_name	sysname	database to store logging tables	a valid database name	NULL (current database)
@log_schema_name	sysname	schema to store logging tables	a valid schema name	NULL (dbo)
@log_table_name_prefix	sysname	prefix for all logging tables	a valid table name prefix	'HumanEventsBlockViewer'
@log_retention_days	integer	Number of days to keep logs, 0 = keep indefinitely	a positive integer, or 0 to keep logs indefinitely	30
@max_blocking_events	integer	maximum blocking events to analyze, 0 = unlimited	0 to 2147483647 (0 = unlimited)	5000
@skip_execution_plans	bit	skip gathering and returning the execution plans result set and jump straight to the findings rollup	0 or 1	0
@help	bit	how you got here	0 or 1	0
@debug	bit	dumps raw temp table contents	0 or 1	0
@version	varchar	OUTPUT; for support	none; OUTPUT	none; OUTPUT
@version_date	datetime	OUTPUT; for support	none; OUTPUT	none; OUTPUT

Back to top

Quickie Store

This procedure will dig into Query Store data for a specific database, or all databases with Query Store enabled.

It's designed to run as quickly as possible, but there are some circumstances that prevent me from realizing my ultimate dream.

The big upside of using this stored procedure over the GUI is that you can search for specific items in Query Store, by:

query_id
plan_id
query hash
sql handle
module name
query text
query type (ad hoc or from a module)

You can also choose to filter out specific queries by those, too.

And you can do all that without worrying about incorrect data from the GUI, which doesn't handle UTC conversion correctly when filtering data.

By default, it will return the top 10 queries by average CPU. You can configure all sorts of things to look at queries by other metrics, or just specific queries.

Use the @expert_mode parameter to return additional details.

More examples can be found here: Examples

More resources:

For a video walkthrough of the procedure, code, etc. there's a YouTube playlist here.
For a text-based adventure, head to my site here.

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@database_name	sysname	the name of the database you want to look at query store in	a database name with query store enabled	NULL; current database name if NULL
@sort_order	varchar	the runtime metric you want to prioritize results by	cpu, logical reads, physical reads, writes, duration, memory, log, tempdb, executions, recent, plan count by hashes, cpu waits, lock waits, locks waits, latch waits, latches waits, buffer latch waits, buffer latches waits, buffer io waits, log waits, log io waits, network waits, network io waits, parallel waits, parallelism waits, memory waits, total waits, rows, total cpu, total logical reads, total physical reads, total writes, total duration, total memory, total log, total tempdb, total rows (avg/average prefix also accepted, e.g. avg cpu, average duration)	cpu
@top	bigint	the number of queries you want to pull back	0 or a positive integer up to 9,223,372,036,854,775,807	10
@start_date	datetimeoffset	the begin date of your search. No offset (or +00:00) means server local time. Any other offset is used as given. Converted to UTC internally.	January 1, 1753, through December 31, 9999	the last seven days
@end_date	datetimeoffset	the end date of your search. No offset (or +00:00) means server local time. Any other offset is used as given. Converted to UTC internally.	January 1, 1753, through December 31, 9999	NULL
@timezone	sysname	user specified time zone to override dates displayed in results	SELECT tzi.* FROM sys.time_zone_info AS tzi;	NULL
@execution_count	bigint	the minimum number of executions a query must have	a positive integer between 1 and 9,223,372,036,854,775,807	NULL
@duration_ms	bigint	the minimum duration a query must have to show up in results	a positive integer between 1 and 9,223,372,036,854,775,807	NULL
@execution_type_desc	nvarchar	the type of execution you want to filter by (regular, aborted, exception, failed); failed bundles aborted and exception	regular, aborted, exception, failed	NULL
@procedure_schema	sysname	the schema of the procedure you're searching for	a valid schema in your database	NULL; dbo if NULL and procedure name is not NULL
@procedure_name	sysname	the name of the programmable object you're searching for	a valid programmable object in your database, can use wildcards	NULL
@include_plan_ids	nvarchar	a list of plan ids to search for	a string; comma separated for multiple ids	NULL
@include_query_ids	nvarchar	a list of query ids to search for	a string; comma separated for multiple ids	NULL
@include_query_hashes	nvarchar	a list of query hashes to search for	a string; comma separated for multiple hashes	NULL
@include_plan_hashes	nvarchar	a list of query plan hashes to search for	a string; comma separated for multiple hashes	NULL
@include_sql_handles	nvarchar	a list of sql handles to search for	a string; comma separated for multiple handles	NULL
@ignore_plan_ids	nvarchar	a list of plan ids to ignore	a string; comma separated for multiple ids	NULL
@ignore_query_ids	nvarchar	a list of query ids to ignore	a string; comma separated for multiple ids	NULL
@ignore_query_hashes	nvarchar	a list of query hashes to ignore	a string; comma separated for multiple hashes	NULL
@ignore_plan_hashes	nvarchar	a list of query plan hashes to ignore	a string; comma separated for multiple hashes	NULL
@ignore_sql_handles	nvarchar	a list of sql handles to ignore	a string; comma separated for multiple handles	NULL
@query_text_search	nvarchar	query text to search for. Matched with LIKE, so _ % [ ] are treated as wildcards unless you set @escape_brackets = 1	a string; leading and trailing wildcards will be added if missing	NULL
@query_text_search_not	nvarchar	query text to exclude. Matched with LIKE, so _ % [ ] are treated as wildcards unless you set @escape_brackets = 1	a string; leading and trailing wildcards will be added if missing	NULL
@escape_brackets	bit	Set this bit to 1 to treat [, ], and _ as literals (not LIKE wildcards) in @query_text_search and @query_text_search_not. Helpful for .NET Entity Framework and other ORM query text. Percent (%) stays a wildcard.	0 or 1	0
@escape_character	nchar	Sets the ESCAPE character for special character searches, defaults to the SQL standard backslash () character	some escape character, SQL standard is backslash ()	\
@only_queries_with_hints	bit	only return queries with query hints	0 or 1	0
@only_queries_with_feedback	bit	only return queries with query feedback	0 or 1	0
@only_queries_with_variants	bit	only return queries with query variants	0 or 1	0
@only_queries_with_forced_plans	bit	only return queries with forced plans	0 or 1	0
@only_queries_with_forced_plan_failures	bit	only return queries with forced plan failures	0 or 1	0
@wait_filter	varchar	wait category to search for; category details are below	cpu, lock, latch, buffer latch, buffer io, log io, network io, parallelism, memory	NULL
@query_type	varchar	filter for only ad hoc queries or only from queries from modules	ad hoc, adhoc, proc, procedure, whatever.	NULL
@expert_mode	bit	returns additional columns and results	0 or 1	0
@hide_help_table	bit	hides the "bottom table" that shows help and support information	0 or 1	0
@format_output	bit	returns numbers formatted with commas and most decimals rounded away	0 or 1	1
@get_all_databases	bit	looks for query store enabled user databases and returns combined results from all of them	0 or 1	0
@include_databases	nvarchar(MAX)	comma-separated list of databases to include (only when @get_all_databases = 1)	a string; comma separated database names	NULL
@exclude_databases	nvarchar(MAX)	comma-separated list of databases to exclude (only when @get_all_databases = 1)	a string; comma separated database names	NULL
@workdays	bit	use this to filter out weekends and after-hours queries	0 or 1	0
@work_start	time	use this to set a specific start of your work days	a time like 8am, 9am or something	9am
@work_end	time	use this to set a specific end of your work days	a time like 5pm, 6pm or something	5pm
@regression_baseline_start_date	datetimeoffset	the begin date of the baseline that you are checking for regressions against (if any). No offset (or +00:00) means server local time. Any other offset is used as given. Converted to UTC internally. Required if you set any other @regression_ parameter. Cannot be used with @sort_order 'recent' or 'plan count by hashes'.	January 1, 1753, through December 31, 9999	NULL
@regression_baseline_end_date	datetimeoffset	the end date of the baseline that you are checking for regressions against (if any). No offset (or +00:00) means server local time. Any other offset is used as given. Converted to UTC internally.	January 1, 1753, through December 31, 9999	NULL; One week after @regression_baseline_start_date if that is specified
@regression_comparator	varchar	what difference to use ('relative' or 'absolute') when comparing @sort_order's metric for the normal time period with any regression time period.	relative, absolute	NULL; absolute if @regression_baseline_start_date is specified
@regression_direction	varchar	when comparing against any regression baseline, what do you want the results sorted by ('magnitude', 'improved', or 'regressed')?	regressed, worse, improved, better, magnitude, absolute, whatever	NULL; regressed if @regression_baseline_start_date is specified
@include_query_hash_totals	bit	will add additional columns to final output with total resource usage by query hash within the requested date window, may be skewed by query_hash and query_plan_hash bugs with forced plans/plan guides	0 or 1	0
@find_high_impact	bit	finds the vital few queries consuming disproportionate resources across cpu, duration, reads, writes, memory, and executions. Cannot be combined with @get_all_databases, @log_to_table, or @find_parameter_sensitive.	0 or 1	0
@primary_window	nvarchar	with @find_high_impact, restricts results to queries whose majority activity is in this window (business, off-hours, or weekend). Requires @find_high_impact = 1.	business, off-hours, or weekend (any unambiguous prefix works: b, biz, off, overnight, w, wknd, etc.)	NULL
@find_parameter_sensitive	bit	finds plan shapes whose runtime metrics swing wildly across executions of the same plan: classic parameter sensitivity. Cannot be combined with @get_all_databases, @log_to_table, or @find_high_impact.	0 or 1	0
@include_maintenance	bit	add maintenance operations such as index creation to the result set	0 or 1	0
@log_to_table	bit	enable logging to permanent tables instead of returning results	0 or 1	0
@log_database_name	sysname	database to store logging tables	a valid database name	NULL; current database
@log_schema_name	sysname	schema to store logging tables	a valid schema name	NULL; dbo
@log_table_name_prefix	sysname	prefix for all logging table names	a valid identifier	QuickieStore
@log_retention_days	integer	days of data to retain, 0 = keep indefinitely	a positive integer, or 0	30
@help	bit	how you got here	0 or 1	0
@debug	bit	prints dynamic sql, statement length, parameter and variable values, and raw temp table contents	0 or 1	0
@troubleshoot_performance	bit	set statistics xml on for queries against views	0 or 1	0
@version	varchar	OUTPUT; for support	none; OUTPUT	none; OUTPUT
@version_date	datetime	OUTPUT; for support	none; OUTPUT	none; OUTPUT

Back to top

Quickie Cache

The plan cache companion to sp_QuickieStore. While QuickieStore digs into Query Store data, QuickieCache uses the same Pareto (80/20) analysis approach against the plan cache DMVs.

It aggregates data from sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, sys.dm_exec_function_stats, and sys.dm_exec_trigger_stats, then scores queries across 7 resource dimensions (CPU, duration, reads, writes, memory grants, spills, executions) using PERCENT_RANK. Only the vital few queries with disproportionate resource consumption are surfaced.

Results come in three result sets:

Workload profile summary: overall concentration analysis with tuning recommendations
High-impact queries: Pareto-scored queries with impact scores, resource shares, and diagnostic signals (parameter sniffing, plan instability, wasteful grants, wait-bound queries, etc.)
Plan cache health findings: plan age distribution, single-use plan bloat, duplicate plan detection, USERSTORE_TOKENPERM pressure

With @find_single_use_plans = 1 or @find_duplicate_plans = 1, the procedure returns only that list and skips the three result sets above. The lists are the largest single-use plans by cached size, or query hashes with more than one cached plan per database.

Requires SQL Server 2016 SP1+ for full memory grant and spill analysis.

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@top	bigint	candidates per metric dimension before dedup	a positive integer	10
@sort_order	varchar	secondary sort after impact_score	cpu, duration, reads, writes, memory, spills, executions	cpu
@database_name	sysname	filter to a specific database	a valid database name	NULL
@start_date	datetime	only include plans created or cached on or after this date	a valid datetime	NULL
@end_date	datetime	only include plans created or cached before this date	a valid datetime	NULL
@minimum_execution_count	bigint	minimum execution count to include a query	a positive integer	2
@ignore_system_databases	bit	exclude system databases (master, model, msdb, tempdb), unless @database_name names one	0 or 1	1
@impact_threshold	decimal(3,2)	minimum impact_score (0.00-1.00) to surface in results	0.00 to 1.00	0.50
@find_single_use_plans	bit	show single-use plans consuming the most memory	0 or 1	0
@find_duplicate_plans	bit	show query hashes with multiple cached plans	0 or 1	0
@debug	bit	print diagnostic information	0 or 1	0
@help	bit	display parameter help	0 or 1	0
@version	varchar	OUTPUT; for support	none; OUTPUT	none; OUTPUT
@version_date	datetime	OUTPUT; for support	none; OUTPUT	none; OUTPUT

-- Basic execution
EXECUTE dbo.sp_QuickieCache;

-- Focus on a specific database
EXECUTE dbo.sp_QuickieCache
    @database_name = N'YourDatabase';

-- Only plans created today
EXECUTE dbo.sp_QuickieCache
    @start_date = '20260403';

-- Lower the threshold to surface more queries
EXECUTE dbo.sp_QuickieCache
    @impact_threshold = 0.25,
    @top = 20;

-- Single-use plans that use the most memory (returns only that list)
EXECUTE dbo.sp_QuickieCache
    @find_single_use_plans = 1;

-- Query hashes with more than one cached plan (returns only that list)
EXECUTE dbo.sp_QuickieCache
    @find_duplicate_plans = 1;

Back to top

Query Repro Builder

This procedure extracts queries and their parameters from SQL Server Query Store and generates executable reproduction scripts that you can run in a new query window.

It's designed to make it easy to reproduce query performance issues by capturing:

Query text with parameter declarations removed
Actual parameter values from the query plan
Context settings (ANSI options, language, date format, etc.)
Warnings about potential reproduction obstacles (temp tables, OPTION(RECOMPILE), etc.)
Embedded constants extracted from plans using parameter embedding optimization

You can filter queries by plan_id or query_id, and optionally specify a date range.

The executable_query column is displayed as clickable XML - click on it in SSMS to view the formatted, ready-to-run script.

More resources:

For a text-based adventure, head to my site here.

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@database_name	sysname	the name of the database you want to look at query store in	a database name with query store enabled	NULL; current database if NULL
@start_date	datetimeoffset(7)	the begin date of your search. No offset (or +00:00) means server local time. Any other offset is used as given. Converted to UTC internally.	January 1, 1753, through December 31, 9999	the last seven days
@end_date	datetimeoffset(7)	the end date of your search. No offset (or +00:00) means server local time. Any other offset is used as given. Converted to UTC internally.	January 1, 1753, through December 31, 9999	NULL
@include_plan_ids	nvarchar(4000)	a list of plan ids to search for	a string; comma separated for multiple ids	NULL
@include_query_ids	nvarchar(4000)	a list of query ids to search for	a string; comma separated for multiple ids	NULL
@include_query_hashes	nvarchar(4000)	a list of query hashes to search for	a string; comma separated for multiple hashes	NULL
@include_plan_hashes	nvarchar(4000)	a list of query plan hashes to search for	a string; comma separated for multiple hashes	NULL
@include_sql_handles	nvarchar(4000)	a list of sql handles to search for	a string; comma separated for multiple handles	NULL
@ignore_plan_ids	nvarchar(4000)	a list of plan ids to ignore	a string; comma separated for multiple ids	NULL
@ignore_query_ids	nvarchar(4000)	a list of query ids to ignore	a string; comma separated for multiple ids	NULL
@ignore_query_hashes	nvarchar(4000)	a list of query hashes to ignore	a string; comma separated for multiple hashes	NULL
@ignore_plan_hashes	nvarchar(4000)	a list of query plan hashes to ignore	a string; comma separated for multiple hashes	NULL
@ignore_sql_handles	nvarchar(4000)	a list of sql handles to ignore	a string; comma separated for multiple handles	NULL
@procedure_schema	sysname	the schema of the procedure you're searching for	a valid schema in your database	NULL; dbo if NULL and procedure name is not NULL
@procedure_name	sysname	the name of the programmable object you're searching for	a valid programmable object in your database	NULL
@query_text_search	nvarchar(4000)	query text to search for	a string; leading and trailing wildcards will be added if missing	NULL
@query_text_search_not	nvarchar(4000)	query text to exclude	a string; leading and trailing wildcards will be added if missing	NULL
@top	bigint	the number of plans you want to pull back, most recently executed first	a positive integer between 1 and 9,223,372,036,854,775,807	10, or every matching plan when an @include_ list or @procedure_name is given
@include_maintenance	bit	set this bit to 1 to keep index and statistics maintenance plans (index builds, statistics updates, StatMan, DBCC) in the result set	0 or 1	0
@query_plan_xml	xml	a single query plan XML to process directly, bypassing Query Store	a valid ShowPlanXML document; when supplied, all Query Store filters are ignored	NULL
@help	bit	return available parameter details, etc.	0 or 1	0
@debug	bit	prints dynamic sql, statement length, parameter and variable values	0 or 1	0
@version	varchar(30)	OUTPUT; for support	none; OUTPUT	none; OUTPUT
@version_date	datetime	OUTPUT; for support	none; OUTPUT	none; OUTPUT

Back to top

Health Parser

The system health extended event has been around for a while, hiding in the shadows, and collecting all sorts of crazy information about your SQL Server.

The problem is, hardly anyone ever looks at it, and when they do, they realize how awful the Extended Events GUI is. Or that if they want to dig deeper into anything, they're going to have to parse XML.

This stored procedure takes all that pain away.

Note that it focuses on performance data, and does not output security details or most other non-performance related data. The one exception is severe errors (severity 16 and up, or 19 and up when @warnings_only = 1).

Does not run on Azure SQL Database.

Typical result set will show you

Queries with significant waits
Waits by count
Waits by duration
Potential I/O issues
CPU task details (including pending task counts and the entry points of pending tasks)
Memory conditions
Memory broker notifications
Memory node out-of-memory events
Overall system health
Scheduler monitor health
Severe errors
CPU intensive requests
A limited version of the blocked process report
XML deadlock report
Query plans for queries involved in blocking and deadlocks (when available)

More resources:

YouTube introduction

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@what_to_check	varchar	areas of system health to check	all, waits, disk, cpu, memory, system, locking (also accepts wait, blocking, blocks, deadlock, deadlocks, lock, locks)	all
@start_date	datetimeoffset	earliest date to show data for. A value without an offset (or with +00:00) is server local time, like the output. It is converted to UTC internally	a reasonable date	seven days back
@end_date	datetimeoffset	latest date to show data for. A value without an offset (or with +00:00) is server local time, like the output. It is converted to UTC internally	a reasonable date	current date
@warnings_only	bit	only show rows where a warning was reported (the blocked process report is not filtered)	NULL, 0, 1	0
@database_name	sysname	database name to show blocking events for	the name of a database	NULL
@wait_duration_ms	bigint	minimum wait duration	the minimum duration of a wait for queries with interesting waits	500
@wait_round_interval_minutes	bigint	interval to round minutes to for wait stats	interval to round minutes to for top wait stats by count and duration	60
@skip_locks	bit	skip the blocking and deadlocking section	0 or 1	0
@skip_waits	bit	skip the wait stats section	0 or 1	0
@pending_task_threshold	integer	minimum number of pending tasks to display	a valid integer	10
@use_ring_buffer	bit	use ring_buffer target instead of file target for faster collection	0 or 1	0
@log_to_table	bit	enable logging to permanent tables instead of returning results	0 or 1	0
@log_database_name	sysname	database to store logging tables	valid database name	NULL (current database)
@log_schema_name	sysname	schema to store logging tables	valid schema name	NULL (dbo)
@log_table_name_prefix	sysname	prefix for all logging tables	valid identifier, 111 characters or fewer	'HealthParser'
@log_retention_days	integer	Number of days to keep logs, 0 = keep indefinitely	integer	30
@debug	bit	prints dynamic sql, selects from temp tables	0 or 1	0
@help	bit	how you got here	0 or 1	0
@version	varchar	OUTPUT; for support	none	none; OUTPUT
@version_date	datetime	OUTPUT; for support	none	none; OUTPUT

Back to top

Log Hunter

The SQL Server error log can have a lot of good information in it about what's going on, whether it's right or wrong.

The problem is that it's hard to know what to look for, and what else was going on once you filter it.

It's another notoriously bad Microsoft GUI, just like Query Store and Extended Events.

I created sp_LogHunter to search through your error logs for the important stuff, with some configurability for you, and return everything ordered by log entry time.

It helps you give you a fuller, better picture of any bad stuff happening.

sp_LogHunter needs EXECUTE permission on master.dbo.xp_readerrorlog, or rdsadmin.dbo.rds_read_error_log if that procedure exists. It does not need sysadmin. To read archived error logs, the login must be a member of the securityadmin fixed server role. Without it, sp_LogHunter reads only the current error log and prints a message. It does not run on Azure SQL Database.

More resources:

YouTube introduction

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@days_back	integer	how many days back you want to search the logs. Not used when @start_date or @end_date is given	an integer. It is converted to a negative number automatically. 0 is treated as -1	-7
@start_date	datetime	if you want to search a specific time frame	a datetime value. Must not be later than @end_date	NULL
@end_date	datetime	if you want to search a specific time frame	a datetime value. Must not be earlier than @start_date	NULL
@custom_message	nvarchar	if you want to search for a custom string	something specific you want to search for. no wildcards or substitutions.	NULL
@custom_message_only	bit	only search for the custom string	NULL, 0, 1. NULL is treated as 0. 1 requires a non-empty @custom_message	0
@first_log_only	bit	only search through the first error log	NULL, 0, 1	0
@language_id	integer	does not translate the search strings, which are English literals. Use @custom_message on non-English servers	SELECT DISTINCT m.language_id FROM sys.messages AS m ORDER BY m.language_id. Any value other than 1033 only triggers a warning	1033
@help	bit	how you got here	NULL, 0, 1	0
@debug	bit	dumps raw temp table contents	NULL, 0, 1	0
@version	varchar	OUTPUT; for support	OUTPUT; for support	none; OUTPUT
@version_date	datetime	OUTPUT; for support	OUTPUT; for support	none; OUTPUT

Back to top

Perf Check

sp_PerfCheck is a comprehensive SQL Server performance diagnostic tool that quickly identifies configuration issues, capacity problems, and performance bottlenecks at both the server and database level.

The procedure performs extensive checks in multiple categories:

Server configuration and resource usage
Memory configuration and pressure indicators
CPU scheduling and pressure
Storage performance and I/O bottlenecks
TempDB configuration
Database configuration settings
Database file auto-growth settings
Query Store health and configuration

Results are returned in a prioritized list, with the most critical issues displayed first. The procedure works on SQL Server 2016+, Azure SQL DB, Azure SQL Managed Instance, and AWS RDS.

Current valid parameter details:

Parameter	Data Type	Default	Description
@database_name	sysname	NULL	Specific database to check; NULL runs against all accessible user databases
@slow_read_ms	decimal(10, 2)	20.0	Flag data-file reads slower than this many ms (High at 5x this value)
@slow_write_ms	decimal(10, 2)	20.0	Flag data-file writes slower than this many ms (High at 5x this value)
@significant_wait_threshold_pct	decimal(38, 2)	10.0	Minimum percent of uptime for a wait to be reported
@wait_high_pct	decimal(38, 2)	50.0	A resource wait at or above this percent of uptime is High priority
@wait_medium_pct	decimal(38, 2)	20.0	A resource wait at or above this percent of uptime is Medium priority
@memory_grant_warning	integer	100	Forced memory grants at or above this cumulative count are Medium
@memory_grant_critical	integer	10000	Forced memory grants at or above this cumulative count are High
@memory_grant_timeout_warning	decimal(38, 2)	10.0	Memory grant timeouts above this cumulative count are Medium
@memory_grant_timeout_critical	decimal(38, 2)	100.0	Memory grant timeouts above this cumulative count are High
@help	bit	0	Displays this help information
@debug	bit	0	Print diagnostic messages and intermediate query results
@version	varchar(30)	NULL OUTPUT	Returns version number
@version_date	datetime	NULL OUTPUT	Returns version date

Back to top

Index Cleanup

This stored procedure helps identify unused and duplicate indexes in your SQL Server databases that could be candidates for removal. It analyzes index usage statistics and can generate scripts for removing unnecessary indexes. It also finds uncompressed indexes that are candidates for page compression.

IMPORTANT: This is currently a BETA VERSION. It needs extensive testing in real environments with real indexes to address several issues:

Data collection accuracy
Deduping logic
Result correctness
Edge cases

Misuse of this procedure can potentially harm your database. If you run this, only use the output to validate result correctness. Do not run any of the output scripts without thorough review and testing, as doing so may be harmful to your database performance.

The procedure requires SQL Server 2012 (11.0) or later due to the use of FORMAT and CONCAT functions.

Current valid parameter details:

Parameter Name	Data Type	Default Value	Description
@database_name	sysname	NULL	The name of the database you wish to analyze. Defaults to the current database when that is a user database
@schema_name	sysname	NULL	The schema name to filter indexes by - limits analysis to tables in the specified schema when used without @table_name
@table_name	sysname	NULL	The table or view name to filter indexes by. The schema defaults to dbo if @schema_name is NULL
@min_reads	bigint	0	Minimum reads for an index to be deduped. Applied per table and per index. An index clears by meeting this or @min_writes. Does not affect unused-index detection or compression
@min_writes	bigint	0	Minimum writes for an index to be deduped. Applied per table and per index. An index clears by meeting this or @min_reads. Does not affect unused-index detection or compression
@min_size_gb	decimal(10,2)	0	Minimum size in GB for an index to be analyzed
@min_rows	bigint	0	Minimum number of rows for a table to be analyzed
@dedupe_only	bit	0	When set to 1, only performs index deduplication but does not mark unused indexes for removal
@get_all_databases	bit	0	When set to 1, analyzes all eligible databases on the server
@include_databases	nvarchar(max)	NULL	Comma-separated list of databases to include (used with @get_all_databases = 1)
@exclude_databases	nvarchar(max)	NULL	Comma-separated list of databases to exclude (used with @get_all_databases = 1)
@sort_order	varchar(20)	default	Controls final result ordering: default groups by script type within each database, object groups all rows for the same index together (Key Subset disables sort under their replacement index)
@help	bit	0	Displays help information
@debug	bit	0	Prints debug information during execution
@version	varchar(20)	NULL	OUTPUT parameter that returns the version number of the procedure
@version_date	datetime	NULL	OUTPUT parameter that returns the date this version was released

Back to top

Query Store Cleanup

Query Store is great, but it collects a lot of noise. System DMV queries, index maintenance, statistics updates, and other background operations all pile up as duplicate entries, wasting space and making it harder to find the queries you actually care about.

This procedure identifies and removes duplicate and noisy queries from Query Store in any database on your server. It uses text pattern matching and hash-based deduplication to find the junk, and removes it using sp_query_store_remove_query.

By default, it targets system queries, maintenance operations, and removes all copies of duplicated query and plan hashes. You can customize what to target, how to deduplicate, and whether to just report or actually remove.

Queries with forced plans are always protected from removal. On SQL Server 2022 or later, the same protection applies to a query with a forced Query Store hint.

On SQL Server 2022 or later, a parameter sensitive plan (PSP) parent query cannot be removed while any of its variant queries remain. The procedure removes parents after their variants, and leaves out a parent that has a variant not on the removal list, instead of failing on it.

For report mode, removal order and compaction, read the sp_QueryStoreCleanup README.

Note: This procedure is not included in the Install-All script due to its destructive nature. Install it separately.

Current valid parameter details:

parameter_name	data_type	description	valid_inputs	defaults
@database_name	sysname	the database to clean query store in	a database name with query store enabled	NULL; current database if NULL
@cleanup_targets	varchar(100)	what to target for cleanup	all, system, maintenance (or maint), custom, none, or comma-separated combination	all
@custom_query_filter	nvarchar(1024)	custom LIKE pattern for query text filtering; also applied when @cleanup_targets = all	a valid LIKE pattern	NULL
@dedupe_by	varchar(50)	deduplication strategy	all, query_hash, plan_hash, none	all
@min_age_days	integer	only remove queries whose last execution is older than this many days	a positive integer	NULL; no age filter
@report_only	bit	report what would be removed without removing	0 or 1	0
@sort_direction	varchar(10)	removal order by query_id	ASC, DESC	ASC
@compact_tables	bit	afterwards, compact Query Store's internal tables	0 or 1	0
@help	bit	how you got here	0 or 1	0
@debug	bit	prints dynamic sql and diagnostics	0 or 1	0
@version	varchar(30)	OUTPUT; for support	none; OUTPUT	none; OUTPUT
@version_date	datetime	OUTPUT; for support	none; OUTPUT	none; OUTPUT

Back to top

Name		Name	Last commit message	Last commit date
.claude/skills		.claude/skills
.github		.github
.vs		.vs
Alerts		Alerts
Clear Token Perm		Clear Token Perm
Create Long IN List		Create Long IN List
Dynamic SQL Logging		Dynamic SQL Logging
Export-SqlResults		Export-SqlResults
Helper Views		Helper Views
Inline Financial Functions		Inline Financial Functions
Install-All		Install-All
Ola Stats Only Job		Ola Stats Only Job
Presentations		Presentations
ProtectSession		ProtectSession
SQL Debug Example		SQL Debug Example
Stack Column Store Database		Stack Column Store Database
String Functions		String Functions
TestBackupPerformance		TestBackupPerformance
sp_HealthParser		sp_HealthParser
sp_HumanEvents		sp_HumanEvents
sp_IndexCleanup		sp_IndexCleanup
sp_LogHunter		sp_LogHunter
sp_PerfCheck		sp_PerfCheck
sp_PressureDetector		sp_PressureDetector
sp_QueryReproBuilder		sp_QueryReproBuilder
sp_QueryStoreCleanup		sp_QueryStoreCleanup
sp_QuickieCache		sp_QuickieCache
sp_QuickieStore		sp_QuickieStore
sp_WhoIsActive Logging		sp_WhoIsActive Logging
.gitattributes		.gitattributes
.gitignore		.gitignore
CLAUDE.md		CLAUDE.md
CNAME		CNAME
CONTRIBUTING.md		CONTRIBUTING.md
LICENSE.md		LICENSE.md
README.md		README.md
_config.yml		_config.yml
image-1.png		image-1.png
image.png		image.png
todo.md		todo.md

Navigation Menu

Repository navigation

About

Repository files navigation

Darling Data: SQL Server Troubleshooting Scripts

Navigatory

Installing

Utility scripts

Who are these scripts for?

Support

Pressure Detector

Human Events

Human Events Block Viewer

Quickie Store

Quickie Cache

Query Repro Builder

Health Parser

Log Hunter

Perf Check

Index Cleanup

Query Store Cleanup

About

Releases

Sponsor this project

Packages

Used by

Contributors

Languages

📋 Server Request & Proxy Logs

⚙️ Pengaturan Proxy Target

Latest commit

History

Folders and files

Repository files navigation

Darling Data: SQL Server Troubleshooting Scripts

Navigatory

Installing

Utility scripts

Who are these scripts for?

Support

Pressure Detector

Human Events

Human Events Block Viewer

Quickie Store

Quickie Cache

Query Repro Builder

Health Parser

Log Hunter

Perf Check

Index Cleanup

Query Store Cleanup

About

Topics

Resources

Contributing

Stars

Watchers

Forks

Releases

Sponsor this project

Packages

Used by

Contributors

Languages