You know all those little things that make you crazy
when you’re developing in SQL Server Management Studio? Those pieces of
functionality that you just can’t understand why Microsoft didn’t just
include it? Stuff like generating a set of CRUD statements (Create,
Read, Update Delete), or running custom scripts right from the SSMS GUI,
or even a way to analyze execution plans rather than read through them?
Yeah, Mladen Prajdic felt exactly the same way. Except unlike the rest
of us, instead of sitting around moaning about how the software doesn’t
do this or that for me, Mladen just started building it for himself.
Then he went one step farther, he let the rest of us have it too. That’s
right, SSMS Tools Pack is a free piece of software that patches over a
lot of the little cracks in SSMS (although I’m certain that Mladen
wouldn’t mind contributions if you really love his work). It works with
SQL Server versions from 2000 to Denali and it does everything that it
does working directly within SSMS so you can stay well and truly inside
your comfort zone, developing with the toolset that you’re used to. In
fact, SSMS Tools Pack will quickly become one of those pieces of
software that you won’t want to live without.
Download and Install
When I heard that a whole new version was becoming available, I couldn’t wait to try it out. I downloaded it the first day it was available. Here is the exhaustive list of actions and requirements to download the software:- Go to http://www.ssmstoolspack.com/Download
- Click on the download button
After you launch the installer, it will recognize what version of SQL Server you have installed on your desktop. I tried the install on a 2008R2 instance and on a Denali instance and didn’t have any troubles with it whatsoever.
On the Denali instance, I had SQL Server Management Studio open when I did the install. I didn’t get any error messages, but when I went to SSMS I also didn’t see any of the new tools available. Upon restarting SSMS, the tools loaded right up.
Functionality
There’s a shocking amount of functionality built into this piece of software. Because it covers so much different functionality trying to find a handle for how to approach the evaluation isn’t entirely simple. So, with the full set of functionality in front of me, I’m going to cover the stuff that I find cool or intriguing. If I leave out your favorite function, there’s your chance to post a comment or write up an article of your own.Mind you, I’m not going right to the coolest stuff. We’ll start slow and build.
Generate CRUD
There’s a small market on code generators for SQL Server. Why? Because most of the queries, the CUD part of CRUD, can be built completely through generation. There just is no reason why SSMS can’t do this on its own. But, instead of having to go to an outside tool or work with code bases other than TSQL, you can just right click on the table you’re interested in and one of the new context menu choices is “SSMS Tools” and right under that, “Create CRUD…” Clicking on it opens a new query window with a set of stored procedures all set up to add, update, remove or read the data from the table selected. It’s that easy. The statements are well formatted too with DROP statements before the create statements so that you can run this as needed when you change your structures.It gets better though. You’re not dependent on the structures that Mladen has provided. If you go to the SSMS Tools menu, there’s a list of options to modify different functionality. CRUD Generation is right there with some of what you can control as shown in Figure 1:
Figure 1
As you can see you can turn the functionality on or off. More
importantly, you can control column order, set stored procedure prefixes
and others. But the important stuff is on the next four tabs. You’re
not dependent on Mladen’s choices for how to format and layout your
procedures (although I don’t have any problems with his choices). If you
wanted to put a standard TRY/CATCH error handler in your Insert
statements, all you have to do is click on the tab and modify the query
you find there, just like you see in Figure 2:
Figure 2
All you have to do is follow the instructions and you can modify
these procedures so that they work exactly the way you’d want them to.One change I’d like to see, make it so that I don’t have to generate the Read procedure because those are mostly not simple, select from one table, affairs on systems I’ve worked with.
Connection Coloring
I’ve never in my life done something like what I’m about to describe, but I’ve heard that others have done so. You hit execute on that RESTORE DATABASE script and you set it to restricted user and put ROLLBACK IMMEDIATE in there because the developers on the development server where you’re restoring the database are never logged out even though they say they are and then your eye drifts down to the bottom of the screen… wait, that’s not DEV02\BoringOldDevServer. It’s PROD42\YouBetterGetYourResumeCleanedUpServer. Oh… *******…Not that I’ve ever done that, or at least, there are no living witnesses that I’ve ever done that. Same thing really.
That’s where Windows Connection Coloring comes in handy. If you go back to the SSMS Tools menu you can find the appropriate menu and open the options window. Once the window is open, you can supply a server name, or, you can use regular expressions to define a pattern, so that you can set a group of servers up to behave a certain way. Figure 3 shows how I configured it locally:
Figure 3
If you do type in a regular expression, make sure you also select the
“IsRegex” checkbox. Once you have things set up appropriately, you’ll
see a colored bar at the top of your query window so you can be very
aware of which server or set of servers you’re currently connected to.
Figure 4 shows the bar on display:
Figure 4
I realize this is a pretty simplistic little thing, but if a simple
little thing can prevent a production outage, it’s pretty cool. In
keeping with my irritate Mladen with more suggestions though, since you
can format the bottom color on connections individually, it would be
good if this also updated that color so that you could see the color
chosen for a particular connection on the top and the bottom of the
query window.Tab Sessions
New with Version 2 of SSMS Tools Pack is the Tab Sessions. This little bit of functionality keeps track of the tabs that you have open in SSMS and the TSQL inside those tabs. On a periodic basis (set within the options screen) it will save the tabs and the code. Then, say after a exiting and reentering SSMS you can hit the Restore Session button on the toolbar and it will reopen your tabs. Which is really cool, but it gets better.If you go to the SSMS Tools menu and to the “SQL History” menu there, you can see the “Tabs History Management” menu choice. Clicking on that will open up a collection of different sets of tabs going back in time. You can see an example in Figure 5:
Figure 5
It’s cool that you can scroll through these and pick one to restore,
but what’s really cool is that you can then click on the tabs, like I
have on the center one, and scroll through the code on that tab to
ensure it’s the one you want. I’m digging this in a major way.One major issue that I do have with it though, and this isn’t just another irritate Mladen request, is that while it knows which TSQL I have in a tab, it’s ignorant of the fact that that TSQL is from a file, so it will restore the TSQL to the appropriate tab on your window, but it won’t link it back up with the file that you had it open from. If you continue editing that TSQL, it won’t be saving to the file until you choose save and overwrite what you saved originally.
Execution Plan Analyzer
I’ve saved the coolest new feature for last. SSMS Tools integrates with your execution plans. There’s a button on the SSMS toolbar, “Show/Hide Execution Plan Analyzer.” Click on that button when you have an execution plan open in SSMS, any plan, even from a file, and your execution plan screen will suddenly change to look like Figure 6:
Figure 6
What you’ve got are different sets of functionality. Across the top
are three buttons, offering you the ability identify the highest cost
operation, search operators, and get suggestions on plan improvements.
Yeah, you heard me, it’ll help you turn your queries.The first piece of functionality, identify the highest cost operation, that’s hardly worth talking about on a plan that looks like the one above. But how a plan that looks like Figure 7?
Figure 7:
Yeah, that’s one of the hairiest plans I’ve ever seen and I sure
would have used this to browse through that plan (I used XQuery to find
the costliest operators, worked, but it wasn’t pretty like this). You
can adjust it too so that it shows more or less operations based on
their cost by using the little slider bar right above it. The tool tip
will show the name of the operator and the cost and clicking on it will
take you to that operator within the plan. That’s extremely handy.But, you can adjust the default values, and you can pick other properties from the Options window and have them bubble to the top. More interested in high numbers of Estimated Rows, change the property and you’ll see those operations that exceed the value that you supply on the bar on the left side of the screen. Again, very handy.
Next, you can search for operators. Want to know if you have a Sort operation in that mess of icons? Click the button and type in the term. You’ll see any and all Sort operations. But, it gets better. What if you’re curious that the primary key was used in the query. Type that in and you’ll see results like Figure 8:
Figure 8.
I left the tooltip up so that you can see that SSMS Tools Pack
searches inside the operators to find the things you were interested in.Now, what about this tuning help? I’ll quote Mladen on this one:
It goes through your plan finding most common problems and gives you the usual solution for each one. This feature isn't meant to replace manual analysis but it will find the usual problematic spots that you can fix up fast.
The query I gave it is very simple and has a couple of issues, primarily structural. You can see that SQL Server thinks I need a different index. Let’s see what SSMS Tools Pack says I ought to do about it.
Figure 9:
It called out two issues, the Merge Join, and the Clustered Index
scan that you can see in Figure 9. On both it pointed out that my
estimated number of rows and my actual number of rows differed by a
significant amount. This is true. I use this query to demonstrate bad
parameter sniffing. The suggested solutions were to update the
statistics on all tables used in the query or to check for missing
indexes. These are excellent suggestions. Neither will work in this case
because I’ve got a skewed data set (like I said, this is my demo code
and it’s broken on purpose), but nonetheless, based on the information
provided, these are the same recommendations I’d make myself. I’m
impressed.And More
Yes, there’s more. I can’t cover everything without generating about 50 pages of documentation to go through all the functionality and all the control over that functionality that you get. Here’s a quick run-down on some of the stuff I didn’t cover:SQL SnippetsSearch Table/View/Database DataRun Scripts on Multiple ServersSearch Results in GridCustom Scripts from Object Explorer
Mladen did a nice job of documenting all the functionality, including the parts I didn’t mention.
No comments:
Post a Comment