SQL SERVER – SSMS: Performance Dashboard Reports – Home Page details

Earlier we discussed in detail what the SSMS: Server Dashboard Report looks like when working with SQL Server Management Studio standard reports. These dashboard reports are an arsenal of amazing wealth of information packed in one single view. The Dashboard homepage for Performance Dashboard reports are no less in that sense. The main page report has tons of useful information which complement the other reports we have seen in the past in new visually appealing format.

The Performance Dashboard starts with 6 various sections which we will discuss one after another. I hope you got a chance to read how the installation and configuration needs to be done for Performance Dashboards. The default landing place for the same would be this Dashboard main page.

Section 1: The Dashboard lands with some vital information about the edition and the local server time on the top.

Then we have a bar chart which shows the CPU Utilization on the server currently for SQL and other processes. This is a quick way to understand who is consuming CPU cycles on our SQL Server box.

There is an interesting warning note we get if SQL Server has been configured to run with lesser number of CPU’s using the configuration. In the image below we can see that I have a Note stating the server is using 5 out of the available 8 CPU’s in the system. As a DBA this is great information because on a dedicated SQL Server box, you don’t want to be running sub-optimal for a given instance.

At this point, I would also want to call out that a similar information is presented in our ErrorLogs when the instance is started. In my local server you can see this information is shown as 8 logical processors being used by my SQL Server instance.

You might want to know how this information is retrieved about CPU history. Under the hood, this report calls stored procedure MS_PerfDashboard.usp_Main_GetCPUHistory from msdb database which shows readable information from below query. It shown last 15 snapshots

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 '%<SystemHealth>%'

Section 2: This is the second section on the top right pane. Generally this section is empty if there are no waits in the system. In my case, I have mimicked a dummy workload to get few waits in our database. The warning note in the top mentions, excessive waits can degrade the performance of the system.

Waits are sometimes inevitable in a super active transactional OLTP systems. The deal is not to have excessive waits and waits that are longer. This brings down the overall efficiency of the system. If you are interested in learning about Wait Stats – look at the month long series I wrote a while back. The same with a bunch of additional information got published as a book too, so feel free to grab that if you are interested – SQL Wait Stats Kindle.

This section fetches information from DMVs sys.dm_exec_requests and sys.dm_exec_sessions. Below is the outline of the query under MS_PerfDashboard.usp_Main_GetRequestWaits

SELECT r.session_id
,r.wait_type
,r.wait_type
,r.wait_time
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s ON r.session_id = s.session_id
WHERE r.wait_type IS NOT NULL
AND s.is_user_process = 0x1


Back to our Performance Dashboard, if we click on any of the Wait Stats from the above graph, we will be presented with more details to what are the queries waiting, what they are waiting, what is the session id etc.

Feel free to click the back button and we will get back to the main dashboard.

Section 3: This section is a summary section of User Requests and Sessions by count, CPU Time, Wait Time and the overall cache hit ratio. As we are on the topic of cache, I want to recall the post from Jonathan Kehayias – though this is about Wait Type, there is plethora of links and resources pointing to cache. It is worth a read for sure. User Requests fetches information from sys.dm_exec_requests whereas User Sessions gets the information from sys.dm_exec_sessions.

Section 4 & 5: The next section is all about a bunch of links which will lead us to a number of other reports. They range from CPU, Logical Reads, Logical Writes, IO statistics, Physical reads etc. The miscellaneous information is about additional information around Active Traces, Extended Event Sessions, Databases and more.

If you are a seasoned DBA, it would be easy for you to guess that “Active Traces” can be seen via sys.traces, “Active XEvent Sessions” by sys.dm_xe_sessions and “databases” by sys.databases catalog view. Based on usage of the system, sometime we might also see missing indexes hyper link which is picked from sys.dm_db_missing_index_group_stats DMV.

In future post, we will use look at some of the reports carved out from this link. Do let me know if you started using these performance dashboard reports. I am eager to know your experiences for sure.

Reference: Pinal Dave (https://blog.sqlauthority.com)