Monday, April 25, 2011

Wait Stats and The Rest of the Story

In my interactions with customers and evaluators of SQL Sentry, I occasionally come across DBA's who have a disproportionate focus on SQL Server wait stats as their primary source for performance analysis.

I understand the attractiveness of wait stats as a way to monitor and improve query performance.  However, I think it is very important to note that while wait stats are a great place to start when looking for performance bottlenecks, very seldom do they provide a complete picture.  This is why our intelligent wait stats analysis is part of, but not the foundation of, our product.  There are many wait stats that have no relevance to performance at all and can tend to clutter the picture.  Others aren’t obvious as to what specific resource or issue they are related, or can even be related to multiple issues at the same time.

“What am I waiting on?” vs. “Why am I waiting?”

Wait stats tend to suggest what a query or queries are waiting for, but not why they are waiting.  In order to get the complete picture, wait stats need to be correlated with other metrics.  This means that ultimately, methods or tools that focus almost exclusively on wait stats routinely require the use of additional tools and manual effort to get to the root cause of an issue and resolve it.  This tends to become a rather inefficient means of performance tuning.

A common example we’ve seen in several environments involves a server showing high disk IO waits.  If you were to only look at wait stats, seeing frequent high disk IO waits would most likely lead you to suspect a bottleneck with the disk system on the server.  Unfortunately, it is quite possible that simply looking at these waits could lead you down the wrong path.  This is because, commonly, the root cause of the performance bottleneck on the server was not the disk system at all, but memory pressure.

Taking a look at the bigger picture and examining other metrics such as Page Life Expectancy quickly revealed that memory pressure was resulting in excessive paging and thrashing of the disk system, which in turn resulted in high Disk IO waits.  Using SQL Sentry, we could easily correlate a spike in Disk IO waits with a drop in page life expectancy, cache hit ratios, and increased read or write paging activity.  In a case like that, the real bottleneck was occurring in memory and the disk performance issues seen in the wait stats were merely a symptom of the issue rather than the cause.

High parallelism waits are another common issue that often is not as simple to diagnose and resolve as it may first seem.  In fact, CXPACKET waits are often the highest wait type on many systems.  Paul Randal (blog|twitter) conducted a survey to this effect recently on his blog at http://www.sqlskills.com/BLOGS/PAUL/post/Survey-what-is-the-highest-wait-on-your-system.aspx

First you need to know that CXPACKET waits refer to parallelism.  This is only one of many wait stats that aren’t readily obvious what they refer to by name alone.  Next, parallelism waits are not always so directly attributed to CPU bottlenecks as you might think.  Simply setting your MAXDOP may or may not improve the performance of the query running at the time of the wait stats collection, but that may still only be treating a symptom instead of the root cause.  I’ve seen that other resource pressure like disk or memory can actually lead to increased parallelism waits too.  Again, without a complete picture of your server’s performance and activity like SQL Sentry provides, you have no choice but to resort to additional tools and manual effort to track down and resolve the issue effectively.

Query Level Optimization

Common root causes of parallelism waits can be missing indexes, outdated statistics and generally inefficient queries.  Examining wait stats at the query level may tell you what the query was waiting on, but it still doesn’t tell you why.  I have found that execution plan analysis (one of the new features in SQL Sentry Version 6) is a much more effective means of investigating query performance than query-level wait statistics.  Our Plan Analysis features not only collect execution plans for long-running and poorly-performing queries, but also provide greatly expanded visibility into the plans themselves.  Since complex query plans can become extremely difficult to diagnose, having an effective tool to analyze them can be as important as having the plan in the first place! 

With SQL Sentry, I can use the combination of performance counter data, wait stats, and top SQL collection to identify my worst performing queries and resource bottlenecks, then dig deeper using Plan Analysis to resolve those issues as discussed in Product Manager Greg Gonzalez’s (blog|twitter) blog post at http://greg.blogs.sqlsentry.net/2011/02/sql-sentry-v6-plan-analysis-features.html.  All of this from one tool!

Hopefully this has served to shed some light on the pros and cons of wait stats analysis, and the thinking behind many of the features SQL Sentry provides.  Ultimately, everything we do is designed to make all our lives easier as SQL Server DBA's and developers.

30 comments:


  1. Hello, i think that i saw you visited my website thus i came to “return the favor”.
    I’m trying to find things to improve my website!I suppose its ok to use some of your ideas!!

    Have a look at my homepage; 풀싸롱
    (jk)

    ReplyDelete
  2. Thanks for sharing excellent information. Your web-site is so cool. I am impressed by the details that you’ve on this web site. It reveals how nicely you perceive this subject. Bookmarked this web page, will come back for extra articles 야한동영상

    ReplyDelete
  3. I assumed it is usually a preview to post in case others appeared to be having problems getting acquainted with nonetheless We're a little bit hesitant merely i'm permitted to decide to put companies plus covers for listed here 오피헌터

    ReplyDelete
  4. Your blog is so amazing. Information is very useful for everyone. such a amazing post please keep posting.

    횟수 무제한 출장

    ReplyDelete
  5. I can see that you are an expert at your field! I am launching a website soon, and your information will be very useful for me.. Thanks for all your help and wishing you all the success in your business 안마

    ReplyDelete
  6. I seriously love your website.. Excellent colors & theme.
    Did you create this amazing site yourself? Please reply back as I’m attempting to create my own site
    and want to know where you got this from or just what the theme is named.
    Cheers!

    Review my webpage - 휴게텔
    (freaky)

    ReplyDelete
  7. Thanks for sharing such great information. I also write about casino& sports betting online on my personal blog. kindly check out my page and learn more about online gambling! Thanks a lot admin!
    고스톱

    ReplyDelete
  8. Its like you read my mind! You seem to know a lot about this, like you wrote the book in it or something. I think that you can do with a few pics to drive the message home a bit, but instead of that, this is excellent blog. A great read. I’ll definitely be back.
    스포츠토토

    ReplyDelete
  9. Simply want to say your article is as astonishing. The clarity in your post is just great and i could assume you’re an expert on this subject. Well with your permission allow me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please continue the enjoyable work.
    성인웹툰

    ReplyDelete
  10. Hello, I think that I saw you visited my weblog so I came to “return the favor". I'm attempting to find things to enhance my website! I suppose its ok to use a few of your ideas!!
    토토사이트

    ReplyDelete
  11. Pretty section of content. I just stumbled upon your site and in accession capital to assert that
    I get actually enjoyed account your blog posts. Any way
    I’ll be subscribing to your feeds and even I achievement you access consistently quickly.
    바카라

    wep

    ReplyDelete
  12. All of our staff are highly sought after and highly experienced and professional handpicked ex-special forces and ex-military and provide bodyguard services
    the best protection in the industry. We mainly provide our services in London; however, we also service the rest of the United Kingdom and are available worldwide if necessary.

    ReplyDelete
  13. QuickBooks repair tool is used to remove all the Quickbooks errors. It can also be utilized to resolve any mistake that may arise when using QuickBooks Desktop. learn how to use this tool by using just simple methods.

    ReplyDelete
  14. Nice blog and unquestionably outstanding. You have a real ability for writing unique content. It was really good to work. I really like your thoughts. Thanks for the great info for the idea.

    사설토토
    카지노사이트
    파워볼

    ReplyDelete
  15. You have given great information. I couldn't imagine anything better than to peruse more on such themes. Thank you especially for it and carry on it.

    스포츠토토
    안전놀이터
    토토사이트

    ReplyDelete
  16. I will have to follow you, the information you bring is very real, reflecting correctly and objectively, it is very useful for society to grow together.

    스포츠토토
    카지노사이트
    파워볼게임
    바카라

    ReplyDelete
  17. during this covid-19, we need health care dissertation help to take care of our selves in this sick world. many people are not taking this seriously but when will we take things seriosly ? everything is not a joke or a part of life. sometimes you need to take action or take care of yourself to protect you and your family.

    ReplyDelete
  18. I can tell you're an authority in your subject! I'm about to establish a best travel agency for sweden, and your knowledge will be quite helpful. Thank you for all of your assistance, and best wishes for further success in your business.

    ReplyDelete
  19. Your blog is very interesting! I have a recommendation for you, Just click the link below:

    토토사이트
    온라인카지노
    파워볼사이트
    카지노사이트

    ReplyDelete
  20. Your blog is awesome! So much to learn! But hey, If your looking for awesome site Just click the following link below:

    스포츠토토
    안전놀이터
    토토사이트
    안전카지노사이트

    ReplyDelete
  21. Hi!!! I love your blog! Great content. Please do visit my webpage and learn more about online casino and sportstoto. Here's more interesting site:

    토토사이트
    카지노사이트
    파워볼사이트
    온라인카지노

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

    ReplyDelete
  23. 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. wecare riteaid com

    ReplyDelete
  24. thanks really effective list to boost up ranking in search engine.Surely this was the best comment of all time on blogs,
    pandoralistens net

    ReplyDelete
  25. Understudies who are seeking after their a-level investigations or gcse courses are thinking that it is hard to Pay To Write Research Paper do schoolwork expertly. They frequently need instant schoolwork papers to accomplish their scholarly objectives. In this way, we as an expert composing organization in the uk have different abstract scholars who know how to compose replies in schoolwork.

    ReplyDelete
  26. Looking for a foundation repair Edmonton ? You've come to the right place! At Rammafoundation, we specialize in repairing all types of foundations, including basement foundations, crawlspaces, and walk-in closets. We use the latest technology and equipment to get your foundation back in working order as quickly and efficiently as possible. Contact us today to schedule an appointment!

    ReplyDelete
  27. CV Nabil menyediakan Travel Bogor Bandung dan Bandung Bogor – Kami merupakan shuttle bogor – bandung dan sebalik nya. Dengan konsep menjemput door to door. Umum nya pada setiap orang, masalah waktu bisa dibilang merupakan satu pertimbangan yang utama dikarenakan faktor penentu, terlebih ada beberapa bagi mereka yang memang sangat dikejar kejar waktu atau memiliki jadwal yang sangat singkat.
    Travel Bogor Bandung Rental Mobil Bis

    ReplyDelete