MSSQLSERVER_2814

Applies to: SQL Server

Details

Explanation

One or more statements caused the query batch to recompile at least 50 times. The specified statement should be corrected to avoid further recompilations.

The following table lists the reasons for recompilation.

To view all compilation codes, run the following dynamic management view query:

SELECT * FROM sys.dm_xe_map_values
WHERE name LIKE '%compile%cause%';

User action

1. View the statement causing the recompilation by running the following query. Replace the sql_handle, starting_offset, ending_offset, and plan_handle placeholders with the values specified in the error message. The database_name and object_name columns are NULL for ad hoc and prepared Transact-SQL statements.

   SELECT DB_NAME(st.dbid) AS database_name,
          OBJECT_NAME(st.objectid) AS object_name,
          st.text
   FROM sys.dm_exec_query_stats AS qs
   CROSS APPLY sys.dm_exec_sql_text(0x01000600B74C2A1300D2582A2100000000000000000000000000000000000000000000000000000000000000
    /* replace the 0x01000600B... value with the actual sql_handle */
   ) AS st
   WHERE qs.statement_start_offset = 123 /* replace 123 with actual starting_offset value */
         AND qs.statement_end_offset = 456 /* replace 456 with actual ending_offset value */
         AND qs.plan_handle = 0x06000100A27E7C1FA821B10600; /* replace 0x06000100A27E7C1FA821B10600 with actual plan_handle value */
   

2. Based on the reason code description, modify the statement, batch, or procedure to avoid recompilations. For example, a stored procedure might contain one or more SET statements. These statements should be removed from the procedure.

   For more examples of recompilation causes and resolutions, see Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005. For more information on recompilations, see Recompile execution plans.

3. If the problem persists, contact Microsoft Customer Support Services.

Related content

• SQL:StmtRecompile Event Class