xp_setpriority

for RuBoard

In closing out this chapter, I'll leave you with an extended procedure that I think could change the way you work, especially if you manage a SQL Server installation in which long-running batch jobs and shorter, time-critical jobs regularly compete for system resources. Earlier, I mentioned that extended procedures run in the SQL Server process space. They also run in the context of the calling thread. That is, unless fiber mode has been enabled on the server, each connection gets its own Win32 thread. This thread is taken from the pool of worker threads that SQL Server creates on start-up and can change from query to query on the same connection. When an extended procedure runs, it runs within the context of this thread. This means that it has full access to the thread and can change certain characteristics of the thread if it so chooses. Whatever changes the extended procedure makes are retained by the thread after it finishes executing.

Xp_setpriority takes advantage of this nuance to allow you to adjust the thread priority of a SQL Server connection. Because SQL Server threads are already created with the highest priority setting permitted by their thread class, you can't raise the initial priority of a connection's thread, but you can lower it. Why would you want to lower it? Why would you want to slow a job down? Very simple: To allow other concurrent jobs to complete more quickly. That is, if you have a batch-type job that normally runs for many hours and often doesn't finish until the middle of the night, slowing it down so that other time-critical routines can execute more quickly may indeed be a viable option for you. If these jobs normally run concurrently anyway, slowing down the batch job a bit will have the net effect of speeding up the other jobsperhaps dramatically.

Let's begin with the source code to xp_setpriority. There isn't much of it:

 RETCODE xp_setpriority(srvproc) SRV_PROC *srvproc; {     int threadpriority = THREAD_PRIORITY_TIME_CRITICAL;     int         nParams;     DBINT       paramtype;     TCHAR           szPriority[20] = "";     BYTE pbType;     ULONG pcbMaxLen;     ULONG pcbActualLen;     BOOL pfNull;     RETCODE    rcXP = XP_ERROR;    // Assume failure until shown otherwise.     // Get number of parameters.     nParams = srv_rpcparams(srvproc);     // Check number of parameters     if (nParams != 1) {     // Send error message and return        srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_SETPRIORITY_ERROR, SRV_INFO,        (DBTINYINT)0,        NULL, 0, 0, "Error executing extended stored procedure: Invalid number        of parameters.",        SRV_NULLTERM);         // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the         // result set of an Extended Stored Procedure.         srv_senddone(srvproc, (SRV_DONE_ERROR  SRV_DONE_MORE), 0, 0);         return(XP_ERROR);         }     // If parameter is not varchar (should be HIGHEST/LOWEST/etc.), send an     // error and return.     paramtype = srv_paramtype(srvproc, 1);     if (paramtype != SRVVARCHAR) {        srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_SETPRIORITY_ERROR, SRV_INFO,        (DBTINYINT)0,        NULL, 0, 0,        "Error executing extended stored procedure: Invalid Parameter Type",        SRV_NULLTERM);         // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the         // result set of an Extended Stored Procedure.         srv_senddone(srvproc, (SRV_DONE_ERROR  SRV_DONE_MORE), 0, 0);         return(XP_ERROR);         }              // Terminate parameter string with NULL.              srv_paraminfo(srvproc,1,&pbType, &pcbMaxLen, &pcbActualLen, szPriority, &pfNull);              szPriority[pcbActualLen] = ' 
 RETCODE xp_setpriority(srvproc) SRV_PROC *srvproc; { int threadpriority = THREAD_PRIORITY_TIME_CRITICAL; int nParams; DBINT paramtype; TCHAR szPriority[20] = ""; BYTE pbType; ULONG pcbMaxLen; ULONG pcbActualLen; BOOL pfNull; RETCODE rcXP = XP_ERROR; // Assume failure until shown otherwise . // Get number of parameters. nParams = srv_rpcparams(srvproc); // Check number of parameters if (nParams != 1) { // Send error message and return srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_SETPRIORITY_ERROR, SRV_INFO, (DBTINYINT)0, NULL, 0, 0, "Error executing extended stored procedure: Invalid number of parameters.", SRV_NULLTERM); // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the // result set of an Extended Stored Procedure. srv_senddone(srvproc, (SRV_DONE_ERROR  SRV_DONE_MORE), 0, 0); return(XP_ERROR); } // If parameter is not varchar (should be HIGHEST/LOWEST/etc.), send an // error and return. paramtype = srv_paramtype(srvproc, 1); if (paramtype != SRVVARCHAR) { srv_sendmsg(srvproc, SRV_MSG_ERROR, XP_SETPRIORITY_ERROR, SRV_INFO, (DBTINYINT)0, NULL, 0, 0, "Error executing extended stored procedure: Invalid Parameter Type", SRV_NULLTERM); // A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the // result set of an Extended Stored Procedure. srv_senddone(srvproc, (SRV_DONE_ERROR  SRV_DONE_MORE), 0, 0); return(XP_ERROR); } // Terminate parameter string with NULL. srv_paraminfo(srvproc,1,&pbType, &pcbMaxLen, &pcbActualLen, szPriority, &pfNull); szPriority[pcbActualLen] = '\0'; if (stricmp(szPriority,"HIGHEST")==0) { threadpriority=THREAD_PRIORITY_TIME_CRITICAL; } else if (stricmp(szPriority,"HIGH")==0) { threadpriority=THREAD_PRIORITY_ABOVE_NORMAL; } else if (stricmp(szPriority,"LOW")==0) { threadpriority=THREAD_PRIORITY_BELOW_NORMAL; } else if (stricmp(szPriority,"LOWEST")==0) { threadpriority=THREAD_PRIORITY_LOWEST; } else if (stricmp(szPriority,"NORMAL")==0) { threadpriority=THREAD_PRIORITY_NORMAL; }  SetThreadPriority(GetCurrentThread(),threadpriority);  srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0); // We got here successfully, let the client know. return XP_NOERROR ; } 
 ';            if (stricmp(szPriority,"HIGHEST")==0)              {                     threadpriority=THREAD_PRIORITY_TIME_CRITICAL;              }              else if (stricmp(szPriority,"HIGH")==0)              {                     threadpriority=THREAD_PRIORITY_ABOVE_NORMAL;              }              else if (stricmp(szPriority,"LOW")==0)              {                     threadpriority=THREAD_PRIORITY_BELOW_NORMAL;              }              else if (stricmp(szPriority,"LOWEST")==0)              {                     threadpriority=THREAD_PRIORITY_LOWEST;              }              else if (stricmp(szPriority,"NORMAL")==0)              {                     threadpriority=THREAD_PRIORITY_NORMAL;              }  SetThreadPriority(GetCurrentThread(),threadpriority);  srv_senddone(srvproc, SRV_DONE_MORE, (DBUSMALLINT)0, (DBINT)0);              // We got here successfully, let the client know.              return XP_NOERROR ; } 

Other than the usual boilerplate parameter processing code, there's really only one significant line of code in xp_setpriority: the call to the Windows API SetThreadPriority() routine. It nests a call to GetCurrentThread() to get the handle of the current connection's thread, then sets its priority to that specified by the user . The routine supports five priority settings: HIGHEST, HIGH, NORMAL, LOW, and LOWEST. These are all relevant to the thread's base class.

To grasp the practical usefulness of this routine, let's have a look at some code. You'll need a SQL Server with little or no activity on it for the following tests to be meaningful. Begin by copying the xp_setpriority.dll file to your SQL Server instance's BINN folder (be sure this is the BINN folder under the instance and not the one associated with the SQL Server Tools folder). Once the DLL is copied , run this script to make xp_setpriority available on your server:

 USE master GO EXEC sp_addextendedproc 'xp_setpriority', 'xp_setpriority.dll' GO 

Once you've added the extended procedure, load the following query into Query Analyzer and run it one time. It initiates a CPU- intensive operationa loop that counts from 1 to 1 millionthen returns. Run it once and note its execution time (Query Analyzer displays the execution time in the bottom frame of each query window):

 SELECT GETDATE() go --EXEC master..xp_setpriority 'LOWEST' DECLARE @i int SET @i=0 WHILE @i<1000000 BEGIN   SET @i=@i+1 END EXEC master..xp_setpriority 'NORMAL' GO SELECT GETDATE() 

On the ancient laptop on which I'm writing this book, this code takes about 23 seconds to run.

Now open a second window in Query Analyzer and load the query into it as well. Once it loads, run it and the first instance of the query simultaneously . This will tell us how much resource contention (in this case, CPU time) affects the execution time of our query. On my laptop, each instance of the query now takes about 42 seconds to runan increase of almost 100%. This makes sense. After all, there's only so much CPU to go around.

But wait a minute: What if you could adjust the execution priority of one of the jobs so that the other one could finish more quickly? What if only one of the jobs was time-critical? What if slowing down one query so that the other could finish more quickly would help your business? That's easily done with xp_setpriority. Simply uncomment the call to it in one (but not both) of the query windows and rerun both of them. On my machine, the query that's still running at NORMAL priority now finishes in 23 seconds even though the other query is competing with it for CPU bandwidth. This means that it takes no longer to run while the second query runs than it did to run it by itself. This is because of the fact that it has a higher thread priority than the query that called xp_setpriority to slow itself down. We've effectively sped up the first query by nearly 50% by simply slowing down its competition. Moreover, the slower query only takes a little longer to run than it did when it was running at NORMAL priority. On my machine, it now takes 58 seconds to run. That's about a 33% reduction in performance by one query to speed up another by nearly 50%a pretty good trade. The trade isn't a wash because, by allowing the higher priority query to finish, the second query is preempted less often than it would be were both queries competing vigorously for CPU time.

Note that a query's thread priority need not be permanent. You can raise it back to normal status or lower it again whenever you want during the execution of the query. Moreover, you can wrap critical sections of code in calls that raise the thread priority momentarily back to normal for the query, then lower it again, so that those particular sections get all the CPU time they possibly can. Using xp_setpriority, you have as much control over your thread's execution time as SQL Server will allow.

A nice feature for a future release of SQL Server would be the ability to set a connection's execution priority via Transact-SQL code. It's reasonable that administrators and developers would want to be able to customize the execution priority for individual connections rather than taking a one- size -fits-all approach and using the same setting for all of them. Until this functionality is added, though, you can use xp_setpriority to accomplish the same thing. If you have long-running, batch-oriented jobs with execution times that are not something you're terribly concerned about, consider lowering their execution time to allow your more critical jobs to run more quickly. You could go so far as to partition your users into classes based on business needs, and allow some to run at a higher priority than others. By tailoring the thread priorities of your SQL Server connections, you may well see an increase in overall performance and efficiency on your server.

WARNING

It's very important that you read through this warning completely before using xp_setpriority. In fact, it's so important that I almost pulled the coverage of xp_setpriority from this book out of concern that people would cause themselves problems by misusing it. Like most powerful tools, xp_setpriority can wreak havoc when used improperly. Please read the following warning completely and be sure you understand it thoroughly before using xp_setpriority on a production server.

Because SQL Server regularly rotates the worker threads that service user connections, it's possiblein fact, likelythat changes you make to a thread via xp_setpriority will affect other connections if you're not careful. I once saw a sign that said, "Your mother does not work here. Please clean up after yourself." It's extremely important that you undo any changes you make to a thread context with xp_setpriority before your query returns. That is, the last statement in any query batch that uses xp_setpriority should be

 EXEC master..xp_setpriority 'NORMAL' 

This ensures that another connection will not inherit the temporary changes you make to the thread priority. And you'll want to be sure that errors that might occur before the final call to xp_setpriority are properly handled so that it's not bypassed. Failing to reset the thread priority properly after lowering it could have the effect of, say, randomly slowing down other connections on your systemsomething you probably wouldn't like, and that would be nigh impossible to troubleshoot.

To see how this works, run the following query several times in a row on a server with lots of activity:

 SELECT kpid FROM master..sysprocesses WHERE spid=@@spid 

The kpid column in sysprocesses is the Win32 thread ID assigned to the connection. Run the query several times in succession on a busy server, and you'll see that this thread ID fluctuates. The thread you used last time may be in use by someone else now, and the thread you have currently may have last been used by a completely different connection. This is why it's important to clean up after yourself. Because SQL Server uses thread pooling, changes you make to a thread can affect other connections.

for RuBoard