13

Does @os_run_priority in sp_add_jobstep actually work, in SQL Server 2008 R2?

It is described as "reserved" or "undocumented". However, I see it in the sp_add_jobstep definition:

@os_run_priority INT = 0, -- -15 = Idle, -1 = Below Normal, 0 = Normal, 1 = Above Normal, 15 = Time Critical)
1
  • I would suspect that this is just reporting the @os_run_priority that was used, rather than giving you a chance to influence the priority. If so, the text is just helping you figure out what priority was used. – RLF Dec 22 '16 at 20:30
11
+200

This is part of the job step definition and you may even see it have values used or defined in other areas.

After taking a look in the source code (I work at Microsoft and have access), while the value is indeed passed as part of the job step information sent to each subsystem, I couldn't find a place that actually set the value as part of the job step execution. There are, however, threads that do run in different priority levels as part of SQL Server Agent and those threads may or may not help with job step functionality or subsystems that fill a specific role.

While I didn't do an exhaustive check, it would be a safe best to assume this value is - as described - "reserved". Just because it doesn't seem to be used doesn't mean it can't be at any other point as the plumbing exists.

0
4

While this value is saved as part of the job step, I can find no evidence that the value is used.

If I set the value by adding the parameter , @os_run_priority = X to EXEC msdb.dbo.sp_update_jobstep, then it shows up correctly in the os_run_priority column of msdb.dbo.sysjobsteps.

I created a job with 2 steps: one T-SQL step and one Operating System (CmdExec) step. I assume that it is more likely that an option such as "os run priority" would affect a CmdExec step, but it is good to test both.

Each step did a WAITFOR DELAY '00:00:30.000' so that it would hang around while I looked at the running processes to see if the priority had changed.

I checked the processes using Process Explorer. As far as I can tell, the values (and I tried 1, 15, and -1) have no effect. I tried using both SQL Server 2012 and 2016 on Windows 10.

I also tried on SQL Server 2008 R2 running on Windows XP. I again saw no indication of this property having any effect on either the SQLAGENT process or the SQLCMD process (which I was using in the CmdExec step to call back into SQL Server to do the WAITFOR DELAY).

Of course, it should be noted that the process itself needs certain permissions in order to change the priority level of a thread. When SQL Server agent is running as the Local System account, it might not have such rights. However, I did test (SQL Server 2016 only) using my own Windows Login as the service account for SQL Server agent, and saw no indication of this property being used.

2
  • 1
    It's passed in the job structure to each subsystem and each subsystem is responsible for choosing what it does with this. I couldn't find a subsystem that implemented such code on 2008R2 w/terminal patches. – Sean Gallardy - Retired User Dec 26 '16 at 1:31
  • @SeanGallardy Tom updated your answer with the missing piece that clears up any concerns :-). I didn't know you had access to the source, and often enough people state things very confidently / emphatically, as if there is direct, observed knowledge and yet it is just a best guess. I have upvoted your answer, but will keep my answer around as it provides some additional info about priorities as well as how to investigate similar issues. – Solomon Rutzky Dec 26 '16 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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