Sets custom execution attributes for a session while the session is active.
sp_setpsexe spid, exeattr, value
is the ID of the session for which to set execution variables. Use sp_who to see spids.
identifies the execution attribute to be set. Values are priority and enginegroup.
is the new value of exeattr. Values for each attribute are as follows:
If exeattr is priority, value is HIGH, MEDIUM, or LOW.
If exeattr is enginegroup, value is the name of an existing engine group.
This example sets the priority of the process with an ID of 1 to HIGH:
sp_setpsexe 1, "priority", "HIGH"
Execution attribute values specified with sp_setpsexe are valid for the current session only and do not apply after the session terminates.
Use sp_setpsexe with caution or it can result in degraded performance. Changing attributes “on the fly”, using sp_setpsexe, can help if the process is not getting CPU time; however, if the performance problem is due to something else, such as locks, changing execution attributes could make the problem worse.
Because you can only set execution attributes for sessions, sp_setpsexe cannot be set for a worker process spid.
Except for the housekeeper spid, you cannot set execution attributes for system spids.
sp_setpsexe does not work if there are no online engines in the associated engine group.
The permission checks for sp_setpsexe differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage any execution class privilege. Any user can execute sp_setpsexe to lower the priority of a process owned by that user. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role. Any user can execute sp_setpsexe to lower the priority of a process owned by that user. |
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_addexeclass, sp_bindexeclass, sp_dropexeclass, sp_showexeclass
