Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
yasirrose
Starting Member
1 Post |
 Posted - 2011-06-27 : 09:41:18
|
| We are trying to run a stored procedure that will make a small update to each 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? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-27 : 10:01:42
|
| just one processor? if multiple processors, you can use the MAXDOP hint.Else, can try setting the db to single user mode. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-06-27 : 16:43:30
|
I would check the WAIT information. Perhaps the disks are slow?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
How to use all resources !!