SQL Server Performance Dashboard Reports unleashed for Enterprise Monitoring !!!

SQL Server 2012 Performance Dashboard Reports is one of most popular SQL Server monitoring solution for customers and SQL community leveraging dynamic management views (DMVs) for monitoring and reporting and available at no cost to consumers. SQL Server Performance Dashboard Reports are available as a set of custom reports in SQL Server Management Studio (SSMS) which runs against the connected instance in Object Explorer. When monitoring large enterprise deployments of SQL Server, hosting SQL Server Performance Dashboard Reports on a central reporting server can provide additional benefits making life easier for enterprise DBAs for monitoring and troubleshooting SQL Server issues. To support hosting SQL performance dashboard reports on a central SQL Server Reporting Services instance, we have customized SQL Server 2012 Performance Dashboard Reports, added new reports and uploaded in Tiger toobox github repository for customers and SQL community. The reports are tested to run against SQL Server 2012, SQL Server 2014 and SQL Server 2016 versions of target SQL Server instance and can be deployed against SQL Server 2012, SQL Server 2014 or SQL Server 2016 Reporting Services instance.

Following are some of the benefits of hosting SQL Performance dashboard reports on central SSRS reporting server.

  • Monitoring Reports accessible anytime, anywhere using browser – This removes the dependency of thick client like SQL Server Management Studio (SSMS) to be present on the workstation server allowing DBAs, DevOps audience to check the health of SQL Server and resource consumption using web browser from any workstation machine with access to the server.
  • Scheduling automatic report delivery – SSRS allows scheduled email or file share delivery of reports. This allows DBAs, application owners and database stakeholders to choose push model where by performance health reports can be scheduled to run against specified SQL Server instances at the specified time and be delivered in their mailbox to proactively monitor overall health of SQL Server instance and detect any anomaly.
  • Performance Base lining using Report Snapshots – SSRS allows you to capture scheduled point in time report snapshots at the specified time interval allowing DBAs to establish performance baselines using historical snapshots for the target SQL Server instances.
  • Linked Reports for Application owners and other stakeholders – In an enterprise environment, most application teams and stakeholders are interested to see the performance, resource consumption, blocking information and overall health of their SQL Server instance on-demand. In such scenarios, DBAs can create linked reports for the target SQL Server instances on the SSRS central server and delegate them permissions to view reports for their target SQL Server instance of interest. This allows application teams, developers to be self-sufficient to check the overall health of their SQL Server instances creating some bandwidth for DBAs who needs to be contacted only if there is an anomaly or problem detected.

Architecture

The following diagram shows high level architecture when deploying SQL Performance Dashboard Reports on a central monitoring SSRS server instance for monitoring all the target SQL Server instances in an enterprise or mid-size deployments of SQL Server.

Setting Up and Configuring SQL Server Performance Dashboard Reports for Monitoring

The following section provides the steps for setting up and configuring SQL Server Performance Dashboard Reports for monitoring.

 

  1. Install and configure SQL Server Reporting service (any version greater than SQL Server 2012 with latest SP and CU) on a server identified as a Central Monitoring Server. The central monitoring server should be part of the same domain and network as the target SQL Server instance.

  2. Download SQL Performance Dashboard Reporting Solution from Tiger toobox github repository.

  3. Download SSDT-BI for Visual Studio 2012 or Download SSDT-BI for Visual Studio 2013 and install BI designer on workstation where github solution is downloaded or copied.

  4. Open PerfDashboard solution using Visual Studio 2012 or 2013 on the workstation and deploy it against the SQL Server Reporting service instance by providing the TargetServerUrl as shown below

  5. Make sure report deployment is successful and browse the report manager url to see the reports deployed under SQL Server Performance Dashboard folder.

     

  6. Run setup.sql script from Tiger toobox github repository against all the target SQL Server instances which creates a schema named MS_PerfDashboard in msdb database of SQL Server instance. All the relevant objects required for SQL performance dashboard reports are contained in MS_PerfDashboard schema.

  7. You should always start with performance_dashboard_main report as a landing page and navigate to other reports from the performance dashboard report. If you have deployed the reports against SQL Server 2016 Reporting services instance, you can set performance_dashboard_main report as favorite for easier navigation as shown below.

     

     

  8. When you browse performance_dashboard_main report, it will ask you the target SQL Server instance against which you wish to see the report. If setup.sql is ran against the target SQL Server instance, you will see the data populated in the report.

     
     

     

  9. You can further click on the hyperlinks to navigate to that report for further drill through as shown below.

     

The steps 2-7 above for downloading and deploying SQL Server Performance Dashboard Reports to SSRS instance is completely automated by one of our MVPs Aaron Nelson (b|t) using PowerShell. So, you can skip steps 2-7 above and instead download and run the PowerShell script from github repository here. Following are some of things to know before running the PowerShell scripts

  • You need to be running the PowerShell script from the workstation machine which has connectivity to internet, from where you can browse your Report Server and which have all the SQL Server instances in your environment registered as Registered Servers in SSMS. Preferably, this is your central management server where SSRS is installed.
  • The script downloads the solution from github for you and unzips the solution in C:\SQL Server Performance Dashboard folder. If you want to unzip and save the solution at a different location, you can modify the $ReportsBaseFolder = 'C:\SQL Server Performance Dashboard' location in the script.
  • In the script, the assumption is, you will be deploying the reports on default instance of SSRS on the local server. If you have named instance of SSRS or SSRS server is located on a different server, you need to modify report server url $SSRSInstance = 'https://localhost/ReportServer' appropriately before running the script.
  • The script automatically deploys the Setup.sql script to all SQL Server instances registered in Registered Servers in SSMS.

Thanks to Aaron, the deployment of SQL Server Performance Dashboard is now smooth and easy and doesn’t require installation or knowledge of SSRS deployment. Aaron’s PowerShell script is integrated in Tiger toobox github repository for customers and SQL community. If you wish to improvise or add or contribute to the solution, feel free to send a pull request in GitHub for the solution.

All the reports use Windows authentication to connect to the target SQL Server instance so if browsing user is part of a different domain or do not have login or VIEW SERVER STATE permissions, the reports will generate an error. Further, this solution relies on Kerberos authentication as it involves double hop (client -> SSRS server -> target SQL instance), so it is important that target SQL Server instances have SPNs registered. The alternative to Kerberos authentication is to use stored credentials in the report which helps bypass double hop but is considered less secure.

If you have also deployed the SQL Performance Baselining solution and System Health Session Reports from Tiger toobox github repository, you can use the same central SSRS server for hosting all the reports and running it against target SQL Server instances as shown below. The SQL Performance Baselining solution can be useful to identify the historical resource consumption, usage and capacity planning while SQL performance dashboard reports and System health session reports can be used for monitoring and point in time troubleshooting.

Parikshit Savjani
Senior Program Manager (@talktosavjani)