Wednesday, October 19, 2011

On The Right Track, or WAITing At The Station?

For the past several months, I have been aggressively tuning my production OLTP server.  We've also upgraded to a new "smart" SAN.  This is a big beefy box, 48 cores, 512GB of RAM, nearly enough to hold all of the production databases in memory.






The machine is NOT taxed, and I rarely hear complaints about performance.


My tuning efforts have been based strictly on metrics that I can collect from SQL Server itself.  The reason for writing this post is that I think I've hit the bottom of the barrel, and I'm soliciting opinions from the audience as to where to go next.

I've been collecting info on the wait stats for the past six months, as shown below.  Notice that for the majority of that time, SQL spent a lot of time waiting on I/O.  When the new SAN was introduced in late September, the I/O waits disappeared completely, replaced with LCK_M_IX.  At that point, I began focusing on missing indexes and high-read queries.  Over the past couple of week, I've eliminated several million reads PER HOUR from the machine's workload, and LCK_M_IX is no longer the top wait type.

I'm now looking at PREEMPTIVE_OS_PIPEOPS, which I believe is stemming from the use of xp_cmdshell for log-shipping and other file-manipulation tasks.  I know, I know, please don't lambaste me with the evils of xp_cmdshell, it is what it is for now.  The real question is, what can I do about this wait, and with an average wait of under 1 second, CAN I do anything about it?


StatsDateWaitTypeAverageWaitSeconds
2011-10-19 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.7953
2011-10-18 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.7464
2011-10-17 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.6384
2011-10-16 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.6897
2011-10-15 00:00:00.000LCK_M_IX2.4724
2011-10-14 00:00:00.000LCK_M_IX2.5408
2011-10-13 00:00:00.000LCK_M_IX2.6177
2011-10-12 00:00:00.000LCK_M_IX2.6951
2011-10-11 00:00:00.000LCK_M_IX2.7822
2011-10-10 00:00:00.000LCK_M_IX2.8687
2011-10-09 00:00:00.000LCK_M_IX2.9573
2011-10-08 00:00:00.000LCK_M_IX3.0560
2011-10-07 00:00:00.000LCK_M_IX3.1617
2011-10-06 00:00:00.000LCK_M_IX3.2784
2011-10-05 00:00:00.000LCK_M_IX3.3528
2011-10-04 00:00:00.000LCK_M_IX3.4955
2011-10-03 00:00:00.000LCK_M_IX3.6444
2011-10-02 00:00:00.000LCK_M_IX3.7869
2011-10-01 00:00:00.000LCK_M_IX3.9452
2011-09-30 00:00:00.000LCK_M_IX4.1280
2011-09-29 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.9454
2011-09-28 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.9050
2011-09-27 00:00:00.000LCK_M_IX5.2260
2011-09-26 00:00:00.000LCK_M_IX4.4864
2011-09-25 00:00:00.000LCK_M_IX2.7938
2011-09-24 00:00:00.000ASYNC_IO_COMPLETION162.4325
2011-09-23 00:00:00.000ASYNC_IO_COMPLETION175.7272
2011-09-22 00:00:00.000ASYNC_IO_COMPLETION194.2177
2011-09-21 00:00:00.000ASYNC_IO_COMPLETION219.2856
2011-09-20 00:00:00.000ASYNC_IO_COMPLETION257.5027
2011-09-19 00:00:00.000ASYNC_IO_COMPLETION319.3036
2011-09-18 00:00:00.000ASYNC_NETWORK_IO0.0486
2011-09-17 00:00:00.000LCK_M_IX1.2955
2011-09-16 00:00:00.000LCK_M_IX1.3107
2011-09-15 00:00:00.000LCK_M_IX1.3246
2011-09-14 00:00:00.000LCK_M_IX1.3365
2011-09-13 00:00:00.000LCK_M_X1.3894
2011-09-12 00:00:00.000LCK_M_X1.4316
2011-09-11 00:00:00.000LCK_M_X1.4661
2011-09-10 00:00:00.000LCK_M_X1.5037
2011-09-09 00:00:00.000LCK_M_X1.5487
2011-09-08 00:00:00.000LCK_M_X1.5983
2011-09-07 00:00:00.000LCK_M_X1.6526
2011-09-06 00:00:00.000LCK_M_X1.7105
2011-09-05 00:00:00.000LCK_M_X1.7661
2011-09-04 00:00:00.000LCK_M_X1.8230
2011-09-03 00:00:00.000LCK_M_X1.8831
2011-09-02 00:00:00.000LCK_M_X1.9520
2011-09-01 00:00:00.000LCK_M_X2.0417
2011-08-31 00:00:00.000LCK_M_X2.1422
2011-08-30 00:00:00.000ASYNC_IO_COMPLETION141.5480
2011-08-29 00:00:00.000ASYNC_IO_COMPLETION139.6930
2011-08-28 00:00:00.000ASYNC_IO_COMPLETION138.0323
2011-08-27 00:00:00.000ASYNC_IO_COMPLETION144.3802
2011-08-26 00:00:00.000ASYNC_IO_COMPLETION143.2951
2011-08-25 00:00:00.000ASYNC_IO_COMPLETION140.3102
2011-08-24 00:00:00.000ASYNC_IO_COMPLETION136.7581
2011-08-23 00:00:00.000ASYNC_IO_COMPLETION132.0145
2011-08-22 00:00:00.000ASYNC_IO_COMPLETION128.7737
2011-08-21 00:00:00.000ASYNC_IO_COMPLETION122.5329
2011-08-20 00:00:00.000ASYNC_IO_COMPLETION133.8007
2011-08-19 00:00:00.000LCK_M_IX1.1325
2011-08-18 00:00:00.000LCK_M_IX0.9612
2011-08-17 00:00:00.000LCK_M_IX0.9091
2011-08-16 00:00:00.000ASYNC_IO_COMPLETION87.3158
2011-08-15 00:00:00.000ASYNC_IO_COMPLETION93.7826
2011-08-14 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.7019
2011-08-13 00:00:00.000ASYNC_IO_COMPLETION147.0161
2011-08-12 00:00:00.000ASYNC_IO_COMPLETION146.5126
2011-08-11 00:00:00.000ASYNC_IO_COMPLETION145.8625
2011-08-10 00:00:00.000ASYNC_IO_COMPLETION145.1408
2011-08-09 00:00:00.000ASYNC_IO_COMPLETION144.3159
2011-08-08 00:00:00.000ASYNC_IO_COMPLETION143.3599
2011-08-07 00:00:00.000LCK_M_IX1.6269
2011-08-06 00:00:00.000LCK_M_IX1.6530
2011-08-05 00:00:00.000LCK_M_IX1.6721
2011-08-04 00:00:00.000ASYNC_IO_COMPLETION145.4297
2011-08-03 00:00:00.000ASYNC_IO_COMPLETION144.5607
2011-08-02 00:00:00.000ASYNC_IO_COMPLETION143.3563
2011-08-01 00:00:00.000ASYNC_IO_COMPLETION141.6229
2011-07-31 00:00:00.000ASYNC_IO_COMPLETION140.4128
2011-07-30 00:00:00.000ASYNC_IO_COMPLETION146.8009
2011-07-29 00:00:00.000ASYNC_IO_COMPLETION145.1902
2011-07-28 00:00:00.000ASYNC_IO_COMPLETION143.2194
2011-07-27 00:00:00.000ASYNC_IO_COMPLETION141.1237
2011-07-26 00:00:00.000ASYNC_IO_COMPLETION138.9945
2011-07-25 00:00:00.000ASYNC_IO_COMPLETION136.0320
2011-07-24 00:00:00.000ASYNC_IO_COMPLETION133.2664
2011-07-23 00:00:00.000ASYNC_IO_COMPLETION145.1821
2011-07-22 00:00:00.000ASYNC_IO_COMPLETION142.6912
2011-07-21 00:00:00.000ASYNC_IO_COMPLETION139.1670
2011-07-20 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.9265
2011-07-19 00:00:00.000ASYNC_IO_COMPLETION125.2301
2011-07-18 00:00:00.000ASYNC_IO_COMPLETION104.5218
2011-07-17 00:00:00.000LCK_M_S2.4249
2011-07-16 00:00:00.000ASYNC_IO_COMPLETION142.5515
2011-07-15 00:00:00.000ASYNC_IO_COMPLETION141.9216
2011-07-14 00:00:00.000ASYNC_IO_COMPLETION141.1482
2011-07-13 00:00:00.000ASYNC_IO_COMPLETION140.3958
2011-07-12 00:00:00.000ASYNC_IO_COMPLETION139.6792
2011-07-11 00:00:00.000ASYNC_IO_COMPLETION137.5899
2011-07-10 00:00:00.000ASYNC_IO_COMPLETION136.7927
2011-07-09 00:00:00.000ASYNC_IO_COMPLETION140.4587
2011-07-08 00:00:00.000ASYNC_IO_COMPLETION139.4960
2011-07-07 00:00:00.000ASYNC_IO_COMPLETION138.4204
2011-07-06 00:00:00.000ASYNC_IO_COMPLETION137.2699
2011-07-05 00:00:00.000LCK_M_SCH_S27.7115
2011-07-04 00:00:00.000LCK_M_SCH_S28.2566
2011-07-03 00:00:00.000LCK_M_SCH_S29.0291
2011-07-02 00:00:00.000LCK_M_SCH_S29.5383
2011-07-01 00:00:00.000LCK_M_SCH_S30.1864
2011-06-30 00:00:00.000LCK_M_SCH_S33.7304
2011-06-29 00:00:00.000LCK_M_SCH_S34.4017
2011-06-28 00:00:00.000LCK_M_SCH_S35.2142
2011-06-27 00:00:00.000LCK_M_IX5.4730
2011-06-26 00:00:00.000LCK_M_IX5.7981
2011-06-25 00:00:00.000LCK_M_IX6.2078
2011-06-24 00:00:00.000LCK_M_IX6.7215
2011-06-23 00:00:00.000LCK_M_IX7.5673
2011-06-22 00:00:00.000LCK_M_IX8.7047
2011-06-21 00:00:00.000LCK_M_IX10.0213
2011-06-20 00:00:00.000LCK_M_IX2.6511
2011-06-19 00:00:00.000LCK_M_SCH_S1664.2028
2011-06-18 00:00:00.000ASYNC_IO_COMPLETION144.0897
2011-06-17 00:00:00.000ASYNC_IO_COMPLETION143.3284
2011-06-16 00:00:00.000ASYNC_IO_COMPLETION142.8788
2011-06-15 00:00:00.000ASYNC_IO_COMPLETION142.4000
2011-06-14 00:00:00.000ASYNC_IO_COMPLETION141.5003
2011-06-13 00:00:00.000ASYNC_IO_COMPLETION140.9441
2011-06-12 00:00:00.000ASYNC_IO_COMPLETION140.5529
2011-06-11 00:00:00.000ASYNC_IO_COMPLETION143.8691
2011-06-10 00:00:00.000ASYNC_IO_COMPLETION143.4363
2011-06-09 00:00:00.000ASYNC_IO_COMPLETION142.9559
2011-06-08 00:00:00.000ASYNC_IO_COMPLETION142.1377
2011-06-07 00:00:00.000ASYNC_IO_COMPLETION141.1368
2011-06-06 00:00:00.000LCK_M_IX1.5562
2011-06-05 00:00:00.000LCK_M_IX1.6175
2011-06-04 00:00:00.000LCK_M_IX1.6823
2011-06-03 00:00:00.000LCK_M_IX1.7465
2011-06-02 00:00:00.000LCK_M_IX1.8304
2011-06-01 00:00:00.000LCK_M_IX1.8280
2011-05-31 00:00:00.000LCK_M_IX1.8692
2011-05-30 00:00:00.000LCK_M_IX1.9600
2011-05-29 00:00:00.000LCK_M_IX2.0590
2011-05-28 00:00:00.000LCK_M_IX2.1797
2011-05-27 00:00:00.000LCK_M_IX2.2281
2011-05-26 00:00:00.000ASYNC_IO_COMPLETION143.3396
2011-05-25 00:00:00.000ASYNC_IO_COMPLETION142.4637
2011-05-24 00:00:00.000ASYNC_IO_COMPLETION141.2364
2011-05-23 00:00:00.000ASYNC_IO_COMPLETION139.2355
2011-05-22 00:00:00.000ASYNC_IO_COMPLETION136.9468
2011-05-21 00:00:00.000ASYNC_IO_COMPLETION150.4211
2011-05-20 00:00:00.000ASYNC_IO_COMPLETION148.4708
2011-05-19 00:00:00.000ASYNC_IO_COMPLETION145.4053
2011-05-18 00:00:00.000ASYNC_IO_COMPLETION141.5062
2011-05-17 00:00:00.000ASYNC_IO_COMPLETION134.0149
2011-05-16 00:00:00.000PREEMPTIVE_OS_PIPEOPS0.6527
2011-05-15 00:00:00.000PREEMPTIVE_OS_WRITEFILE0.0222
2011-05-14 00:00:00.000ASYNC_IO_COMPLETION158.1498
2011-05-13 00:00:00.000ASYNC_IO_COMPLETION157.9670
2011-05-12 00:00:00.000ASYNC_IO_COMPLETION157.6677
2011-05-11 00:00:00.000LCK_M_IS8.3349
2011-05-10 00:00:00.000LCK_M_IS8.5990
2011-05-09 00:00:00.000LCK_M_IS9.0284
2011-05-08 00:00:00.000LCK_M_IS9.3260
2011-05-07 00:00:00.000LCK_M_IS9.5627
2011-05-06 00:00:00.000LCK_M_IS9.8709
2011-05-05 00:00:00.000ASYNC_IO_COMPLETION161.0149
2011-05-04 00:00:00.000ASYNC_IO_COMPLETION161.0506
2011-05-03 00:00:00.000ASYNC_IO_COMPLETION161.0995
2011-05-02 00:00:00.000ASYNC_IO_COMPLETION160.7030
2011-05-01 00:00:00.000ASYNC_IO_COMPLETION160.0951
2011-04-30 00:00:00.000ASYNC_IO_COMPLETION167.4388
2011-04-29 00:00:00.000ASYNC_IO_COMPLETION167.1990
2011-04-28 00:00:00.000ASYNC_IO_COMPLETION166.7961
2011-04-27 00:00:00.000ASYNC_IO_COMPLETION166.4862
2011-04-26 00:00:00.000ASYNC_IO_COMPLETION164.8733
2011-04-25 00:00:00.000ASYNC_IO_COMPLETION163.0835
2011-04-24 00:00:00.000ASYNC_IO_COMPLETION161.0020
2011-04-23 00:00:00.000ASYNC_IO_COMPLETION175.6034
2011-04-22 00:00:00.000ASYNC_IO_COMPLETION173.2586
2011-04-21 00:00:00.000ASYNC_IO_COMPLETION169.3020
2011-04-20 00:00:00.000ASYNC_IO_COMPLETION163.5116
2011-04-19 00:00:00.000ASYNC_IO_COMPLETION152.1601
2011-04-18 00:00:00.000ASYNC_IO_COMPLETION125.0617
2011-04-17 00:00:00.000LCK_M_S28.2886
2011-04-16 00:00:00.000ASYNC_IO_COMPLETION193.3536
2011-04-15 00:00:00.000ASYNC_IO_COMPLETION193.2422
2011-04-14 00:00:00.000ASYNC_IO_COMPLETION192.9083
2011-04-13 00:00:00.000ASYNC_IO_COMPLETION192.3132
2011-04-12 00:00:00.000ASYNC_IO_COMPLETION191.5557
2011-04-11 00:00:00.000ASYNC_IO_COMPLETION190.9004
2011-04-10 00:00:00.000ASYNC_IO_COMPLETION190.2397
2011-04-09 00:00:00.000ASYNC_IO_COMPLETION195.3725
2011-04-08 00:00:00.000ASYNC_IO_COMPLETION194.3511
2011-04-07 00:00:00.000ASYNC_IO_COMPLETION192.7628
2011-04-06 00:00:00.000ASYNC_IO_COMPLETION191.9506
2011-04-05 00:00:00.000ASYNC_IO_COMPLETION190.2253
2011-04-04 00:00:00.000ASYNC_IO_COMPLETION189.0877
2011-04-03 00:00:00.000ASYNC_IO_COMPLETION187.8966
2011-04-02 00:00:00.000ASYNC_IO_COMPLETION194.9759
2011-04-01 00:00:00.000ASYNC_IO_COMPLETION194.0448
2011-03-31 00:00:00.000ASYNC_IO_COMPLETION192.6620
2011-03-30 00:00:00.000ASYNC_IO_COMPLETION191.2033
2011-03-29 00:00:00.000ASYNC_IO_COMPLETION189.8906
2011-03-28 00:00:00.000ASYNC_IO_COMPLETION188.2785
2011-03-27 00:00:00.000ASYNC_IO_COMPLETION186.0158
2011-03-26 00:00:00.000ASYNC_IO_COMPLETION197.8071
2011-03-25 00:00:00.000ASYNC_IO_COMPLETION196.3743

0 comments:

Post a Comment