SQL Technology Insider -13 Analysis of the SQL Optimization methodology instance-level waits

The first step in the optimization methodology is to find out at the instance level what type of wait is taking up most of the wait time, which can be queried by the dynamic management diagram (dmv,dynamic management view) sys.dm_os_wait_stats

Running the query will return the wait information in your system and sort by type.

SELECT  wait_type,        waiting_tasks_count,        Wait_time_ms,        Max_wait_time_ms,        Signal_wait_time_ms From  sys.dm_os_wait_stats  ORDER by Wait_type

The DMV starts accumulating values from the last reboot of the server, and if you want to reset his value, run the code

DBCC SQLPERF (' sys.dm_os_wait_stats ', CLEAR)

• The DMV sys.dm_os_wait_stats contains attributes:

        1.wait_type,        2.waiting_tasks_count,   represents the total amount of time, in milliseconds, that the class waits for        3.wait_time_ms, which    contains signal_wait _time_ms)        4.max_wait_time_ms,        5.signal_wait_time_ms  It is the time difference between the waiting thread from the time it receives a signal notification to its start run. From the time that the thread collects the signals available to the resource, the thread gets CPU time and begins to use the resource location to go through. It can be thought that if the value of this property is high, it usually indicates that there is a problem with the CPU.

• I/O-related waits are the most common wait (for example, Iolatch wait), for several reasons, I/O is often the most expensive resource involved in data processing operations. Also, when a query or index is not well-set or optimized, the results are bound to cause a large amount of I/O. Database systems, not just CPU-focused, but also very robust I/O subsystems.
  • For network-related waits (for example: Async_network_io), their value is too high to indicate a possible network problem.

SQL Technology Insider -13 Analysis of the SQL Optimization methodology instance-level waits