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:
Is there a definative source to explain what this measure means?
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 :)
This question is related to
sql
sql-server
sql-server-2005
optimization
sql-execution-plan
I understand it’s an old question – however I would like to add an example where cost is same but one query is better than the other.
As you observed in the question, % shown in execution plan is not the only yardstick for determining best query. In the following example, I have two queries doing the same task. Execution Plan shows both are equally good (50% each). Now I executed the queries with SET STATISTICS IO ON
which shows clear differences.
In the following example, the query 1 uses seek
whereas Query 2 uses scan
on the table LWManifestOrderLineItems. When we actually checks the execution time however it is find that Query 2 works better.
Also read When is a Seek not a Seek? by Paul White
QUERY
---Preparation---------------
-----------------------------
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON --IO
SET STATISTICS TIME ON
--------Queries---------------
------------------------------
SELECT LW.Manifest,LW.OrderID,COUNT(DISTINCT LineItemID)
FROM LWManifestOrderLineItems LW
INNER JOIN ManifestContainers MC
ON MC.Manifest = LW.Manifest
GROUP BY LW.Manifest,LW.OrderID
ORDER BY COUNT(DISTINCT LineItemID) DESC
SELECT LW.Manifest,LW.OrderID,COUNT( LineItemID) LineCount
FROM LWManifestOrderLineItems LW
WHERE LW.Manifest IN (SELECT Manifest FROM ManifestContainers)
GROUP BY LW.Manifest,LW.OrderID
ORDER BY COUNT( LineItemID) DESC
Statistics IO
Execution Plan
SET STATISTICS TIME ON
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
SET STATISTICS TIME OFF;
And see the message tab it will look like this:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
(778 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Query Execution Time:
DECLARE @EndTime datetime
DECLARE @StartTime datetime
SELECT @StartTime=GETDATE()
` -- Write Your Query`
SELECT @EndTime=GETDATE()
--This will return execution time of your query
SELECT DATEDIFF(MILLISECOND,@StartTime,@EndTime) AS [Duration in millisecs]
Query Out Put Will be Like:
To Optimize Query Cost :
Click on your SQL Management Studio
Run your query and click on Execution plan beside the Messages tab of your query result. you will see like
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.
Use SET STATISTICS TIME ON
above your query.
Below near result tab you can see a message tab. There you can see the time.
Source: Stackoverflow.com