SSMS Custom Report for SQL Server Database Metrics


By:   |   Updated: 2021-04-02   |   Comments   |   Related: 1 | 2 | More > Monitoring


Working Around the Workload

Free MSSQLTips Webinar: Working Around the Workload

How can the production database support everything the business wants to throw at it, and be continuously available, and perform well? Tune in as we discuss how (and why) businesses keep adding on to their existing environments, and how you, as the keeper of the database, can keep it all in check.


Problem

When working with SQL Server it is helpful to have an overview of your database that you are working with. You could run several different queries and review each of the datasets, but a better approach would be to create a SQL Server Reporting Services (SSRS) report that could gather the data and display in one central location and better yet, be able to do from SSMS.

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 Database Dashboard report to SSMS and what is included in the report.

SSMS Custom Reports

You may have noticed that SQL Server Management Studio has standard reports that you can run, but did you know you could also create your own custom reports that can be run right from SSMS. A custom report is just importing your own reports into SSMS, these reports are standard .rdl files that you can develop using SSDT. See below.

Standard reports

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 Database 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

This is a database level report, so to import the report, right click on a database name and follow the steps below.

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

Custom reports option

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

downloaded reports

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 Database Info

In Part 1, we looked at the Server Dashboard report and how the database info section gave us some information regarding the database as shown below:

Database Information, part1

Let's take a look at the database specific report to see even more information about databases.

Database Dashboard Report Overview

To access the report right click in any SQL Server database you want to check and go to Reports, the report should now appear there:

StudyYourServer - Database info

The report will display and will present you the information regarding that database (we will discuss each section in a moment):

Database dashboard main page

Overall, the report presents multiple sections and highlights areas that need your attention. These are based on "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. Also, tool tips are provided when you hover over an attention area, hinting what needs to be done to address each finding.

General Database Information

Here we can find information about the configuration, status and options of the database. There are other options you may consider important that are not here, but these are the options I check most when checking for overall information of the database, so feel free to edit the report and add/remove as needed.

Database general information

In the case of the collation, it is marked as a warning because is different from the one configured on the master database:

tooltips

Backup Information

This shows you the latest backup taken for each type of backup and the average size in GB. When backups are missing the last backup date is highlighted. I did not highlight older backups, because the meaning of old can vary from user to user, but this is something you could change in the report if you want.

backup information

Database Files Space Usage Chart

Visual representation of free and used space for each data file is shown here, this complements the database file information we will see later in this tip.

size of the datafiles chart

Filegroups

This shows filegroups information for the database.

Filegroups

Data and Log Files

Similar to the SSMS tab in the database properties, it gives you information about each data file and its properties.

data and log files

Top 10 Tables by Size

This table include information and a visual representation of the top 10 biggest tables ordered by size, this will help you quickly identify space utilization issues.

top ten tables by size

User Sessions

This show connections currently using the database, you can obtain information about hostname, program name, login, and status for easy troubleshooting. Note: for busy systems, this section of the report can be very large.

User sessions

Orphan Users

An orphan user is when a database user is not correctly mapped to a login. This is commonly occurs when you backup and restore databases from a different server. Having this section empty is a good sign, but if you have an orphan user detected, you can check how to fix them here.

Orphan users

Suspect Pages

This section obtains information from the suspect_pages table in msdb, but is scoped to the current database. A healthy databases should have this section empty.

Suspect pages

Export Report Content

You can also export this data to various formats. Right click on any empty portion of the report and select Export > Excel (or any option):

export option

Once you have saved the file, you can copy and edit values if needed:

report in excel file

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





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


Article Last Updated: 2021-04-02

Comments For This Article





download














get free sql tips
agree to terms