Achievements

« SSIS OLEDB Command and procedure output params | Main | Improved partition loading »
Friday
Jun102011

Statistics IO parser in Ruby & Python

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!

Here is the Ruby version.     Here is the Python version.  

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>