In order to provide an easy way to get insights about the waits, each wait is categorized to a resource.
PI Category | comments |
CPU | Wait Time (seconds) until the CPU resource is available. Time spent by the session waiting in the system's run queue for CPU cycles. The amount of time is dependent upon the number of concurrent processes and threads requesting CPU time. The metric value should be inspected in conjunction with the value of the Run Queue Length metric.
SOS_SCHEDULER_YIELD is categorized as ‘wait for CPU’. |
IO | Time spent waiting for disk input/output operations to complete. Input/output (I/O) is one of the most expensive operations in a database system. SQL statements that are I/O intensive can monopolize memory and disk use and cause other database operations to compete for these resources. Generally, I/O Wait is caused by poorly-tuned SQL queries or applications which generate a significant amount of logical I/O translating into excessive physical disk usage. In this case, SQL/application tuning can reduce the logical I/O- induced load. However, it could also be caused by poorly-configured disks or storage sub-systems. SQL PI considers PAGELATCH* events as IO. Log events are counted separately. |
Log | Time spent waiting by the various processes waiting for a log operation to complete. |
Network | Network related waits between the server and the client. |
Memory | Time spent waiting by the various processes waiting for the completion of a log operation. Time spent by the various sessions waiting for outstanding HTTP connections to complete and exit. |
Lock | Time spent waiting for blocking locks to be released. |
Latch | Time spent by the session being blocked by a latch, waiting for it to be released. Latches do not need to be locked for the duration of a transaction. They are low-overhead, short-term memory synchronization objects. They are used mostly to protect a row when queried for a connection. SQL PI excludes PAGELATCH* waits. |
CLR | Time spent by the statement waiting for CLR code execution to complete. CLR, which stands for Common Language Runtime, introduced in SQL Server 2005, allows hosting of the Microsoft .NET common language runtime engine within SQL Server. |
Remote Provider | The time spent by the various processes waiting for a remote OLEDB call to complete or DTC synchronization. |
XTP | In-Memory OLTP events. |
AlwaysON | In PI these events are subcategory of AlwaysON under Other. AlwaysON waits are the amount of time (in seconds) that the query waited for always on related events |
Mirror | In PI these events are subcategory ‘Mirror’ under the ‘Network’ category. Mirror waits are the amount of time (in seconds) that the query waited for Mirror related events |
Backup | In PI these events are subcategory ‘Backup’ under ‘Other’ category. Backup waits are the amount of time (in seconds) that the query waited for Backup/Recovery tasks to complete. |
Preemptive | In PI these events are subcategory ‘Misc’ under ‘Other’ category. |
Service Broker | In PI these events are subcategory ‘Service Broker’ under ‘Other’ category. This is the time spent by the various sessions waiting for Service Broker event handlers and endpoints. |
| Other | Time spent by the various sessions waiting for other database operations. |
| Parallel Coordination Wait | CXPACKET. The time spent by the various processes coordinating parallel query threads and exchanging data. If the server on which SQL Server is running has multiple CPU cores, SQL Server can run a single query in parallel, using multiple threads. In addition to running user queries on multiple processors, SQL Server can also use multiple threads to build indexes. Typically, queries that make heavy use of CPU cycles are good candidates for parallel execution. For example, a query joining several large tables and sorting the output before returning it to the user is likely to benefit from a parallel execution plan. |
