![option recompile sql option recompile sql](https://www.sqlbadpractices.com/wp-content/uploads/2011/06/BoostSQLServerPriority.png)
![option recompile sql option recompile sql](https://blog.engineer-memo.com/wp-content/uploads/2019/03/image_thumb-24.png)
Described problem sounds to me like it should be resolved with multi-column stats or indexes. It is better to use sprecompile to mark object for recompilation if you suspect that cached query plan is bad. SET STATISTICS TIME ON Įvery time that statement runs, it will be compiled again, costing those 5 ms of CPU time. Josh, Option recompile should be used with great caution and not without extensive testing.
![option recompile sql option recompile sql](https://www.sqlshack.com/wp-content/uploads/2019/06/exec-sql-example-with-recompile.png)
JOIN Users as U2 on Posts.LastEditorUserId = U2.Id JOIN Users as U on Posts.OwnerUserId = U.Id To see the cost of compilation, just use statistics time. This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. Okay, but what about the cost of compilation? There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sprecompile system stored procedure. To prove that, I’ll right-click on the top SELECT INTO and view the properties. exec 4Įach “query” in this example is a separate statement. Then, I’ll get the actual execution plan for post type 4. I’ll run the stored procedure with the value of 3, first. This stored procedure has OPTION(RECOMPILE) on only one statement INT) asįROM Posts where PostTypeId = PostTypeId, Tags, Body Except for this example, I can’t use the estimated execution plan because there’s a temp table. I’m going to re-use the stored procedure from this post on parameter sniffing and using the estimated execution plan. This option is useful if you don't want SQL Server to cache the query plan for the SP. There’s also the fact that the hint applies strictly to the statement level, not the entire query. recompilation by adding OPTION (RECOMPILE) to your statements. Using option recompile will use extra cpu and memory every time the statement compiles. I have a few reasons why this hint is dangerous. In fact, the statement with option recompile won’t be stored in cach e. It also means that the statement itself won’t be vulnerable to parameter sniffing from other queries in cache. There’s some benefits, like something called “ constant folding.” To us, that just means that the execution plan might be better than a normal execution plan compiled for the current statement. SQL Server will compile an execution plan specifically for the statement that the query hint is on. What happens when a statement has OPTION(RECOMPILE) I’ve used it before without fully understanding the impact. I wish I knew that when I started query tuning.