For looking wait statistics problems need to use
1
| SELECT * FROM sys.dm_os_wait_stats
|
For clear wait statistics neet to run this :
1
| DBCC SQLPERF(N'sys.dm_os_wait_stats',CLEAR)
|
The are a lot of wait types , most important are :
- CXPACKET - most common type, it's mean that we have parallel operation is SQL Server, this happens when when we have table scans.
- PAGEIOLATCH_XX - wait for data file page to be read into memory. Modes : SH - page will be read, EX - page will be changed
- ASYNC_NETWORK_IO - SQL Server waiting for client to send data.
- WRITELOG - waiting for transaction log for saving to disk.
- PAGELATCH_XX - waiting for access to in-memory data file page