locked
Bench marking queries RRS feed

  • Question

  • Hi,

    I'm testing how fast I can retreive data from two different table layouts.

    I'm running queries to extract the same information from the different table layouts repeatedly, and then averaging to see which is faster.

    I want to make sure that subsequent runs of a query are not getting cached results, so I'm using the following commands

    CHECKPOINT

    dbcc

     

    freeproccache

    dbcc

     

    dropcleanbuffers

    before I run the queries. This is in a test database on a test server where these queries are the only thing running. 

    I'm also using

    SET

     

    STATISTICS TIME ON

    in order to collect my times.

    I'm looking at both parse and execution times.

    Is this a reasonable approach ?

    I'd be grateful for any pointers or suggestions.

    thanks in advance.  

     


    Sean
    • Moved by Tom Phillips Wednesday, May 12, 2010 4:08 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Wednesday, May 12, 2010 2:02 PM

Answers

  • Sean,

    The following link deals with query/sproc execution time measurement:

    http://www.sqlusa.com/bestpractices2005/executioninms/

    For accurate measurement make sure there are no other activities on the server which may influence results.

    In addition to timing, you can compare the execution plans in SSMS Query Editor.

    SET STATISTICS IO ON

    is also helpful in performance comparison. The basic objective of query optimization is reads reduction.

    To achieve high performance you need to configure the disk resources correctly. Related link:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    Let us know if helpful.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Naomi N Wednesday, May 12, 2010 7:10 PM
    • Marked as answer by Sean2000 Thursday, May 13, 2010 9:10 AM
    Wednesday, May 12, 2010 5:43 PM

All replies

  • Hi,

    I've been collecting stats using the above method and I can't seem to get a consistent picture of the relative speed of the different queries, the number of milliseconds a query takes seems to vary wildly between executions.

     

     

     

     


    Sean
    Wednesday, May 12, 2010 3:10 PM
  • Sean,

    The following link deals with query/sproc execution time measurement:

    http://www.sqlusa.com/bestpractices2005/executioninms/

    For accurate measurement make sure there are no other activities on the server which may influence results.

    In addition to timing, you can compare the execution plans in SSMS Query Editor.

    SET STATISTICS IO ON

    is also helpful in performance comparison. The basic objective of query optimization is reads reduction.

    To achieve high performance you need to configure the disk resources correctly. Related link:

    http://www.sqlusa.com/bestpractices/configureharddisk/

    Let us know if helpful.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Naomi N Wednesday, May 12, 2010 7:10 PM
    • Marked as answer by Sean2000 Thursday, May 13, 2010 9:10 AM
    Wednesday, May 12, 2010 5:43 PM
  • thanks this was useful

    I do execute on a seperate server to avoid any other pro=ceswes affecting the results.

    I was wondering about

    DBCC FREEPROCCACHE -- Delete this line to exclude recompile time

    and this comment helps, I assume that this isn't of use or queries just procedures.

    I'll definitely start executing in a loop as well, as I seem to be doing quite a bit of this.

    Much appreciated.

     

     


    Sean
    Thursday, May 13, 2010 9:16 AM
  • Sean,

    When you clear procedure cache, the database engine is forced to recompile. For simple queries/sprocs, compile time may not be significant when compared to execution time. However, for complex sprocs, it may be significant and it has to be taken into account. A known  performance problem is unintentionall stored procedure recompile.

    Demo follows:

    USE AdventureWorks2008;
    GO
    CREATE PROC uspSalesSummary AS
    SELECT pvt.salespersonid, 
        pvt.fullname, 
        pvt.jobtitle, 
        pvt.salesterritory, 
        pvt.[2002], 
        pvt.[2003], 
        pvt.[2004] 
    FROM  (SELECT soh.salespersonid, 
            p.firstname + ' ' + Coalesce(p.middlename,'') + ' ' + p.lastname AS fullname, 
            e.jobtitle, 
            st.name                             AS salesterritory, 
            soh.subtotal, 
            Year(Dateadd(m,6,soh.orderdate))                 AS fiscalyear 
        FROM  sales.salesperson sp 
            INNER JOIN sales.salesorderheader soh 
             ON sp.businessentityid = soh.salespersonid 
            INNER JOIN sales.salesterritory st 
             ON sp.territoryid = st.territoryid 
            INNER JOIN humanresources.employee e 
             ON soh.salespersonid = e.businessentityid 
            INNER JOIN person.person p 
             ON p.businessentityid = sp.businessentityid) AS soh 
        PIVOT 
        (Sum([subtotal]) 
        FOR [fiscalyear] IN ( [2002],[2003],[2004] ) ) AS pvt; 
    GO
    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE
    SET statistics time ON
    EXEC uspSalesSummary
    EXEC uspSalesSummary
    SET statistics time OFF 
    
    GO 
    DROP PROC uspSalesSummary
    /*
    SQL Server parse and compile time: 
      CPU time = 47 ms, elapsed time = 160 ms.
    
    (14 row(s) affected)
    
     SQL Server Execution Times:
      CPU time = 62 ms, elapsed time = 233 ms.
    
     SQL Server Execution Times:
      CPU time = 109 ms, elapsed time = 393 ms.
    SQL Server parse and compile time: 
      CPU time = 0 ms, elapsed time = 0 ms.
    
    (14 row(s) affected)
    
     SQL Server Execution Times:
      CPU time = 31 ms, elapsed time = 33 ms.
    
     SQL Server Execution Times:
      CPU time = 31 ms, elapsed time = 33 ms.
    */

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Thursday, May 13, 2010 10:53 AM