Category: SQL Server Management Studio

Louis Davidson has a quick tip on using :EXIT in SQLCMD mode:

The first use I will cover will help you stopping from running an entire file’s worth of SQL statements without meaning to. I use this mostly when doing demo code, but it certainly finds its way into some of my other code as well. The hotkeys in SSMS for hiding the results (Ctrl-R), and executing a query (Ctrl-E) are next to each other. In a demo, where you are showing code, statement by statement, accidentally executing the entire file of queries can cause you to spend unwanted time recovering with many eyes staring back at you. This is only slightly better than accidentally running code in your office system, and losing your job.

Read on for more.

Wayne Sheffield continues his SQL Server Management Studio Tips & Tricks series:

Regardless of whether you like to use tabs or spaces, this is where you go to configure your settings. The first part of the screen controls the indenting options. If “None” is selected, then the next line will start at the beginning of the line. If you have selected “Block”, then it will align the next line with the previous line. And if you are using “Smart”, then the appropriate language will determine which indenting style to use.

The next section controls the tab size / indent size. This controls how many characters that a tab takes. It also controls whether tabs are converted to spaces or kept as tabs.

You can read more about these options at this link: Manage Code Formatting.

I turn on the View Whitespace option that Wayne mentions because I’m a formatting pedant that way.

Michael J. Swart shows a scenario where the deadlock graph fails to open in SQL Server Management Studio:

I recently got this error in Management Studio when trying to view a deadlock graph that was collected with an extended events session:

Failed to initialize deadlock control.
Key cannot be null.
Parameter name: key

I found this error in a session that included the xml_deadlock_report event.

Read on for more information, and do check the comments where Lonny Niederstadt points out that even a victimless deadlocking scenario can have an ultimate victim:  performance.

Wayne Sheffield has another SSMS tip for us:

Do you ever find yourself working on a query and realize that you need just a bit more real estate in the SSMS window? Or perhaps you find that all the toolbars, menus, etc. are cluttering things up? To solve these issues, you can toggle the full screen mode in SSMS on. It will remove all that clutter and maximize the query window. Below, you can see a cluttered SSMS with two rows of buttons, and toolbars on both sides of it.

Click through to see how to enable full-screen mode.

Steve Jones gives SQL Server Management Studio 17.5 a spin and tries to classify some data:

There’s a getting started link, which takes me to the SQL Server Security Blog. I suspect that’s an incorrect link. I think it should go here: SQL Data Discovery and Classification.

Below this, I see a list of the recommendations. This has grabbed tables that appear to continue to contain some data that might be sensitive and require classification. One of the tenets of the GDPR  is that you know your data. You aren’t allowed to figure this out later, but rather you must proactively know what data you are collecting and processing.

It’s a good overview of the feature.  Like Steve mentions, I appreciate this data being stored as extended properties:  that way, third party and custom-built tools can make use of it.  You can also script them out for migration.

Alan Yu announces SQL Server Management Studio 17.5:

SSMS 17.5 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.5 comes with several exciting new features:

• Data Discovery and Classification is a new feature for discovering, classifying, labeling and reporting sensitive data in your databases.
• Query Editor now has support for a SkipsRows option for the Delimited Text External File Format for Azure SQL Data Warehouse.
• Showplan has enabled the display of estimated plan button for SQL Data Warehouse.

SSMS 17.5 also includes key bug fixes to Showplan, Query Editor, SMO, and Templates, which can be found in the Release Notes.

Aside from data discovery and classification, it’s probably not a major update for most people, but this does look like an interesting tool.

Wayne Sheffield wraps up his 31 SSMS tricks in 31 days series.  First, he shows off the presentation settings in SSMS.  Presenter mode isn’t nearly as full-featured as I’d like to see it be, but it’s an improvement over the old version, at least.

He then shows standard reports built into SSMS.

The last post in the series is all about the Activity Monitor:

The Activity Monitor can be useful for seeing a mile-high view of a SQL Server instance. However, leaving it running can be as big a drag on the instance as is the use of SQL Profiler. The Resource Waits section of Activity Monitor, which would have been one of the strongest features, has been dumbed down by the filtering of wait types, and that many others are grouped together into categories. Sure, using the Activity Monitor convenient – but spend the time to develop your own scripts or XE sessions to get this information in a more efficient, with less impact. Overall, refrain from using it… and especially don’t set up SSMS to open it automatically on startup.

Agreed.  For all of Wayne’s tips and tricks, check out his index page.