Tools of the trade

Hi all, in this post I wanted to go over what I consider to be the must have tools if you work with SQL Server. All these tools make working with SQL Server so much easier and will also save your bacon more times than you can count.

Before I start I’m not affiliated with any of the companies I’m going to mention and have taken no under the table bribes either Smile

 

SSMS Tools Pack  SSMS Tools Pack Logo

(http://www.ssmstoolspack.com/)

The SSMS tools pack by a guy called Mladen Prajdić (Twitter @MladenPrajdic). It adds a world of new functionality to management studio such as allowing you to create sql code snippets, a tab connection colouring whereby you can set up different colours to represent different servers and all tabs connected to that server will be headed by that colour (see below) 

image

Also as I’ve shown above it can record all the queries you have run and saves them to a file. This way, if management studio crashes on you, all those queries you’ve written are safe! I’ve lost count of the number of tweets from people saying this has saved their bacon.

These are just a few of the features, I don’t want to sell it short as there’s loads that SSMS Tools Pack offers so go have a look for yourself. And best of all, its free! (You can make a donation though to help Mladen keep the development up and maintain the website etc)

’ve had the pleasure of spending a bit of time with Mladen and have also seen him at SQLBits speaking. He’s a good guy, always up for a chuckle and knows his beans. Be aware though if you do follow him on twitter we don’t call him the NSFW MVP for nothing Winking smile

 

SQL Sentry Plan Explorer  SQL Sentry Plan Explorer(http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

Another little free gem that surprisingly few people seem to know about is SQL Sentry’s Plan Explorer tool. If you are working with query plans, in my opinion this tool is invaluable. Looking at query plans without it now feels like having one hand tied behind my back (which is especially bad for me! Anyone that has met me will get this joke hopefully Winking smile)

As it ‘says on the tin’ this tool is designed to help you read visual show plans easier and also identify potential problems much much quicker.

To show you what I mean I’ve done a simple select from a view in adventure works. This view has a load of tables in it and produces quite a wide and deep query plan. Viewing it in SSMS is a pain (see below, I had to zoom out five times to get it to fit):

image

Not particularly helpful in my opinion, you either have to zoom way out or scroll around the plan to see what’s what. Also there isn’t a lot of information readily available here in this view, you have to hover over or select the properties of each element to get more info.

Now, all you need to do is save the query plan somewhere and then open up Plan Explorer to load it in.

lets look at what Plan Explorer gives us:

image

Firstly, you can see all the plan! Its all nice and tidy next to each other. The next thing you’ll notice is the colours above some of the operators, these denote that these operators are using a high percentage of the plan using a traffic light type system (yellow, orange and red for the highest).

So instantly I can now see the whole plan and I can also quickly visually identify the operators with the highest % of plan use, cool ah!

But that’s not all, if you see above the diagram, there are tabs:

  • Plan Diagram (Currently in view)
  • Plan Tree
  • Top Operators
  • Query Columns
  • Join Diagram

All of these do what they say on the tin and all have a whole wealth of knowledge to give you at a glance with the ability to drill down further. Lets look at the top operators tab:

image

The list of columns here is actually much longer but in the interests of keeping the image readable I’ve got it down. here you get all the operators in a list with all the information about each you could want. By default it is sorted by operator cost but you can sort and arrange it any way you wish.

Finally I just wanted to show you the Join Diagram tab:

image

This tab visualises all the joins in the query. This is really handy to see what’s joining to what and how.

All in all this is a cracking free tool and one I couldn’t do without. Again I’m probably selling it short so go download it and have a go for yourself.

 

Bids Helper  BIDS Helper

(http://bidshelper.codeplex.com/)

As the name implies this is a plugin for BIDS (Business Intelligence Development Studio). It adds a whole bunch of functionality for all the different types of BIDS projects (SSIS, SSRS, SSAS). Again I find this invaluable and really miss it when I don’t have it.

The main reoccurring feature I feel of this is the deploy functionality it adds. Ever had to manually upload packages one by one into the package store (msdb) or manually deploy your reports one by one to you reporting services server?  Well, no longer with BIDS Helper, simply right click and properties on your project file:

image

 

You’ll now see there is a new property “Deploy (BIDS Helper)”. Select this and choose your require deployment type:

image

Then simply fill in the relevant information in the remaining boxes and click ok:

image

 

Once you’ve done this, right click the project file again and this time select “Deploy” and BID helper will go off and deploy the solution / reports etc for you!

image

 

This is one of the cooler features, there are loads more features that it gives you in each of the various project types, such as in SSIS, it will put coloured triangles in the corners of entites that have properties being dynamically set from package configurations or have expressions etc

image

 

There’s loads more too this tool and again its free so go download it an give it a go Smile

 

Redgate SQL Prompt     Red Gate logo

(http://www.red-gate.com/products/sql-development/sql-prompt/)

 

Now in my opinion, I’ve saved the best to last. This is the only one of the tools in this post that isn’t free (although you can download a free trail) but again in my opinion if you’re writing SQL in SQL Server and you’re not using this tool, you’re doing it wrong.

This is an intellisense tool, what that means is it gives you code completion and shortcuts that make writing SQL ridiculously fast and easy.

It includes things such as snippets, which are fully customisable code chunks that can be called up in a few key presses. so for example, if I want to type “Select * From” I instead type “ssf”, hit the tab key (you can customise which keys activate certain things) and wham, its typed “select * from” for me. Cool, no?

But thats not all, now I want to add a table to my select, so I either start typing the table name or hit CTRL + Space and a box appears giving me suggestions:

image

I can then continue to type and it will filter the list down as I type to suggest the relevant table and also give me a bit of information about the currently highlighted table:

image

I then simple hit tab (or which ever keys you’ve configured) and it fills in the rest of the table name for me (optionally complete with the schema name etc).

The next really cool this is fields, we all know that “select *” is bad, so put the cursor next to the * :

image

And as the tip says I press tab and bam again, it explodes out all the fields in the table for you!

image

Very handy when you got a table with loads of columns. Alternatively I could have deleted the star and used the menu based column picker to only select the columns I wanted, or simply typed the columns and had the full column names auto completed in exactly the same manner as the table name.

You can also hover over anything and get a context sensitive menu, so if I hover over a table or stored procedure / function, I get the meta data about the columns / parameters but I also get the option to view the code for them:

image

image

image

As you can see on the bottom image, there’s a copy button to copy the code to clipboard and you can also resize the box so you can view the whole code within it should you wish to.

This tool is so customisable its unreal too, all the code that is generated, you can configure how its formatted, it can generate table aliases for you if you like too. There’s so much to configure and tweak so you can have it exactly how you like to see your SQL.

I’ve only covered a very small fraction of what this tool can do, so download the trial and if you like it, buy it! It will take an hour or so to get used to but trust me, once you get used to it you’ll be hooked!

I genuinely love this tool and honestly believe that no one should be writing SQL without it

As a final note, Red Gate also do a couple of free tools, SQL Search and SQL Tab magic. They are both totally awesome as well and I use them both on a daily basis. As with sql prompt they are both SSMS plugins but also plug in to BIDS / SSDT.

Oh and also, RedGate do a whole host of paid for developer and DBA tools, I’ve tried most of them and they are all cracking. I don’t have them myself as I’m a consultant and move around a lot, however I recommend them highly

 

Well I hope you’ve enjoyed this post and that tools I’ve covered bring you as much productivity and happy feeling as they do for me Smile

 

Enjoy!

 

Dave

 

 

P.S Any of the vendors I’ve mentioned in this post, please contact me if there’s something you’re not happy about. I’m simply trying to share my love for your great tools and am happy to make any amendments you wish

Advertisements

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