There are a whole bunch of things that could br going on here.

First some questions. Is this a new cluster you have just built, or an existing cluster just experiencing...

Jeff Moden (5/6/2010)

As well as what Leo said, sometimes...

In SQL 2005, Managment Studio

Right Click the server ->...

How big is the table you are testing against?

Also are the table layouts the same?

Also doing a DBCC DROPCLEANBUFFERS before each run avoids the use of the cache.

Leo

Yes!

I'm not sure what you mean byt this:

.... However my problem is that SQL will automatically wraps delete in a transaction, and thus moves of the records its about to...

You said: In other words, the records were added chronologically most of the time (but not always).

Check if ther are any records that would be incorrectly deleted if...

If you are also shrinking the database my recommendations are:

Do the shrink on the file

Shrink it in small amounts multiple times, rather than trying to go down in one big...

You asked: Why are you saying that to find the TOP (1000000) sql must check all the records?

The only way to find the TOP record is to look at them...

A further comment is to try use a clustered index/primary key in the WHERE clause. You don't say if the table is clustered on [DATE].

If [DATE] isn't clustered but there's...

1st problem you have is in:

IF EXISTS SELECT TOP 1..

2nd Problem:

DELETE TOP (1000000)..

To find the TOP (1000000) sql must check all the records.

Rather:

SET ROWCOUNT 1000000

DECLARE @deleted

SET @deleted = 1000000 ...

That's about 7 messages a second (assuming 24 hour day), which isn't so bad unless there are big peaks.

Sorry to say it, but this is a typical response from a...

MAX Degree of Parallelism. Or the number of processors SQL will assign to a query. Opinion varies on what it should be set to, but on SQL2K and SQL2K5 OLTP...

sp_helpfile will give you the info you want, but you would need to run it against each database.

Alternately use:

SELECT * FROM dbname.dbo.sysfiles ...

Note that

ALTER DATABASE DBNAME

SET MULTI_USER;

Will "work" on a database in READ_ONLY mode, i.e. it doesn't return an error, but it also doesn't take the database out of READ_ONLY mode. The...

Have you tried running the command out of Managment Studio. I often find I get much better feedback here than when I tests directly from the SQL Agent.

My typical build...