Managing a SQL Server environment can be a challenging task, especially for accidental DBAs who may not have extensive experience in database administration. One common issue that arises is the need to understand what happened in the system leading up to a particular problem, such as high CPU usage. In order to troubleshoot effectively, it is crucial to have access to historical data on CPU usage. In this article, we will explore a query that provides detailed CPU usage history for both SQL Server and the operating system.

The Query

Here is a query that can be used to retrieve CPU usage history:

DECLARE @ticks_ms BIGINT
SELECT @ticks_ms = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 60 id
	,dateadd(ms, - 1 * (@ticks_ms - [timestamp]), GetDate()) AS EventTime
	,ProcessUtilization as 'SQL CPU'
	,SystemIdle 'Idle CPU'
	,100 - SystemIdle - ProcessUtilization AS 'Others (100-SQL-Idle)'
FROM (
	SELECT record.value('(./Record/@id)[1]', 'int') AS id
	,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
                          AS SystemIdle
	,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') 
                          AS ProcessUtilization
		,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 '%<SystemHealth>%'
		) AS sub1
	) AS sub2
ORDER BY id DESC

This query retrieves the top 60 records of CPU usage history, including the event time, SQL CPU usage, idle CPU usage, and other CPU usage. It utilizes the sys.dm_os_ring_buffers dynamic management view to access the system health information.

Interpreting the Results

While this query alone may not provide a definitive answer to what caused high CPU usage, it does offer valuable insights into the performance of SQL Server in a deployed system. By analyzing the CPU usage history, you can identify patterns and trends that may help in troubleshooting and optimizing the system.

Tools and Processes for Troubleshooting

As a SQL Server professional, it is important to have a set of tools and processes in place for effective troubleshooting. Some commonly used tools include SQL Server Profiler, Extended Events, and Performance Monitor. These tools provide real-time monitoring and analysis capabilities, allowing you to identify and resolve performance issues.

In addition to tools, having a well-defined troubleshooting process is essential. This process should include steps such as gathering information, analyzing logs and performance metrics, identifying potential causes, and implementing appropriate solutions. By following a systematic approach, you can efficiently troubleshoot and resolve SQL Server performance problems.

Conclusion

Understanding CPU usage history is crucial for troubleshooting SQL Server performance issues. By utilizing the provided query and analyzing the results, you can gain valuable insights into the system’s performance. Additionally, having the right tools and processes in place will enable you to effectively troubleshoot and optimize your SQL Server environment.

What tools and processes do you use for troubleshooting SQL Server performance? Share your thoughts and experiences in the comments below!