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

EXEC sp_BlitzCache;

Details of Implementation

Procedure Execution

Database Modifications and History Tracking

Output and Reporting

Recovery and Error Handling

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.