Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"

Question

I'm trying to determine the relative performance of two different queries and have two ways of measuring this available to me:
1. Run both and time each query
2. Run both and get "Query Cost" from the actual execution plan

Here is the code I run to time the queries...

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME SET @start = getDate()
EXEC test_1a
SELECT getDate() - @start AS Execution_Time
GO

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @start DATETIME SET @start = getDate()
EXEC test_1b
SELECT getDate() - @start AS Execution_Time
GO

What I get is the following:

Stored_Proc     Execution_Time     Query Cost (Relative To Batch)

test_1a         1.673 seconds      17%
test_1b         1.033 seconds      83%

The results of the execution time directly contradict the results of the Query Cost, but I'm having difficulty determining what "Query Cost" actually means. My best guess is that it is an aggregate of Reads/Writes/CPU_Time/etc, so I guess I have a couple of questions:

1. Is there a definative source to explain what this measure means?

2. What other "Query Performance" metrics do people use, and what are their relative merits?

It may be important to note that this is a medium sized SQL Server, running MS SQL Server 2005 on MS Server 2003 Enterprise Edition with multiple processors and 100+ concurrent users.

EDIT:

After some bother I managed to get Profiler access on that SQL Server, and can give extra info (Which supports Query Cost being related to system resources, not Execution Time itself...)

Stored_Proc    CPU      Reads    Writes   Duration   

test_1a        1313     3975     93       1386
test_1b        2297     49839    93       1207

Impressive that taking more CPU with MANY more Reads takes less time :)

Answer 1

The results of the execution time directly contradict the results of the Query Cost, but I'm having difficulty determining what "Query Cost" actually means.

Query cost is what optimizer thinks of how long your query will take (relative to total batch time).

The optimizer tries to choose the optimal query plan by looking at your query and statistics of your data, trying several execution plans and selecting the least costly of them.

Here you may read in more detail about how does it try to do this.

As you can see, this may differ significantly of what you actually get.

The only real query perfomance metric is, of course, how long does the query actually take.

Answer 2

The profiler trace puts it into perspective.

• Query A: 1.3 secs CPU, 1.4 secs duration
• Query B: 2.3 secs CPU, 1.2 secs duration

Query B is using parallelism: CPU > duration eg the query uses 2 CPUs, average 1.15 secs each

Query A is probably not: CPU < duration

This explains cost relative to batch: 17% of the for the simpler, non-parallel query plan.

The optimiser works out that query B is more expensive and will benefit from parallelism, even though it takes extra effort to do so.

Remember though, that query B uses 100% of 2 CPUS (so 50% for 4 CPUs) for one second or so. Query A uses 100% of a single CPU for 1.5 seconds.

The peak for query A is lower, at the expense of increased duration. With one user, who cares? With 100, perhaps it makes a difference...