Sql Server: Which is More Important for Query Optimization, CPU or Logical Reads?

In last post Filtered Index a beauty of Sql Server 2008 , where we have discussed about importance of filtered index by comparing different query performances. For comparison we have use logical reads of different queries as cost figure. One of blog reader asked, why we didn’t use CPU time to compare different query performance.

Sql Server has a cost based optimizer called query optimizer. While generating a query execution plan, query optimizer weighs many factors. CPU, memory and disk I/O are few major factors. Each one of these has their own importance.

During query optimization process, we need a non-fluctuating cost figure as a reference, so we can guess query performance increment or decrement after the steps we have taken to improve query performance and re-executed the same query for multiple times.

CPU value may fluctuate significantly when re-executing the same query with no change in base table schema or indexes or even data. It happens because background applications running on the SQL Server machine affects continuously the processing time of the under observation query. So CPU value is not cost figure we can depend on for query optimization.

While Reads or logical reads remains same when a same query with fixed table schema and data is executed multiple times.

That’s why during query optimization process, if we have reduced number of reads then we definitely reduced the data access cost and made an improvement.

CPU value can’t be ignored when need to reduce CPU intensive operations, such as stored procedure recompilations, aggregate functions, sorting and hash joins.