Milena Petrovic

Frequent query recompilations – a SQL query performance killer – detection

April 11, 2014 by
In the previous part of this article, we presented query recompilation basics. We explained when a query is compiled, when recompiled, and what preferred behavior is. We also showed the how to trigger a recompilation using T-SQL and provided examples. In this part, we will focus on detecting frequent query recompilations and frequently recompiled queries.

There are certain scenarios when the query is automatically removed from cache, so the next time the query is called, SQL Server will not find its query execution plan in cache and will have to recompile it.

Whenever SQL Server is restarted, all query execution plans stored in cache are removed from cache.

Excessive SQL Server activity. New queries are compiled, added to cache which size is limited, so at a point, the old query execution plans are moved out of cache to make space for the new ones.

When a database that contains the stored procedure, or any object referenced by the query is restored.

When a schema of an object referenced by the query is changed. These changes include:

Table and view column changes (a column added or dropped)
Index changes
Constraint, defaults, and rule changes
Triggers created or dropped
Table statistics drops

When a table referenced by the query has more data changes than specified by the threshold. The threshold can be changed using the KEEP PLAN query hint, as described in Frequent query recompilations – a SQL query performance killer –introduction

When you execute a query or stored procedure where a schema change is followed by a data operation, such as SELECT, INSERT, UPDATE, or DELETE. In other words, when DDL (Data Definition Language) statements are interleaved with DML (Data Manipulation Language) statements. The query will be recompiled when it reaches the first data operation.

When you run

There will be three recompilations, as indicated in the comments

When a SET option is changed
    It’s recommended not to save any of SET options (ANSI_NULLS, Quoted_identifier, etc.) in a stored procedure, as they     cause recompilation

Since SQL Server 2005, only the statement that causes a recompilation is recompiled. This is a statement-level recompilation. In the earlier versions, all statements in the stored procedure were recompiled, which affected SQL Server performance even more.

Detecting recompilations using Windows Performance Monitor

Windows Performance Monitor can be used to track both compilations and recompilations.

The metric that tracks the number of compilations per second is SQL Compilations/Sec. It’s located in the SQL Server Statistics section. The metric shows the number of query and procedure compilations in a second. It’s expected that the value is high on SQL Server start up, but is should fall and stabilize after a while.

The metric that shows the number of query and procedure recompilations in a second is SQL Re-Compilations/Sec. A high value is a clear indication of frequent recompilations that lead to processor stress and SQL Server performance degradation.

The ideal value for the number of recompilations per second is zero. There is no specific threshold value. You should watch for the metric trend and set a baseline. However, it is also recommended to have the Compilations/sec value to be less than 10% of the Batch Requests/sec value. Another recommendation is that the number of Recompilations/sec should be less than 10% of Compilations/sec.

If the values are constantly high, further investigation is required.

  1. Start Windows Performance Monitor, using one of the following methods:
    • Open Start (Windows + C for Windows 8), select Search, type perfmon, and press Enter
    • Open Start, Run (Windows + R for Windows 8), type perfmon, and press Enter
    • Open Control Panel, select System and Security, Administrative Tools, and click Performance Monitor
  2. Select Monitoring Tools, Performance Monitor in the left pane
  3. Click the Add icon in the menu
  4. In the Add counters dialog select the machine name in the drop-down list
  5. In the list of available counters, select MSSQL$<sql server instance>: SQL Statistics
  6. Expand the list, select SQL Re-Compilations/sec, and click Add. To track the recompilations as well, select SQL Compilations/sec

    Tracking recompilations by selecting SQL Compilations/sec in Windows Performance Monitor

  7. Click OK

    The Performance Monitor graph

    The Performance Monitor graph will show the number of compilations and recompilations per second.

The advantages of Performance Monitor are that it is available in Windows Server 2003 and later and easy to use. It shows the graphs in real-time, adds little overhead, and you can monitor only specific counters. Although it clearly shows the number of recompilations, it doesn’t show the queries and stored procedures recompiled, which is necessary for troubleshooting.

Detecting recompilations using sys.dm_os_performance_counters

The SQL Compilations/Sec and SQL Re-Compilations/Sec metrics are also available in the sys.dm_os_performance_counters dynamic management view.

As it’s useful to compare the number of recompilations to the number of compilations and batch requests, select all three metrics.

Detecting recompilations using sys.dm_os_performance_counters

The counter type value 272696576 indicates these are cumulative values since the last SQL Server restart, and should be recalculated to get the number per second. We’ll use a 10 second sampling.

Again, there’s no information about the queries and stored procedures that are recompiled, which is necessary for troubleshooting

Detecting recompilations using SQL Server Profiler

Recompilations are captured in SQL Server traces. To find out whether a recompilation has occurred, use SQL Server Profiler to read the traces.

  1. Start SQL Server Profiler
  2. On the File menu, select New Trace
  3. In the Connect to Server dialog, select the SQL Server instance and provide credentials
  4. Click Connect
  5. In the General tab, specify the trace name

    Specifying the trace name in the SQL Server Profiler's General tab

  6. Open the Events Section tab

    Note that recompilations are not captured into SQL traces by default. To capture them, you have to select them manually.

  7. Select the Show All Events checkbox
  8. In the Events grid, scroll down to the Stored Procedures node and expand it
  9. Select the SP:Recompile row

    Trace properties dialog - Selecting the SP:Recompile row

    This option is only for tracking recompilations on stored procedures

  10. To track recompilations on ad hoc queries, expand the TSQL node and select the SQL:Stmt Recompile row

    Selecting the SQL:Stmt Recompile row to track recompilations on ad hoc queries

  11. Click Run

The stored procedure recompilations are shown as SP:Recompile event class. The statement that is recompiled is shown in the text data column, as well as in the lower pane of the SQL Server Profiler window.

SQL Server Profiler window - stored procedure recompilations

Ad hoc query recompilations are shown as the SQL:Stmt Recompile event class.

SQL Server Profiler offers the Find option to search for the specific events, but it doesn’t enable filtering by a specific event class which would make recompilation analysis easier. The statements recompiled are shown, so that can be used to find the most often recompiled queries and stored procedures. The downside of this method is that running SQL Server traces can add overhead to SQL Server performance.

In this article, we showed when the recompilation occurs automatically. We also presented three different methods that can be used for monitoring recompilations. Windows Performance Monitor and sys.dm_os_performance_counters view show the number or recompilations in a second, but cannot be used for deeper analysis and troubleshooting. SQL Server Profiler shows the recompiled statements, therefore provides enough information for further investigation.

Milena Petrovic