Now that CFMLDeveloper.com supports SQL Server, I thought some SQL Server articles may be appropriate.
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:
That’s right. No registration, no email address, no home phone, none of the standard stuff that you have to supply in order to get a piece of software. Did I mention that this is free software and that Mladen doesn’t really want to know who you are in order to supply you with it?
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.
This article was syndicated from simple talk.
Recent Comments