Question: I’ve been reading about wait statistics and I see that most scripts people have published filter out some of the wait types. Why is that? Surely all the wait types can be problematic and should not be ignored?
Answer: The simple answer is that not all waits need to be analyzed when doing performance troubleshooting.
(As an aside, you can see an example script for analyzing wait statistics returned by the sys.dm_os_wait_stats DMV in my blog post.)
Given the way that SQL Server’s thread scheduling works, with concurrent operations in databases you’re always going to see waits occurring. The key when performance troubleshooting is to make sure you’re investigating waits that are relevant and are likely to be involved in your performance problems.
You may read some people online saying that every wait type can be a potential problem, and that’s true, but some of the wait types are only a problem in extremely rare circumstances. For these wait types, for the vast majority of the time it’s not worth having the results of the query to pull wait statistics polluted by these benign waits – and nearly all performance tuning experts agree on that.
For example, the WAITFOR wait is one of those that my query filters out. It only occurs when someone issues a WAITFOR DELAY statement and so including it in a query that aggregates and sorts waits to find the top waits by wait time would cause the results to become skewed such that actionable waits would be missed.
As another example, in SQL Server 2012 a bunch of new wait types were introduced in support of various features added by the release. There are two waits, DIRTY_PAGE_POLL and HADR_FILESTREAM_IOMGR_IOCOMPLETION, that occur very frequently and cause data skew as described above. The first is to do with the indirect checkpoints (which should really have been called ‘continuous checkpoints’, in my opinion) and the second is to do with AlwaysOn Availability Groups. I’ve added these to my list of usually benign waits to filter out as they occur even when the two features they pertain are not enabled.
As with any troubleshooting scenario, getting rid of information that might lead you on a wild-goose chase is always important, so I’ll always recommend filtering out those waits known to be generally benign.