Query cost and Query Execution time - Difference

This post is my 100th post :) Was inactive in blogging for few years in between. Now back in full flow. Hope the 200 doesn't take this long !!!

When analysing query plans, one would have noticed the Query cost percentage ( which appear on top of each query in estimated and actual query plans). Query Cost percentages indicate the resource consumed by each query relative to the batch of queries that were executed.

The point to note is highest query cost doesn't mean that the query took longest time to run, it just means that the query highest amount of resources ( CPU, I/O or Memory ). Query with the highest cost may not always be the longest running query. Please refer to this example. Run the following script with the option "Include Actual Query Plan"

SET NOCOUNT ON

Declare @dt Datetime

SET @dt = getdate()

WAITFOR DELAY '00:00:10'

select * from sys.databases where database_id = 5

Print 'time spent in First Query - '

Print Datediff(ms,@dt,getdate())

Print char(10)

/*************** End of First Batch ******************/

SET @dt = getdate()

select syscolumns.name,sysobjects.name,syscolumns.*

from syscolumns, sysobjects where syscolumns.id = sysobjects.id

and sysobjects.xtype = 'u'

order by sysobjects.crdate

Print 'time spent in Second Query - '

Print Datediff(ms,@dt,getdate())


We would use query plans to compare the query cost of first and second queries. The Variable @dt used in the script would be used to track the time taken to run each query.

The first query set of queries, have a delay of 10 seconds and finishes little over 10 seconds. While the second query finishes in 140 milliseconds. Refer to the text result below

time spent in First Query -

10110

time spent in Second Query -

140

Comparing the query plans, the first query consumes just 12% of the total cost of the batch of queries while second query being little complex consumes 88% of total query batch cost, in spite of the first query taking longer to complete