This helps the query engine to develop a "Plan" of attack for how it will do the query, for example the type of method it will use to match keys between tables using a hash or looking through the entire set. A database stores statistics on the range and distribution of the types of values in various column on tables and indexes. Often when there is a drastic difference from run to run of a query I find that it is often one of 5 issues. If you are still having problems after updating your stats, I would suggest posting both execution plans.Īnd to answer your question - yes, I would say it is highly unusual for your best option to be recompiling the execution plan every time you execute the query. Rebuilding statistics can be an essential part of DBA work depending on your situation.
#Sql server option recompile update
I suspect you just need to update your statistics and your execution plan. In summary - I don't see any reason that OPTION(RECOMPILE) would be a benefit here. This means that if you create the query where there are 10 records in your database and then execute it when there are 100,000,000 records the cached execution plan may no longer be the most effective. NET but if you are using a parameterized query then this ends up being a stored proc call) SQL Server attempts to determine the most effective execution plan for this query based on the data in your database and the parameters passed in ( parameter sniffing), and then caches this plan. When a stored procedure is created (I suspect you are calling ad-hoc sql from. I have never heard that described as creates a new lookup strategy but maybe we are just using different terms for the same thing. This will ensure that when your execution plan is created it will be using the latest information.Īdding OPTION(RECOMPILE) rebuilds the execution plan every time that your query executes. This can be done by running the following: EXEC sp_updatestatsĪnd then recreating your execution plan. Before you explore whether this makes sense in your situation I would recommend rebuilding your statistics. In my experience the only time this is a viable option is when you are using dynamic SQL. 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.There are times that using OPTION(RECOMPILE) makes sense. 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 kommentar(er)
