Over the past few months, I spent a lot of time using Excel 2013 and the Power View add-in for completing various kinds of analysis on System Health Session data for SQL Server 2012. I thought it would be a good idea to create an Excel sheet with a template which would allow me to do these activities at the click of a button. I had recently written a post on how to retrieve Report Server execution statistics using Power Pivot and Power View. I will use the same philosophy to generate the CPU usage statistics using the System Health Session.
Now, you may ask why not query the data directly into an Excel sheet. The reason I do not do this is because it allows me to add calculated fields. Additionally, it allows me to write a query to fetch the data from the System Health Session that runs on SQL Server 2012 and above instances.
I had written an article on SSWUG on how to parse the scheduler_monitor_system_health_ring_buffer_recorded data and retrieve the CPU usage statistics using a SSRS report. This time around I fetched the data into a PowerPivot table. Then I added a new column for retrieving the day of the month using the DAY function. I also created a new calculated column for tracking the CPU usage of other processes using the SQL process utilization and system idle values. The new column is named “CPU usage (other processes)”.
Now that the PowerPivot table is ready, I used the created table as the source for a PowerView sheet in Excel 2013.
I created clustered column chart to track the following information:
- Max CPU utilization for the SQL Server instance
- Max CPU usage of all other processes
- Average CPU utilization for the SQL Server instance
- Average CPU usage of all other processes
- Minimum CPU utilization for the SQL Server instance
- Minimum CPU usage of all other processes
The clustered column chart uses the calculated Day field to make the chart more reader friendly. The second chart in the sheet is a line graph which tracks the average CPU usage for the SQL Server instance and other processes across a timeline. The slider at the bottom of the chart allows you to zoom in and out interactively of a time window that you are interested in.
Here is a screenshot of the final output as shown in screenshot 1. The Excel file with this visualization is available on SkyDrive at http://sdrv.ms/10O0udO
Stay tuned to TroubleshootingSQL.com for more such visualizations!