If I am comparing 2 queries by checking their relative "Query cost" as a high level indicator of performance, does the buffer status have any effect on the values shown in the execution plan?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Yes and no, depending on what you mean by 'buffer status'.
The state (content) of the buffer pool does not directly affect the estimated cost of an execution plan (the cost model assumes every query starts with a cold data cache.
The size (not content) of the buffer pool can have an impact on plan selection for several reasons.
The first occurs when memory-consuming operations like hash and sort are present. The amount of memory estimated to be available for the memory grant depends on buffer pool size (note interesting bug in this area).
The following AdventureWorks-based query shows an example of this costing difference based on the size of the buffer pool. In this case, the plan shape is the same, but these costing differences can, and do, change plan selection with larger tables or more complex queries:
Second, the optimizer contains logic to assess (for example) the chance that a page read from disk (under the cold cache assumption) during execution will be required again, and if so, whether it will still be available from cache.
More to the point though, optimizer cost estimates are mainly only useful for internal server purposes. They are not intended to be used to assess potential performance, even at a 'high level'. The model is an abstraction that happens to work reasonably well for the internal purposes it was designed for. The chances that estimated costs bear any sensible resemblance to real execution costs on your hardware and configuration is very small indeed.
Choose other metrics to compare performance, based on whatever real issues are important to you.
No. The query optimizer assumes that all the data will be in the cache. This is because the query engine only pulls data from cache. If the data isn't in cache then the storage engine pulls it from disk and puts it into cache.