Here I am working on tuning a rather large stored procedure; old school. I have statistics IO and showplan turned on. I work through the obvious missing indexes and improper joins and now I'm into the 'Let's try this technique', to improve different pieces. The gauge for me is the logical IO count in the statistics IO output. The lower the IOs the better the query. Since a change in one place might reduce the IO of that section, but might increase the IO of another section, I have to count up all the IOs. An example of this would be adding/removing an index on say a temp table. It might improve inserts, but kill you later on down the road during look-ups. Needless to say I got really tired of counting the logical reads, adding them up and seeing if my change worked. I had about 50 lines of stats output from the one procedure!
Wanting to flex a different portion of my brain, I decided to play around with a few programming languages. I need a problem to solve in order to really learn a language and this was a perfect problem. I needed to quickly count up all of the IOs for the run, but more importantly I need it broken out by table. Having the total counts by table will enable me to quickly pinpoint areas that need the most attention. The end result are two programs, one written in Ruby and the other in Python. The command line for each is identical. I'll go through how to use them, using the Ruby version in the example.
I run my query in SSMS, with statistics io turned on, and save the output to a file: io_output.txt
Since logical IOs are what I am interested in, I will look for "logical reads". The command line for the program is quite simple:
Voila! Broken out by table, including a total for the entire query/procedure.
Here is output looking for 'Scan count'. Yes, it is case sensitive!
