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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s