Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-08-15 : 10:17:51
|
| Pat writes "I'm wondering if there are any facilities for controlling the processing priority for connections, batches or stored procedures. The application is a medium-scale OLTP system with many (up to 200) data entry clerks submiting time sensitive data with few or no predictable breaks. Occasionally we need to run a few longer running procedures that are actually manipulating data (so in other words offloading the procedure to a second replicated server wont help as it would if the processes were simply complex reports) but the problem we face is that these data-manipulating, longer-running (but still under 1 minute) stored procedures redline the server and produce noticeable, in fact intolerable, slow-downs at the data entry stations. Assuming that upgrading server hardware is not an option, what can be done to reduce this effect and protect the data entry performance? Ideas that spring to my mind are hinting rowlocks on the updates and nolocks on selects in the longer running procs where applicable, but what I'd really like to do is set some flag that says "Every data entry task takes priority over this task" or more specifically "this task should yield to any other task the server is being asked to perform." There are plenty of opportunities in the procedure to "give up" time to other process, so it need not be a "pre-emptive" solution, but the only option for giving up time I've found is DELAY WAITFOR which appears to have a granularity of one second, which is a larger amount of time than I'd like to give up. Do the resident gurus have any thoughts? Are there any ways of controlling OS thread priority from TSQL that I'm missing? I would be very grateful for a manageable solution to this quandry. Thanks for the help." |
|
|
|
|
|
Process Priority