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.