Posted by Erland Sommarskog on 07/20/07 21:50

bobdurie@gmail.com (bobdurie@gmail.com) writes:
> Alex, i will go back and re-read those articles, but we've implemented
> as much as we can from them. As for the clustering of the tables
> based on the timestamps, thats something i'm totally unfamiliar
> with!!! Our schema is created straight from java with as much
> database agnostic code as possible, could you perhaps hint at how to
> do this for a noob like me?

CREATE CLUSTERED INDEX CreateDateIX ON loads(CreateDate)

and change this to:

LoadID BIGINT NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED,

> The insert looks likes this:
> exec sp_executesql N'INSERT INTO loads (Type, CreateDate, OtherDate,
> ObjectID) VALUES ( @P0 , @P1 , @P2 , @P3 )',N'@P0 int,@P1
> datetime,@P2 datetime,@P3 int',2,''2007-07-20
> 09:38:15:477'',NULL,10

And CreateDate is typically now?

> Heres a purge:
> declare @p1 int
> set @p1=2
> exec sp_prepare @p1 output,N'',N'DELETE FROM loads WHERE CreateDate <
> dateadd(SECOND, -40, CURRENT_TIMESTAMP)',1
> select @p1

40 seconds? Egads, why do you save the data at all? :-)

There is no index at all on CreateDate, so it will have to scan the
full table. It's surprising that you need that many concurrent processes
for it to happen.

If CreateDate is the time for insertion and never changed before the
purge, it would be a good idea to cluster on that column. That would
minimize the point of conflict between the DELETE and the INSERT (but
the non-clustered indexes could still be a conflict area.)

But the SELECT COUNT(*) and the "normal" SELECT is still problematic.
Particularly the normal SELECT looks like it would table scan to me
since the WHERE clause is "Type >= @P0". But that depends on the
values actually used and the distribution in the table.

You could avoid this if you use snapshot isolation, but that is only
available to SQL 2005.

I don't really know what Alex meant that they set their deadlock priority
to high. What I meant is that the purging process sould have SET
DEADLOCK_PRIORITY LOW to declare itself a voluntary victim. In any
case, on SQL 2000 the only deadlock priorties are LOW and NORMAL. SQL
2005 also has HIGH and numbered priorities.



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]