8

I have a job set up in SQL Server Agent to run an update query. I want this job to execute every night between midnight and 5am and I want it to repeat every 5 minutes during that time frame. But I only want the job to actually execute if the CPU is idle, that is the CPU utilization is below say 20%. I know I can set up the schedule, and I can also set up the CPU Idle requirement separately. Is there a way for me to set it up so that both 'schedules' have to be true for the job to run?

Thanks

Improve this question

4 Answers 4

Reset to default
6

Or you can have one job that runs every 5 minutes with 2 steps:

Step #1 -- checks the CPU Usage (PowerShell), just google and there are plenty of scripts out there; If <20% exit with success go to Step 2, Else Exit with Error and finish the job (No Step #2)

Step#2 -- is the actual UPDATE.

Or as Ken suggested.

Improve this answer
1
  • 2
    I like this one. I think it's a better solution.
    – KenWilson
    Commented Jan 31, 2014 at 20:43
4

CPU utilization is below say 20%

Configure SQL Agent through the Advanced properties window for Idle CPU Condition. You would set the "Average CPU usage falls below" value to 20%, and then how long it has to keep at that level before the job will run.

After that configure the job step to check the time ensuring it is within your window, if within the window issue the update command.

Now the requirement for every 5 minutes may not be meet exactly with this setup. I could not find anything on this schedule type that indicates how often the job would be executed. [e.g. if you set the "and remains below this level" to 30 seconds for Idle CPU condition, does it run the job that frequently?] That would require a bit of testing as I have never used this configuration before. If I get a chance I can try to see what it does and will update this answer.

Improve this answer
1
  • The way the CPU Utilization condition works is: If the CPU has been under the specified threshold for the specified duration, the event will fire and run the job. If the CPU remains idle for even another second, then the event would be fired again thus the job would try to run again. But only one instance of a job can run at a time so the real world result is the job will continue to start, execute, finish, repeat as long as the CPU remains idle.
    – c.dunlap
    Commented Jan 31, 2014 at 21:58
2

One way would be to have three jobs. Job one set on a schedule that enables job two at the beginning of the window. Job two is set to run when the CPU is idle and the third job disables job two at the end of the window. I don't think that's the only way to do it but it definitely the easiest in my mind.

Improve this answer
2

Denis was very close. But the actual solution was a little swapped. As powershell cannot check the CPU over a duration, only the current usage, the SQL Server check had to be used.

So then my first step of the job was to run a PowerShell script that checks to see if the current system time is between the two time constraints. If it is, then the powershell script does nothing. The SQL Server job interprets this as a success.

Then the Job moves on to the last step and performs the update query.

If however, the current time is outside of the range I perform a Start-Time -s 600 to pause the powershell script for 10 minutes (simulating the "once ever 5 - 10 minutes requirement). This is to keep the SQL server agent from constantly trying to run this job.

After 10 minutes, the powershell script throws a custom error. Since I have $ErrorActionPreference = "Stop" at the beginning of the script the powershell script stops executing after the error. This step is very important.

Since powershell stopped and did not return, the SQL Server Agent interprets this as a failure and will not continue to the step that performs the Update query.

P.S. You guys led me in the right direction. If I had enough reputation, I would upvote you guys.

Edit: Added Powershell Script

$ErrorActionPreference = "Stop"

$StartTime = Get-Date "12:00 AM"
$EndDate = Get-Date "05:00 AM"
$CurrentDate = Get-Date
$ExecuteJob = $StartTime -lt $CurrentDate -and $EndDate -gt $CurrentDate
if(!$ExecuteJob){    
    Start-Sleep -s 600
    throw "Current Time not within Server downtime"
}
Improve this answer
2
  • 1
    PowerShell can check the CPU over a duration by using Get-Counter cmdlet. You can poll the value every 5 seconds over 45 seconds with: $t = Get-Counter "\Processor(_total)\% Processor Time" -SampleInterval 5 -MaxSamples 10. You can then parse the string to just get the values by: $t.Readings.Trim("\\server\processor(_total)\% Processor time :")
    – user507
    Commented Jan 31, 2014 at 23:56
  • Yes, but this would require me to allow sql server to run that job every 5 seconds. Since SQL server is already monitoring the CPU usage over a duration I chose to go that route. This allows me to only execute the job every 10 minutes when the CPU is too high.
    – c.dunlap
    Commented Feb 3, 2014 at 19:06

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.