In perfmon, this is accounted for with "SQLServer:Wait Statistics(*)\Log buffer waits". To gauge if transaction log write performance is a bottleneck for high data rate ETL, I typically use "Waits started per second". Sometimes I use "Waits started per second" in conjunction with "(Average wait time (ms)".
Some values are obviously bogus - various flavors of 1.84E+19 such as 18446744073709500000 are among my favorites
But today among the useful values in the range from 0 to 301 on a 6 vcpu system, I see an abundance of these values, too.
38894
40103
40108
40111
40404
40711
40714
40736
40737
40738
40739
Anyone else seeing bogus values in the SQL Server wait-related perfmon numbers? Is there something clever to eliminate these bogus values - maybe ensuring that 64 bit perfmon is launched, or some other trick?
******
5761 values collected, in 15 second increments. This was the distribution. I'll look through several more days worth of data. the 1.84E+19 value is pretty easy to discard - even moreso if it always reports 0 as an average wait time. All of the bogus values 38894 to 40739 report 49 ms as the average wait time. Hmmm... so I may be able to discard values in a range with a particular reported wait time.
Average Wait Time | Waits Started in 15 Seconds | Occurrences |
0 | 0 | 3488 |
0 | 1 | 1 |
0 | 1.84467E+19 | 722 |
14 | 4 | 1 |
19 | 24 | 1 |
21 | 60 | 1 |
22 | 3 | 1 |
24 | 13 | 1 |
24 | 301 | 1 |
25 | 8 | 1 |
28 | 14 | 1 |
29 | 3 | 1 |
29 | 4 | 1 |
32 | 152 | 1 |
34 | 3 | 1 |
34 | 12 | 1 |
34 | 25 | 1 |
35 | 6 | 1 |
37 | 4 | 1 |
38 | 4 | 1 |
45 | 7 | 1 |
46 | 8 | 1 |
49 | 38894 | 718 |
49 | 40103 | 11 |
49 | 40108 | 2 |
49 | 40111 | 3 |
49 | 40404 | 1 |
49 | 40711 | 2 |
49 | 40714 | 103 |
49 | 40736 | 18 |
49 | 40737 | 126 |
49 | 40738 | 7 |
49 | 40739 | 525 |
53 | 5 | 1 |
55 | 3 | 1 |
56 | 47 | 1 |
58 | 4 | 1 |
62 | 14 | 1 |
63 | 2 | 1 |
67 | 9 | 1 |
71 | 1 | 1 |
106 | 85 | 1 |
115 | 17 | 1 |
119 | 1 | 1 |
134 | 1 | 1 |
166 | 4 | 1 |
177 | 2 | 1 |
184 | 4 | 1 |
When I asked #sqlhelp about this, Paul Randal indicated (rightly so) that there are more reliable methods of gathering this information - such as extended events and the SQL Server wait DMV. I use the wait DMV pretty extensively, too - usually in 5 minute increments. But I also have a need for something very lightweight, minimally invasive (particularly requiring low privileges), and easily deployable across numerous systems - so I'll still pursue this a bit.
No comments:
Post a Comment