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

137 comments:

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

    ReplyDelete
    Replies
    1. ExcelR- Data Science, Data Analytics, Business Analytics Course Training Mumbai
      learn data analytics course in mumbai and earn a global certification
      with minimal cost .
      for further details

      304, 3rd Floor, Pratibha Building. Three Petrol pump, Opposite Manas Tower, LBS Rd, Pakhdi, Thane West, Thane, Maharashtra 400602
      Hours:
      Open ⋅ Closes 10PM
      Phone: 091082 38354
      Appointments: excelr.com
      https://g.page/ExcelRDataScienceMumbai

      Delete
  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. Thanks for Sharing this valuble information and itis useful for me and CORE SAP learners.We also provides the best SAP Online Training

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

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

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

    ReplyDelete
  8. Keep working ,great job!
    Awesome post
    sap sd training

    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 sharing the valuable information about the topic for the further information visit our site
    Oracle Fusion Financials Training

    ReplyDelete
  17. 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
  18. 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
  19. and how to find memory usage after the query has been executed.

    ReplyDelete
  20. 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
  21. The best thing is that your blog really informative thanks for your great information!
    Web design institute chennai

    ReplyDelete

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

    ReplyDelete
  23. 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

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

    ReplyDelete
  25. 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

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

    ReplyDelete
  27. kingroot
    king root
    kingoroot
    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.

    ReplyDelete
  28. 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

    ReplyDelete
  29. 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



    ReplyDelete
  30. This comment has been removed by the author.

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

    ReplyDelete
  32. 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

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

    ReplyDelete
  34. 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

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

    ReplyDelete
  36. This comment has been removed by the author.

    ReplyDelete
  37. اجود وارخص الشركات التي توجد في منطقة مكة المكرمة والتي تعمل في مجال نقل العفش مع الفك والتركيب تلك الشركة الجيدة التي تقدم خدمات جيدة وتسمى افضل شركات نقل العفش بجدة ايضا وهي التي تختص بنقل العفش من بيت الى بيت آخر في مدينة جدة وما جاورها من مناطق تابعة لها مثل باقي مسميات شركات نقل الاثاث او نقل عفش في مدينة جدة

    ReplyDelete
  38. This comment has been removed by the author.

    ReplyDelete
  39. This comment has been removed by the author.

    ReplyDelete
  40. nice topic which you have choose.
    second is, the information which you have provided is better then other blog.
    so nice work keep it up. And thanks for sharing



    any one want to learn digital marketing visit us

    ReplyDelete
  41. I would like to read more of your posts. Very nice post thank you for sharing.
    Velachery Serviced apartments

    ReplyDelete
  42. I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
    Home Decor Services in Delhi NCR

    ReplyDelete
  43. I feel happy about and learning more about this topic. keep sharing your information regularly for my future reference. This content creates new hope and inspiration within me. Thanks for sharing an article like this. the information which you have provided is better than another blog.
    Proprietorship Firm Registration in India
    online company registration in india

    ReplyDelete
  44. Thanks for this nice post. Please keep posting like that.
    Mukul Sharma   When the film “Birds of Prey” was released on 07 Feb 2020, trade pundits projected it to gross $50 to $55 million during the opening weekend in the US and Canadian markets. Warner Bros, the distributors of the film had their own projection pegged at $45 million. However, It could muster only […]
    https://onlineidealab.com/warner-bros-loses-22-million-in-a-weekend-due-to-poor-seo/

    ReplyDelete
  45. Effective blog with a lot of information. I just Shared you the link below for Courses .They really provide good level of training and Placement,I just Had SQL Server DBA Classes in this institute,Just Check This Link You can get it more information about the SQL Server DBA course.


    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  46. Thanks for the wonderful share. Your article has proved your hard work and experience you have got in this field. Brilliant. I love it reading.I also would like to share with you about polo tshirts online.

    ReplyDelete
  47. This comment has been removed by the author.

    ReplyDelete
  48. Top Technologies to learn
    Excellent blog, good to see someone is posting quality information. Thanks for sharing this useful information. Keep up the good work.

    ReplyDelete
  49. Top Technologies to learn
    Excellent blog, good to see someone is posting quality information. Thanks for sharing this useful information. Keep up the good work.

    ReplyDelete


  50. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.i want to share about websphere online training .

    ReplyDelete
  51. hardware and networking question and answer
    Important Networking Interview Questions and Answers for freshers and experienced to get your dream job in Networking field! Basic & Advanced Networking Interview Questions for Freshers & Experienced.

    ReplyDelete
  52. http://www.practicalsqldba.com/2013/07/sql-server-performance-tuning_17.html?showComment=1542876770761#c6445476357531986417
    http://www.philadelphia-electricians-how-to.com/2016/04/our-work-is-guaranteed-this-work-was-not.html?showComment=1542877176704#c3234861179566329472

    ReplyDelete
  53. I could give you a long list of reasons why sales managers lack respect from their sales team and why their credibility is shot. However, I want to focus on the one problem that I see quite often. The quickest way for a sales manager to lose credibility with their sales team members is their lack of taking decisive action when dealing with poor sales performers. Salesforce training in Chennai

    ReplyDelete
  54. A successful career in Salesforce is an interesting question in the present IT trends. Salesforce training in Chennai

    ReplyDelete
  55. World is moving with lightning speed and super fast pace that creating many innovative and disruptive technologies which are eco friendly and undiscovered earlier. Salesforce training in Hyderabad

    ReplyDelete
  56. Thanks for providing a useful article containing valuable information. start learning the best online software courses.
    Sailpoint Certification
    Oracle Fusion HCM Training
    Workday Training
    AWS‌ ‌Data‌ ‌Engineering‌ Training

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

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

    ReplyDelete
  59. If You are genuine gentlemen want hot and sexy beautiful Delhi Teen Escorts lady or girls for your complete enjoyment and relaxations with body massage then contact my mobile number first when you call us then my manager asking you your choices for Escorts in Vasant Vihar depending on your needs means which type of staff you want. College, University, Models, High Profiles escorts in Delhi, Housewife, Russians and Celebrity Escorts Available For Fun. Very attractive, friendly behavior and safe & secure all facilities available in Delhi Escorts Service the best independent Punjabi Model escort in Aerocity - Accommodations:- If You have good place then we send your place like a hotel, home or guest houses if you have not a space we will provide you safe and secure neat and clean independent flat or five star hotels including madam available inside the rooms Delhi Escort Service

    ReplyDelete
  60. Great blog.thanks for sharing such a useful information
    BDD with Cucumber Online Training

    ReplyDelete
  61. Great blog. Thanks for sharing such a useful information.
    Java training chennai

    ReplyDelete
  62. How can a user get help from the robinhood support team?
    Are you searching to know how to get the help from Robinhood Support team if yes then stop the search because you have found the right site? At this site, you will know how to help from the robinhood support team but for this, you have to click on this given link.

    ReplyDelete
  63. Do you want to call Robinhood Support for account-related help?
    Do you want to call Robinhood support for account-related help if you want then you have come to the right place to get the number to call customer services of Robinhood. At this place, you will get the phone number to call Robinhood customer support and you have to do nothing for this except a click on this given link.

    ReplyDelete
  64. You’re so interesting! I don’t believe I’ve truly read something like this before. So great to find someone with genuine thoughts on this issue. Really.. many thanks for starting this up. This website is something that’s needed on the internet, someone with some originality!

    CBSE Schools In Ahmedabad
    CBSE Schools In Surat
    CBSE Schools In Rajkot
    CBSE Schools In Visakhapatnam
    CBSE Schools In Kangra
    CBSE Schools In Shimla
    CBSE Schools In Jammu
    CBSE Schools In Solan
    CBSE Schools In Mangalore
    CBSE Schools In Mysore

    ReplyDelete
  65. "After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article".
    Best Refrigerator Repair Service in Hyderabad

    ReplyDelete
  66. cool stuff you have and you keep overhaul every one of us data scientist course in kanpur

    ReplyDelete
  67. This post is so interactive and informative.keep update more information...
    Web Designing Course in Tambaram
    Web Designing Course in chennai

    ReplyDelete
  68. This post is so interactive and informative.keep update more information…
    graphic design courses in velachery
    graphic design courses in Chennai


    ReplyDelete
  69. I feel very grateful that I read this. It is very helpful and very informative and I learned a lot from it.
    cyber security course

    ReplyDelete
  70. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. data science training in surat

    ReplyDelete
  71. Wonderful blog post. This is absolute magic from you! I have never seen a more wonderful post than this one. You've really made my day today with this. I hope you keep this up! data science course in kanpur

    ReplyDelete
  72. I adore your websites way of raising the awareness on your readers. data science training in surat

    ReplyDelete
  73. Extremely gorgeous composed article. it'll be in settlement to all individuals who uses it, like me. hold achieve what you're placed it on canr stand by to acknowledgment additional posts. Gilisoft Secure Disk Creator Crack

    ReplyDelete
  74. I have a troublesome come to be past depicting my brain going a propos for content, but I practically felt I want to here. Your article is totally ubiquitous. I later the eccentricity you composed this spread. Microsoft Visio Crack

    ReplyDelete
  75. Need professional WordPress Web Design Services? We're experts in developing attractive mobile-friendly WordPress websites for businesses. Contact us today! https://just99marketing.com/wordpress-web-design

    ReplyDelete
  76. Great Post, thanks for sharing valuable information Java Training In Pune

    ReplyDelete


  77. India's Cyber ​​Security Solutions: Building a Resilient Digital Defense Force: In today's connected world, cyber security is paramount. Indian Cyber ​​​​Security Solutions is at the forefront of shaping ethical hacking professionals who can defend against evolving threats. Our training program covers the latest methodologies and gives you an edge in protecting critical information. Choose us and join the ranks of the vanguard of cyber security.

    ReplyDelete
  78. Are you ready to embark on a journey through the enchanting land of Rajasthan? Look no further than Rajasthali.org.in, your ultimate guide to exploring the vibrant culture, rich history, and breathtaking landscapes of this royal state. With its unique blend of tradition and modernity, Rajasthan offers a mesmerizing experience like no other, and Rajasthali.org.in is here to make sure you don't miss a single moment.

    Unveiling the Treasures of Rajasthan
    Rajasthali.org.in is your gateway to the wonders of Rajasthan. From the magnificent forts and palaces of Jaipur, Jodhpur, and Udaipur to the vast Thar Desert and its golden sand dunes, every corner of this magical state has something extraordinary to offer. With Rajasthali.org.in, you can explore these hidden gems, discovering the true essence of Rajasthan at every step.

    Tailor-Made Travel Experiences
    At Rajasthali.org.in, we believe in creating unforgettable memories. Our team of seasoned travel experts is dedicated to curating personalized itineraries that suit your preferences, ensuring a seamless and enjoyable journey. Whether you're an adventure seeker, a history enthusiast, or a nature lover, we have the perfect itinerary designed just for you.

    Immerse Yourself in Culture and Tradition
    Rajasthan is renowned for its vibrant culture, colorful festivals, and age-old traditions. With Rajasthali.org.in, you can immerse yourself in the rich tapestry of Rajasthani heritage. Witness the lively folk dances, indulge in the mouthwatering local cuisine, and interact with the warm and welcoming locals who will regale you with captivating tales of valor and romance.

    Luxury Accommodation and Exquisite Dining
    After a day filled with exploration, Rajasthali.org.in ensures that you experience the epitome of comfort and luxury. We handpick the finest hotels and resorts, offering you a royal treatment fit for a king or queen. Indulge in sumptuous Rajasthani delicacies, savor aromatic spices, and allow our expert guides to lead you on a culinary journey that will tantalize your taste buds.

    Unforgettable Camel Safaris and Desert Adventures
    No trip to Rajasthan is complete without a camel safari through the mesmerizing Thar Desert. Rajasthali.org.in organizes thrilling desert expeditions, where you can ride the majestic camels across golden dunes, witness mesmerizing sunsets, and experience the nomadic life of the desert dwellers. Let the desert winds whisper tales of an ancient land as you create memories that will last a lifetime.

    Book Your Rajasthan Adventure Today
    Rajasthali.org.in invites you to embark on a once-in-a-lifetime adventure through the heartland of Rajasthan. With our expertise and passion for this magnificent state, we guarantee an immersive and unforgettable experience. So, pack your bags, leave your worries behind, and let Rajasthali.org.in guide you on a journey through the majestic beauty of Rajasthan. Book your adventure today and unlock the wonders of this extraordinary destination!




    ReplyDelete
  79. I really enjoyed this article. I need more information to learn so kindly update it. Ziyyara Edutech’s dedicated and experienced tutors provide personalized one-on-one sessions, empowering students to excel in their academic journey.
    For more info Contact us: +91-9654271931, +971-505593798 or visit Online tutoring sites for class 11

    ReplyDelete
  80. Great experience I got good information from your blog. Tackle reading challenges head-on with our top-notch online phonics classes designed to eliminate common challenges in literacy development.
    visit phonics online classes

    ReplyDelete
  81. There's an underlying emphasis on empowerment, equipping readers with knowledge and insights that enable informed decision-making.

    calacatta marble in Sharjah

    ReplyDelete