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

33 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
    Replies
    1. i agree with you bro :).
      your website is one of the best sql platform for me to learn sql online.

      http://sapfullform.com/sap-full-form/

      Delete
  3. 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
  4. Your blog was excellent. Posted best topics for the readers help them to get to know things. Also helps the readers to choose the better career reading this.
    SAP Online Training

    ReplyDelete
  5. Thanks for Sharing this valuble information and itis useful for me and CORE SAP learners.We also provides the best SAP Online Training

    ReplyDelete
  6. Thanks for nice topic. I like it. We are Providing Online Training Classes. SAPMMonlinetraining

    ReplyDelete
  7. Top SAP Online Course Providers http://www.todaycourses.com

    ReplyDelete
  8. Top SAP Online Course Providers http://www.todaycourses.com

    ReplyDelete
  9. Hi,
    I Read your Article , it is really informative and beneficial.This is such a great resource that you are providing and you give it away for free. I love seeing websites that understand the value of providing a quality resource.
    Sap Fico Online Training

    ReplyDelete
  10. Wonderful post! You made some very astute observations and I am thankful for the the effort you have put into your writing. Its clear that you know what you are talking about. I am looking forward to reading more of your sites content.
    sap basis training
    sap pega training

    ReplyDelete
  11. I am eagerly waiting for the great info is visible in this blog and hope you providing the great info are visible in this blog. Thank you very much.KBS Training Insitute

    ReplyDelete
  12. You can build the components on Java which is used in PEGA. So that you can learn basics of PEGA. In future if you want move towards on PEGA as a skill, it is much easier. With these you can learn Java and Pega at the same time. Get step into FITA for the training and achieve a wonderful career.
    Thanks,
    PEGA Training in Chennai | PEGA course in Chennai | PEGA Training center in Chennai | PEGA Training institute in Chennai

    ReplyDelete
  13. 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.SAP Hybris Online Training

    ReplyDelete
  14. Thanks for posting this blog i in reality loved it and put up some particular blogs approximately sap........visit our internet site associated with sap fusion all modules education
    from
    oracle fusion procurement online training

    ReplyDelete
  15. The blog posted was very informative and useful thanks for sharing, Our on-line coaching supplier gained the high commonplace name through worldwide for its teaching.
    Oracle fusion financials training

    ReplyDelete
  16. Thank you for this valuable information. I have got some important suggestions from it. I'm working in Brave Technologies Private Limited. We provides lowest price of ERP Software for our clients. Contact us on info@bravetechnologies.in. ERP Providers in Chennai

    ReplyDelete
  17. thank you for sharing the valuable information about the topic for the further information visit our site
    Oracle Fusion Financials Training

    ReplyDelete
  18. Hi,
    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! . your website is one of the best sql platform for me to learn sql online.
    Thank you,
    Oracle EBS Online training

    ReplyDelete
  19. Thank you for this valuable information. Get your business to the next level in simple steps. We provides lowest price of erp Software for our clients cloud erp in Chennai | erp software solutions provider in chennai

    ReplyDelete
  20. and how to find memory usage after the query has been executed.

    ReplyDelete
  21. Oracle Fusion financials online training institutes is available at calfre.com, you just
    need to provide your interested coaching center location and course you prefer to learn
    with this information we can provide you the institutes list which are top rated in that particular area. thank you

    Oracle Fusion Financials online Training

    Oracle fusion Financials Trainin

    ReplyDelete
  22. The best thing is that your blog really informative thanks for your great information!
    Web design institute chennai

    ReplyDelete

  23. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث

    ReplyDelete
  24. شركة نقل اثاث بالدمام التفاؤل شركة نقل اثاث بالخبر كما انها افضل شركة نقل اثاث بالجبيل نقل عفش واثاث بالجبيل والخبر والقطيف والدمام
    شركة نقل اثاث بالدمام
    شركة نقل اثاث بالجبيل

    ReplyDelete
  25. شركة نقل عفش واثاث بالدمام ابيات الشرقيه لخدمات نقل العفش والاثاث بالدمام
    شركة نقل عفش بالدمام
    نقل عفش بالخبر
    شركة نقل اثاث الدمام
    نقل عفش الدمام
    نقل عفش بالدمام
    ان اردت نقل عفش منزلك بالدمام ابيات الشرقية من اهم شركات نقل العفش بالدمام والخبر والجبيل والقطيف والاحساء

    ReplyDelete