Set task priority in MS SQL

Examples

Set task priority in MS SQL
Next stored procedure let's you set thread priority in MSSQL. You can use the function to set some task in idle or another priority. --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 Be sure you set default thread priority at the end of your SP/SQL batch (execute xp_set_thread_priority without parameters). --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

Next stored procedure let's you set thread priority in MSSQL. You can use the function to set some task in idle or another priority.

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

Be sure you set default thread priority at the end of your SP/SQL batch (execute xp_set_thread_priority without parameters).

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

Other links for Set task priority in MS SQL

ScriptUtils.Thread

Thread object. Timing informations, priority settings and Ids.

ScriptUtils

Easy to use, hi-performance ASP file upload component with progress bar indicator. Let's you upload multiple files with size up to 2GB to a disk or database along with another form fields. Works with large posts, any character set (including unicode utf-8). Contains one-click multiple files/folders download with on-the-fly compression (Using BinaryWrite/BinaryRead).

      Hi-performance text file logging for ASP/VBScript/VBA applications. Lets you create daily/weekly/monthly log files with variable number of logged values and extra timing and performance info.
       Lets you work with safearray binary data in VBS/JS. It also enables conversion between binary and String data using several code pages. ZLib compress and uncompress functions. Lets you transfer files using compressed stream from a client to server using IE.
      This library also enables calling of some Kernel and Advapi functions (performance, timing, sleep, configuration ...) and enables native work with INI files.

	Set task priority in MS SQL
	Sample for ScriptUtils.Thread.Priority