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.
We can also go to SQL Server Management Studio website and there are two links for installation as shown below.
- 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.
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.
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.
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 Management Studio v17.3
All the icons internal to SSMS have changed and look different as shown below.
SQL Server 2016 Management Studio
SQL Server Management Studio v17.3
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 Management Studio v17.3
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:
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.
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.
Once we clicked on PresentOn, we can see the difference in the font size, it is automatically set to a large font size.
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:
With this the RestoreDefaultFonts default font size is restored.
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.
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:
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.
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:
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
- Read more about SQL Server Management Studio – Changelog.
- SQL Server 2017 officially launched on October 2, 2017. Check out the SQL Server 2017 Release notes.
- Explore SQL Server 2017 What’s new in SQL Server 2017.
- Read more about SQL Server 2017 tips.
- Explore Performance dashboards reports.
