Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 Management Studio Enhancements


By:   |   Last Updated: 2016-08-29   |   Comments (7)   |   Related Tips: More > SQL Server Management Studio

Problem

In this previous tip New Features in SQL Server Management Studio for SQL Server 2016, we have seen some of the new SQL Server Management Studio features and improvements.  However, the tool has improved a lot since that tip and in this tip we will look at exciting enhancements to SQL Server Management Studio.

Solution

We have seen in tip SQL Server 2016 RC0 Installation and Configuration Changes that SQL Server Management Studio is now not a part of the normal SQL Server installation, we have to download it separately and install.

In this tip I am working on the latest SQL Server Management Studio July release which can be download from this link.

While working with this latest version of Management Studio I came across some important and exciting features that I will outline below.

Highlight Current Line in SQL Server Management Studio

First thing you will notice when connected to Management Studio is a box appearing in the Query Window. Basically the box is pointing to the current line position of the cursor.

Highlighting Current Line Option in SQL Server Management Studio

As we move to other lines it moves to that line and we can also see a blue vertical bar to highlight this as shown below:

Highlighting Multiple Lines in SQL Server Management Studio

This is pretty useful to point out the current line however if we don't want to use this functionality, we have the option to disable it via: Tools -> Options -> Text Editor -> General -> Highlight Current Line.  By default this options is enabled, so to disable remove the check box from the Highlight Current Line as shown below.

Highlight Current line in Tools | Options Settings

Searching for Options in SQL Server Management Studio

While exploring the option to disable the highlight current line option, I noticed there is a search feature to find options in the Tools -> Options menu. For example, if we want to search for "highlight current", the search will show which tree items include the string.  For this example it shows Test Editor -> General.

This is a good enhancement to search for a particular options instead of browsing each tab when we do not know exactly where the setting resides.

Inline Search Option in SQL Server Management Studio

SQL Server Management Studio Quick Launch

Another search option provided in Management Studio is the Quick Launch, which we can see on top right of the interface:

SQL Server Management Studio Quick Launch

This is a new search tool to launch menu items, options and files. It will not work for searching text inside a Query Window.

Suppose you want to use the Database Engine Tuning Advisor, if you type "database" in the Quick Launch it will show all the options matching the word "database" as a link:

SQL Server Management Studio Quick Launch to search for database

Clicking on the option will open the item which in this case is the Database Engine Tuning Advisor:

SQL Server Database Tuning Advisor Launched

Later when I search in the Quick Launch for "data", I can see the Database Engine Tuning Advisor listed in the Most Recently Used section. Quick Launch caches the last 5 opened items in the Most Recently Used section that match the criteria.

Basically it includes four sections as part of search:

Most Recently Used in the SSMS Quick Launch
  • Most Recently used: Matching results from most recently used search, maximum 5
  • Menus: Matching results available from menu section.
  • Options: Matching results from Options section.
  • Open documents: Matching results from open document section.

If we type @ in the quick launch, it gives you shortcuts by which you can limit the search.

Shortcuts in SSMS Quick Launch

The options are:

  • @mru: for searching only in Most Recently Used
  • @menu: for searching in Menus section
  • @opt : for searching in Options section
  • @doc: for searching in Open Documents section

Suppose we want to search Live Query Statistics for Menus only, we type:

SSMS Quick Launch for menus only for live q

To see options for Quick launch, type quick launch in the search box and click on the Options item:

SSMS Quick Launch Settings

This shows the options for quick launch as:

Management Studio Tools | Options Quick Launch functionality

We can see we have the option to:

  • Enable or disable the quick launch functionality
  • Enable or disable the quick search area (i.e. if we don't want to search in Open Documents, remove the check box)
SSMS Disable Open Documents option

When disabling a Quick Launch option from search, SQL Server Management Studio needs to be restarted for the change to commit.

We can see matching results for Open Documents is not shown after a Management Studio restart:

SSMS Quick Launch matching results for data

SQL Server Management Studio Tabs Customization

When we open a New Query Window, by default its name contains the following information: FileName.Instance Name.Database Name along with the credential used to connect as shown below:

SQL Server Management Studio Tabs customization

Sometimes we don't want that much information to be shown.  To customize the display, go to Tools -> Options -> Editor Tab and Status Bar.

Here we can see the options for Tab Text:

  • Include Database Name - True
  • Include File Name - True
  • Include Login Name - True
  • Include Server Name - True
SQL Server Management Studio Tabs customization in Tools | Options

Suppose we want to have only server name and database name for the tab text, so disable the other options by setting them to False as shown below:

disable File Name and Login Name in SQL Server Management Studio

If we open a new tab it will include only server name.database name:

SQL Server Management Studio Tabs customization with the Instance and Database Name

Pin Tabs in SQL Server Management Studio

In a New Query Window tab there is a new toggle pin as highlighted below:

Pin Tabs in SQL Server Management Studio

This feature enables us to pin query windows we like or use frequently as shown below (i.e. backup script and performance script).

Pin Important Queries in SQL Server Management Studio

I have two pinned tabs and after I open several new query windows I can still see my pinned tabs.

To better organize the pinned tabs, we can keep them on a separate row by navigating to Tools -> Options -> Tabs and Windows then check the box for Show pinned tabs in a separate row as shown below.

Pin Tabs in Tools | Options of SQL Server Management Studio

Now the pinned tabs will be in a separate row as shown below which make it easier to manage.

Separate row for Pinned Tabs in SQL Server Management Studio

One item to keep in mind is that if we close Management Studio all pinned tabs will be removed. When we start a new session, we will have to pin the tabs once again.

Next Steps


Last Updated: 2016-08-29


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 07, 2016 - 9:14:51 PM - Fiona Back To Top

 uninstalled SSMS 2016 many times.  It was a known bug with the connection issue but it was meant to be fixed in the latest update.

 

 

Hi Fiona

 

I would suggest to install a fresh copy of the management studio and if still it is not able to connect to server , check the server name or connection parameters like port, login etc.


Saturday, September 03, 2016 - 6:08:08 AM - rajendra Back To Top

 

Hi Fiona

 

I would suggest to install a fresh copy of the management studio and if still it is not able to connect to server , check the server name or connection parameters like port, login etc.


Thursday, September 01, 2016 - 7:47:38 PM - Fiona Back To Top

 I don't know if it's just me but it constantly gets stuck connecting to a server and can run forever with the connect to server screen.  Went back to 2014.

 

 


Tuesday, August 30, 2016 - 11:43:02 AM - Randy Petty Back To Top

This time the error refers to out of memory exception.  But the VM I remote onto ( Server 2012 ) has 12GB of memory with only 5GB in use.

Application: Ssms.exe

Framework Version: v4.0.30319

Description: The process was terminated due to an unhandled exception.

Exception Info: System.OutOfMemoryException

Faulting application name: Ssms.exe, version: 2015.130.15700.28, time stamp: 0x57ab9fa7

Faulting module name: KERNELBASE.dll, version: 6.3.9600.18340

 


Tuesday, August 30, 2016 - 10:22:40 AM - rajendra Back To Top

Hi Randy: I have never faced this kind of issue with remote desktop particularly.it is true that ssms used to hun sometimes but it seems to better as far i have tested. it is worth to check the event viewer if this happens.


Tuesday, August 30, 2016 - 8:36:24 AM - Randy Petty Back To Top

 Thanks for the very helpful tips.   The only problem I have with SSMS, and it's only a little better with 2016, is that when used on a remote desktop, the program crashes about twice per week.  Usually I'm able to restore the queries I had open since they are "auto-saving," but it is a big time waster.

Something about working with SSMS on a remote desktop makes it unstable.


Monday, August 29, 2016 - 4:12:54 AM - Igor Back To Top

Very useful hints, thank you for sharing!


Learn more about SQL Server tools