Mehedi's Earth

• SQL Server Monitoring Demystified: Part-1
• SQL Server Monitoring Demystified: sp_Blitz
• SQL Server Monitoring Demystified: Real-Time Performance Monitoring with sp_BlitzFirst

Query performance tuning is a critical aspect of SQL Server administration, and identifying resource-intensive queries is the first step toward optimization. Enter sp_BlitzCache, a powerful tool from the SQL Server First Responder Kit designed to analyze query execution plans and pinpoint high-cost queries. Whether you’re dealing with slow queries, high CPU usage, or memory pressure, sp_BlitzCache provides the insights you need to take corrective action. In this article, we’ll explore how to use sp_BlitzCache to optimize query performance and keep your SQL Server running smoothly.

sp_BlitzCache

sp_BlitzCache is designed to analyze SQL Server’s plan cache and identify the most resource-intensive queries. Instead of manually digging through execution plans, this stored procedure helps DBAs and developers quickly pinpoint performance bottlenecks by sorting queries based on execution statistics such as:

• CPU Usage
• Logical Reads (I/O operations)
• Logical Writes
• Execution Time (Duration)
• Execution Count

When to Run sp_BlitzCache

In the previous part of this series, you learnt how to identify top bottlenecks of your SQL Server. From that point, you will run sp_BlitzCache to find out the top resource-intensive queries.

Running sp_BlitzCache

As listed below, if you run this stored procedure, you will get two result sets.

1. Top 10 resource-intensive queries default sort order by CPU - list of queries ruins your SQL Server
2. Explanation of the warnings - prioritized warnings and their resolution.

EXEC sp_BlitzCache;

Figure-1: sp_BlitzCache output

Important Notes

•  Always, check the warnings result set part first.
• Look for priority 1 warnings as they indicate you can’t trust the top results, usually due to memory pressure or un-parameterized queries. If there is no priority 1 warnings like figure-1, then you can trust the top result set.
• Read the URL to fix them.

Common wait types, reasons and Resolution

 Table-1: Common Wait Types and Their Resolution

As per the sp_BlitzFirst output of previous article, top wait types were CXCONSUMER and PAGEIOLATCH and as per our above table, you need to sort by READ. Let's do that.

EXEC sp_BlitzCache @SortOrder = 'READ'

Figure-2: sp_BlitzCache output sort by READ

As your wait type is read, so you should look at the check the Total Reads, Avg Reads and Read Weight columns. See how the value of Total Reads column drastically fails down. In most of the cases, you will see that top handful of queries are consuming most of your resources. So, resolving them will remove your headache. #Executions and Executions/Minutes column shows how often your queries are running. Similarly if your wait type is CPU, you need to focus on Total CPU, Avg CPU and CPU Weight columns.

Some More About Parameters

• @MinutesBack = 90 - sometimes, your daily maintenance job queries like backup, restore, index maintenance might appears in top results set. To filter out this, you can add the parameter @MinutesBack = 90 which shows only queries whose ran within last 90 minutes.
• @SortOrder = 'duration' - identifies long running queries. It sums up the execution time together and sorts descending order.
• @SortOrder = 'avg duration' - some queries do not run that much frequently. However, when they take long time. They may not arrive in @SortOrder = 'duration'. To reveal them, you should use @SortOrder = 'avg duration'.
• @SortOrder = 'duplicates' - when you use dynamic or ad hoc queries without parameters, there will be many plans for apparently same query, to identify such queries, use this parameter.
• @SortOrder = 'recent compilations' - shows the top queries which are compiled recently.
• @ExportToExcel = 1 - returns a result set which is copy/paste friendly. It doesn’t include plans. Useful for sending the top 10 list to your developers.

Conclusion