Sql Server 2005 Speed Issue

We are trying to run a stored procedure that will make a small update to each开发者_JS百科 row of a table with more than 10 million rows (it's a large database, as you can imagine). We are running SQL Server 2005.

The stored procedure I created is behaving strangely. When it starts, it moves very slowly - occupying very few CPU resources. Then suddenly, it spikes for an hour or two, occupying 99% of the CPU resources - at which point it moves very quickly (which is what we want).

But then, suddenly, it slows down again, working very slowly!

We want it to run fast (ie. take all the resources required) so it can finish the task (as this is a ONE TIME stored procedure designed to update the database).

Our server has the following resources: Intel (R) Xeon (R) CPU E5520 @ 2.27 Ghz 2.27 Ghz, 18 GB of Ram

Could someone please help me understand why SQL Server is doing this, and how to fix my stored procedure so it runs quickly and uses the resources available CONSISTENTLY?

Start out by identifying what's going on when the SP is working slowly - is the bottleneck your disk or perhaps network related?

You can follow Brent Ozar's great guide on using perfmon to identify just that: http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Once you know where the bottleneck is at, it becomes a lot easier to identify the core issue. Is the SP joining to any other tables? Is there a noneven distribution of joined data causing greater amounts of disk IO at certain rows compared to others?