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%20Carter's gravatar image

Breck Carter
26.9k437609883
accept rate: 21%

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

(29 Oct '10, 20:42) Volker Barth

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

(30 Oct '10, 06:08) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×245

question asked: 29 Oct '10, 16:02

question was seen: 497 times

last updated: 29 Oct '10, 16:02