SQL Server – Stored Procedure Causing 100% CPU Usage

sql serverstored-procedures

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?

Best Answer

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.

Related Question