2

I can get the available memory by querying the [master].[sys].[dm_os_sys_memory] view, but I haven't found anything similar for getting info about the CPU load.

I only find info about querying CPU load by the SQL server itself which is not my primary concern, I need the full load on the system. I've looked through the system views but I can't find anything or at least don't understand if I've found it...

The reason I want to get this via T-SQL is that we already have a monitoring application that is querying all our systems for other info and it would be nice to be able to expand that instead of settings up something completely new.

Currently the systems of interest are running 2014 and 2017 express but it will probably include 2019/2022 express soon enough.

2 Answers 2

2

You can create an Agent job in Powershell that runs every say 10 seconds and captures this information

CREATE TABLE CounterLog (id varchar(10) PRIMARY KEY, Counter decimal(18,9));
INSERT CounterLog (id) VALUES ('CPU');

EXEC msdb.sys.sp_add_job
    @job_name = N'Get Counters';

EXEC msdb.sys.sp_add_jobstep
    @job_name = N'Get Counters', 
    @step_name = N'Get CPU Load',
    @subsystem = N'PowerShell',  
    @command = N'
$load = (Get-Counter "\Processor(_Total)\% Processor Time").CounterSamples[0].CookedValue;

Invoke-SqlCmd -ServerInstance "." -Database "YourDb" -Query "
UPDATE CounterLog
SET Counter = ${$load}
WHERE id = ''CPU'';"
',   
    @on_success_action = 3;

EXEC msdb.sys.sp_add_jobschedule
    @job_name = N'Get Counters',
    @freq_type = 4,
    @freq_interval = 1, 
    @freq_subday_type = 2, 
    @freq_subday_interval = 10;
3
  • If I understand the docs correctly SQL Server Agent isn't supported on Express versions? Commented Aug 10, 2022 at 8:31
  • 2
    Then create it as a Windows Scheduled Task Commented Aug 10, 2022 at 8:33
  • yeez sometimes I think my brain hasn't come back from vacation yet... thanks. Commented Aug 10, 2022 at 8:43
1

I believe you can achieve that using xp_cmdshell to run one of the commands from this other Q and A:

EXEC xp_cmdshell 'wmic cpu get loadpercentage';

Just be sure to read the Remarks of that procedure documentation since using it has security implications.

1
  • 1
    That would work but xp_cmdshell is a no go for security reasons unfortunately. But an upvote since you actually gave me a way to do it :) EDIT: seems like I have to low rep to actually give you an upvote :( Commented Aug 9, 2022 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.