Does the Query cost in Sql Server account for buffering?

Question

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?

Answer 1

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);

-- 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);

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.