Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
In SQL Server, is there any time threshold to evaluate candidate plans? It is true that optimizer can't evaluate all possible plans so actually when it decides to quit and go for optimum plan execution?
If threshold does exists, is there any change in threshold for SQL Server 2017 ?
As far as I know, there is no hard "limit" in terms of some absolute value of time that the optimizer searches for a plan.
Each execution is budgeted some amount of percentage of time to explore the plan options. There are a number of factors that go into this - I trust that those factors are always changing somewhat behind the scenes between versions of SQL Server - there are optimizer improvements made and some new options or approaches to options could come out, the budget calculated could change, etc.
SQL Server (thankfully) uses a process where it looks for a "good enough plan" or a reasonable plan. The more complex your query is the more insanely high the number of permutations of choices the optimizer has to execute your query. So we want it to find a reasonable enough plan and move on - so our query can actually start executing. The whole point is "spend enough time to give me a plan that should work alright, don't spend too much time optimizing and exploring options"
We really don't have control over that. And we really shouldn't. We should look to the plan the optimizer came up with and focus more on tuning our queries, tuning our designs and tuning our indexes to best serve the business need.
Another way - in about 15 years of SQL Server Tuning, I've not really ever had to consider "darn it, if only the optimizer had 4 more ms of optimization" - normally all you need to know to make your queries faster is right there in the query plan you did get, or exploring questions like "what about a serial plan instead of a parallel plan? what happens if I throw a join hint in just to force order and see what decisions the optimizer makes so I can rewrite accordingly? What about row goals? What if I split this logic up, etc"
