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.

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?

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

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:

-- Set to 256 MB
EXEC sys.sp_configure
    @configname = 'max server memory (MB)',
    @configvalue = 256;

RECONFIGURE;

-- Estimated cost 5.92241 with 256MB
SELECT DISTINCT
    ProductID,
    TransactionDate,
    Quantity,
    ActualCost
FROM Production.TransactionHistory AS th
OPTION (MAXDOP 1);

256MB plan

-- Set to 2GB
EXEC sys.sp_configure
    @configname = 'max server memory (MB)',
    @configvalue = 2048;

RECONFIGURE;

-- Estimated cost 5.48355 with 2GB
SELECT DISTINCT
    ProductID,
    TransactionDate,
    Quantity,
    ActualCost
FROM Production.TransactionHistory AS th
OPTION (MAXDOP 1);

2GB plan

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.