Tag: Full text index causing recompilation issues

Sometimes you may notice below warning messages in SQL Server ErrorlogA 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.