I built a small ruby program a while back to help with two things:
- Quickly identify what tables have the highest IO in a particular query
- Learn a new language (Ruby)
The problem was simple. I needed to easily identify the problem areas of a complex stored procedure. By problem areas I mean sections with a large logical read count. The statistics IO output was rather large ( a few hundred lines) and was a bear to work with. I thought, "hmm. If I could get a summarized count I could easily pinpoint potential problem areas and spend more time problem solving than problem hunting". The end result is query_io_reader.rb.
Originally the program would ingest the statistics io output (saved to the filename of your choice) and sum up the numbers based upon what you are looking for. I nearly always use "logical IO", but the program will sum up any identifier. The tables were listed in the order they appeared in the incoming file. The output provided the identifier you searched by, name of the table, count of the identifier and a total count at the bottom. It was effective, but the other night I realized I could make a few enhancements to this, namely ordering the result, adding the number of times a tabled appeared, and I added pipe delimeters so you can easily push this into excel or another tool for additional analysis.
The end result is the second iteration of the utility, which you can download by clicking on the link above or just going to the Downloads section of my website. Here is a screenshot of the output of a sample
