SSMS ToolsPack – Powershell Turboboost


I have been using the rather brilliant SSMSToolsPack from Mladen Prajdić recently and love the query execution history feature.

I like to keep my history around for a while – I do a lot of work that then has to be repeated later – and this tool lets me not worry about forgetting to save that important query I ran for someone weeks ago.

I ran in to a little problem though.  All those query executions have to be stored somewhere, this is done by setting a folder to store the query text. 

SSMSToolsPack stores the query text in txt files, these can get out of control if you run enough queries.  They are then stored per day in a folder; so for today the queries would be stored in the folder “2010-12-03”.

If you want to search through the history and have a great number of files and folders, the search can get very slow (I suspect this has to do with the directory and file traversal).  I am lucky to have a small SSD on my main machine, I store source code and the execution history files/folders on there.  This speeds things up, but it seems that even then the search is sluggish (takes about 30 seconds to index on my machine).

I took a quick look at these files and saw that they were basically all the same content wise.  I tried just combining the files to see if that could improve the performance of searching and lo-and-behold search was blazingly fast!

Being lazy, I whipped up a script in Powershell to make this easier/semi-automatic and here it is:

clear host
$path = "" #Set path here!
foreach ($folder in Get-ChildItem $path)
{
  if ($folder.PSIsContainer -eq "False")
    {
      foreach ($file in Get-ChildItem $folder.FullName)
       {
         $target = $path+$folder.Name+"\output.txt" #set output file
         if ($file.Name -ne "output.txt")
          { #concatenate content of all files except output
            cat $file.FullName > $target 
            #del $file.FullName #delete the file after processing
          }
       }
    }
}

It is nothing special, but maybe if you use SSMSToolsPack and have experienced a similar slowdown, you can use this to help.

Advertisements

4 thoughts on “SSMS ToolsPack – Powershell Turboboost

  1. William

    Nice one. I’d be interested to see what you thought of the Execution Audit Trail in SQL Everywhere – does a similar thing – saves SQL Executed, Messages, Errors & Result Sets – but everything is zipped, so the number of files doesn’t get silly – and the search is built in, so it’s not a pain to find stuff…

    Great script though – have you let Mladen know about it?

    M

    • Hi Matt,

      thanks! It is nothing special really, concatenate and clean up after.

      I told Mladen about this, but he thinks it is a non-issue – I found it annoying enough to want to clean it up.

      As I mentioned on ASK, I have done you no justice as yet. I know your tools are good, but only from a cursory glance. I really need to give them a test drive – something for the holiday period I think.

  2. Hey!

    I’ve done some optimizations on this to work faster but it’s not an issue of just combining files.
    Saving them in folders by day has proven extremely useful for majority of people because they usually know on which day they did something. so it’s faster to filter out.
    as for the large number of files, i save one file per tab open. this has also shown to be better than combining everything into a single file.
    those are the reasons i probably won’t go into chanign the internals of this.

    Cool script thoug! 🙂

    • Mladen,

      thanks for the feedback!

      I have no problems with how the tool works, I am just an impatient person 🙂 and I often search through a couple of weeks of scripts to find stuff I did way back then.

      I like the improvements in general that you have made so far – keep it up and thanks for such a cool tool!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s