Saturday, September 7, 2013

0 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.

image

image

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

image

image

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

image

Select the Performance_dashboard_main.rdl

image

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.

image

image

image

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.

image

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.

image

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

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates