SSMS Execution Plan Improvements

Kendra Little shows Management Studio execution plan improvements in 2016:

The best features are the ones that you use all the time. SQL Server 2016 Management Studio’s bringing improvements in navigating around execution plans.

Click + Mouse Scroll: Zooming!

You can now make your plans bigger and smaller with this combo. It will zoom into the region where you have the mouse.

Click + Drag lets you move the plan

This is really handy for moving right to left.

Good for those times when SQL Sentry Plan Explorer isn’t available.

Restoration Failures

Tibor Karaszi shows us cases in which Management Studio can generate an invalid database restoration sequence:

Above, the GUI incorrectly base the restore on a copy only backup. After using the timeline dialog to point to an earlier point in time, you can see that the GUI now has changed so it bases the restore on this potentially non-existing copy only backup. Not a nice situation to be in if the person doing the restore hasn’t practiced using the T-SQL RESTORE commands.

It’s important to be able to write the relevant T-SQL queries to restore your database, just in case you run into one of these issues.

Filtering SSMS Tables

Jens Vestergaard shows us how to filter tables in Management Studio:

As a quick tip, this on is one of these tool tips, that just makes your everyday much easier. Sometimes, more than often, you run into databases that contains a huge number of tables – all listed alphabetically. This can, at times, be cumbersome and annoying to browse. SQL Server Management Studio (SSMS) actually has a feature that will assist you in getting your job done, quicker; It’s called Filtering.

My most common filter criterion is by schema, but there are several filtering options available.

SSMS February Preview

Aaron Bertrand notes that there’s a new SSMS preview available:

Gianluca Sartori (@spaghettidba) blogged about the issues you may have noticed with older versions of Management Studio on HiDPI, UQHD, or 4K/5K screens in his post, “SSMS in High-DPI Displays: How to Stop the Madness.” Essentially, Windows tries to scale things for your DPI settings and, depending on the technology used to render your fonts and dialogs, this can end up with ugly and sometimes unusable screens. Gianluca’s fix is to make a registry change and add a manifest file to the SSMS directory so that SSMS no longer tries to render Windows’ scaling changes. In the latest builds of SQL Server 2016, SSMS now acts as if the manifest file were in place. Take a look at the following image:

That’s music to my ears; I use a high-resolution laptop and before Gianluca’s solution, it was impossible to use SSMS.  I’m looking forward to SSMS 2016, but probably won’t move until the add-ons I use are supported; I’ve grown to like them too much to make the jump, even on a trial basis.

Plan Explorer Math

Aaron Bertrand walks through display differences between SQL Sentry Plan Explorer and SSMS:

Now, in the process of developing Plan Explorer, we have discovered several cases where ShowPlan doesn’t quite get its math correct. The most obvious example is percentages adding up to over 100%; we get this right in cases where SSMS is ridiculously off (I see this less often today than I used to, but it still happens).

Interpreting execution plans is not a trivial exercise, and this is an interesting look at how SQL Sentry developers (and supporters within the broader community) have worked on it through the years.


Dave Ballantyne discusses the ASYNC_NETWORK_IO wait stat:

Simply put ASYNC_NETWORK_IO waits occur when SQL Server is waiting on the client to consume the output that it has ‘thrown’ down the wire.  SQL Server cannot run any faster, it has done the work required and is now waiting on the client to say that it has done with data.

Naturally there can be many reasons for why the client is not consuming the results fast enough , slow client application , network bandwidth saturation, to wide or to long result sets are the most common and in this blog I would like to show you how I go about diagnosing and demonstrating these issues.

Dave goes on to explain this using Management Studio examples, but the information also applies to other client applications.

Make SSMS Beep

Denny Cherry shows us that you can make Management Studio beep when a batch completes:

The next time you run a query (you might need to close all your query windows or restart SSMS, you usually do with this sort of change in SSMS) it’ll beep when the query is done.

Personally I’ve actually used this as an alarm clock when doing long running overnight deployments so that I’d get woken up when the script was done so I could start the next step. It’s also handy when you want to leave the room / your desk while queries are running.

I must have seen that screen dozens of times and never once noticed this checkbox.

SSMS Keyboard Shortcuts

Slava Murygin shows us some keyboard shortcuts in SQL Server Management Studio:

If you work with SQL Server for a long time you’ve probably learn some Keyboard combinations to speed up your administration or development process.
The full list of SSMS Shortcut keys you can find in MSDN

I will try to re-categorize the most interesting ones

If you spend a lot of time in Management Studio, learning keyboard shortcuts will make your life easier.

Configuring SSMS

James Anderson walks us through SQL Server Management Studio, including some configuration:

Sometimes when using SSMS you will see a redline under a table or object name in your T-SQL. This means SSMS thinks the object doesn’t exist in the current database. Usually it’s right, but if you have just created the object, the query editor wont know as it’s local cache is not regularly refreshed. To force a refresh you can hit Ctrl + Shift + R but I always forget keyboard shortcuts. For this I like to add a button to the toolbar.

This is a good intro-level article on SSMS basics and some configuration options.


Allen McGuire talks about SSMSBoost:

Tip 4: Results Grid aggregates – Some people do a lot of calculations with their data, whether it’s sales data or whatever.  You end up saving the query results to Excel and about five minutes later you have the totals, etc.  With SSMSBoost, you can have your totals in seconds.  Below I’m selecting 10 records from a sales related table.  Say I want to get the SUM, MIN, MAX and Count of the SALEPRICE.  All I do is slide my mouse down the column highlighting the cells and a pop-up will appear with that information:

This is probably my second-favorite feature of SSMSBoost; my favorite is automated crash recovery, and my third-favorite its snippet support.  SSMSBoost has a free version and I use it myself.  I try not to push many tools here on Curated SQL, but this is one worth checking out.


May 2017
« Apr