#Recompile stored procedure how to#
#Recompile stored procedure code#

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. 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. We donвЂ™t want to add a heap of new indexes, or maintain some sort of indexed view for this query. This stored proc is run frequently for about 4 weeks of the year. at some time later the start location changes you have to recompile the code. SQL has to take the time to decide again what the best way is to retrieve the data you requested.ДЇor this scenario, it appears to be the best option. From the OS We have them stored in registers so that we can access them.

#Recompile stored procedure code#

OPTION (RECOMPILE) does exactly what it sounds like -it recompiles the code and finds a new execution plan based on the parameters passed in. I ended up deciding to add OPTION (RECOMPILE) to the critical select statement in the stored proc.

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 needed it to pick the best option every single time. If it picked the wrong option, it could run for 10-20 minutes. When the stored proc picked the best plan for the parameters passed in, it would run in about 10-20 seconds.

However, it would then cache that plan and use from then on. 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. The problem was that depending on the data chosen from table1, it could be better to filter by either table2, table3 or table4. The data from all of the tables was then compared with the other 3 tables to get a final result. A very small table (table1) was being joined to 4 large tables (table2, table3, table4). force it to recompile this time: EXEC YourProcedureName WITH. In this case, the data was all over the place. force it to recompile each time it is run: CREATE PROCEDURE YourProcedureNameWITH RECOMPILE. In most cases, this is the best thing for SQL to do, as the plan it comes up with is usually the best option. 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.

#Recompile stored procedure how to#

So I tried to work out how to make it run well for all scenarios. 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.