sp_BlitzCache

Job details

Name:	sp_BlitzCache
Platform:	Sqlserver
Category:	Performance extension (Brent Ozar)
Description:	Runs the sp_BlitzCache procedure (Brent Ozar) which displays your most resource-intensive queries from the plan cache.
Long description:	Runs the sp_BlitzCache procedure (Brent Ozar) which displays your most resource-intensive queries from the plan cache
Version:	1.0
Default schedule:	5 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version = ‘2005′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]/blitz_obj_id/obj_name[id=‘sp_BlitzCache’]/instance

Parameters

Name	Default value	Description
location of dbo.sp_BlitzCache procedure	master	Name of the database where the dbo.sp_BlitzCache procedure is located.
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when ???
@Top	100	The number of records to retrieve and analyze from the plan cache. The following DMVs are used as the plan cache: dm_exec_query_stats, dm_exec_procedure_stats, dm_exec_trigger_stats. This is input parameter to the sp_BlitzCache procedure.
@SortOrder	Reads	Data processing and display order. @SortOrder will still be used, even when preparing output for a table or for excel. Possible values are: “CPU“, “Reads”, “Writes”, “Duration”, “Executions”, “Recent Compilations”, “Memory Grant”, “Unused Grant”, “Spills”, “Query Hash”. This is input parameter to the sp_BlitzCache procedure.
@ConfigurationDatabaseName		This is input parameter to the sp_BlitzCache procedure.
@ConfigurationSchemaName		This is input parameter to the sp_BlitzCache procedure.
@ConfigurationTableName		This is input parameter to the sp_BlitzCache procedure.
@DurationFilter		Excludes queries with an average duration (in seconds) less than @DurationFilter. This is input parameter to the sp_BlitzCache procedure.
@OutputServerName		This is input parameter to the sp_BlitzCache procedure.
@OutputDatabaseName		The output database. This is input parameter to the sp_BlitzCache procedure.
@OutputSchemaName	dbo	The output schema. This is input parameter to the sp_BlitzCache procedure.
@OutputTableName	BlitzCache	The output table. This is input parameter to the sp_BlitzCache procedure.
delete old output	YES	If set to “YES“ (default) old statistics from previous executions of sp_BlitzCachep procedure will be deleted
@IgnoreSystemDBs		Ignores plans found in the system databases (master, model, msdb, tempdb, and resourcedb).
@OnlyQueryHashes		A list of query hashes to query. All other query hashes will be ignored. Stored procedures and triggers will be ignored.
@OnlySqlHandles		One or more sql_handles to use for filtering results.
@IgnoreSqlHandles		A list of query hashes to ignore.
@QueryFilter		Filter out stored procedures or statements. The default value is ALL. Allowed values are procedures, statements, functions, or all (any variation in capitalization is acceptable).
@DatabaseName		A database name which is used for filtering results.
@StoredProcName		Name of stored procedure you want to find plans for.
@SlowlySearchPlansFor		String to search for in plan text. % wildcards allowed.
@Reanalyze		The default is 0. When set to 0, sp_BlitzCache will re-evaluate the plan cache. Set this to 1 to reanalyze existing results
@BringThePain		When using @SortOrder = all and @Top > 10, we require you to set @BringThePain = 1 so you understand that sp_BlitzCache will take a while to run.
@MinimumExecutionCount		Queries with fewer than this number of executions will be omitted from results.
@MinutesBack		How many minutes back to begin plan cache analysis. If you put in a positive number, we’‘ll flip it to negtive.

Job Summary

Purpose: The monitoring job is designed to track and analyze the performance of SQL Server by using the “sp_BlitzCache” procedure. This procedure highlights resource-intensive queries from the plan cache, suggesting optimizations to improve performance.
Why: This job is crucial for maintaining efficient database operations by identifying queries that consume excessive resources. Optimizing these queries can lead to faster response times and more efficient resource usage, ultimately benefiting overall server performance and user experience.
Manual checking: For manual verification, administrators can execute the “sp_BlitzCache” stored procedure directly within SQL Server Management Studio (SSMS) using the following command:

EXEC sp_BlitzCache;

Details of Implementation

The XML configuration specifies the job runs every hour as indicated by the scheduling parameter `<default-schedule>5 * * *</default-schedule>`.
The job involves checks specifically for SQL Server instances running version 2005 or newer and focuses on both regular SQL Server and Azure SQL Managed Instances.
Dependencies include creating necessary procedures and tables to store execution history and analysis results, ensuring the cleanup of these components if job initialization fails.

Procedure Execution

The central part of this job is the execution of the “sp_BlitzCache” procedure, which assesses the most resource-intensive queries.
Parameters allow customization of the procedure’s behavior including filtering and sorting of query results to focus the analysis on particular areas, such as CPU usage, read/write operations, and execution counts.
The job also involves managing data from previous executions, characterized by parameters that determine whether old data should be deleted before each run.

Database Modifications and History Tracking

This job includes commands to create tables necessary to store historical data and execution details of the “sp_BlitzCache” procedure. These tables preserve records of each execution, supporting long-term analysis and trend monitoring.
A history table `tab_histr_sp_blitzcache` and an execution details table `sp_blitzCache_exec_tab_histr` are created to hold detailed analysis outcomes and operational metrics, respectively.

Output and Reporting

The system generates reports detailing the execution history and the performance analysis results, including metrics such as CPU weight, read and write weights, and the total number of returned rows.
Detailed logging of each run’s parameters helps in troubleshooting and audits, thus ensuring transparency of the monitoring job’s operation.

Recovery and Error Handling

Error handling mechanisms are built into the implementation, capturing and logging exceptions along with specific details about the operation stage at which the error occurred.
Additionally, each operation within the procedure is closely monitored with rollback mechanisms ready to activate if any part of the procedure fails, ensuring system stability and data integrity.

In summary, this dbWatch Control Center job plays a vital role in optimizing and maintaining database performance through detailed analysis of query efficiency, helping database administrators manage resources effectively.