How do I slow down a connection more than PRIORITY = 'Background'?

[Breck_Carter]

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?

[VolkerBarth]

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).

[Breck_Carter]

@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.