Darling Data: SQL Server Troubleshooting Scripts
Navigatory
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:
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:
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:
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 |
|
README 内容过长，已截取前 64 KB。完整内容请前往 GitHub 仓库查看。
评论