Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Server Management Studio Performance Dashboard Reports Overview


By:   |   Last Updated: 2015-12-21   |   Comments (8)   |   Related Tips: More > SQL Server Management Studio

Problem

In my previous tip about First Steps for SQL Server Performance Troubleshooting I briefly commented on the advantages of this free set of reports. Although you will find other tips about how to install them, in this tip I will show you its features and how to interpret these reports.

Solution

When you work as a SQL Server DBA in a production environment, speed is critical when you are facing an incident. We don't have much time to write complex queries to access the system views and for this reason most of us have a battery of queries ready to execute. But when a user complains about performance degradation he won't give you much detail other than "it doesn't work"; so you have to quickly figure out where the problem lies. That leads to the question: Which of my hundred queries should I run first? To help us, Microsoft created a set of free reports named Performance Dashboard Reports which be downloaded from this link: http://www.microsoft.com/download/en/details.aspx?id=29063.

After installing these reports using the steps provided on this tip Install SQL Server 2012 Performance Dashboard Reports you are ready to view the reports.

The next image is a screen capture that outlines what you are going to see.

Performance Dashboard Overview.

On the top of the report you will see two graphics. The one on the left shows the system CPU usage over time. Notice that the graphic bars have two colors in order to distinguish amongst the actual SQL Server instance CPU usage and the other system processes. I find it very useful to quickly determine if your instance is under CPU pressure (blue bars will be high), and even more useful when your SQL Server instance is running on a virtualized environment to detect CPU contention (orange bars will be high). But in order to confirm the CPU contention diagnosis you should check which server process is consuming that amount of CPU. If you are under CPU contention it will be the services.exe process, the system process or the interrupts. To do so I suggest that you use Microsoft's Sysinternals Process Explorer which you can download for free from this link https://technet.microsoft.com/en-us/sysinternals/processexplorer.

If you click on a bar in the graphic, you will be redirected to a sub-report that shows you an overview of the queries responsible for the recent CPU activity.

Recent CPU Activity Sub Report.

As a side note, this sub-report won't work if your regional settings are other than EN-US, because this sub-report receives the time of the bar you have clicked as a parameter of NVARCHAR data type instead of DATETIME. So if you experience this issue modify the following stored procedure to convert the DATETIME value to an ODBC canonical NVARCHAR string.

USE msdb
GO

ALTER PROCEDURE MS_PerfDashboard.usp_Main_GetCPUHistory
as
begin
	declare @ms_now bigint
	
	select @ms_now = ms_ticks from sys.dm_os_sys_info;

	select top 15 record_id,
		CONVERT(NVARCHAR(30), dateadd(ms, -1 * (@ms_now - [timestamp]), GetDate()),121 )as EventTime, 
		SQLProcessUtilization,
		SystemIdle,
		100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
	from (
		select 
			record.value('(./Record/@id)[1]', 'int') as record_id,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
			timestamp
		from (
			select timestamp, convert(xml, record) as record 
			from sys.dm_os_ring_buffers 
			where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			and record like '%%') as x
		) as y 
	order by record_id desc
	
end
GO

The graphic at the upper right shows the current waiting requests by category. Something to note is that this graphic only shows information relative to user processes. As with the System CPU Utilization chart, you can click on the bars and it will take you to the General Waits sub-report. This sub-report will show the same graphic on the upper part and in the lower part it will show a table grouped by wait category where you can see the details about the wait type, the session waiting, the resource the session is waiting for and the query text.

General Waits Sub Report.

If you want, you can click on the query text to see the query plan in another sub-report.

Query Plan Sub Report.

Also, if you click on the session id you will be redirected to a report showing the session details.

Session Details Sub Report.

Back to the main report. On the lower part on the left there is a table that shows the current activity by sessions and active requests. The most relevant indicator of this table is the cache hit ratio (the percentage of pages found in the buffer cache without having to read from disk), if this value is too low you may need to increase the memory available for the instance.

Current Activity Panel Overview.

On the lower part on the right you will see two boxes grouping other sub-reports. The upper box group reports are related to historical information about waits and IO statistics. Also, it contains six reports about expensive queries: by CPU, by Duration, by Logical/Physical reads, by logical writes and by CLR time. The other box shows miscellaneous information like active traces and extended events sessions, databases information and missing indexes.

Historical Information Panel Overview.

The Historical Waits report shows a bar chart resuming the wait time by category for a quick view and below it there is a table for a deeper analysis. The table shows the wait categories ordered by the percentage of wait time and gives us the option to expand a wait category to view its wait types also ordered by percentage of wait time. The next image below is a screen capture of this report exported to an Excel spreadsheet.

Historical Waits Sub Report.

The Historical IO report shows a table ordered by Database Name with IO related information. The most useful columns are the percentage of total IO, the percentage of reads and writes and the average read and wait times.

Historical IO Sub Report.

Below the previous table is a treeview with all the databases. When you expand any database it will show a table with the top 20 objects responsible of the most physical IO. This table gives us information about the number of object's index lookups and index range scans, if the object has missing indexes and IO wait information. One drawback is that this table includes system objects which could add some confusion when reading.

Historical IO Sub Report.

As you could see by now, this set of reports will aid you to get a quick server diagnostic. But I think that there is one point missing: volume free space. To compensate this I created a modified version of the main report with a new bar chart on the bottom that shows the available free space of disks that contains database files.

Database Volume Free Space.

If you want to use this modification you will need to replace the performance_dashboard_main.rdl file with the one on this zip file and create the following stored procedure.

USE msdb
GO

CREATE PROCEDURE MS_PerfDashboard.usp_Main_GetFreeDiskSpace
AS
 
SELECT  volume_mount_point ,
        CAST(total_bytes / 1024 / 1024 / 1024 AS NUMERIC(20, 2)) [Used Space] ,
        CAST(available_bytes / 1024 / 1024 / 1024 AS NUMERIC(20, 2)) [Free Space] ,
        CAST(CAST(available_bytes * 100 / CAST(total_bytes AS NUMERIC(20, 2)) AS NUMERIC(5,
                                                              2)) AS VARCHAR(50))
        + ' %' AS [Percentage Free]
FROM    sys.master_files AS f
        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) b
GROUP BY b.volume_mount_point ,
        total_bytes ,
        available_bytes
ORDER BY volume_mount_point;

GO
Next Steps


Last Updated: 2015-12-21


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, December 01, 2017 - 8:36:56 PM - DVP Rao Back To Top

 I tried to install teh report.  one usp checkdependencies is missing is teh error i am gettting .

Thanks for the article . 

 


Tuesday, March 15, 2016 - 3:33:47 AM - Greenfield Back To Top

The subreports are not available after installation.


Wednesday, December 23, 2015 - 5:36:27 AM - PerfDashboard send by mail on scheduled basis Back To Top

 Hi Daniel,

Thanks a lot for the explanations about the Performance Dashboard report and the little part that you added on the database volume.

 

Is that possible to scheduled this report to be ran and sent by mail automatically?


 

Thanks

Bryan


Monday, December 21, 2015 - 7:34:19 PM - Joe Back To Top

Do I need two sets of reports to monitor server in an Availability Group configuration?

 


Monday, December 21, 2015 - 1:13:15 PM - Greg Robidoux Back To Top

Thanks Juvat. 

The link has now been fixed.


Monday, December 21, 2015 - 12:26:00 PM - AJ Back To Top

The custom RDL and proc you post at the end breaks compatibility for SQL 2008.  It continues to work for 2008R2 + though.

 


Monday, December 21, 2015 - 12:16:30 PM - juvat Back To Top

 Thanks for the info.

It looks like the link in your first "next step" is bad.

 


Monday, December 21, 2015 - 10:14:34 AM - beamonc Back To Top

 Excellent resource.

 


Learn more about SQL Server tools