Sunday, June 13, 2010

Finding CPU utilization in SQL Server 2005:


Finding the CPU percentage used by SQL Server is perhaps the first step in performance monitoring. Often, finding the CPU % as shown by task manager, from SQL Server can be tricky as SQL Server's sysprocesses and exec_requests gives only CPU ticks and not the exact CPU % used. Logging into each and every server manually and checking the CPU can be a pain if you are administering a large number of servers. So here is a solution.


DECLARE @ts_now BIGINT

SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
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 '%%') AS x) AS y
ORDER BY record_id DESC


The script provided above provides the approximate CPU usage % as shown in task manager. The result is shown below


Column description is provided below:
SQLProcessutilization: Percentage used by SQL Server process ( sqlserver.exe)
OtherProcessutilization: Percentage used by other processes in the server
Eventtime: Time at which the CPU percentage was recorded.
systemidle: Perecentage of CPU left.
record_id: Just a incremental row id

The CPU percentage is obtained from sys.dm_os_ring_buffers DMV. sys.dm_os_ring_buffers records the CPU percentage
for every one minute and it maintains CPU % for the last 256 minutes. Like all DMVs if the server is restarted then
the DMVs lose the data.

Now for the credits :)

The Script provided above is obtained from the scripts deployed by Performance dashboard tool explained in the last post Performance Dashboard uses the sp [MS_PerfDashboard].[usp_Main_GetCPUHistory] for getting the reports on CPU. I just tweaked the sp to obtain the CPU % for 256 minutes instead of the last 15 minutes returned by Performance Dashboard. Thanks Performance Dashboard again :)

12 comments:

r5d4 said...

Hi Nagaraj,

Just to let you know I had to change cpu_ticks_in_ms to ms_ticks to make this work on SQL 2008.

rgds
rich

Nagaraj Venkatesan said...

Hi Rich,

Yes. One needs to make the change when using it on SQL 2k8. Thanks for pointing out.

Regards,
Nagaraj

WoundedEgo said...

This line fails with an arithmetic error:

DATEADD(ms, -1 * ( @ts_now - [timestamp] ), GETDATE()) AS eventtime ,

Nagaraj Venkatesan said...

Hi WondedEgo,
The script has been working on all my servers. I guess the problem should be with the large value of @ts_now. Can you please post the value of @ts_now variable and also try executing the same script on any other SQL 2k5 box and see if you have the same issue.

WoundedEgo said...

10000

Anonymous said...

Mr. Ego,
The correct line to set the @ts_now variable for sql server 2008 is

select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info;

This will fix the arithmetic error.

WoundedEgo said...

That did it, thanks!

Nagaraj Venkatesan said...

Hi Anony,
Thanks!!!

Hi WoundedEgo,
I thought you were trying it on 2k5. Glad that its working now.

Regards,
S.V.Nagaraj

nicky said...

Hi do you know how to get CPU PERCENTAGE FOR AN INDIVIDUAL QUERY AT THAT PERTICULAR TIME OF RUNNING.
OR SOMEWAY OTHER CAN WE KNOW HOW TO INTERPRETE CPU TICKS IN TO PERCENTAGES.

Nagaraj Venkatesan said...

@nicky,
there is no direct way to convert cpu ticks into cpu percentage. At the maximum we can co relate the cpu ticks of a query to a surge in CPU % increase.

nicky said...

Thanks for clarification. How do we relate cpu ticks of query to a surge in CPU%Increase? Do you have a script available ?if so please provide..Thanks.

Kennesaw Mountain Gamers said...

I am getting an error from the script. It says 'Incorrect syntax near the keyword FROM'. It states this is on line 11 of the script which has the following FROM (SELECT record.value('(./Record/@id)[1]', 'int')

Can someone help me?