Hi

I wondered if SQL Server 2005 would allow some sort of option to be set that
would allow a specific Stored Procedure to receive a higher execution
priority.

Thanks in advance.

David

Not that I know of.
What is the use case for such a requirement?
Show quoteHide quote

I regularly had a need for this requirement on one system. The most
common use case is that there is a long running monthly or yearly
procedure that while it runs brings the system to it's knees, but given
it takes about 4 hours, it may as well take 8 hours and run on a lower
priority

That's exactly the requirement that I have...any answer??

David

Show quoteHide quote

answer??? what kind of a forum do you think this is.

as you may have guessed from my post, I never really solved this
problem (I was on SQL 2000 though).

The best solution I came up with was to actually run the job on a
separate server that is tied to the main server using replication.
However this would only work if you were sure your data wasn't going to
change while the procedure was running.

Apart from that I don't think there is a nice answer (perhaps schedule
the task out of peak hours), but I look forward to hearing words of
wisdom from others on this subject.

I had thought of creating a second instance of SQL Server and use the
priority boost configuration option for the original default instance.

Regards
David

Show quoteHide quote

That would still require you to have 2 separate copies of the database,
so you may as well put a copy on an unused desktop somewhere (or
ideally a different server) then run the job and sync them up.

We had a similar requirement wherein long running database intensive jobs
where killing the performance of online users. Rather than looking at
separate SQL servers to manage this requirement (and increasing the
complexity of the solution), we looked at the propsect of re-writing our
solution by refactoring the database intesive code into application tier
logic that works on batches of data. Then, on the application tier, we
applied the WSRM
http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx (Windows
Resource Manager) tool to control the resource allocation so that, for
example, if we limit the CPU and memeory used by this process, it would get
lesser priority and then not punish the database often.

In our case, this has worked really well and clients are able to share the
system between long running batch jobs and regular OLTP work. Not sure if
your situation is like this, but just wanted to add my 2 cents.
Show quoteHide quote

D Johnson (DJohn***@discussions.microsoft.com) writes:
No, there isn't such feature in SQL 2005, and I am skeptic to that it
would be a good idea.

Of course, if you have an evil query than run its in own database, it is
not going to hurt anyway else, if that query runs with lower priority. But
if the query runs in a databaase where there is other traffic, the process
could hold locks and block other processes. In this case, lower priority
can be detrimental, as these processes may be blocked for a longer time.
Of the lower priority does not matter anyway, because all other processes
are blocked out.

There is nevertheless one option that may be worth to investigate, and
this concerns both SQL 2000 and SQL 2005, and that is the query hint
OPTION (MAXDOP 1). MAXDOP = Max Degree of Parallelism. The effect of
this hint is that the query is limited to the number of processors that
are specified in the hint. (0 = all available CPUs.) By restricting the
query to use only one CPU, you leave the rest of the CPU to the rest of
the processes in the system. The process itself may suffer from this -
or it may benefit. Far from all parallel plans are effective.

D Johnson (DJohn***@discussions.microsoft.com) writes:
As I said in another post, there is no such feature in SQL 2005. However,
I happened to notice this request on the MSDN Product Feedback Centre:

http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=21919441-58b7-42b0-8efa-8399fdf140a3

You could go there and vote for it, if you like.

assign a table to a path and filename?
CREATE ASSEMBLY failed because it could not open the physical file
Help with a SQL Join statement
query timeout expired
sequential file and Parent ID's
nested queries
Storing text files in a column
xp_sendmail error
query?
ROLLUP and summarazing values