asked
Analysing Wait Stats
Hi there, Are these wait stats good or bad? Is there anything I can do to improve them? WaitType Wait_S Resource_S Signal_S WaitCount Percentage CXPACKET 4559990.57 4408210.42 151780.16 1345553902 69.32 LATCH_EX 432576.65 417746.66 14829.99 187055483 6.58 WRITELOG 366045.00 363659.99 2385.01 31203883 5.56 PREEMPTIVE_OS_FILEOPS 320012.23 320012.23 0.00 197033862 4.86 ASYNC_NETWORK_IO 226064.30 221570.16 4494.14 115503756 3.44 IO_COMPLETION 116366.25 116328.10 38.15 5488961 1.77 BACKUPTHREAD 112862.60 112847.47 15.14 36063 1.72 LCK_M_U 93283.01 92943.79 339.21 3716100 1.42 PREEMPTIVE_OS_CREATEFILE 68301.61 68301.61 0.00 1772140 1.04 TIA [UPDATE: 20170613] 5 weeks later the wait stats are now: -- WaitType Wait_S Resource_S Signal_S WaitCount Percentage CXPACKET 10812596.43 10526923.84 285672.59 1958950859 36.51 PREEMPTIVE_OS_FILEOPS 6817267.81 6817267.81 0.00 1453847924 23.02 OLEDB 5543525.85 5543525.85 0.00 893989879 18.72 LATCH_EX 1508763.90 1491463.71 17300.19 218026340 5.10 PREEMPTIVE_OS_CREATEFILE 1261392.29 1261392.29 0.00 9710362 4.26 PAGELATCH_EX 603545.43 158418.64 445126.79 251608675834 2.04 WRITELOG 563611.07 560343.45 3267.61 37238422 1.90 PREEMPTIVE_OS_CLOSEHANDLE 422626.28 422626.28 0.00 12289488 1.43 BACKUPTHREAD 349340.25 349298.75 41.51 73099 1.18 ASYNC_NETWORK_IO 297021.83 291496.82 5525.01 149418788 1.00
There's a lot of common, 'normal' wait types in that list, so I wouldn't be too worried. A full list of wait types can be found at [Paul Randal's SQL Server Wait Types Library][1] CXPACKET ([simply indicating that parallelism is happening][2]) is accounting for 69% of your wait types (in that sample). That might be high for your workload. If it's OLTP then that might mean 1. cost threshold for parallelism is not [configured correctly][3]. The default of 5 is seen as too low for most workloads today. 2. MAXDOP is not [configured correctly][4] 3. Queries have a cost greater than the cost threshold for parallelism and so may be going parallel. Are the correct indexes in place? Are they handling too much data - selecting too many columns, select *, too many unfiltered rows? 4. Parallel queries are [skewed][5] What's normal? If CXPACKET 'normally' accounts for 20% and now it's showing 69% - then yes, something has introduced a change in behaviour and may need further investigation. It's better to take readings of wait stats at regular intervals to spot these changes. [1]:
https://www.sqlskills.com/help/waits/ [2]:
https://sqlperformance.com/2015/06/sql-performance/knee-jerk-wait-statistics-cxpacket [3]:
http://www.scarydba.com/2017/03/13/change-the-cost-threshold-for-parallelism/ [4]:
https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-configuration-option-in-sql-server [5]:
https://sqlperformance.com/2015/08/sql-performance/more-on-cxpacket-waits-skewed-parallelism
17 People are following this question.
