limite the resource a stored procedure can use

Dear sir,
Some times, some unimportant stored procedure on my application are using
too much resource, etc 50% of CUP power. Is there a way to set the priority
of a SP or limite the max resource it can use?
thanks,
Guoqi Zheng
http://www.ureader.comThe only thing you can limit in SQL Server is how many processors a SQL
statement can use on a multi-processor machine. You can do this by adding
the OPTION (MAXDOP n) at the end of the statement, where n is the maximum
number of processors you want the statement to use.
Jacco Schalkwijk
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:dfc72681649d44efadd824ba066e2126@.ur
eader.com...
> Dear sir,
> Some times, some unimportant stored procedure on my application are using
> too much resource, etc 50% of CUP power. Is there a way to set the
> priority
> of a SP or limite the max resource it can use?
> thanks,
> Guoqi Zheng
> http://www.ureader.com|||To add on to Jacco's response, consider index and query tuning if you
haven't already done so. Excessive CPU utilization can be an indication
that additional indexes are needed or a poorly formulated query. It's best
to address the underlying cause when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:dfc72681649d44efadd824ba066e2126@.ur
eader.com...
> Dear sir,
> Some times, some unimportant stored procedure on my application are using
> too much resource, etc 50% of CUP power. Is there a way to set the
> priority
> of a SP or limite the max resource it can use?
> thanks,
> Guoqi Zheng
> http://www.ureader.com|||Thanks.
The CPU usage is because of some calculation. like using SELECT COUNT(*),
SUM(*) to update another table, and the records to be counted or summed are
a few 100000s.
I haven't know what might be better to do those calculation yet. The
calculation needs to be done a few times per day.
any suggestions?
regards,
Guoqi Zheng
http://www.ureader.com|||You might consider using an indexed view for frequently used aggregations.
Covering indexes may also help.
Hope this helps.
Dan Guzman
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:b685cc9d335546c0891cf9e7fe284d09@.ur
eader.com...
> Thanks.
> The CPU usage is because of some calculation. like using SELECT COUNT(*),
> SUM(*) to update another table, and the records to be counted or summed
> are
> a few 100000s.
> I haven't know what might be better to do those calculation yet. The
> calculation needs to be done a few times per day.
> any suggestions?
> regards,
> Guoqi Zheng
> http://www.ureader.com