Posted by Erland Sommarskog on 06/14/05 00:59

pb648174 (google@webpaul.net) writes:
> The only thing about taking out the pauses is that it hammers on the
> database so hard, it pegs the CPU.. With the pauses other processes
> have a chance to go forward, but with this locking it doesn't seem to
> matter.

On a little more serious note... if the machine has more than one
CPU, you could consider to reduce the degree of parallelism, to leave
some CPUs to the rest of the pack. You do this by adding

OPTION (MAXDOP n)

at the end of the query. In fact you can even try 1, to abort parallelism
entirely. SQL Server appears to be over-optimistic by the benefits of
parallelism, and non-parallel plans may be better.

If your CPU(s) are hyper-threaded, you should server-configuration
parameter "Max degree of parallelism" to the number of physical CPUs.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]