Today we are announcing the public preview of the Query Store hints feature which provides an easy-to-use method for shaping query plans and behavior without changing application code. This feature ...
Updated Jun 09, 2021
Version 1.0
Blog Post
1. suppose I have a stored proc or parameterized sql that is executed frequently - that I do not want a recompile every time. Suppose parameters include @StartDate , @EndDate
If start - end dates is 1 or 2 days, then I want one plan, but a different plan for multi-day inputs. The first situation should use a precompiled plan for narrow date range. The second could have a precompiled plan for wide range or get a recompile because its going to be an expensive query anyways and compile is no big deal.
2. Complex query with unavoidable propagating row estimate deviation. Could there be a hint to evaluate the small tables (having a SARG specified) to estimate the rows in the big tables, and base the plan optimization on that? One option, I write a query with a series of CTE's, and have a hint to actual evaluate the rows in one or more CTE's to optimize the plan for the final query?
3. Intelligent query processing is great, but plans derived from a cost model based on disk IO (random vs. sequential) is very different from when data is in memory. It is time to change the execution plan cost model