Darling Data SQL Server Troubleshooting Suite

The sp_QuickieStore stored procedure is a high-performance tool for analyzing SQL Server Query Store data. It allows you to investigate query performance for a specific database or all databases with Query Store enabled.

Unlike the standard SQL Server Management Studio (SSMS) GUI, sp_QuickieStore provides reliable UTC conversion for time-based filtering and allows for precise searching and filtering of queries using several identifiers. By default, the procedure returns the top 10 queries ranked by average CPU usage.

The sp_IndexCleanup stored procedure is a tool for identifying unused and duplicate indexes in SQL Server databases. It analyzes index usage statistics to suggest candidates for removal and can generate scripts to automate the cleanup process.

Warning: BETA VERSION This procedure is currently in beta. It requires extensive testing in real environments to validate data collection accuracy, deduplication logic, result correctness, and edge cases.

Safety Warning Misuse can harm database performance. Do not run any generated output scripts without thorough review and testing. Always ensure you have a recent backup before making index changes.

Requirements

• SQL Server 2012 (11.0) or later (required for FORMAT and CONCAT functions).

The Human Events Toolkit provides two SQL Server stored procedures designed to simplify the management and analysis of Extended Events:

• sp_HumanEvents: Simplifies common Extended Event scenarios such as blocking, query performance (metrics and execution plans), query compilations, query recompilations, and wait stats.
• sp_HumanEventsBlockViewer: Specifically designed to analyze blocked process reports.

Use these tools to troubleshoot performance issues without needing to manually configure complex Extended Event sessions.

Darling Data is a collection of SQL Server troubleshooting scripts designed for rapid performance diagnostics. These scripts are intended for DBAs and developers who need to identify performance issues immediately without manually querying complex Dynamic Management Views (DMVs), parsing Extended Events XML, or navigating difficult SSMS interfaces.

Key Capabilities:

• Detect CPU and memory pressure.
• Perform comprehensive SQL Server health checks.
• Track query performance issues using Extended Events.
• Analyze blocked process reports.
• Navigate Query Store data efficiently.
• Detect high-impact queries via Pareto analysis of the plan cache.
• Generate reproduction scripts from Query Store data.
• Identify unused or duplicate indexes.
• Clean up Query Store noise and duplicates.

sp_PerfCheck is a comprehensive SQL Server performance diagnostic tool designed to quickly identify configuration issues, capacity problems, and performance bottlenecks. It performs both server-level health checks and database-specific checks for all accessible user databases.

Key Capabilities:

• Detects resource pressure signals (CPU, memory, I/O).
• Analyzes wait statistics to identify bottlenecks.
• Identifies misconfigurations and suboptimal database settings.
• Compatible with SQL Server 2016+ and Azure SQL options.

sp_QuickieCache is a SQL Server troubleshooting tool designed to analyze the plan cache. It uses a Pareto (80/20) analysis approach against plan cache Dynamic Management Views (DMVs) to identify the small number of queries consuming a disproportionate amount of system resources.

It works by:

1. Collecting data from dm_exec_query_stats, dm_exec_procedure_stats, dm_exec_function_stats, and dm_exec_trigger_stats.
2. Selecting candidates by taking the top N queries across multiple metric dimensions (CPU, duration, reads, writes, memory grants, spills, and executions).
3. Scoring candidates using PERCENT_RANK across those dimensions.
4. Surfacing high-impact queries and diagnostic signals.

It is the plan cache companion to sp_QuickieStore (which analyzes Query Store data).

The ClearTokenPerm scripts provide a way to monitor and manage the SQL Server security token cache (TokenAndPermUserStore). In environments with high user counts or frequent application role usage, this cache can grow to several gigabytes, causing high memory usage and performance degradation.

This toolset includes:

• A stored procedure to monitor and clear the cache based on a GB threshold.
• A SQL Agent job to automate the process.
• A demo script to simulate cache inflation for testing.

After running the procedure, the following result sets are provided to help interpret the performance data:

1. All configurations ranked by throughput: A complete list of every tested combination ranked from fastest to slowest.
2. Best config per compression + encryption pairing: The single fastest configuration found for each unique combination of compression and encryption settings.
3. Parameter impact: Identifies which parameter (e.g., buffer count vs. file count) has the largest effect on performance (indicated by a larger spread).
4. Efficiency: Shows the best throughput per MB of buffer RAM, filtered to configurations within 80% of the peak performance.
5. Consistency: Provides min, max, and stddev for each configuration (only available if @iterations > 1).

The script determines how to handle data based on the first XML node found in the content. Certain XML types are always spilled to separate files regardless of size, while others only spill if they exceed -InlineMaxChars.

| Content | Extension | Spills |
|---------|-----------|--------|
| root ShowPlanXML | .sqlplan | always |
| root deadlock / deadlock-list | .xdl | always |
| root blocked-process-report / blocked-process / blocking-process | .xml | always |
| root event (raw Extended Events wrapper) | .xml | always |
| other XML | .xml | over -InlineMaxChars |
| plain text | .sql or .txt | over -InlineMaxChars |

You can use sp_QuickieStore to find specific queries or filter out unwanted ones using the following identifiers:

• query_id
• plan_id
• query_hash
• sql_handle
• module_name
• query_text
• query_type (e.g., ad hoc or from a module)

Additionally, you can change the ranking metric from the default (average CPU) to other performance metrics to identify different types of resource consumers.