Wait Statistics

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