The CompiledValue and RunTimeValue is the same for all parameters even in the bad.sqlplan This doesn't look like parameter sniffing. Other hints were tried to achieve the same thing - trace flag 4136 etcīefore going to " recompilation", we explored params sniffing and tried optimize for unknown Statistics were updated through sp_updatestats sql-server-index-and-statistics-maintenance.html Why would the result differ if the query hits the server for the first time in first scenario and therefore should compile the plan for the first time too? When query is hinted with OPTION (RECOMPILE) it is instant (and the plan is much more reasonable). When we DBCC FREEPROCCACHE and the submit the query, original ~30 sec run time. Query is generated by Entity Framework and of course has some inefficiencies so that's not the point. We have a query that takes ~30 sec to run for a number of reasons judging by execution plan. Posting this as a broader question, but please let me know if actual query would help figuring this out.
#Sql option recompile how to
Gail Shaw - How to Confuse the SQL Server Query Optimizer Jes Schultz - Parameter Sniffing in SQL Server So in the end the covering index was actually used on prod the whole time, just not by my local bad query. Because of the "feature" described above this resulted in a different query plan than expected. I took slow queries from "Azure Performance Insight" and used local variables to test them. If you use EF Core, the only option is injecting the recompile for certain slow queries This would explain the observed behaviour. That seems to be the difference to normal execution where local variables are ignored.īecause the value of local variables is not known until runtime, the query optimizer needs to make a very rough estimate of row counts prior to execution. Thanks to the responses I found the part of the documentation that explains why queries with local variables perform better when executed with "option (recompile)".įrom the official documentation for recompile:īut in addition to using the procedure's current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when you compile the statement. If you can somehow wire the query WITH RECOMPILE, that would seem to be the way to go-you get a hit on query compilation, but it's a trivial query and shouldn't take that much time, and overall it's superior to continuous table scans. Hands-on messing around would produce more info on this.) (Sorry, I don't know why the lack of ORDER BY and OFFSET commands produce a better query. Time, realizes (via the table statistics) that the index scan + bookmark lookups beats the table scan, and proceeds accordingly. When WITH (RECOMPILE) is included, it presumably does consider the actual values being used , checks the stats on the column, does NOT "sniff" the variables (this is not a stored procedure) and, not knowing anything about them, figures reading the whole table is the safest thing to do so as to avoid excessive bookmark lookups. The query optimizer looks at the query, sees there are from/through variables to apply to column If I use literal values instead of variables, the fast query plan is generated every time: I can not use option recompile here because we use EF Core and injecting it for one specific query is pretty impractical. What really irritates me is that without option recompile the QO refuses to use the index even after updating the statistics and emptying the query cache. ORDER BY a.EarliestStart -> index is used "ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE " -> no improvementĪdded " option (recompile)" -> index is used
0 kommentar(er)
