without using a temporary table (variable)? Is there an simple way to get an recompile-optimized plan with select =. Where is this significant de-optimization behavior with respect to option(recompile) and/or selecting into a scalar variable documented or otherwise discussed in depth?ģ. Where descrim = (recompile) - explicit recompile here does NOT optimize plan for Is this de-optimization "expected"?Ģ. (The plan is still correctly optimized when using a literal value in the query text.) declare int = 2 However, as soon as a "select into scalar variable" is used, the plan becomes de-optimized as it does not eliminate the non-used union. Where descrim = (recompile) - explicit recompile here "works" Normal query, which results in an optimized query plan touching only one of the union all branches: declare int = 2 while the view shows the scenario, it might not be required to reproduce the issue. Using the same table here is irrelevant and, test_table (col1 int, primary key (col1)) Here is a reproduction case in SQL Server 2017: - A table, don't need any data.Ĭreate table. Selecting the result into a temporary table variable does not de-optimize the recompilation. However, this constant-folding optimization appears defeated when selecting the result into a scalar variable. This normally works well when using option(recompile), as the query planner will eliminate the non-reachable union all paths before selection of a query plan. Summary of the downsidesĮach statement with option recompile consumes extra cpu and memory and doesn’t store the execution plan in cache, preventing performance tuners from seeing metrics like total execution count, or total worker time in dm_exec_query_stats.We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). SET STATISTICS TIME ON Įvery time that statement runs, it will be compiled again, costing those 5 ms of CPU time. 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. Okay, but what about the cost of compilation? 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. There’s also the fact that the hint applies strictly to the statement level, not the entire query. 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.
0 Comments
Leave a Reply. |