DarlingData

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:

• Top Answers
• DBA Stack Exchange

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

README 内容过长，已截取前 64 KB。完整内容请前往 GitHub 仓库查看。