Custom SSMS SQL Server Instance Level Metrics Report


By:   |   Updated: 2021-03-24   |   Comments (2)   |   Related: 1 | 2 | More > Monitoring


Problem

As a database administrator or consultant, having a quick view of a SQL Server environment can be extremely helpful.  There are many tools and queries that you can run to gather information, but putting this all together into a comprehensive dashboard is not that hard with the help of a SQL Server Reporting Services (SSRS) report.  In this article, we look at a custom SSRS report that gathers information from many different parts of the SQL Server environment and displays them all in one central location for quick analysis and troubleshooting.  And since this is an SSRS report, it is easily customizable to meet your needs.

Solution

I created an open-source solution called StudyYourServer, that is a set of SSRS reports built to work as custom reports inside SQL Server Management Studio (SSMS). In this tip I will show you how to import the Server Dashboard report to SSMS and what is included in the report.

SSMS Custom Reports

You have probably noticed in SSMS that there are standard built in reports.  In addition, there is an option to import your own custom SSRS reports.

Standard SSMS Reports

A custom report is just importing your own reports into SSMS, these reports are standard .rdl files that you can develop using SSDT.

Requirements for this Solution

Since the most recent SQL Server Data Tools (SSDT) version was used, there are some compatibility requirements for the reports to work:

  • Supported for SQL Server 2012 or newer only
  • Azure SQL database is not supported
  • SSMS 17.9 or newer, you can download the latest SSMS version here.

Once you have validated all the requirements, you can import the reports.

Download Custom Report

To download the custom Server Dashboard report, click here.  Save the RDL file in a location on your desktop to be used in the import step.

Import Custom Reports into SSMS

Note: for instance-level reports right click on the instance name and for database-level reports right click on the database and follow the steps above.

To import the custom Server Dashboard report, right click on the instance name in SSMS, select Reports > Custom Reports and then select the RDL file you want to import.

Import Server dashboard report

Locate the StudyYourServer - Dashboard.rdl file you saved previously.

Import server dashboard report 2

Note: even when the reports are imported, do not delete the original files or the next time you try to access them it will be removed from SSMS.

Server Dashboard Report Overview

To access the report, right click on the SQL Server instance and go to Reports and the report should now appear as shown below. Click on the report to view the report contents.

SSMS imported custom report

The report will display and will present you information about that instance (we will discuss each section):

StudyYourServer - Dashboard main page

Overall, the report presents multiple sections highlighting areas that need your attention, which are considered "best practices", but please note that a best practice is not a rule and some of the highlighted information may not apply to your environment. Tool tips are provided when you hover over attention areas, hinting what needs to be done to address each finding.

Now let us go to each section of the report.

General Instance Information

Most of the information here is relevant to the OS configuration, it also indicates the current version, edition, and patching level of the SQL Server. For SQL Server running on Linux, some of these fields will be empty.

For my laptop we can see that Lock pages in memory is not enabled, so it highlights this field as something important.

General information

If you hover over the Lock Pages in memory section, extra information is displayed as shown below.

Network Protocols

The network protocols shows the currently enabled protocols and for TCP/IP the port is displayed (for static ports), if the port is dynamic the port information will be empty.

network protocols

Instance Components

Components configured for that instance, along with the status and account information (note that shared components are not displayed here).

Instance Components

Disk Information

Relevant information related to disk drives where database files exists (other drives in the operating system are not displayed since report is instance scoped).

If any drive has low free space it will show a warning between 20% and 10% (yellow) and alert below 10% (red).

Also, a bar chart is included for easy comparison of drives (this example was done on my personal laptop, where I just have one drive).

Disk Information

Database Information

This is one of the sections I use most. We can quickly check for important database configuration options and quickly retrieve the size of all the databases in the instance. We can easily spot databases that do not have the latest compatibility level, have the wrong recovery model or the wrong page verification option.

The msdb database has a special check for size, since a big msdb database could indicate missing maintenance and cleanup tasks, that can slow down other processes in the instance.

In case you need to dig deeper into any of the databases, there is a database scoped report available in part 2.

Database Information

Tooltips are configured as well:

database information tooltips

Logins with Elevated Permissions

Does your instance have more sysadmins than needed, that could be a security concern.

Principals with Sysadmin privileges

Trace Flags Enabled

This shows which trace flags are enabled on the instance at the moment of report execution.

Trace Flags enabled

Instance Configuration Values

This shows the settings you can manipulate via sp_configure. Most relevant options are always highlighted in green regardless of its value, if the value in use needs your attention it will be highlighted in yellow.

This report does not enable the 'show advanced options' option, so running this report does not change any configuration on your instance.

Instance configuration values

As with the other highlighted information, tooltips are also configured showing you more information about a specific option:

Instance configuration values tooltip

Suspect Pages

This obtains information from suspect_pages table in msdb. If this section returns records, it is worth checking the database further issues. Healthy instances should have this section empty.

Suspect pages

Memory Dumps

The last section of this report is related to information from sys.dm_server_memory_dumps. If records are in this section, it is worth checking them for potential issues, especially if the dumps have a recent creation date or you have multiple entries.

Memory dumps

Fully customizable

Since this is an open-source report, you can modify it to include more information or change how the information is presented. If you encounter issues or incorrect information you can simply change as needed.

Remember, a best practice is just a standardized way of doing things that has proven to work in most cases and is generally accepted, but some of those recommendations may not work for you. Use these reports as a general guide and always use common sense and your expertise to determine if an option may or may not work for you.

Next Steps


Last Updated: 2021-03-24


get scripts

next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips



Comments For This Article




Thursday, March 25, 2021 - 6:27:03 PM - Rohan Back To Top (88461)
Very nice. Anything related to query stats would be very helpful as well. Looking forward for next reports.

Wednesday, March 24, 2021 - 1:17:53 AM - Eitan Blumin Back To Top (88456)
This looks pretty cool! Good job!


download





Recommended Reading

SSMS Custom Report for SQL Server Database Metrics

Determining space used for all tables in a SQL Server database

Killing a SPID and Checking Rollback progress in SQL Server

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications














get free sql tips
agree to terms