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.

35 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 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. Your blog is very interesting! I have a recommendation for you, Just click the link below:

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

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

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

    ReplyDelete
  15. 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
  16. This comment has been removed by the author.

    ReplyDelete
  17. 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
  18. 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
  19. 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
  20. 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
  21. Such a nice blog. I read this story I appreciate your effort in this story. I get inspired by the writing skills of the writer. It's a really informative article for me. Now it's time to get Long distance taxi for more information.

    ReplyDelete
  22. Your stories consistently have outstanding content, as far as I can tell. The information in this article is very valuable to me, so thank you for offering it. Please get in touch with him if you ever need a psychology assignment writer for a psychology essay.

    ReplyDelete
  23. What a helpful post. This blog has a lot of interesting stuff, and the post is quite helpful. Thank you for sharing your knowledge and expertise.
    how to get a divorce in Virginia

    ReplyDelete
  24. embroidery digitizing online said...
    thanks for this interesting and informative article and this is very helpful for many people and once again thankyou and keep it up.

    ReplyDelete
  25. What an amazing post is this. The creative ideas behind is awesome. Thanks for sharing. Traffic Lawyer Henrico VA

    ReplyDelete
  26. Paying taxes demands accuracy, and processing taxes and completing tax returns is complicated Tax Advisory . Hiring a tax advisor may be the best course of action if your staff needs more room to manage your company's tax structure than your employment restrictions allow.

    ReplyDelete
  27. Thanks for sharing lt's a very impormative blog.
    virginia prenuptial lawyer

    ReplyDelete
  28. I'm delighted to see a discussion on wait stats, which are often a critical but overlooked aspect of system performance. While the topic primarily revolves around SQL Server and database systems, it's essential to recognize that power supplies, like the 39Y7408 - IBM 2900-Watts Power Supply for BladeCenter H, also play a crucial role in maintaining the overall health of your infrastructure.

    ReplyDelete
  29. embroidery digitizing said...
    Wait stats, often overshadowed in the realm of data analysis, hold the untold narrative of system performance. Beyond mere numbers, they unveil the intricate story of bottlenecks, inefficiencies, and hidden opportunities within databases. Understanding these stats isn't just about identifying delays; it's about deciphering the rest of the story. It's the key to optimizing processes, enhancing user experiences, and ultimately, unveiling a more efficient and responsive system. In this tale of data, wait stats serve as the breadcrumbs guiding us to uncover the full story, enabling smarter decisions and elevating performance to its peak potential.

    ReplyDelete
  30. Please stay us up to date like this. Thank you for sharing. abogado de dui cerca de mí va

    ReplyDelete
  31. A Reckless Driving Lawyer in Hanover, Virginia, is a crucial ally for individuals facing reckless driving charges. These legal professionals specialize in local traffic laws, providing expert advice and representation. Assessing case specifics, negotiating with the prosecution, and working to minimize fines and potential consequences are integral aspects of their expertise. With in-depth knowledge of Hanover's legal landscape, a skilled reckless driving lawyer offers vital support to protect driving privileges and mitigate the impact on individuals' records in the region.reckless driving lawyer hanover virginia

    ReplyDelete
  32. Wait stats are essential metrics in database management, revealing insights into the performance bottlenecks and resource utilization. These statistics track the time spent by a system waiting for resources like CPU, disk, or memory. Understanding wait stats allows administrators to pinpoint and address issues, optimizing database performance. As for the "rest of the story," it depends on the context. If referencing a narrative, it implies there's more to unfold.
    divorce center new york ny

    ReplyDelete