I/O, Input/Output, Reads/Writes what ever you want to call it. It is one of the most aggravating aspects of SQL Server. The physical spinning hard drives of your SAN can bring your entire database server to its knees, no matter how powerful a beast of a server you have.
When you as the DBA are at the mercy of the SAN Administrator (or better yet 3rd party SAN administrator), your hands are somewhat tied on configuration changes. So what do you do? Bang your head against a wall and shout, “It’s not my fault, it’s not my fault!” Then the big burly guys in white shirts will come visit your cubicle, ask you some questions and then take you on a little “vacation”.
That’s not the path I want to go down.
Not having any type of 3rd party monitoring solution, I needed a way to prove which drive was having trouble and just how much work it had to do!
Now I have done my due diligence and have read about I/O stalls, I/O Latency, other I/O measurements and such; but most everything I found, because the way SQL Server works, were cumulative information. Glenn Berry’s DMV Diagnostic Information Queries is great place to learn where to get this information. But again, it is based on SQL Server cumulative information. I hope my Read Latency is not 150+ ms!
I needed a way to say, “for this sample period” this was the read latency of a drive?
Then I found Jon Gurgul b|t, his post titled Delta and Cumulative IO Stats was a great read; however it wasn’t exactly what I needed. But it did get me thinking about his method of capturing two result sets with a time delay and finding the difference. So why couldn’t I do this for Glenn Berry’s Read/Write Latency script.
Below is what I came up with. It probably is not perfect, but it works for me. I have setup a SQL Agent job to run this code every 15 minutes with a 5 min delay between result sets. Then the results are stored in a Diagnostic database so I can chart and report on trends.
I am eager to see the trending over time and truly see what my Latency time for my SAN drives are!