Write Server's CPU Usage Percent to a Table

SQL Server Administration
1

I want to trap the cpu percentage on an hourly basis and collect it into a table.
Is the total cpu percent stored in any system tables?
Thanks John

2

Can you clarify what you are looking for here? Clearly the total percent of anything is 100. I'm probably moving too slow this morning to grasp your intent...

3

you can use perfmon to capture the CPU usage counter.

4

We use @@cpu_busy, logged every minute and compared to the previous value, to detect when the server is busy (and throttle connections etc.)

We use this in conjunction with some other values from master..sysperfinfo such as Page Splits, Transaction/sec and so on. We deny new connections if the server has tipped over a "busy threshold" to allow currently connected users to continue, finish their session and allow the server to calm down! We have this just as Belt & Braces, it rarely gets triggered - but we built it specifically because once a server is overloaded all the connected users start seeing slow response, or errors, and they all start pressing refresh - which means that each page starts again from the beginning which only increases the server's load! Denying new connections means that the load normal recovers within a minute, so new users are only locked out for that long (we serve them an explanatory page asking them to try again in a few minutes and using a skinny Javascript AJAX request every 10 seonds to check if the Server Busy has finished.

5

To Stephen_Hendricks. My post was poorly worded. I want to collect the CPU usage number i.e. at this moment the usage is 31 % fro example. I would schedule a job to run at certain intervals.

I believe Kristens idea, to capture @@cpu, might be what I need. Thanks Kristen

6

If its just CPU per min, why don't you use the below scripts that looks at the ring buffer (works for SQL 2008 onwards). You will still need to run it on a SQL job and log that to a table, it can get 50 records, so around 50 mins, i assume you add some logic in to capture it every 45 mins should do the job.

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(50) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
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;