Infinite Recompile Warning Messages in SQL Server Errorlog

SQLServerF1

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

Description

1

Schema changed

2

Statistics changed

3

Deferred compile

4

Set option changed

5

Temp table changed

6

Remote rowset changed

7

For Browse permissions changed

8

Query notification environment changed

9

Partition view changed

10

Cursor options changed

11

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

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

 

SQL Server Errors or Failures Error: 2792 to Error: 3006

SQLServerF1

 

Error: 2792, Severity: 16, Cannot specify a sql CLR type in a Schema-bound object or a constraint expression.,
Error: 2793, Severity: 16, Specified owner name ‘%.*ls’ either does not exist or you do not have permission to act on its behalf.,
Error: 2794, Severity: 16, Message text expects more than the maximum number of arguments (%d).,
Error: 2795, Severity: 16, Could not %S_MSG %S_MSG because the new %S_MSG ‘%.*ls’ does not match the FILESTREAM %S_MSG ‘%.*ls’ of the table.,
Error: 2796, Severity: 16, Cannot specify database name with $partition in a Schema-bound object, computed column or constraint expression.,

Error: 2797, Severity: 16, The default schema does not exist.,
Error: 2798, Severity: 16, Cannot create index or statistics ‘%.*ls’ on table ‘%.*ls’ because SQL Server cannot verify that key column ‘%.*ls’ is precise and deterministic. Consider removing column from index or statistics key, marking computed column persisted, or using non-CLR-de,
Error: 2799, Severity: 16, Cannot create index or statistics ‘%.*ls’ on table ‘%.*ls’ because the computed column ‘%.*ls’ is imprecise and not persisted. Consider removing column from index or statistics key or marking computed column persisted.,
Error: 2801, Severity: 16, The definition of object ‘%.*ls’ has changed since it was compiled.,
Error: 2802, Severity: 10, SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.,
Error: 2803, Severity: 10, SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations.,
Error: 2809, Severity: 16, The request for %S_MSG ‘%.*ls’ failed because ‘%.*ls’ is a %S_MSG object.,

Error: 2812, Severity: 16, State: 1, Line: 1
Msg: 2812, Level: 16, State: 1, Line: 1
Could not find stored procedure ‘%.*ls’

This error occurs if the name of the stored procedure specified in the query does not exist on the database. Verify that you are running the query against the correct database and make sure there are no spelling mistakes with the name of the stored procedure
Error: 2813, Severity: 16, %.*ls is not supported on this edition of SQL Server.,
Error: 2814, Severity: 10, A possible infinite recompile was detected for SQLHANDLE %hs, PlanHandle %hs, starting offset %d, ending offset %d. The last recompile reason was %d.,
Error: 3002, Severity: 16, Cannot BACKUP or RESTORE a database snapshot.,
Error: 3003, Severity: 10, This BACKUP WITH DIFFERENTIAL will be based on more than one file backup. All those file backups must be restored before attempting to restore this differential backup.,
Error: 3004, Severity: 16, The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.,
Error: 3005, Severity: 10, The differential partial backup is including a read-only filegroup, ‘%ls’. This filegroup was read-write when the base partial backup was created, but was later changed to read-only access. We recommend that you create a separate file backup of the ‘%ls’ ,
Error: 3006, Severity: 16, The differential backup is not allowed because it would be based on more than one base backup. Multi-based differential backups are not allowed in the simple recovery model, and are never allowed for partial differential backups.,

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

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