Infinite Recompile Warning Messages in SQL Server Errorlog


Sometimes you may notice below warning messages in SQL Server Errorlog
A possible infinite recompile was detected for SQLHANDLE 0x020000006E8C161EA193363B55EBDF5A21857C439998726E, PlanHandle 0x06000B006E8C161E40017CDE0A0000000000000000000000, starting offset 0, ending offset -1. The last recompile reason was 6.
It is not so eay to troubleshoot this error as this may happen due to various different reasons and sometimes it may be intermittent and gets resolved itself or sometimes it happens continuously until fixed. These messages sometimes may also be logged due to changes to statistics causing recompile and can be ignored.

This message is logged to SQL Server errorlog if one or more SQL statements caused the query batch to recompile at least 50 times or more. The specified statement should be corrected to avoid further recompilations. In the warning message you will find the SQLHandle and planhandle which can be used to find the query or batch that is causing this warning message to be logged to SQL errorlog. Also, there is another important piece of information mentioned in the warning message, which is “The last recompile reason was %d”. Below are the possible recompile reason code and associated description.

Reason code



Schema changed


Statistics changed


Deferred compile


Set option changed


Temp table changed


Remote rowset changed


For Browse permissions changed


Query notification environment changed


Partition view changed


Cursor options changed


Option (recompile) requested

The most common recompile reason is the recompile code 2. From the table above, we can see that this occurs due to statistics-based recompile. If statistics on a table accessed by a SQL query or batch changes, a recompile for the query will be triggered, and if they change often enough, then you could legitimately see this warning in the error log. Therefore, if you see the infinite recompile message appear in the errorlog with reason equal to 2, and you know the statistics on your tables are likely changing often (because the doing a lot of data modification), you can simply ignore the warning.

If the recompile reason was 6, it will be related to remote rows changing on linked server. If this warning message appears continuously, then to resolve this, you may need to look into code and take appropriate action. Some of the possible resolutions for this include selecting the remote rowset into a local temporary table before joining with the remote data, this way the statistics will be available, locally. This may also lead to performance issues that you may be observing in your environment, so bringing locally may also resolves the performance issue we are experiencing.

The other recompile messages are not so often and are self explanatory. You can find the SQL batch or statement causing the infinite recompile by running below query and passing plan_handle, starting_offset and ending_offset which you can find in the warning message logged.

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 (sql_handle) AS st
WHERE qs.statement_start_offset = starting_offset
AND qs.statement_end_offset = ending_offset
AND qs.plan_handle = plan_handle;

Once you know the statement, you can now check the code to make the required adjustments to avoid this message from occurring.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2014

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings


Leave a Reply

Your email address will not be published. Required fields are marked *