Pages

Wednesday, 17 July 2013

SQL Server: Performance Tuning :Understanding Set Statistics Time output

In the last post we have discussed about Set Statistics IO and how it will help us in the performance tuning. In this post we will discuss about the Set Statistics Time which will give the statistics of time taken to execute a query.

Let us start with a example.

USE AdventureWorks2008
GO
            DBCC dropcleanbuffers
            DBCC freeproccache

 GO
SET STATISTICS TIME ON
GO
SELECT *
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID
    WHERE ProductID BETWEEN 700
        AND 800
GO
SELECT *
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID
    WHERE ProductID BETWEEN 700
        AND 800

            
















There aretwo select statement in the example .The first one is executed after clearing the buffer. Let us look into the output.

SQL Server parse and Compile time : When we submit a query to SQL server to execute,it has to parse and compile for any syntax error and optimizer has to produce the optimal plan for the execution. SQL Server parse and Compile time refers to the time taken to complete this pre -execute steps.If you look into the output of second execution, the CPU time and elapsed time are 0 in the SQL Server parse and Compile time section. That shows that SQL server did not spend any time in parsing and compiling the query as the execution plan was readily available in the cache. CPU time refers to the actual time spend on CPU and elapsed time refers to the total time taken for the completion of the parse and compile. The difference between the CPU time and elapsed time might wait time in the queue to get the CPU cycle or it was waiting for the IO completion. This does not have much significance in performance tuning as the value will vary from execution to execution. If you are getting consistent value in this section, probably you will be running the procedure with recompile option.
SQL Server Execution Time: This refers to the time taken by SQL server to complete the execution of the compiled plan. CPU time refers to the actual time spend on CPU where as the elapsed time is the total time to complete the execution which includes signal wait time, wait time to complete the IO operation and time taken to transfer the output to the client.The CPU time can be used to baseline the performance tuning. This value will not vary much from execution to execution unless you modify the query or data. The load on the server will not impact much on this value. Please note that time shown is in milliseconds. The value of CPU time might vary from execution to execution for the same query with same data but it will be only in 100's which is only part of a second. The elapsed time will depend on many factor, like load on the server, IO load ,network bandwidth between server and client. So always use the CPU time as baseline while doing the performance tuning.

If you liked this post, do like my page on FaceBook

Posted by at

5 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete

  2. Congratulations for this post. I would love if you can make a post related to whether to use Flag in SP or not. In the company where I work we use the following:

    Create Procedure usp_Tabla_Sel
    (
    @Flag integer,
    @Campo1 char,
    @Campo2 integer,
    @Campo3 decimal(15,2),
    @Campo4 varchar(100)
    )
    AS
    Begin
    If Flag = 1 then
    Select Campo1, Campo2 From Tabla Where Campo1 = @Campo1
    Where
    begin
    End
    If Flag = 2 then
    Begin
    Select Campo1, Campo2 From Tabla Where Campo2 = @Campo2
    End
    End

    Thank you very much.

    ReplyDelete

  4. Thank you for the post. I came across it as I have been having some trouble trying to understand the output of SET STATISTICS TIME and your article has been very helpful!

    I have a specific question that you touched-on, but I was hoping you could clarify for me:
    If I am running queries from SSMS to a remotely-hosted database, does the "elapsed time" specified under execution time include network communication time or is that a separate measurement?

    Since you said there are three things included in query execution elapsed time
    1. signal wait time,
    2. wait time to complete the IO operation and
    3. time taken to transfer the output to the client
    ... is there a way to determine which of these three parts of the query execution are taking the longest?

    ReplyDelete
    Replies

    1. It is possible to do with the extended events to capture the wait stats of single query execution .That give you the time taken by each

      Delete

Subscribe to: Post Comments (Atom)