Changed behaviour of OPTION RECOMPILE syntax in SQL Server 2008 SP1 cumulative update #5

On Monday we released Cumulative update package 5 for SQL Server 2008 Service Pack 1, under KB975977. Contained in this was a very interesting fix to a long running issue with regards to the option recompile syntax and its changed behaviour in SQL Server 2008. This fix has its own KB article at KB976603.

In SQL Server 2008 RTM we introduced behaviour that allowed potentially better plans to be created when using the OPTION RECOMPILE syntax. The way this worked in principal was that if the OPTION RECOMPILE syntax was used, SQL could assume that the plan for the statement in question would not need to re-used, since it was always going to recompile. Therefore the query optimizer could safely use the actual parameter values passed to the statement when choosing a plan, as opposed to just using a parameterised template. In certain circumstances this can lead to dramatic performance gains, especially when you have parameter values which vary wildly and need different plans dependent upon the values passed.

The well known MVP Erland Sommarskog demonstrates these techniques (along with many others) in his excellent article

Dynamic Search Conditions in T-SQL

However currently this article has been edited to remove reference to this technique (but I believe that Erland will soon add it back). We were forced to withdraw this functionality as part of Cumulative update package 4 for SQL Server 2008. The reason for this was that on rare occasions batches using this syntax could return incorrect results if multiple connections were running the same batch simultaneously, due to the fact that the query optimizer associated such plans with the specific thread upon which they were compiled, whereas the actual execution of the batch could occur subsequently on a different thread.

Therefore if you run SQL Server 2008 RTM CU#4 or above, or SQL Server 2008 SP1 between release and CU#4, the OPTION RECOMPILE syntax works as it did in SQL Server 2005.

However this latest update releases a full and thorough fix for the original problem. It has re-instated the enhanced functionality of parameter embedding optimization, and resolves the incorrect results problem seen earlier. The reason that this fix took a while to come out in this form was down to the fact that the changes required in the query optimizer were relatively complex. Initially it was not viable to release them quickly due to the amount of testing required, so this was why we were forced to just withdraw the functionality, because the potential for incorrect results is one of the most serious types of problem SQL Server can encounter.

In summary, here is a matrix showing the different builds and functionality:

SQL 2005 – no parameter embedding optimization

SQL 2008 RTM to SQL 2008 CU#3 – new functionality , but potential for incorrect results

SQL 2008 CU#4 onwards - no parameter embedding optimization

SQL 2008 SP1 RTM to SQL 2008 SP1 CU#4 - no parameter embedding optimization

SQL 2008 SP1 CU#5 onwards – new functionality enabled again, no possibility of incorrect results

Remember that RTM and SP1 are separate branches, so even though the fix is now out in the SP1 branch, it is not in the latest cumulative update on the RTM branch, and it is unlikely to ever be put in there.