Hi,

I have a stored procedure which works fine under normal load.
But under heavy load on SQL Server, my procedure is taking very long time to execute.
Is there any way to increase the priority of my procedure so that it get proper time slot.

My scenario is something is like this:
My procedure PMain is continously reading a table T1 processing  rows one by one and delete all rows of the table which are processed.
There are set of procedures which are continously inserting rows in table T1 which will be read by procedure PMain.

Procedure PMain is able to process with almost same speed as rows are added to table T1.

But situation becomes worse when my another application fires a select query on 20 tables and each table containing 30000 rows. This application takes round about 1 hour to complete and during this time performance of my procedure goes down badly which I don't want.

Please suggest me some ways so that I can improve performance of this procedure.

Thanks in advance.
Pushkar

Pushkar (pushkartiw***@gmail.com) writes:
Show quoteHide quoteNo, there is no process priority in SQL Server.

It is not clear to me whether the other application access the table(s)
that PMain works with or some completely unrelated tables. In the former
case, there could be blocking issues, in the latter there is only a case
of competition of resources.

Unfortunately, without knowing what is is Pmain, I can't give any advice.
But it could be the case that Pmain is not optimally written - or does
not have the opitimal indexing to work with. As long as the server is
not loaded, the performance is good anyway.

Why query optimizer does not select the index?
Popuate field with value from last row
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation