In my post ‘CPU Pressure Part 1’ (https://justinsetliffe.com/2015/05/28/cpu-pressure-part-1/) we looked at excessive query compilation and optimization.  In this post we will further dig into CPU issues and more specifically look into unnecessary recompilation, all of which can be found in Microsoft’s Troubleshooting Performance Problems in SQL Server 2008 (https://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx).

When batches or RPCs (remote procedure calls) are submitted to the SQL engine SQL checks for a valid and correct query plan to use.  If the check fails the batch may have to get recompiled to produce a different query plan.  These recompilations are needed for correctness and to ensure an optimal query plan is used. Recompiling batches is CPU intensive and can result in problems on the system.

Since SQL 2005 only statements that need to be recompiled in stored procedures will get recompiled.  In SQL 2000 the entire procedure would get recompiled, which cause more CPU pressure and locking.  Recompiles happen in the given situations:

• Schema changes
• Statistics changes
• Deferred compilation
• SET option changes
• Temporary table changes
• Stored procedure creation with the RECOMPILE query hint or the OPTION (RECOMPILE) query hint

How to detect these recompiles?  One way is to use Performance Monitor.

The SQL Statistics object provides counters to monitor compilation and the type of requests that are sent to an instance of SQL Server. You have to monitor the number of query compilations and recompilations in conjunction with the number of batches received to find out whether the compilations are contributing to high CPU use. Ideally, the ratio of SQL Recompilations/sec to Batch Requests/sec should be very low, unless users are submitting ad hoc queries.

These are the key data counters:

• SQL Server: SQL Statistics: Batch Requests/sec
• SQL Server: SQL Statistics: SQL Compilations/sec
• SQL Server: SQL Statistics: SQL Recompilations/sec

For more information, see SQL Statistics Object (http://msdn.microsoft.com/en-us/library/ms190911.aspx) in SQL Server 2008 Books Online.

Another way is to use SQL Server Profiler Trace.

SP:Recompile / SQL:StmtRecompile

The SP:Recompile and the SQL:StmtRecompile event classes indicate which stored procedures and statements have been recompiled. When you compile a stored procedure, one event is generated for the stored procedure and one for each statement that is compiled. However, when a stored procedure recompiles, only the statement that caused the recompilation is recompiled. Some of the more important data columns for the SP:Recompile event class are listed here. The EventSubClass data column in particular is important for determining the reason for the recompilation. SP:Recompile is triggered once for the procedure or trigger that is recompiled and is not fired for an ad hoc batch that could likely be recompiled. In SQL Server 2008 and SQL Server 2005, it is more useful to monitor SQL:StmtRecompile, because this event class is fired when any type of batch, ad hoc, stored procedure, or trigger is recompiled.

The key data columns to look at in these events are as follows.

• EventClass
• EventSubClass
• ObjectID (represents stored procedure that contains this statement)
• SPID
• StartTime
• SqlHandle
• TextData

For more information, see SQL:StmtRecompile Event Class (http://technet.microsoft.com/en-us/library/ms179294.aspx) in SQL Server 2008 Books Online.

If you have a trace file saved, you can use the following query to see all the recompilation events that were captured in the trace.

SELECT

    spid,

    StartTime,

    Textdata,

    EventSubclass,

    ObjectID,

    DatabaseID,

    SQLHandle

FROM

    fn_trace_gettable ( ‘e:\recompiletrace.trc’ , 1)

WHERE

    EventClass in(37,75,166)

EventClass  37 = Sp:Recompile, 75 = CursorRecompile, 166 = SQL:StmtRecompile

You can also group the results from this query by the SqlHandle and ObjectID columns, or by various other columns, to see whether most of the recompilations are attributed by one stored procedure or are due to some other reason (such as a SET option that has changed).

Showplan XML For Query Compile

The Showplan XML For Query Compile event class occurs when SQL Server compiles or recompiles a Transact-SQL statement. This event has information about the statement that is being compiled or recompiled and includes the query plan and the object ID of the procedure in question. Capturing this event has significant performance overhead, because it is captured for each compilation or recompilation. If you see a high value for the SQL Compilations/sec counter in Performance Monitor, you should monitor this event. With this information, you can see which statements are frequently recompiled. You can use this information to change the parameters of those statements. This should reduce the number of recompilations.

DMVs

When you use the sys.dm_exec_query_optimizer_info DMV, you can get a good idea of the time SQL Server spends optimizing. If you take two snapshots of this DMV, you can get a good feel for the time that is spent optimizing in the given time period.

SELECT * FROM sys.dm_exec_query_optimizer_info;

Counter                     Occurrence Value

optimizations         121                1

elapsed time           121                 0.026198347107438

The elapsed time value is the time elapsed due to optimizations, which will be close to CPU time.

Another DMV that is useful for capturing this information is sys.dm_exec_query_stats.

The data columns to look at are as follows:

• Sql_handle
• Total worker time
• Plan generation number
• Statement Start Offset

For more information, see sys.dm_exec_query_stats (http://msdn.microsoft.com/en-us/library/ms189741.aspx) in SQL Server 2008 Books Online.

In particular, plan_generation_num indicates the number of times the query has recompiled. The following sample query gives you the top 25 stored procedures that have been recompiled.

SELECT * FROM sys.dm_exec_query_optimizer_info;

SELECT top 25

    sql_text.text,

    sql_handle,

    plan_generation_num,

    execution_count,

    dbid,

    objectid

FROM

    sys.dm_exec_query_stats a

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sql_text

WHERE

    plan_generation_num >1

ORDER BY plan_generation_num DESC;

If you detect excessive compilation and recompilation, consider the following options:

• If a changed SET option caused recompilation use SQL Profiler to find the changed SET option.  Try not to change the SET option in the future, or change it at the connection level.
•  Temp table recompile thresholds are lower than for normal tables.  If statistics are causing recompilations use table variables as opposed to temp tables.
• Use KEEP PLAN query hint.  This sets the recompile level on temp tables to be the same as for regular tables.  The EventSubclass column displays “Statistics Changed” for an operation on a temporary table.
• To avoid recompilations that are due to changes in statistics specify the KEEPFIXED PLAN query hint. This option makes sure recompilations can only happen to ensure correctness (for example, when the underlying table structure has changed and the plan no longer applies) and not to respond to changes in statistics. For example, a recompilation can occur if the schema of a table that is referenced by a statement changes, or if a table is marked with the sp_recompile stored procedure.
• Turning off the automatic updates of statistics for indexes and statistics that are defined on a table or indexed view will prevent recompilations because of changes to statistics on objects.  Turning off the auto-stats feature by using this method is not usually a good idea. This is because the query optimizer is no longer sensitive to data changes in those objects and suboptimal query plans might result. This should only be used as a last resort.
• Batches should use fully qualified object names (dbo.Table4) to avoid recompiles and ambiguity.
• To avoid recompilations that are due to deferred compilations, do not interleave DML and DDL or create the DDL from conditional constructs such as IF statements.
• Run index tuning tools (I happen to love Brent Ozar Unlimited’s free suite of tools) to see if new/different indexes can help execution times.
• Check if stored procedure with created with either WITH RECOMPILE or RECOMPILE query hints.
• If a procedure was created with the WITH RECOMPILE option, in SQL Server 2008 or SQL Server 2005, you may be able to take advantage of a statement-level RECOMPILE hint if a particular statement within that procedure needs to be recompiled. Using this hint at the statement level avoids the necessity of recompiling the whole procedure each time it executes, while at the same time allowing the individual statement to be compiled. For more query hint information check out  SQL 2014 books online (https://msdn.microsoft.com/en-us/library/ms181714.aspx).