SQL Server Management Studio 17.x Important Features

By: Rajendra Gupta   |   Updated: 2018-08-20   |   Comments (1)   |   Related: > SQL Server Management Studio

Problem

SQL Server Management Studio is now a standalone installation and is not part of the SQL Server setup. In my previous tips, New Features in SQL Server Management Studio v17 and SQL Server Management Studio Import Wizard Improvements, we explored some of the important features of SQL Server Management Studio 17.x version. In this tip, we will go through some other exciting features.

Solution

SSMS 17.x is the latest generation of SQL Server Management Studio and provides support for SQL Server 2017 and older versions.  The idea behind the separate installation file of SSMS is to keep it separate from the server component and Microsoft is providing regular release versions with new features and enhancements over previous SSMS versions. SSMS 17 is common for all versions and editions of SQL Server and does not require any license to use it. The below table shows SSMS 17.x release, build information.

We can view SSMS properties from Help > About.  Below the SQL Server Management Studio versions is 17.8.1.

In this tip, we will be exploring these features of SQL Server Management Studio 17.x:

1. Upgrade SSMS to the latest version
2. SQL Server AUTOGROW_ALL_FILES Option
3. The Built-in Performance Dashboard

Let us explore these one by one.

1. Upgrade SSMS to the latest version

If we want to install SSMS 17.x latest version, we have two options:

• For a new installation, we need to download SSMS from the first link Download SQL Server Management Studio 17.8.1 and install it.
• If we want to upgrade SSMS 17.8.1 from the previous 17.x version, we do not need to install the complete SSMS set up, instead, we can just use the incremental upgrade package. For this, use the link Download SQL Server Management Studio 17.8.1 upgrade package (Upgrades 17.x to 17.8.1)

Note: While upgrading SSMS, close SSMS if it is running or you will get this error message.

2. SQL Server AUTOGROW_ALL_FILES Option

SQL Server 2016 provided an option to control auto-growth of the database files within the same filegroup at the database level. In SQL Server 2016, we could do it using the ALTER DATABASE option AUTOGROW_ALL_FILE.  You can go through the tip Expand All SQL Server Database Files Simultaneously.

If we go to the database properties by right clicking on the database and then click on Filegroups, in previous SSMS versions other than 17.8.1 we see the following:

While if we look at the same properties in SSMS 17.8.1, we get the below options.  As we can see, we get a new option with checkboxes for 'Autogrow All Files' against each filegroup row.

In SSMS 17.8.1 version, if we want to enable the 'Autogrow All Files', just put a check against the filegroup name. For example, let's enable this option for USERDATA filegroup by putting a checkbox against the USERDATA filegroup as shown below.

If the database is in use, we get the below error message. So, you need to first close database connections before applying this setting.

We can script out this option using Script > Script Actions to New Query Window.

It generates the below script:

USE [WideWorldImporters]
GO
declare @autogrow bit
SELECT @autogrow=convert(bit, is_autogrow_all_files) FROM sys.filegroups WHERE name=N'USERDATA'
if(@autogrow=0)
   ALTER DATABASE [WideWorldImporters] MODIFY FILEGROUP [USERDATA] AUTOGROW_ALL_FILES
GO

Note: The current SSMS 17.8.1 version generates two scripts - one script with a USE statement and the second script with a USE master statement. The script with USE master is generated in error and should be discarded. Run the script that contains the USE statement. This should be fixed in the next release.

Now let's look at the AutoGrowthEnable property for all of the filegroups in the database.

• AutoGrowthEnable=1 shows 'Autogrow All Files'is enabled for the particular filegroup.
• AutoGrowthEnable=0 shows 'Autogrow All Files'is disabled for the particular filegroup.

SELECT
    DB_NAME() DatabaseName,
    DBF.name AS FileName,
    FileG.name as FileGroupName,
    FileG.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileG
    ON DBF.data_space_id = FileG.data_space_id

We can see the 'Autogrow All Files' is enabled for USERDATA filegroup. AutoGrowthEnable is set to 0 for other filegroups, as we did not enable 'Autogrow All Files' for all filegroups.

In the next step, let's view the behavior of enabling 'Autogrow All Files' for a filegroup.

Create a new database 'DemoDatabase' with 3 files in the filegroup Primary as shown below.

Set 'Autogrow All Files' by putting a check on the checkbox.

Create a table and insert data into it.

Use Database DemoDatabase
Go
CREATE TABLE SQLBlogs
( 
  BlogName NVARCHAR(500)
)
Go
Insert into SQLBlogs values('MSSQLTips')
Go 15000

We can see that the database expanded at the same time, resulting in the same size for all files in the filegroup.

Generate database script

Now we will generate a database script for the database we enable AutoGrow All Files options in the above demo.

To do so, right click on the database and select Script Database as > CREATE To > New Query Editor Window.

This generates the database script as shown below:

We can see in the script that the ALTER DATABASE statement contains a script to enable the filegroup for AUTOGROW_ALL_FILES.

3. The Built-in Performance Dashboard

The SQL Server Performance dashboard contains reports to troubleshoot performance issues with the help of multiple reports. Previously, we needed to download the performance dashboard reports and then install before using.  You can find instructions to download and install in Install SQL Server Performance Dashboard Reports.

With SQL Server Management Studio 17.2 onwards, the Performance Dashboard now comes as part of the Standard Reports. We do not need to download and install.

As per the SSMS 17.2 release notes, the Performance Dashboard:

• Can connect to SQL Server 2008 and newer versions.
• The missing indexes sub-report uses scoring to assist in identifying most useful indexes.
• The historical wait stats sub-report now aggregates waits by a category. Idle and sleep waits filtered out by default.
• There is a new historical latches sub-report.

To use the embedded performance dashboard, connect to the SQL instance in SSMS and go to Reports > Standard Reports > Performance Dashboard.

This launches the Microsoft SQL Server Performance Dashboard containing the below section:

• System CPU Utilization
• Current Waiting Requests
• Current activity (User Requests and User Sections)
• Historical information (Waits, IO Statistics)
• Miscellaneous information (Active Traces, Database Information)

This dashboard contains a few links (in Blue) to dig into SQL Server and find out more information. For example, below we can see historical waits as per their wait category and wait time.

The IO statistics link provides information about IO breakdown in terms of reads, writes, write wait times and read wait times grouped by the database.

We can also get the expensive queries to order by:

• CPU
• Duration
• Logical reads
• Physical Reads
• Logical Writes

For example, the below graph shows expensive queries - Logical reads (Cumulative Data):

We can see the databases present in the instance with their properties, this is also an interactive kind of report where we can sort out the results as required.

If we want to view details about any particular session, we can click on the session id and get the details.

Since SQL Server Management Studio provides a built-in performance dashboard, it becomes easy to troubleshoot and get the insights without any additional installation or scripts.

I have given a brief overview of SQL Server Performance Dashboard. You can explore more about the performance dashboard from the links in the Next Steps section.

Next Steps

• Read more about SQL Server Management Studio release notes.
• Get overview about SQL Server Management Studio Performance Dashboard Reports.
• Read more on SQL Server Management Studio tips.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips