It is always handy to obtain a pre-configured report about system configuration and resource usage, in this regard SQL Server Management Studio embedded with series of Server-wide and Database-scope reports which are called Performance Dashboard Reports.

These reports are useful to obtain issues and problems that may resolve to find the root cause too, in many cases in my Consulting engagements I always use them to obtain the necessary information. The report that delivers is snapshot of system state which confirms how the process is used and more information including:

• CPU bottlenecks (and what queries are consuming the most CPU)
• IO bottlenecks (and what queries are performing the most IO)
• Index recommendations generated by the query optimizer (missing indexes)
• Blocking
• Latch contention

The above list is just highlight of what these dashboard reports gets you information pertaining to performance tuning related statistics. By default the DMVs are used to capture the required information, as no additonal tracing or schedules are necessary to obtain the results. Out of all there is no necessity of Reporting Services installation on the server or client machine to obtain these reports. Though they are available as in-box reports you can also download the relevant setup from this download link.

Once you have downloaded and installed the the Performance Dashboard Reports you can use SSMS to open the setup.sql script that will be installed (by default unless you change the folder during installation) in [%ProgramFiles(x86)%\Microsoft SQL Server\110\Tools\Performance Dashboard] folder and run the script.  ONce the script is executed successfully then you can obtain the reports using right-click on instance --> Choose Reports --> Custom Reports. Then go to relevant installation directory (again) to open the performance_dashboard_main.rdl file.  This is useful to explore the health of your server by clicking on the various charts and hyperlinks in the report.

Finally the relevant remaining reports are accessed as drill-through operations from the main page of the report, to see more help on these performance dashboard reports you can open the help file, PerformanceDashboardHelp.chm from above specified folder.

The above stuff is useful for Performance related reports, however there is another Management dashboard reports available in SQL Server 2012, it is PowerPivot Management Dashboard.

PowerPivot Management Dashboard is a collection of predefined reports and web parts in SharePoint Central Administration that help you administer a SQL Server PowerPivot for SharePoint deployment.

See below for more information on the concept:

Prerequisites

Dashboard Roadmap

Open PowerPivot Management Dashboard

Edit PowerPivot Dashboard

Create Custom Reports for PowerPivot Dashboard

Source Data in Dashboards