Plot SQL Server CPU Usage With R

EDIT 2017-06-16: I revised the script a little bit to add in total CPU as well, making it easier to see when you’re at 100% utilization.

This is a quick way of grabbing the last 256 minutes of CPU usage data from SQL Server and plotting it using R.

The SQL Server script comes from Glenn Berry’s outstanding set of DMV queries.  Here’s the R code:

if(!require(RODBC)) {
    install.packages("RODBC")
    library(RODBC)
}

if(!require(ggplot2)) {
    install.packages("ggplot2")
    library(ggplot2)
}

if(!require(tidyverse)) {
    install.packages("tidyverse")
    library(tidyverse)
}

# Fill in your own connection string details here
conn <- odbcDriverConnect("Driver=SQL Server;Server=RDU-SQL-R-01.auctionrover.com;Initial Catalog=DemandForecaster;Provider=SQLNCLI11.1;Integrated Security=SSPI")

# This is Glenn Berry's query to get CPU percentage data for the last 256 minutes.
# https://www.sqlskills.com/blogs/glenn/category/dmv-queries/
cpu_query <- paste("DECLARE @ts_now BIGINT = (SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK) );

SELECT TOP (256)
	SQLProcessUtilization AS SQLCPU,
	SystemIdle AS Idle,
	100 - SystemIdle - SQLProcessUtilization AS OtherCPU,
	DATEADD(ms, -1 * (@ts_now - [TIMESTAMP]), GETDATE()) AS EventTime
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 WITH (NOLOCK)
			WHERE
				ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
				AND record LIKE N'%<SystemHealth>%'
		) AS X
	) AS Y
ORDER BY
	record_id DESC OPTION (RECOMPILE);")

cpu_usage_by_minute <- sqlQuery(conn, cpu_query)

cpu_usage_by_minute$TotalCPU <- cpu_usage_by_minute$SQLCPU + cpu_usage_by_minute$OtherCPU
cpu_usage_by_minute$EventTime <- lubridate::ymd_hms(cpu_usage_by_minute$EventTime)
cpu_usage_by_minute_plot_df <- cpu_usage_by_minute %>%
    gather(key = CPUType, value = PercentUse, SQLCPU, OtherCPU, TotalCPU) %>%
    select(EventTime, CPUType, PercentUse)

ggplot(cpu_usage_by_minute_plot_df, aes(x = EventTime, y = PercentUse, color = CPUType)) +
    geom_line(stat='identity')

close(conn)

At the top of the script, we have a construct to load a package if it already exists; if it doesn’t already exist, install and load it.  The packages we will use today are RODBC (to connect to SQL Server), ggplot2 (to visualize the data), and tidyverse (to structure the data in a nicer format).  Here is an example of the script in action:

On my instance, I have SQL Server R Services installed and am hammering away at the box with it, training a bunch of neural nets concurrently.  We can see that I got started with this sometime around 10:30 AM, stopped it for a few minutes, and then went nuts around 10:45 AM.