Using OPTION (RECOMPILE)
This week, I was trying to improve the performance of a bit of SQL and noticed that it generated significantly different query plans depending on the parameters passed in. So I tried to work out how to make it run well for all scenarios.When SQL Server first runs a stored procedure, it works out the ideal execution plan based on the parameter passed in and saves it for next time. In most cases, this is the best thing for SQL to do, as the plan it comes up with is usually the best option.
In this case, the data was all over the place. A very small table (table1) was being joined to 4 large tables (table2, table3, table4). The data from all of the tables was then compared with the other 3 tables to get a final result. The problem was that depending on the data chosen from table1, it could be better to filter by either table2, table3 or table4. When I cleared my cache and ran each of the test scenarios, it would always pick a great option to filter the data as quickly and efficiently as possible. However, it would then cache that plan and use from then on.
When the stored proc picked the best plan for the parameters passed in, it would run in about 10-20 seconds. If it picked the wrong option, it could run for 10-20 minutes. I needed it to pick the best option every single time. I spent some time changing indexes and trying to get a plan that would be suitable for all (or almost all) scenarios but I couldn’t find a suitable option.
I ended up deciding to add OPTION (RECOMPILE) to the critical select statement in the stored proc.
OPTION (RECOMPILE) does exactly what it sounds like -it recompiles the code and finds a new execution plan based on the parameters passed in. Recompiling takes extra resources. SQL has to take the time to decide again what the best way is to retrieve the data you requested.
For this scenario, it appears to be the best option. This stored proc is run frequently for about 4 weeks of the year. We don’t want to add a heap of new indexes, or maintain some sort of indexed view for this query. By using OPTION (RECOMPILE), we will incur a cost to compile the plan every time it runs, but for a stored proc that use to run in 20 minutes to now run in 20 seconds, this works out well.
Normally I wouldn’t recommend OPTION (RECOMPILE), as the fix is usually to sort out your indexes, or the joins, or the columns you’re selecting, or your statistics, or countless other things. But sometimes, when you know that SQL can work out the right answer, it’s the best way to fix the problem.