Testing indicates that a hard-working I/O bound stored procedure may not be slowed down much by SET TEMPORARY OPTION PRIORITY = 'Background' in 22.214.171.1246. This matches my previous impression that "background priority doesn't have much visible effect".
SELECT DATEDIFF ( MILLISECOND, '2010-10-29 11:11:17.281', '2010-10-29 11:14:38.187' ) AS "background", DATEDIFF ( MILLISECOND, '2010-10-29 11:24:37.953', '2010-10-29 11:26:31.937' ) AS "foreground"; background,foreground 200906,113984
In this particular case, the responsiveness of other processes (even Wordpad) is sometimes adversely affected by this procedure even when running in "background" priority, and I want to give the user the option to slowwwwww it down even more. CPU usage is low, it is disk activity that is crushing the machine.
I am considering executing WAITFOR DELAY after each COMMIT to let the disk catch up to the I/O frenzy (Queries From Hell, plus deletes). The delay amounts would vary with the user-input "speed" setting.
Is that a good idea?
asked 29 Oct '10, 16:02