>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.
Advertisements

Posted on January 5, 2011, in Performance Tuning. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: