![]() One It is a stored procedure level recompilation based on WITH RECOMPILE, and the other is a statement-level recompilation based on OPTION (RECOMPILE). 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.When considering recompiling T-SQL (or stored procedures), there are two ways to implement forced recompilation (provided that other factors leading to recompilation are ignored, such as rebuilding indexes, updating statistics, etc.), Rebuilding statistics can be an essential part of DBA work depending on your situation. #WITH RECOMPILE VS 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. There are times that using OPTION(RECOMPILE) makes sense. ![]() Select calling it from my C# program, the parameters are passed in via the SqlCommand.Parameters property.įor the purposes of this discussion, you can assume that the parameters never change so we can rule out sub-optimal parameter smelling as the cause. When running the test from Query Analyzer, I prepend the following lines: declare int UPDATE: I've been asked to post the query… select acctNo,min(date) earliestDate Sorry for the entry-level question but I can't really make heads or tails of this. Is it highly unusual to have a query that requires a recompilation hint on every single call? So why is it then, that subsequent queries that omit the OPTION (RECOMPILE) are so slow? Shouldn't the subsequent queries be making use of the lookup strategy that was computed on the previous call which included the recompilation hint? ![]() It apparently creates a new lookup strategy for the query. My understanding from the posts is that OPTION (RECOMPILE) is an expensive operation. I'm fairly comfortable with writing SQL but have never used an OPTION command in a query before and was unfamiliar with the whole concept of plan caches until scanning the posts on this forum. The query is always called with the same parameters. Calling (or not calling) DBCC FREEPROCCACHE or DBCC dropcleanbuffers makes no difference Query results are always returned instantaneously with OPTION (RECOMPILE) and greater than five minutes without it. This is the case when the query is executed from Query Analyzer or from my C# program via SqlCommand.ExecuteReader(). I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |