Can we boost up the priority of a procedure

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 pro
per 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 r
ound about 1 hour to complete and during this time performance of my procedu
re goes down badly which I don't want.
Please suggest me some ways so that I can improve performance of this proced
ure.
Thanks in advance.
PushkarPushkar (pushkartiwari@.gmail.com) writes:
> 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.
No, 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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx