Infinite recompile message in the errorlog


SQL Server 2005 sometimes outputs an infinite recompile warning to the errorlog.  This blog post explains what this warning is about and what to do if you see it in the errorlog.


The new warning has the following format:



A possible infinite recompile was detected for SQLHANDLE <handle>, PlanHandle <handle>, starting offset <offset>, ending offset <offset>.  The last recompile reason was <number>.


SQL Server 2005 prints this warning to the error log whenever a statement in the batch recompiles one hundred times in a row.  The warning contains pertinent information such handle to the SQL text of the batch in which the recompile occurred, the handle to the corresponding compiled plan, the begin and end offset of the statement that recompiled (where by convention the end offset of -1 means the end of the batch), and the numeric code that identifies the reason for the recompile. 


 


The warning message is produced because SQL Server assumes a large number of consecutive recompiles may be indicative of a problem.  The purpose of the message is to reduce troubleshooting time for queries that appear to hang.  If a query hits a true infinite recompile, it will never complete, and you will see the infinite recompile warning repeated indefinitely in the errorlog.  The warning message allows you to easily identify the reason why the query never completes – it is because of the infinite recompile.


 


However, the message does not mean that there definitely is a problem; there are cases when the warning message legitimately appears in the errorlog.  The most common recompile reason in the warning is 2 – the numeric code for statistics-based recompile.  If statistics on a table accessed by a query change, a recompile for the query will be triggered, and if they change often enough, 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.


Comments (5)

  1. Jerrin K George says:

    I am getting the following message in the error log

    2009-05-30 11:46:51.70 spid575     A possible infinite recompile was detected for SQLHANDLE 0x02000000C200F3216E865B49E60C5CFB5C60B94851B03E37, PlanHandle 0x06000700C200F321B8210431000000000000000000000000, starting offset 48, ending offset -1.  The last recompile reason was 3.

    Could you please explain what does Recompile reason 3 means??

  2. adbirka says:

    Recompile code 3 refers to Delay Name Resolution.  Hitting this recompile code one hundred times is potentially indicative of a bug.  Do you have a repro for this issue?

  3. tgrchandran says:

    I’m getting the below log.

    …starting offset 3594, ending offset 5780.  The last recompile reason was 1.

    could you please explain what does reason 1 means and provide the complete list of the recompile reasons?

    Thanks in advance.

  4. adbirka says:

    Hi,

    Recompile Reason 1 is Schema Change, which indicates that one of the tables, views, functions etc. referenced by the query has been modified by DDL.  If you’re hitting this and your app is not doing DDL, it may be indicative of a bug.

    Recompile reasons are documented in Books Online documentation for SP:Recompile trace event, in the EventSubClass column.