1

I'm using SQL Server 2008 R2

We have a single stored procedure that causes 100% CPU usage when ran. The said SP is ran once a month and performs heavy calculation for approximately 5 minutes.

Since the SP is ran once a month, we don't particularly care if it takes say 30 minutes or even longer to complete the process, but taking up all of the available CPU resource is problematic. We'd like to limit the CPU usage for this particular stored procedure.

Is there such an option in SQL Server?

|improve this question
  • 3
    Sounds like your maxdop setting isn't properly set... However, to directly respond to your question; yes it's available in enterprise edition and it's called resource governor. Although I still think the answer is also a correct maxdop setting. – Sean Gallardy Feb 6 '15 at 5:09
  • 1
    And to clarify, there is MAXDOP for the server, but you can also add a query hint for the particular statement to limit the MAXDOP just for it for example to 1. – James Z Feb 6 '15 at 9:10
  • If you're on Enterprise Edition, the Resource Governor might help you setting CPU limits enforced in case of contention. High CPU usage indicates that you're doign calculations that would probably be a better fit for a client application. Is this the case? – spaghettidba Feb 6 '15 at 9:39
5

If a stored procedure is maxing out your CPU, you really need to tune that query to fix the root cause rather than working around it. Analyse the query plan using native SSMS or SQL Sentry Plan Explorer.

Some things to look out for to improve the query plan from http://www.brentozar.com/ are

To answer your question however, yes, you can limit CPU using Resource Governor.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.