Survey: what is the highest wait on your system?

I've recently been creating some content about wait stats analysis and I think it would be really interesting to see what kind of waits people are seeing out there in the wild. Hopefully it'll also introduce a bunch of people to the waits-and-queues performance troubleshooting methodology and how it can be really useful to them.

[Edit: comments are closed on this post as I have all the info I need – thanks!]

Here's what I'd like you to do:

  • Run the T-SQL query from the end of this post. It's completely benign and only reports on statistics that SQL Server is already gathering. It will not cause any perf issues on your production systems.
  • Look at the output – it'll be something like (from random anonymous client system):

 

  • Look at the top WaitType and fill in the simple survey below. If your top wait isn't listed, click on Other and cut-and-paste the top WaitType into the box provided.


The free survey system only allows a single vote per IP address – if you have any other results, send them in email (mailto:paul@SQLskills.com?Subject=Wait stats) or attach a comment below. 

In a week or two I'll report on the results. It would be great to get a few hundred responses. 

Thanks!

T-SQL code to run (works on 2005 onwards) – based on code by Glenn Berry:

WITH Waits AS
    (SELECT
        wait_type,
        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     )
SELECT
     W1.wait_type AS WaitType,
     CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
     CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
     CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
     W1.WaitCount AS WaitCount,
     CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
     ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) – W1.Percentage < 95; — percentage threshold
GO

Invitation to participate in T-SQL Tuesday #12 – Why are DBA skills necessary?

In November we're doing T-SQL Tuesday a week early (Tuesday November 2nd) so that it doesn't clash with PASS and I'll be your host!

What is T-SQL Tuesday?

If you haven't seen it yet, T-SQL Tuesday is the brain-child of fellow-MVP Adam Machanic (blog|twitter). It happens once a month on the 2nd Tuesday (different this month) and is hosted by a different person in the SQL community each time. The idea is that it's kind of a rallying point to get a bunch of people to blog about a particular topic on the same day, giving a wide and varied set of opinions and interpretations of that month's topic. The hoster then posts a wrap-up commenting on all the contributions. I think it's a great idea and I contribute whenever I have time.

What is the topic for November?

This month I'd like to step back from the deep technical stuff and ask "why are DBA skills necessary?"

I don't want to color people's opinions by giving my own yet, but some things to consider are:

  • What problems have you seen in your career that could have been avoided with some DBA skills?
  • At what point does a SQL Server installation need a real DBA to look after it?
  • How could DBA input help prevent design problems in data applications?
  • Should there be cross-over been developer skills and DBA skills? What about architects? Storage admins?
  • How can business continuity be affected by lack of DBA skills?
  • How much can we rely on auto-tuning to ensure performant work loads?
  • Is Microsoft doing enough to foster DBA skills as a point of excellence?
  • What about on other RDBMS platforms? What about no-SQL?

I could go on for hours… I'm really looking forward to seeing where you take this topic and I'm expecting some great posts.

What are the rules?

As with any organized event, there are inevitably some rules that help make things run smoothly.

  • You most post your contribution between Tuesday November 2nd 00:00:00 GMT and Wednesday November 3rd 00:00:00 GMT (i.e. Monday 8pm-Tuesday 8pm EST, Monday 5pm- Tuesday 5pm PST, etc)
  • Note: there was a daylight-savings change on October 31st in Europe. I will accept posts from 4pm PST Monday to 5pm PST Tuesday as even I made that mistake :-)
  • Your post has to link back to the hosting blog, and the link must be anchored from the logo (found above) which must also appear at the top of the post.
  • Leave a comment here (below) or I won’t be able to find your post. I expect trackbacks work properly, but if they don’t check back here just in case and leave a comment if necessary.

Sample HTML to include the logo and link-back is:

<a href="http://www.sqlskills.com/blogs/paul/post/Invitation-to-participate-in-T-SQL-Tuesday-12-e28093-Why-are-DBA-skills-necessary.aspx"><img src="http://www.SQLskills.comwp-content/uploads/2010/7/TSQL2sDay150x150.jpg" border="0" alt="" width="150" height="150" /></a>

NOTE: THIS DOES NOT DO A 'TRACKBACK' – YOU WILL NEED TO ADD A LINK IN A COMMENT ON THIS POST.

It would also be really nice if you…

  • Include a reference to T-SQL Tuesday in the title of your post. (The more we bloggers advertise T-SQL Tuesday, the more we bloggers get T-SQL tuesday readers)
  • Tweet using the hash tag #TSQL2sDay to follow links and other relevant conversations.
  • Consider hosting T-SQL Tuesday yourself. If you’re interested let Adam Machanic Know. If you’ve participated in two T-SQL Tuesdays previously and you don’t let your blog go stale (blog once a month for the last six months) then he’ll put you in the rotation.

Summary

Don't fret about the rules, it's nothing too complex. If you're having trouble linking properly on the day, drop me an email (paul@SQLskills.com) or tweet (@PaulRandal) and I'll help you out – I'll be watching things all day.

As I said, I'm really looking forward to seeing a really diverse set of posts and I'll very quickly post a long summary with lots of editorial commentary and opinions too.

Have fun!

Myths and misconceptions: 60-page PDF of blog posts

Back when I did the DBA-Myth-A-Day series in April, many people asked for the 30 blog posts to be collected up into a document for easy printing/reading/etc. I promised, but never got around to it, until I had an email from a blog reader, Peter Maloof, who had very kindly done all the work of producing a Word doc with everything collected together. I’ve prettified the doc and produced a PDF which you can print out and redistribute as you see fit.

You can download the PDF once you sign up for our bi-weekly newsletter here (no advertizing or other nonsense), and once you’ve signed up, you can always get it from the past newsletters page (link in every newsletter). Note: some people get confused: when you complete the sign-up process, the final welcome email you get has the link to the prior newsletters and the PDF.

Make sure to also check out my 4-hour online training course on Pluralsight: SQL Server Myths and Misconceptions.

Enjoy!