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

Microsoft, do you care …..?

I have recently been revising for and taking a series of three Microsoft exams, namely 70-457, 70-458 and 70-460 which are the three exams required to upgrade my current SQL Server 2008 Business Intelligence MCITP to SQL Server 2012 MCSE in Business Intelligence (you also get the MCSA in SQL Server as part of this).

Now I have taken numerous Microsoft exams in the past (SQL Server 2005 and 2008) and have always been disappointed, not with the outcome (I’ve both passed and failed exams), but the quality of the exams and the conditions under which these exams are taken.

I really had high hopes that with SQL Server 2012 things would change for the better with the exams, these high hopes however, have mostly been destroyed after taking the first two of the three exam set I require to do my upgrade, to the point where I am now asking myself, why should I bother?

let me explain

Firstly I’d like to firstly say that yes, some things in this process have improved. The Promteric website where all the tests are booked for example, has had a bit of a face lift and the navigation has improved significantly from the days of old where it felt like a bunch of unconnected web pages cobbled together in one afternoon by a curious high school student. This being said, its still far from perfect.

Also the actual testing software at the test sites has been updated, it has a new look and feel and to be fair is a little less clunky than it was before. There are also some new formats in which questions are asked (some of these are good and some are god awful IMHO Smile). But again its far from perfect.

 

My First complaint

Before I start this first comment I will add the caveat that I have only ever used one test centre and so my comments may not be indicative of all test centres.

So my first issue is the hardware that is used to host these tests, old Pentium 4 PC’s with I’d imagine nothing more than 1GB or 2GB of RAM, old crappy gunked up mice that jump, tiny old flat screen monitors and just enough space to get a mouse mat right on the edge of the desk. Now the mouse and monitor thing are minor niggles but I wanted to mention them anyway, however the fact these machines are so low spec actually affects the testing process as switching between questions and loading the review stage etc is VERY slow and frustratingly the timer doesn’t appear to stop while these slow loading processes go on.

Note: Please drop me a comment below and let me know what your experiences of the hardware on test sites was like, I’d be interested to see if the site I use is an isolated case (I suspect not)

 

The Main Event

Now the hardware is annoying and yes it is a hindrance to the test taking, however in my opinion that all pales into obscurity in comparison to the quality of the test it self, or more to the point, the questions and the answers.

Now as I mentioned above there are some new question formats, some are actually ok but others are so stupidly counter intuitive it hurts. Also there are numerous spelling mistakes, typo’s and other grammatical issues and even syntax errors in both the questions answers. These bits may sound pedantic however to a dyslexic such as myself reading the questions takes longer than it should anyway, without having to try and decipher what they actually mean through all the miss spelled words and typo’s etc.

This aside there is also so much ambiguity and poor wording in the the way the questions are posed, in more questions that not, that it is some times difficult to see what the question is actually asking of you.

Again this is annoying but the kicker of all kickers is when a question comes up that, no matter how you could read or interpret it, none of the multiple choice answers they provide are actually correct!! I have had this in both the 2012 exams and the 2008 exams I have taken. Now some of you might be thinking “Ah, you just read the question wrong, stop complaining”, well there might be occurrences where you’re right (even though I always read those questions very slowly and carefully like 15 times) but there are also occurrences that are so blatantly wrong its (almost) laughable, let me give you an example:

(If any MS lawyer types are reading this and thinking of getting me on the NDA Smile, don’t worry this is a loose example based on an actual question I got, please mail me if there’s an issue)

So the question gave a scenario and a requirement and asked “which isolation level should you choose for this scenario”. The four possible answers where something akin to:

  • Create a user defined data type in msdb
  • Create a user defined data type in master database
  • Create a user defined data type in user database
  • Create a user defined data type in user2 database

Clearly, none of these are isolation levels!

Back To My Original Question …

So having gone through all the fact this bring me back to the title of my post, does Microsoft actually give a damm about these qualifications? From the experiences I have had from booking to taking the exams I really have to say no, they don’t give a damm and I can’t see a logical argument to say otherwise.

Yes I know that the 2012 exams are new, fine, but the 2008 ones weren’t when I took them and they where just as bad if not worse. Also, just because they are new doesn’t mean that the level of poor wording, spelling mistakes, typo’s, syntax errors, ambiguity and just plain wrong questions are acceptable. You can’t tell me that if a company of Microsoft’s size and resource actually cared about these things that we’d get exposed to the level of, quite frankly rubbish, that we currently do. Also, that they wouldn’t source a second or replacement testing partner along side the Prometeric’s of this world to offer us a little choice in the matter.

So I get left with the feeling, if they don’t give a crap, why should I? why should any of us? It’s not like these exams are cheap (£99 a pop as it stands at time of writing), so why should we spend our hard earned cash on a half hearted effort by Microsoft?

(Just as a foot note, for anyone thinking it, this is not a post of bitterness due to a failed exam, I have quite happily passed Open-mouthed smile)

I’d be really interested in everyone’s opinion on this, am I being to harsh / picky? What is your experience of these or other Microsoft exams? Drop me a comment below.

Cheers,

Dave!