As soon as he showed me the report I knew he had an outdated script.
Each new version of SQL Server introduces new wait types, often (but not always) related to new features. For example, waits related to AlwaysOn (the feature formerly known as HADRON) have a prefix of HADR.
The wait at the top of his list, HADR_FILESTREAM_IOMGR_IOCOMPLETION means "The FILESTREAM AlwaysOn I/O manager is waiting for I/O completion" - the catch is the AlwaysOn I/O manager is almost always waiting for I/O completion as data flows back and forth - this isn't indicative of any issue, so it can be excluded.
Paul Randal (@PaulRandal/blog) keeps a list of the "excludable" wait types in a blog post he maintains related to wait stats. The post includes a version of the Wait Stats script from Glenn Berry's (@GlennAlanBerry/blog) DMV scripts, and that Wait Stats script has a large list of exclusions:
WHERE [wait_type] NOT IN (If you refer back to my colleague's original screen shot, you'll see that the top ten items on his list - everything above "OLEDB" - is on the excludable list!
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_SHUTDOWN_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
--
The broader lesson here is to make sure you update your script libraries regularly - even if a script still runs and provides output (that is, you think it "works") it doesn't mean you are receiving valid data.
Although this example is about wait stats and wait types, it is applicable to a wide array of configurations and settings. Changes like this are often version-related, but even within a version it can be decided that a particular wait type/trace flag/sp_configure setting/etc. is no longer important and can be ignored - or even worse, that some item is now important but wasn't included in your original scripts!
A little regular maintenance and ongoing research will help your toolbox stay clean and organized.
 |
| http://s421.photobucket.com/user/48548/media/tools/IMG_3765.jpg.html |
Hope this helps!
No comments:
Post a Comment