Testing indicates that a hard-working I/O bound stored procedure may not be slowed down much by SET TEMPORARY OPTION PRIORITY = 'Background' in 11.0.1.2276. 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 Breck Carter |
My "light-weight" impression of the priority option is that is influences the internal SA scheduler, i.e. it could slow down/fasten the execution of a particular request (task?) compared to other requests (tasks). But it would not have influence on other processes like Wordpad. Just my imagionation (and with V8, the old background_priority works that way).
@Volker: The influence on Wordpad is indirect: By sucking up all the disk resources, other processess that need the disk are sometimes slowed down a lot. At least, that's my explanation. The fact is, sometimes when this stored procedure is running, nothing else works properly; when the stored procedure stops, everything else is fine.