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 Management Studio 17.x Important Features


By:   |   Last Updated: 2018-08-20   |   Comments (1)   |   Related Tips: More > 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.

Release Number Build Number Release Date
17.8.1 14.0.17277.0 June 26, 2018
17.7 14.0.17254.0 May 09, 2018
17.6 14.0.17230.0 March 20, 2018
17.5 14.0.17224.0 Feb 16,2018
17.4 14.0.17213.0 December 7,2017
17.3 14.0.17199.0 October 10,2017
17.2 14.0.17177.0 August 8,2017
17.1 14.0.17119.0 May 24,2017
17.0 14.0.17099.0 25 April 2017

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

SSMS Property

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:

SSMS Download Options

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

SSMS upgrade error

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:

Database properties SSMS versions older than 17.8.1

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.

Database properties SSMS versions 17.8.1

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.

 Database properties SSMS 17.8.1 - Enable AutoGrow All Files

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

Database properties SSMS 17.8.1 - Enable AutoGrow All Files error

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

Database properties SSMS 17.8.1 - Enable AutoGrow All Files - Generate script

It generates the below script:

Database properties SSMS 17.8.1 - Enable AutoGrow All Files - Generated 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
Database properties SSMS 17.8.1 - AutoGrow All Files Property

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.

New Database

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

New Database filegroup property

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.

Datafile properties

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.

Script database option

This generates the database script as shown below:

Generated script

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.

Performance Dashboard Option

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)
Performance Dashboard report

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.

Performance Dashboard - Historical Waits

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.

Performance Dashboard - Historical IO Report

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):

Performance Dashboard - Expensive queries - Logical reads

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.

Performance Dashboard - Database overview

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

Performance Dashboard - Session 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


Last Updated: 2018-08-20


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.



    



Tuesday, September 04, 2018 - 4:01:49 PM - Terry Grignon Back To Top

One of the frustrating things for me with 17.x which has been there from the beginning is the fact that outlining is always on even if you turn intellisense off it keeps coming back. Does anyone have a tip to put a knife in outlining?


Learn more about SQL Server tools