SQL Server Management Studio is an integrated graphical interface that is used to configure, manage, monitor and administrate the SQL Server instances hosted on the local machine, on a remote server or in the cloud. It provides us with editing, debugging and deploying environment for the T-SQL, XML, MDX and DMX languages.
With the release of any new SQL Server version that introduces new features and enhancements, SQL Server Management Studio will be also upgraded and enhanced to fit these enhancements, having the same version number of that SQL Server version.
For example, in SQL Server 2016, the version number of the SQL Server Database Engine starts with 13.0, and the SQL Server Management Studio also takes the same version number 13.0. SQL Server 2017 was announced now, taking a new version number of 14.0. But the SQL Server Management Studio that comes with the 14.0 SQL Server version has taken a different versioning track, where the last version number for the SQL Server Management Studio released recently is 17.2, rather than using 14.0, which is the version number of the SQL Server 2017. The build number for the last released SQL Server Management Studio is 14.0.17177.0 as you can see in the below snapshot taken from the About choice of the HELP menu:
The SQL Server Management Studio 17.2 version provides support to all features of SQL Server 2008 through SQL Server 2017, in addition to supporting the Azure SQL Database and Azure SQL Data Warehouse features. SSMS 17.2 can work with SQL Server 2000 and SQL Server 2005 versions, but you may face some problems when working with some features. SQL Server Management Studio 17.2 can be installed on a machine that works with the Windows 7 through Windows 10 and on a server, that works with Windows Server 2008 R2 through Windows Server 2016, making sure that you have installed the latest available service pack for that operating system.
Similar to SQL Server 2016, the SQL Server Management Studio installation is not included within the SQL Server 2017 shared features installation media. Instead of that, you will find a separate line for Install SQL Server Management Tools in the Installation tab. This separate line forwards you to the Microsoft SQL Server Download Center from where you can download a separate installation file to install the SQL Server Management Studio. This installation file includes all SQL Server Management Tools including SQL Server Management Studio, SQL Server command-line utilities, SQL Server PowerShell provider, SQL Server Profiler and the Database Tuning Advisor, as shown below:
Once the installation file is downloaded completely, you can easily go through the simple installation steps for the SQL Server Management Studio that starts with the window shown below:
If there is another version of SQL Server Management Studio installed previously in your machine, SSMS 17.2 will not upgrade or replace the already exists copy, it will be installed side by side with that old version, with the ability to work on both copies on that machine, by choosing between it as shown below:
In this article, we will go through the new enhancements that are included in the SQL Server Management Studio 17.2 version.
The Connection Dialog Box
In SQL Server Management Studio 17.2, the connection dialog box supports an extra three authentication methods that are used to connect to Azure SQL Server Database and Data Warehouse instances, in addition to the old Windows and SQL Server authentication methods. The five supported authentication methods are listed below:
- Windows Authentication
- SQL Server Authentication
- Active Directory – Universal with MFA support
- Active Directory – Password
- Active Directory – Integrated
The Active Directory – Password and Active Directory – Integrated are non-interactive authentication methods supported by the Active Directory Universal Authentication and can be used in many applications such as the ODBC and JDBC. The Active Directory – Universal with MFA support is an interactive authentication method that supports Azure Multi-Factor Authentication, which provides a strong authentication with a range of easy verification options, and the user can choose the method that he prefers.
The five authentication methods are shown in the Authentication drop down list below:
The Default Scripting Option
In the previous SQL Server Management Studio versions, the generated script will target the latest released SQL Server version. In SQL Server Management Studio 17.2, the Match Script Settings to Source has been added, with the default True value means that the generated script will target the source SQL Server instance’s version, edition, and engine type, where the False value will force the scripting to behave as the previous SQL Server Management Studio versions.
The Match Script Settings to Source option in addition to all scripting options are no longer available under the General Scripting Options section. They can be found now under its own new section, called the Version Options. You can browse it by choosing Options from Tools in the menu bar as shown below:
Under the SQL Server Object Explorer tab, choose the Scripting sub-tab and you can override the True default value of the Match Script Settings to Source option under Version Options section as you can see below:
The Embedded Performance Dashboard
Microsoft SQL Server 2005 Performance Dashboard provides us with a group of reports that help database administrators in identifying different types of performance issues and resolve it easily. These performance reports present the performance statistics using the system Dynamic Management Views. With all the updates applied to the performance dashboard to work with the new SQL Server versions and take benefits from the newly defined dynamic management views, it still not included within the SQL Server installation media, and requires a separate download for its installation file from the Microsoft download page to be installed on your machine.
The good news is that, in SQL Server Management Studio 17.2, the Performance Dashboard is included as a built-in Standard Report, and available for use without any extra download or installation process. You need only to connect your SQL Server 2008 or later instance using the SQL Server Management Studio 17.2 and open the Performance Dashboard to browse its different performance reports. From the SQL Server Management Studio, right-click on the instance name, choose Reports, then Standard Reports, from where you can open the Performance Dashboard embedded reports as shown below:
The Performance Dashboard provides us with performance data that can be used to detect and resolve many performance issues, such as the CPU bottlenecks, I/O bottlenecks, missing indexes and blocking issues. The first view of the Performance Dashboard that you will see when you open it from the SSMS 17.2 will be as below:
As you can see from the previous report, the Performance Dashboard provides us with useful information about the most expensive queries in terms of CPU consumption, I/O consumption, execution time, logical and physical reads and logical writes, with the ability to dive deeply in the reports to get detailed information about these queries, as shown below:
The Performance Dashboard helps us in getting information about missing indexes that could help DBAs improve query performance as shown below:
In addition to metadata about the monitored databases hosted in the connected SQL Server instance as follows:
Showplan Node Search
When you analyze a big execution plan to identify a specific operator, table name, column name or the node with the highest cost, it may take you a long time and big effort to find it, due to a large number of nodes and information provided in the execution plan. It was a dream from the database administrator to click CRTL+F and search for such plan information. With SQL Server Management Studio 17.2 the dream comes true, as it includes the ability to search for a lot of information in the graphical showplan. 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.
To start the node information search in the opened graphical showplan, right-click on the plan and choose Find Node as shown below:
Or just click CTRL+F when you open the graphical showplan, and a new search bar will be displayed. Assume that we need to tune the below SELECT query by studying the graphical execution plan using SQL Server Management Studio 17.2:
|
1 2 3 4 5 6 |
SELECT Emp_First_Name , Emp_Last_Name , Emp_Adress , EMP_PhoneNumber , EMP_Salary , DEP.DEP_Name ,DEP.DEP_Location FROM Employees EMP JOIN Departments DEP ON EMP.EmpDepID =DEP.DEP_ID WHERE Emp_Adress like '%USA%' OR Emp_Last_Name LIKE '%EVA%' |
Once the graphical plan is opened, click CTRL+F and the search bar will be displayed where you can start the node information search on the opened plan as shown below:
The new search mechanism allows you to choose from different properties, making the graphical showplan navigation process easy. With the ability to search for an exact value using (=) operator or a similar value using the (Contains) operator. The list of properties that you can search for are shown below:
For example, you can search for a node that represents the operation on a specific table, by choosing the table property, choose the comparison operator, which is contains in my example, then write the table name in the blank and click on the arrow that will show you the search result as shown below:
Another example, is to search for the node that reads more than 1000 rows, by choosing the ActualRows property, choose the comparison operator, which is contains in the below example, then write (1000) in the blank and click on the arrow in order to see the search result as shown below:
The last example here, with infinite number of search criteria that you can perform, is to search for the node with a specific operator, which is scan operator in our situation, by choosing the PhysicalOp property, choose the comparison operator, which is contains in the below example, then write (scan) in the blank and click on the arrow and you will see the search result as shown below:
Conclusion
SQL Server Management Studio is a graphical user interface that can be used to develop and administer the SQL Server instances hosted on the local machine, remote server or in the cloud. SQL Server Management Studio 17.2 is the latest released version of the SSMS that comes with a number of enhancements such as the new authentication method used to connect to Azure SQL Database and SQL Data Warehouse, the new scripting options, the integrated Performance Dashboard and the Showplan node search that are described in details within this article.
Next articles in this series
- What’s new in SQL Server Management Studio 17.3; Import Flat File wizard and XEvent Profiler
- What’s new in SQL Server Management Studio 17.4; Always On AG dashboard enhancements and more
- What’s new in SQL Server Management Studio 17.5; Data Discovery and Classification and more
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021