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

User · Answer

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

User · Answer

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

User · Answer

SET STATISTICS TIME ON  SELECT     FROM Production ProductCostHistory WHERE StandardCost  lt  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

User · Answer

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

User · Answer

Use SET STATISTICS TIME ON  above your query   Below near result tab you can see a message tab  There you can see the time

User · Answer

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  lt  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

[sql] Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to optimization

Examples related to sql-execution-plan