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 Transparent Data Encryption Alternative - Free Webinar
 

New Features in SQL Server Management Studio v17


By:   |   Last Updated: 2017-11-28   |   Comments (3)   |   Related Tips: More > SQL Server Management Studio

Problem

SQL Server Management Studio (SSMS) is one of the most important tools for working with SQL Server. It provides a user-interface for performing numerous SQL Server database administration and development tasks. SSMS has improved a lot over the years along with each new SQL Server version. Microsoft recently released v17 of SSMS with a new look and exciting new features. In this tip, we will present an overview of SSMS v17.x and explore some of its important features.

Solution

SQL Server Management Studio is the primary tool to for SQL Server DBAs and Developers for SQL Server on-premises, Azure SQL Database, and SQL Data Warehouse. As we have seen in SQL Server 2016, the SQL Server Management Studio installation is not part of the server installation. In SQL Server 2017 also we need to download it separately from the Internet. We can download the SSMS version we want.

In the SQL Server 2017 Installation Center, there is a link to install SQL Server Management Tools (i.e. Microsoft SQL Server Management Studio, SQL Server command-line utilities (SQLCMD, BCP, etc.), SQL Server PowerShell provider, SQL Server Profiler and the Database Tuning Advisor) as shown below.

SQL Server 2017 Installation Center

We can also go to SQL Server Management Studio website and there are two links for installation as shown below.

SQL Server v17.x Management Studio Installation options
  • Download SQL Server Management Studio 17.3: This link downloads and installs SSMS v17.3. This installation will not impact existing SSMS versions.
  • Download SQL Server Management Studio 17.3 upgrade package: If we have installed SSMS v17.x and want to upgrade it to 17.3, we can use this upgrade package.

Once the installation file download is complete, we can simply run the installation and it will be installed on your system.

If we look at SQL Server 2016 Management Studio, it shows a build version although there is no SSMS version specified.  The build version matches the version number of the SQL Server Database Engine starting with 13.0.

SQL Server 2016 Management Studio Properties

Now when we install the SQL Server Management Studio v17.x and look at the properties, we can see the SSMS version also incorporated as shown below.

SQL Server v17.3 Management Studio Properties

We can see above, that the SQL Server Management Studio version which I installed is 17.3 with build version 14.0.17199.0 which is for SQL Server 2017.  At the time of writing, it is the most recent release for SSMS v17.x.

Now let's take a look at some of the new and exciting features of SSMS 17.x.

New SSMS Application Icon and Graphical Interface

SSMS v17.x doesn't replace the existing SSMS versions included with previous SQL Server installations. Instead, it installs it separately with a new icon named Microsoft SQL Server Management Studio 17. Both the SSMS versions (Old and New) can be launched and worked on in parallel.

SQL Server v17.x Management Studio launch options

Once we launch the SSMS v17.x version it comes up with a new graphical interface in line with the Visual Studio shell.

SQL Server 2016 Management Studio

SQL Server 2016 Management Studio start up screen

SQL Server Management Studio v17.3

SQL Server v17.3 Management Studio startup screen

All the icons internal to SSMS have changed and look different as shown below.

SQL Server 2016 Management Studio

SQL Server 2016 Management Studio icons

SQL Server Management Studio v17.3

SQL Server v17.3 Management Studio icons

Active Directory - Universal with MFA authentication in SSMS

We can see a new authentication method: Active Directory Universal with MFA support.  This option is used to connect to Azure SQL Server Database and Data Warehouse instances, in addition to the previous Windows and SQL Server authentication methods.

SQL Server 2016 Management Studio

SQL Server 2016 Management Studio authentication options

SQL Server Management Studio v17.3

SQL Server v17.3 Management Studio authentication options

So now the supported authentication methods are:

  • Windows Authentication
  • SQL Server Authentication
  • Active Directory Universal with MFA support
  • Active Directory Password
  • Active Directory Integrated

As per Microsoft documentation, Active Directory - Universal with MFA authentication is an interactive method that also supports Azure Multi-Factor Authentication (MFA). Azure MFA helps safeguard access to data and applications while meeting user demand for a single sign-in process. It delivers strong authentication with a range of easy verification options (phone call, text message, smart cards with a pin, or mobile app notification), allowing users to choose the method they prefer. Interactive MFA with Azure AD can result in a pop-up dialog box for validation.

SSMS Embedded Performance Dashboard

SQL Server Performance Dashboard Reports are available as a set of custom reports in SQL Server Management Studio (SSMS) which runs against the connected instance in Object Explorer. These performance reports present the performance statistics using the system Dynamic Management Views. Until now, we have had to download the Performance Dashboard Reports and install them on SQL Server along with the execution of several scripts provided.

Now in SQL Server Management Studio v17.x, the Performance Dashboard Reports are included as built-in reports. We don't have to make extra arrangements for the installation of the reports and scripts. We can simply navigate from the SQL Server Management Studio, right-click on the instance name, choose Reports, then Standard Reports, from here you can open the Performance Dashboard embedded reports as shown below:

SQL Server v17.x Management Studio Performance Dashboard Reports

Once we click on Performance Dashboard, we can see useful information about the most expensive queries in terms of CPU consumption, I/O consumption, execution time, logical and physical reads and logical writes. We can also get detailed information from the dashboard reports.

SQL Server v17.x Management Studio Performance Dashboard Report with System CPU Utilization and Current Waiting Requests

You can find more information about performance dashboard from the link provided in Next Steps section.

SSMS Presenter Mode

With SSMS version v17.x, we can enable a new feature called Presenter Mode, which automatically sets the fonts and sizes of the SSMS interface to improve visibility when screen sharing your system in a conference call or using a projector.

To enable this feature, type the word PresentOn in the Quick Launch textbox on the top right of the screen or press Ctrl+Q.

SQL Server v17.x Management Studio Presenter Mode option

Once we clicked on PresentOn, we can see the difference in the font size, it is automatically set to a large font size.

SQL Server v17.x Management Studio Presenton Mode output is set to a large font

To switch it back to the default view, the command is not PresentOff as we might expect. Instead, we must type RestoreDefaultFonts in the Quick Launch textbox as shown below:

SQL Server v17.x  Management Studio revert back to default mode from presentation option

With this the RestoreDefaultFonts default font size is restored.

SQL Server v17.x  Management Studio revert back to default mode from presenton output

Note: The results grid will not take on the new settings until SSMS is restarted. Remember this when switching Presenter Mode on and off.

SSMS Showplan Node Search

Normally when we analyze a large execution plan, we want to check for the specific table name, column name, operator, etc. But until now it has been difficult to find this particular information in large execution plans. We have to find the particular item manually and if the plan is very large, it is very difficult to do so.

SQL Server Management Studio v17 provides Database Administrators the ability to search for plan information by using Ctrl+F. We can also right click on the execution plan and choose Find Node.  This new search feature can be used with any feature that uses the graphical showplan, such as the Query Store, the Plan Comparison and the Plan Scenarios.

Suppose we have an execution plan as shown below and we want to search for specific information.  Right click on the execution plan and choose Find Node. This opens a new window to search as per your requirements.

SQL Server v17.x Management Studio showplan node option
SQL Server v17.x Management Studio showplan node search screen

The search mechanism allows you to choose from different properties, making the graphical showplan navigation process easy. We also have the power to search for an exact value using (=) operator or a similar value using the (Contains) operator. The list of properties that we can search for are shown below:

SQL Server v17.x Management Studio showplan node search field list of properties

Suppose we want to search in the execution plan for the Clustered Index Scan.  So we type this in the search box as shown below. This will take us directly to the particular operator/node.

SQL Server v17.x Management Studio show plan search example-1

We can click on the Arrow icons to search for the next similar operator/node.

In another example, if we want to search for EstimatedRowRead we can find it as shown below:

SQL Server v17.x Management Studio show plan seaxh example-2

At the time of writing, SQL Server Management Studio v17 is the latest version of the product and contains lots of new, exciting and helpful features. We will explore more of these features in future tips.

Next Steps


Last Updated: 2017-11-28


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, November 29, 2017 - 7:59:41 AM - David Bird Back To Top

 

 Thanks for researching all the new features. I will be using that new find command for execution plans.


Tuesday, November 28, 2017 - 7:17:34 PM - buddy arbuckle Back To Top

 I have not seen anywhere in SSMS 2016/2017 where you can easily save Additional Connection Parameters.  I work in Availablitiy Groups where connections to the AG listener is used.  MultiSubNetFailOver and ApplicationIntent still cannot be saved when creating a registered server without doing some work around.  I have gone to SSMS Boost to provide the support (great tool).  Just suprised MS has not done anything with this need.

 


Tuesday, November 28, 2017 - 10:23:30 AM - Robert Plata Back To Top

 This is great!  Thanks, Rajendra, for this article.  

 


Learn more about SQL Server tools