patternsqlModerate
A possible infinite recompile was detected for SQLHANDLE
Viewed 0 times
infiniterecompiledetectedsqlhandlepossibleforwas
Problem
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 the
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 the
Solution
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.
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.
Context
StackExchange Database Administrators Q#114354, answer score: 15
Revisions (0)
No revisions yet.