#Sql server option recompile update#

Option recompile fixed the problem because a new plan was generated before each run. Meaning the next time this script ran it used the bad query plan with different dates and puked. It was NOT always small after the query was executed again with a different set of parameters. The query plan for the amount of data within those parameter ranges was cached as if the amount of data in those plans was always small. This caused the performance go haywire if the query was run once on a set of dates that returned few records, then immediately after running on a different set of parameters that returned hundreds of thousands of records. The solution - Adding option recompile to the stored procedure of course! Parameter sniffing was the issue because the stored procedure was executing dynamic SQL and embedding parameter date values into the executed statement. That means SQL Server will execute the same action multiple times, degrading performance (high CPU usage). I had stumbled across what is known as ' parameter sniffing'. When you use the WITH RECOMPILE option, the store procedure gets a new execution plan every time it runs. The generated query plan differed greatly from what I saw previously in the quick runs.

I accidentally ran the procedure once with incorrect parameters and then a second time after putting in the correct parameters was able to duplicate the issue. I had a feeling it had to do with a bad query plan but couldn't figure out why until I was able to luckily reproduce the issue in staging. All of these problems were ruled out as we made sure statistics were up to date, the SQL server load was low, and no maintenance scripts were running yet the problem still occurred. And it was not possible to reproduce in staging with the same data.Įxperimentation - Normally in this situation when the problem could not be reproduced on prod data I would think it's most likely load related, outdated statistics, or even a maintenance script running at the wrong time. This has a very important implication: the plan must work with all possible input values of the parameters. Without this hint, SQL Server produces a plan that will be cached and reused. In this case the query was only slow sometimes in production. OPTION (RECOMPILE) The hint instructs SQL Server to recompile the query every time. Usually a slow running query could be easily reproduced in testing, which would lead to an eventual fix. The problem - We had a scheduled stored procedure taking a really long time in production. By adding the option recompile statement at the end of a stored procedure you are telling SQL to always recompile the query plan instead of fetching it from the cache. But, this could be terrible for performance if the database objects changed drastically from the previous run.īy default the query plan is fetched from the cache and is only recompiled when the underlying tables change or SQL server is restarted. The efficient query plan can easily be fetched from the cache and won't have to be generated again which is generally good for performance. This is really good if the relative sizes of the objects used in the stored procedure don't change very much from the last time the query plan was generated. INNER JOIN a stored procedure is compiled the generated query plan is optimized for the current state of the database. You could try something like this instead:

#Sql server option recompile update#

I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a. The estimated recompile threshold starts an automatic recompile for the query when the estimated number of indexed column changes have been made to a table by running one of the following statements: UPDATE DELETE MERGE INSERT Specifying KEEP PLAN makes sure a query won't be recompiled as frequently when there are multiple updates to a table. If the table TargetsBeforeCurrentCriterion has dupes on CompanyID, then the results from this query will be meaningless. compilationquery-hintssqlsql-serversql-server-2008. To Re Or Not To Re Long compile times: Admittedly pretty rare, and plan guides or forced plans are likely a better option. I assure it works for parallel execution but what other benefit it can have? After that, as a part of the query execution process, the query plan is generated and stored in.

syntaxsql Copy OPTION ( .

Transact-SQL Syntax Conventions Syntax Syntax for SQL Server and Azure SQL Database.

This clause can be specified in the SELECT, DELETE, UPDATE and MERGE statements. First it parses and normalizes the SQL Server statements then it compiles and optimizes the statements. Only one OPTION clause can be specified with the statement. What OPTION (RECOMPILE, QUERYTRACEON 8649) will do when i add it to query? At a high level, the query processor goes through multiple phases before producing a query plan in SQL Server.