I have two queries that are identical. For tuning purposes, I created two indexes that are slightly different and used index hints on each query so that each one uses a specified index. After I ran the identical queries, I looked at each query's execution plan. Query 1 was responsible for 26 percent of the batch cost, while Query 2 was responsible for 74 percent. However, Query 2 ran faster than Query 1. Query 1 cost 7.19, and Query 2 cost 19.9. Query 1 (the lower-cost query) took 3 seconds to run, and Query 2 executed in 2 seconds. Something doesn't seem right about the cost estimates that the query processor provided. Can you explain what's going on?
Query cost estimates are just what the name says—estimates. There are several things to keep in mind when trying to compare a SQL Server cost number to actual performance. First, the inputs to SQL Server's costing algorithm are estimates. For example, the number of rows affected by a particular step during an execution plan is an important contributing factor to the actual cost of a query; However, SQL Server doesn't know the actual number of rows affected until the plan is executed. Additionally, SQL Server uses the estimated query cost to help it choose between execution plans for a particular query. However, cost doesn't translate into time in a direct way. It's possible that a lower-cost query could run slower than a higher-cost query based on memory, I/O, and CPU configurations, in addition to other queries that are running. Costing algorithms don't take into account full information about all the hardware resources available on your machines. The algorithms are Microsoft proprietary and are based on the performance of reference machines in a SQL Server lab in Redmond. Therefore, two queries' cost might be the same, but their response time could vary greatly depending on what type of machine the query is running on.
Defining cost as an absolute measure is impossible. The cost value wouldn't reflect realtime execution speed unless the algorithms were tuned dynamically to reflect the real-world performance differences between hardware capabilities on every server in the world. The SQL Server optimizer doesn't do this. The estimated cost information is a useful way to get a feel for how expensive a query is, but you can't map the estimated cost to a prediction for exact response time.