Experts' Perspective Webcast: SSIS Tricks and Tips

For those of you who were unable to attend the SSIS webcast I gave for Quest Software, here is the corrected link to the recorded session : The Experts Perspective - 14 July 


Webcast: SSIS Tricks & Tips
Date: Thursday, July 14
Time: 8 a.m. PT / 11 a.m. ET / 4 p.m. UK / 5 p.m. CE
Duration: 45-60 minutes
Cost: Free

Don’t miss this SQL Server Integration Services (SSIS) webcast featuring invaluable guidance from industry-leading expert Josef Richberg. You’ll gain a deeper understanding of SSIS’s quirks, capabilities, and the pitfall of parallel path choicing.

Plus, you’ll learn how to:

  • Build a source component to extract data from a stored procedure.
  • Create a destination component to extract additional information from the database (like identity columns).
  • Use the Advanced Editor to improve Excel performance.

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.  

Page 1 ... 3 4 5 6 7 ... 24 Next 5 Entries »