Take the 2-minute tour ×
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's 100% free, no registration required.

Given the following constants:

  • The same database with the same structure (tables, indexes, etc)
  • The same data
  • The same SQL Server and hardware configuration
  • The same statistics
  • The same SET options in the client
  • The same SQL Server versions
  • The same trace flags

Given these constants, will SQL Server always produce the same plan for a given query?

If not, are there other considerations? Is there also an element of nondeterminism to consider as well?

share|improve this question
    
it will be incorrect to say same plan, but we can say similar plan. Along with the external factors that DBA / Developer can control; "query plan optimization algorithms" and "internal query cost algorithms" are written inside SQL server engine. we do not have that control of telling "what to choose" in all ways. we can set external env to guide engine to choose best. for a given query if optimizer has come up with two execution plan with lets say 0.001 cost difference then I guess it will be irrelevant what execution plan it choose. –  Anup Shah May 10 at 14:57
    
I dont think you should be to theoretical about this. I have worked 25 years with different databases and its almost always possible to rewrite a query to execute better. And sometimes its very hard to understand why its better with one way of writing the query. –  user38295 May 13 at 5:34

1 Answer 1

up vote 11 down vote accepted

Given these constants, will SQL Server always produce the same plan for a given query? If not, are there other considerations? Is there also an element of nondeterminism to consider as well?

Query compilation is deterministic as far as I am aware. One of the original QO design goals was that it should be possible to reproduce execution plans on a different system using a statistics-only copy of the database. There are a couple of subtleties to that, around configuration parameters like the amount of memory available, and the number of logical processors, but these are covered by your list of things to synchronize.

Caveat: That is true provided the word 'same' in your list is taken to mean exactly the same in all respects. For example, the 'same' statistics might exist on the two systems, but they are only exactly the same if the histogram steps and density information are identical.

That said, the optimization process is also extremely complex, meaning it can be difficult to ensure that all inputs to this deterministic process are identical, and that all internal states are similar enough to ensure the same code path is taken through the optimizer for a particular compilation. If the query contains access outside the database (to another database or instance), those environments must be identical too.

One thing I would add to your list is to check that any plan guides also exist in the second database.


The use of non-deterministic functions like GETDATE() in queries might mean you get a different plan too. While the main optimizer does not use the value directly, cardinality estimation can (see Constant Folding and Expression Evaluation During Cardinality Estimation). I am unsure if this class of difference falls within the scope of the question though, because both systems would produce the same plan if executed at the same time (or, more generally, with the same input variables, parameters, and function values).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.