"I do not know how to interpret this. Can you help me?"
My reply is 7 months late, but I will take a stab at answering (for the sake of the blog).
We do not know whether the presented metrics reflect a specific period of time during which a problem was known to be occurring (over the majority of that time period), whether the metrics reflect a period of time during which a much briefer problem period may have occurred, or whether the above metrics simply reflect has happened since SQL Server was last restarted or since these metrics were last cleared (AKA the metrics reflect no noticeable problem).
Wait types are accumulated by hundreds (if not a thousand) worker threads running in parallel. There are both interdependencies and independencies between wait types. What those threads are doing depends upon what T-SQL clients are submitting (and when), at any given moment.
It is important to concentrate upon the dm_os_wait_stats while a problem is happening, but it is equally important to avoid considering dm_os_wait_stats when no problem is happening. The caveat: It is nice to have baseline data (captured during a time when no problem was happening) for comparison purposes. No system (SQL Server, mechanical, or electrical) is infinitely fast - every system is always waiting on some wait_type. You will face a Herculaen task if your goal is to “eliminate all waits.” Invariably, it is a human judgment that decides when a piece of string (a wait type) is “too long”. Choose your battle wisely :).
BROKER_TASK_STOP == "Occurs when the Service Broker queue task handler tries to shut down the task. The state check is serialized and must be in a running state beforehand." Also see http://blogs.msdn.com/b/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx.
BROKER_RECEIVE_WAITFOR == "Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received." Also see http://blogs.msdn.com/b/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx.
WRITELOG == "Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits."
SOS_SCHEDULER_YIELD == "Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed."
BACKUPIO == "Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount."
PAGEIOLATCH_SH== "Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem."
The Broker% wait_time_s indicate this system's Service Broker should largely be idle. You should compare the above time period's data with another time period (by following "
Present Waits results for period" in above code example.)
The WRITELOG wait_time_s indicates that frequent log flushes are overwhelming (queuing) the logs' disks. See http://blogs.msdn.com/b/sqlsakthi/archive/2011/04/17/what-is-writelog-waittype-and-how-to-troubleshoot-and-fix-this-wait-in-sql-server.aspx (you will need to find that blog's referenced an d important SQLCAT document, because the SQLCAT team has moved their URLs to msdn).
The SOS_SCHEDULER_YIELD wait_time_s indicates threads must yield their scheduled run, so that other threads can take their turn at being run. You should compare the above time period's data with another time period (by following "
Present Waits results for period" in above code example.)
The BACKUPIO 12,172.66 wait_time_s is about 1/3rd of SOS_SCHEDULER_YIELD's 34,304.94 wait_time_s. I believe there is qualitative point where a DBA should not worry about "diminishing returns". Worrying about BACKUPIO can be "making a mountain out of a mole hill", especially when "there are bigger fish to fry". The same applies to PAGEIOLATCH_SH.
Assuming the dm_os_wait_stats metrics reflect a specific period of time during which a problem was persistently occurring (for the majority of the time period), I think you should solely concentrate upon the causes of WRITELOG. You can and should revisit the other wait types, with new data in hand, later (i.e. their ranked importance can change, after you address WRITELOG). For WRITELOG, the SQLCAT reference covers inadequate disk performance, while the above msdn blog covers the impact of excessively chatty singleton updates, deletes, or inserts. Use the perfmon counters identified within the SQLCAT reference to investigate inadequate disk performance. More broadly: Use sqlio to determine your disks' throughput capacities, and consider the system's dm_io_virtual_file_stats. Also for WRITELOG, the msdn blog's cause can be identified by looking for excessively "chatty" DML, using a trace that was caught *while* the WRITELOG waits were accruing (and concentrating upon the number of executions per tokenized DML statement).
Of course, if the data files, the log files, and the backups were all going to the same physical disk (perhaps a RAID 5 disk, with its parity disk write penalty), WRITELOG waits should not be surprising (and neither would be the SOS_SCHEDULER_YIELD, BACKUPIO, and PAGEIOLATCH_SH).
While it is possible to look at DMVs captures at time A, perfmon captures at time B, and traces at time C, the odds are heavily against your uncovering the true cause of a performance problem There is an old story about many blind men who were feeling an elephant and describing entirely different beasts. Similarly, many DBAs can describe many beasts, when those DBAs do not collect DMVs, perfmon, and traces all at the same time. Juggling those data collection tasks is no small feat :)