#With recompile sql how to
#With recompile sql update
#With recompile sql code
#With recompile sql plus

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers. Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform. For example, creating a temp table, running an update, and then creating another temp table forces a recompile of the stored procedure following the second temp table's.

Mixing DML statements and DDL statements in a stored procedure can cause a recompile.

A large change in the number of rows in the base table referenced by the query (if AUTO_UPDATE of statistics is enabled), even rows inserted by a trigger.

An example is a SELECT * on a table that does not include a predicate. SQL Server cannot cache execution plans for reuse if the plan has a single best method for execution. The current query does not block, but it uses the out-of-date statistics for the current execution.

#With recompile sql update

If asynchronous statistics updates occur, the update occurs in the background. If statistics for data referenced by the query update, SQL Server blocks execution and recompiles based on the newer statistics. But stored procedures can recompile midstream.įollowing are the most common causes of an automatic recompile: Single statement query recompiles simply cause the query to recompile the next time it's executes. SQL Server then marks the Execution Plan as invalid and generates a new Query Execution Plan the next time it executes. But sometimes, when you know that SQL can work out the right answer, it’s the best way to fix the problem.Because the Execution Plan is based on a combination of the SQL statement, the indexes, and the data, a number of changes might cause SQL Server to decide that the execution plan is no longer valid, or no longer the optimal plan. 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. 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.

#With recompile sql 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. One is if this is the only statement in the procedure that calls for a recompile every time, resources are saved by not recompiling the rest. This is more powerful than WITH RECOMPILE for two reasons. The data from all of the tables was then compared with the other 3 tables to get a final result. If we suspected this and/or knew this when we were executing (from the client) then we could use OPTION (RECOMPILE) to force SQL Server to get a new plan: DECLARE ExecStr nvarchar(4000) SELECT ExecStr ‘SELECT FROM dbo. OPTION (RECOMPILE) This tells SQL Server to recompile this statement every time it is executed. A very small table (table1) was being joined to 4 large tables (table2, table3, table4). In this case, the data was all over the place. 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.

#With recompile sql 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.