1 SQL Server Performance Dashboard

SQL Server performance dashboard are the step of reports that will give user the overview of the SQL Server performance .

You can download the installer from here.

The installer is actually just copy set of files into the install directory.

For every monitor instance, we need to run this setup.sql to add necessary store procedure to the database. This would create bunch of SPs and functions in msdb.

In SSMS, Select the instance –> Reports –> Custom Reports

Select the Performance_dashboard_main.rdl

This would open up the Front page of the Performance Dashboard . The report is interactive, you can drill down for more information. The report is utilize the DMV, hence, there is no actual overhead on the SQL Server to collect those information.

Permission

Below 2 permission are required in order for user to execute the reports.  The report would not create the new trace but it still need the alter trace permission.

• View server state
• Alter trace

If we don’t grant the “alter trace” permission, we would get  SYS.TRACES error.

SYS.TRACES is the catalog view. We can not just grant the select permission on it. Therefore the entire “ALTER TRACE” permission are required.

By default, the SQL Server catalog views only show objects on which the principal is granted a permission.  Therefore if user does not have the permission on the object level, the report would not display.

Compare below 2 reports. The first one is executed by sysadmin. User can see all the database.

The 2nd reports is executed by user in the test database but has just the public user in the database.