Set task priority in MS SQL

--set idle priority for the task
EXECUTE sp..xp_set_thread_priority -15, 0

--call some SP in idle priority
EXECUTE sp_longrunning

--set default priority
EXECUTE sp..xp_set_thread_priority 

--Let's you set thread priority in MSSQL
CREATE PROCEDURE xp_set_thread_priority(@Priority INT = 0, @PriorityBoost INT = 1) AS
  DECLARE @Thread INT, @OLEResult INT, @Out INT

  --Create ScriptUtils.Thread object
  EXECUTE @OLEResult = sp_OACreate 'ScriptUtils.Thread', @Thread OUT
  IF @OLEResult <> 0 
    RAISERROR ('ScriptUtils.Thread cannot be created. Result : 0x%X', 1, 1, @OLEResult)
  ELSE
    BEGIN
      --Set priority boost of actual thread
      EXECUTE @OLEResult = sp_OASetProperty @Thread, 'PriorityBoost', @PriorityBoost
      IF @OLEResult <> 0 RAISERROR ('Cannot set PriorityBoost. Result: 0x%X', 1, 1, @OLEResult)

      --Set priority of actual thread
      EXECUTE @OLEResult = sp_OASetProperty @Thread, 'Priority', @Priority
      IF @OLEResult <> 0 RAISERROR ('Cannot set Priority. Result: 0x%X', 1, 1, @OLEResult)

      --Destroy Thread object
      EXECUTE @OLEResult = sp_OADestroy @Thread
    END
RETURN @OLEResult