Get CPU Utilization Data from SQL Server
SQL Server is built on top of the Windows operating system and shares many of the same components with other Windows-based applications. This means that SQL Server is subject to the same potential performance issues as any other Windows application. In this article, we’ll take a look at how to get CPU utilization data from SQL Server so that you can identify and troubleshoot any performance issues.
There are a few different ways to get CPU utilization data from SQL Server. The most direct way is to use the Windows Performance Monitor tool. This tool can be used to collect a variety of performance data from a variety of sources, including SQL Server. To use the Performance Monitor tool, you’ll need to first add the “SQL Server:Buffer Manager” counters. These counters will give you information about the amount of work that SQL Server is doing to manage its buffers.
Another way to get CPU utilization data from SQL Server is through the use of the sp_monitor system stored procedure. This procedure can be used to collect a variety of performance data, including CPU utilization data. To use this procedure, you’ll need to first enable the trace flag T1118. This trace flag will cause the sp_monitor procedure to output its data to the SQL Server error log.
Once you’ve enabled the trace flag, you can run the sp_monitor procedure as follows:
|
1 2 |
EXEC sp_monitor GO |
This will output a variety of performance data to the error log, including CPU utilization data. You can then use this data to troubleshoot any performance issues that you may be experiencing.
Another way to get CPU utilization data from SQL Server is by using the belwo script. Benjamin Nevarez described in his blog on how to “Get CPU Utilization Data from SQL Server” and he inspired me to repost the information. Unfortunately this webpage is not accessible currently.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/* Author: Benjamin Nevarez Original link: http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx */ DECLARE @ts_now BIGINT; SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks) FROM sys.dm_os_sys_info; SELECT record_id , Dateadd(ms, -1 * ( @ts_now - [timestamp]) , Getdate()) AS EventTime , sqlprocessutilization , systemidle , 100 - systemidle - sqlprocessutilization AS OtherProcessUtilization 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 WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '%<SystemHealth>%') AS x) AS y ORDER BY record_id DESC; |
Result
Conclusion
In conclusion, getting CPU utilization data from SQL Server is a important task for any DBA. There are various ways to collect this data, and the method you choose will depend on your specific needs. However, all methods have one thing in common: they require you to have a solid understanding of SQL Server internals.
