A possible infinite recompile was detected for SQLHANDLE

Question

I have been finding strange error messages on the sql error log:

Bocss: same deadlock taking place every hour – needs investigating

Also lots of recompiles are listed in the error log for other SPIDs as per following examples:

09/04/2015 14:30:10,spid64,Unknown,A possible infinite recompile was detected for SQLHANDLE 0x0200000059631A288882589E0C54B76404CAE1B97E08D3680000000000000000000000000000000000000000 PlanHandle 0x0600040059631A2860A62B654100000001000000000000000000000000000000000000000000000000000000 starting offset 1038 ending offset 2600. The last recompile reason was 2. 09/04/2015 14:30:10,spid150,Unknown,A possible infinite recompile was detected for SQLHANDLE 0x02000000EF886F018C4E0B163812B8B20150FE8FC7E6A06A0000000000000000000000000000000000000000 PlanHandle 0x06000400EF886F01901A816E0600000001000000000000000000000000000000000000000000000000000000 starting offset 998 ending offset 2520. The last recompile reason was 2. 09/04/2015 14:30:09,spid67,Unknown,A possible infinite recompile was detected for SQLHANDLE 0x0200000057C4C632D9052275CFF2B683B80F29501EE91D730000000000000000000000000000000000000000 PlanHandle 0x0600040057C4C63200EAC2BE3000000001000000000000000000000000000000000000000000000000000000 starting offset 1064 ending offset 2652. The last recompile reason was 2. 09/04/2015 14:30:09,spid163,Unknown,A possible infinite recompile was detected for SQLHANDLE 0x02000000E7C7BF0E5D70DE55759C7842860272AD474D69AB0000000000000000000000000000000000000000 PlanHandle 0x06000400E7C7BF0EF0EB68A52C00000001000000000000000000000000000000000000000000000000000000 starting offset 1028 ending offset 2580. The last recompile reason was 2.

what could have caused this?

Looks like I don't have the plans in cache anymore.

following the advice of this post http://www.sqlservercentral.com/Forums/Topic1479420-146-1.aspx

then as a safety measure disabled the full text catalogs, this made no difference, so then I fully rolled back the changes (dropped the new objects etc). This also made no difference, in the end the only thing that seemed to stop it was a restart of the SQL instances, this immediately resolved the issue.

this sorted me out as well, however, I am still to find what caused this mess at the first place?

Answer 1

According to Infinite recompile message in the errorlog on the SQL Programmability & API Development Team Blog, this message is triggered when a statement in a batch recompiles 100 times in a row.

This message does not necessarily mean there is a problem; it exists to help troubleshoot statements that might legitimately be recompiling that often (for example, due to rapid changes in statistics), as well as real infinite compilation loops (which would be rare in the extreme).

You should start by identifying the triggering statement from the information provided, and evaluate it in the context of the numeric code giving the reason for the recompilations. There is a table of these codes and their meanings in several places in Books Online, including under the SP:Recompile Event Class.

There is more information available in Plan Caching and Recompilation in SQL Server 2012.

Answer 2

I also could not find the plan until I come across this script, which finds the exact sql statement which originated that warning:

Declare @ErrorLog Table (LogID int identity(1, 1) not null primary key,
        LogDate datetime null, 
        ProcessInfo nvarchar(100) null,
        LogText nvarchar(4000) null);

Insert Into @ErrorLog (LogDate, ProcessInfo, LogText)
Exec master..xp_readerrorlog;

With Recompiles
As (Select LogID, LogDate,
        SPID = Replace(ProcessInfo, 'spid', ''),
        SQLHandle = Convert(varbinary(64),
                SUBSTRING(LogText, CHARINDEX('SQLHANDLE', LogText) + 10,
                    CharIndex(',', LogText) - CHARINDEX('SQLHANDLE', LogText) - 10), 1),
        PlanHandle = Convert(varbinary(64),
                SUBSTRING(LogText, CHARINDEX('PLANHANDLE', LogText) + 11,
                    CharIndex(',', LogText, CHARINDEX('PLANHANDLE', LogText)) -
                    CHARINDEX('PLANHANDLE', LogText) - 11), 1),
        StartingOffset = Convert(int,
                SUBSTRING(LogText, CHARINDEX('starting offset', LogText) + 16,
                    CharIndex(',', LogText, CHARINDEX('starting offset', LogText)) -
                    CHARINDEX('starting offset', LogText) - 16)),
        EndingOffset = Convert(int,
                SUBSTRING(LogText, CHARINDEX('ending offset', LogText) + 14,
                    CharIndex('.', LogText, CHARINDEX('ending offset', LogText)) -
                    CHARINDEX('ending offset', LogText) - 14)),
        LastRecompileReason = SUBSTRING(LogText, CHARINDEX('last recompile reason was', LogText) + 26,
                CharIndex('.', LogText, CHARINDEX('last recompile reason was', LogText)) -
                CHARINDEX('last recompile reason was', LogText) - 26)
    From @ErrorLog
    Where CharIndex('A possible infinite recompile was detected for SQLHANDLE', LogText) > 0)
Select R.SPID, R.LogDate,
    LastRecompileReason = Case R.LastRecompileReason When 1 Then 'Schema changed'
            When 2 Then 'Statistics changed (' + R.LastRecompileReason + ')'
            When 3 Then 'Deferred compile (' + R.LastRecompileReason + ')'
            When 4 Then 'Set option changed (' + R.LastRecompileReason + ')'
            When 5 Then 'Temp table changed (' + R.LastRecompileReason + ')'
            When 6 Then 'Remote rowset changed (' + R.LastRecompileReason + ')'
            When 7 Then 'For Browse permissions changed (' + R.LastRecompileReason + ')'
            When 8 Then 'Query notification environment changed (' + R.LastRecompileReason + ')'
            When 9 Then 'Partition view changed (' + R.LastRecompileReason + ')'
            When 10 Then 'Cursor options changed (' + R.LastRecompileReason + ')'
            When 11 Then 'Option (recompile) requested (' + R.LastRecompileReason + ')'
            Else 'Unknown (' + R.LastRecompileReason + ')'
        End,
    DBName = DB_NAME(ST.dbid),
    ObjectName = IsNull(OBJECT_SCHEMA_NAME(ST.objectid, ST.dbid) + N'.', '') +
        IsNull(OBJECT_NAME(ST.objectid, ST.dbid), N'ad hoc or prepared'),
    Command = SUBSTRING(ST.TEXT,
            R.StartingOffset / 2 + 1,
            ((Case R.EndingOffset When -1 THEN DATALENGTH(ST.text)
                Else R.EndingOffset
            End) - R.StartingOffset) / 2 + 1),
    QueryPlan = QP.query_plan,
    FullSQLText = ST.text
From Recompiles As R
Outer Apply sys.dm_exec_sql_text(R.SQLHandle) As ST
Outer Apply sys.dm_exec_query_plan(R.PlanHandle) As QP
Order By R.LogID Desc;