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
            DBCC dropcleanbuffers
            DBCC freeproccache

FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
AND 800

FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
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


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

  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)
    If Flag = 1 then
    Select Campo1, Campo2 From Tabla Where Campo1 = @Campo1
    If Flag = 2 then
    Select Campo1, Campo2 From Tabla Where Campo2 = @Campo2

    Thank you very much.

    1. i agree with you bro :).
      your website is one of the best sql platform for me to learn sql online.

  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?

    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

  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

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

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

  7. Top SAP Online Course Providers

  8. Top SAP Online Course Providers

  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

  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

  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

  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.
    PEGA Training in Chennai | PEGA course in Chennai | PEGA Training center in Chennai | PEGA Training institute in Chennai

  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

  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
    oracle fusion procurement online training

  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

  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 ERP Providers in Chennai

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

  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

  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

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

  21. Oracle Fusion financials online training institutes is available at, 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

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


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

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

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


  26. your site is genuine to view sir
    thank you for your valluable information sir

  27. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Android Training in Chennai
    Ios Training in Chennai


  28. Nice post..Thanks for sharing it with ours..Looking forward for more articles like this in future..Very Informative...Loving it...!!!
    Visit us sap bi in dubai
    for more details..

  29. Your website content nice nice and interesting to observe.
    Programmierung in Lüdenscheid

  30. Are you searching for SAP Hybris Online Training from certified professional expert ?

  31. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion Financials Online Training

  32. Thanks for one marvelous posting! I enjoyed reading it; you are a great
    author. I will make sure to bookmark your blog and may come back
    someday. I want to encourage that you continue your great posts, have
    a nice weekend!

    java training in chennai

    java training in bangalore

  33. This blog is really useful and it is very interesting thanks for sharing, it is really good and exclusive.
    Best Summer Courses in Chennai|Best Summer Courses in Velachery

  34. This site has lots of advantage awesome really enjoyed reading thanks for sharing for grate info training

  35. Thanks for sharing the information, Salesforce experts a lot of openings in multi-level companies, for more information n
    Salesforce Training
    Instructor-LED Salesforce Online Training
    Professional Salesforce CRM Training

  36. Great Explanation and more useful for anyone.Thanks for sharing...
    oracle course in chennai

  37. you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Web Design Training

  38. It is very important to get your goods insurance while you are relocating. While several vendors provide insurance services as part of their services, some of them don’t. In due course, you will be able to contact us for your goods insurance requirements.
    packers and movers coimbatore
    packers and movers in nashik
    packers and movers in panchkula
    packers and movers in zirakpur
    packers and movers allahabad
    packers and movers in amritsar
    packers and movers mangalore
    packers and movers bilaspur
    packers and movers in ajmer
    packers and movers pondicherry

  39. Anyone reading this text must have come across the need for a home relocation. This requirement is typical of the city life andcareer-moves. Moving India provides comprehensive home relocation covering not only the furniture, fixtures and utensils but also your vehicle and pets.
    packers and movers satna
    packers and movers bhilai
    packers and movers in cuttack
    packers and movers in durgapur
    packers and movers in meerut
    packers and movers rajahmundry
    packers movers jodhpur
    packers and movers kakinada
    packers and movers korba
    packers and movers dhanbad

  40. Loading and unloading comes as a part and parcel of our moving services. In case you are particular about use of machinery for loading and unloading, you can get your option through one of our providers. For very professional and fully managed relocations, opt for our VManage Premium package.
    packers and movers thrissur
    packers and movers in belgaum
    packers and movers kharagpur
    packers & movers in nagpur
    packers and movers btm layout

  41. kingroot
    king root
    kingroot app
    kingroot download
    In case you are particular about use of machinery for loading and unloading, you can get your option through one of our providers.

  42. All are saying the same thing repeatedly, but in your blog I had a chance to get some useful and unique information, I love your writing style very much, I would like to suggest your blog in my dude circle, so keep on updates.
    RPA courses in Chennai
    RPA Training Institute in Chennai
    German Classes in Chennai
    German Language Classes in Chennai
    German Language Course in Chennai
    German Courses in Chennai

  43. Very inspiring post and very impressed to me. I want to more post related to this concept. I would you like to thanks for the grand post and keep it up...
    Ethical Hacking Course in Chennai
    Hacking Course in Chennai
    Ethical Hacking Training in Chennai
    Certified Ethical Hacking Course in Chennai

  44. Goyal packers and movers in Panchkula is highly known for their professional and genuine packing and moving services. We are top leading and certified relocation services providers in Chandigarh deals all over India. To get more information, call us.

    Packers and movers in Chandigarh
    Packers and movers in Panchkula
    Packers and movers in Mohali

  45. hi admin,
    Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision....
    URL :

  46. Wonderful post!!! These ideas are very nice and worthful content. I feel very good to read your great post and Thanks for your brief explanation. Well done and good job. I like more updates to your blog...
    Hadoop Admin Training in Chennai
    Hadoop Administration Training in Chennai
    Html5 Training in Chennai
    Drupal Training in Chennai
    Pega Training in Chennai
    Photoshop Classes in Chennai
    Hadoop Admin Training in OMR
    Hadoop Admin Training in Porur

  47. This comment has been removed by the author.

  48. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    Thanks & Regards,
    VRIT Professionals,
    No.1 Leading Web Designing Training Institute In Chennai.

    And also those who are looking for
    Web Designing Training Institute in Chennai
    SEO Training Institute in Chennai
    Photoshop Training Institute in Chennai
    PHP & Mysql Training Institute in Chennai
    Android Training Institute in Chennai

  49. Thank you for sharing your awesome and valuable article this is the best blog for the students they can also learn.

    Workday Online Training

  50. Hi, I am Anirudh. I am a makeup artist by profession. Click here to know about Maybelline Fit Me Foundation Review.

  51. Thanks for this grateful information. all this information is very important to all the users and can be used good at all this process.

    Best Web Designing Training Academy in Kanchipuram

  52. Your article was so impressive and informative. Its very interesting to read. Thanks for sharing,
    Best web design Training Institute in Kanchipuram

  53. Very interesting blog Really excellent information and thank you for giving your valuable information

    Workday HCM Online Training

  54. Thank you for sharing such a nice and interesting blog and really very helpful article

    Workday Online Training

  55. افضل شركة تنظيف بمكة المكرمهتلك التي تستخدم مواد تنظيف عالية الجودة وتعتمد على فريق عمل مدرب ومحترف

  56. An overwhelming web journal I visit this blog, it's unfathomably amazing. Unusually, in this present blog's substance made inspiration driving truth and reasonable. The substance of data is enlightening
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

  57. TreasureBox is operated by a group of young, passionate, and ambitious people that are working diligently towards the same goal - make your every dollar count, as we believe you deserve something better.
    Check out the best
    rattan outdoor furniture nz
    headboard nz
    laptop table nz

  58. One of the primary tools in traditional Chinese medicine, acupuncture is a safe and tested approach to holistic healing used to relieve pain, prevent disease, and restore balance in the body. Acupuncture involves the insertion of fine, sterile needles into specific locations of the body to elicit a therapeutic response. While some may be quick to assume that acupuncture is painful, many patients report a feeling of deep relaxation and increased energy both during and following treatment.
    physiotherapy calgary nw
    physiotherapy calgary
    Acupuncture Calgary nw
    Best Chiropractor Calgary nw
    sports massage calgary nw
    massage therapy nw calgary
    Exercise Therapy Calgary nw

  59. Acupuncture is designed to help facilitate blood flow, remove muscle obstructions, and relax the nervous system. Those who undergo treatment at our facility may experience a decrease in ongoing pain and improvement in sleep and digestive issues. At Beddington Physiotherapy Ltd, we’re committed to providing effective, comprehensive acupuncture treatments in a warm, welcoming environment. So if you’re thinking about acupuncture, we invite you to visit our facility to learn more.
    physiotherapy calgary
    physiotherapy nw calgary
    Acupuncture Calgary
    Physiotherapy Calgary NW
    spinal manipulation treamtment in Calgary
    massage therapy Calgary nw
    Exercise Therapy Calgary
    Exercise physiotherapy Calgary

  60. The mission of Nishtha is to promote that knowledge base along with ethics and good conduct in professionals, educate and orient them in the field of project management and develop skills in them that would then bring immense value in Organizations for management of their projects in a superior and successful manner.
    pmp training chennai
    ms project training in chennai
    pmp certification online
    pmp classes in chennai


  61. شركة تركيب طارد الحمام بالمدينة المنورة
    الآن توفر لكم شركة تركيب طارد الحمام بالمدينة المنورة أفضل انواع طوارد تمنع من وقوف الحمام على النوافذ والشرفات، على الرغم من أن البعض يعشق الحمام والطيور ويرغبون باللعب والمرح معهم بهدف التسلية، ولكن البعض الآخر يعتقد أن الحمام يعمل على نشر الأمراض مما يجعلهم يلجئءون إلى شركة تركيب طارد الحمام بالمدينة المنورة لكي تساعدهم على تركيب طارد يحد من وقوف الحمام على منازلهم ولكن لا تعمل تلك الطوارد على أذى الطيور أو قتلها، بل هي فقط تمنع من وجودهم على المنازل وهذا ما يحتاج له العميل.

  62. Thanks for sharing such a nice Blog.I like it.
    AVG support number

  63. This comment has been removed by the author.

  64. We At Cheam Airport Services Come Up With Top Quality, Highly Professional And Timely Services To Our All Important Clients Irrespective Of The Location.
    cheam cabs
    gatwick taxi
    taxi to stansted
    heathrow minicab
    luton airport taxi

  65. We At Epsom Airport Minicabs Services Come Up With Top Quality, Highly Professional And Timely Services To Our All Important Clients Irrespective Of The Location.
    cabs in epsom
    gatwick airport taxi
    stansted airport taxi
    heathrow cabs
    luton minicab

  66. We are a reputed airport transport providing company providing reliable taxi, Minibus & airport transfer services 24/7 in London.
    Cheap Airport Minibus
    Gatwick 7 Seater minibus
    Stansted Airport Minibus
    Heathrow 8 Seater minibus
    Airport minibus to epsom
    Cheam 9 Seater minibus

  67. Great blog thanks for sharing Looking for the best creative agency to fuel new brand ideas? Adhuntt Media is not just a digital marketing company in chennai. We specialize in revamping your brand identity to drive in best traffic that converts.

  68. Nice blog thanks for sharing Set up a aesthetic work environment that employees love to spend time in and relieve their stress. Your company needs the best corporate gardening service in Chennai and Karuna Nursery Gardens in happy to oblige you in the endeavour to make your infrastructure something worth flaunting about.

  69. Excellent blog thanks for sharing Run your salon business successfully by tying up with the best beauty shop in Chennai - The Pixies Beauty Shop. With tons of prestigious brands to choose from, and amazing offers we’ll have you amazed.

  70. Very interesting blog Really excellent information and thank you for giving your valuable information

    Oracle Fusion HCM Online Training