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. 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(). #With recompile vs option recompile update#The issue was first fixed in the following cumulative update of SQL Server.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. This occurs because of an extremely rare timing issue that exists between concurrent compilations. This problem occurs because the parameter values for the query are changed to incorrect values after you run the query on your connection. Important This timing issue is extremely rare. In this scenario, you may receive incorrect results from the procedure that is run on your connection if the following additional conditions are met during the time interval that occurs between the end of query compilation and the start of query execution on your connection:Ĭondition 1: Another concurrent connection makes updates to the joined table that are large enough to trigger another recompilation of the query on your connection.Ĭondition 2: The same query was recompiled from another connection and then put into cache. Your server is under a heavy load that is causing each of these procedures to take more time than usual. This procedure is run on multiple concurrent connections that have different sets of parameter values. It has a WHERE clause that contains parameters. It joins a table that is updated very frequently. The procedure contains a query has the following conditions: You run a procedure in Microsoft SQL Server 2014 or SQL Server 2012. SQL Server 2014 Developer SQL Server 2014 Developer SQL Server 2014 Enterprise SQL Server 2014 Enterprise SQL Server 2014 Standard SQL Server 2014 Standard SQL Server 2012 Enterprise SQL Server 2012 Developer SQL Server 2012 Express SQL Server 2012 Standard SQL Server 2012 Web SQL Server 2012 Business Intelligence SQL Server 2012 Analysis Services SQL Server 2012 Developer SQL Server 2012 Enterprise SQL Server 2012 Standard More.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |