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)
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.
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.
