Design flaw - Too slow lock escalation

Hi!
I seem to have a problem opposite to everyone else.
Lock escalation does not occur.
My client runs a DTS-job with some 30 steps every night.
Randomly some of the the steps fail with:
Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot obtain
a LOCK resource at this time.
When the failing steps are rerun at daytime they "never" fails on 1204.
I have been trying to pin-point a locking culprit for some time but haven't
found any.
On Friday night I followed the DTS-job closely, running sp_who2 and sp_lock
at short intervals
and also had a perfmon running. When the 1204 at last occured after midnight
I found the following:
The transaction is a
Insert into BIG_TABLE
select * from WORK_Table
Most indexes, but not all are dropped from BIG_Table and later recreated.
Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
server)
and within a few seconds these 5 threads had created +1GB of Key locks, and
shortly Error 1204 occured and the transaction was rollbacked.
The DTS-job was the only active transaction in the server so there was no
competition for resources.
My theory is that the lock monitor, (that I presume handles lock
escalation), sleeps too long sometimes,
and fails to do the lock escalation. In this case the server is quite
powerful and has good fibre access the SAN-disk,
and when CPU and I-O is freely available at nighttime lock consumimg is too
fast. At daytime when there is competition for both CPU and I-O,
the lock-creating process is not fast enough to eat all available memory.
Please enlighten me if I'm wrong.
We have circumvented the problem by adding a WITH (TABLOCK) to the insert
statement,
but this is undesirable, if we for other reasons have to rerun a jobstep at
daytime.
Is this a known problem or as designed?
Should I go into the tedious process of raising a support case with MS?
By the way:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
\dg
Dan van Ginhoven
Sweden
Is this an all or nothing process? By this I mean that can it be done in
smaller batches of say 10K rows at a time? Usually this can be done and
will limit the number of locks to ~10K. If not then a Table level lock may
be best. The most likely reason why the lock is not escalating to a tablock
is due to another lock on that table at some level. Even a single shared
row lock will stop the escalation process. So if users are selecting from
the table it may not escalate on it's own unless there is a lull in the
selects.
Andrew J. Kelly SQL MVP
"Dan van Ginhoven" <danfan46@.hotmail.com> wrote in message
news:shR6f.148931$dP1.506911@.newsc.telia.net...
> Hi!
> I seem to have a problem opposite to everyone else.
> Lock escalation does not occur.
> My client runs a DTS-job with some 30 steps every night.
> Randomly some of the the steps fail with:
> Error 1204 , Severity Level 19 , Message Text ,The SQL Server cannot
> obtain
> a LOCK resource at this time.
> When the failing steps are rerun at daytime they "never" fails on 1204.
> I have been trying to pin-point a locking culprit for some time but
> haven't
> found any.
> On Friday night I followed the DTS-job closely, running sp_who2 and
> sp_lock
> at short intervals
> and also had a perfmon running. When the 1204 at last occured after
> midnight
> I found the following:
> The transaction is a
> Insert into BIG_TABLE
> select * from WORK_Table
> Most indexes, but not all are dropped from BIG_Table and later recreated.
> Sp_who2 showed that the insert was parallelled in 5 threads, (6 cpu
> server)
> and within a few seconds these 5 threads had created +1GB of Key locks,
> and
> shortly Error 1204 occured and the transaction was rollbacked.
> The DTS-job was the only active transaction in the server so there was no
> competition for resources.
> My theory is that the lock monitor, (that I presume handles lock
> escalation), sleeps too long sometimes,
> and fails to do the lock escalation. In this case the server is quite
> powerful and has good fibre access the SAN-disk,
> and when CPU and I-O is freely available at nighttime lock consumimg is
> too
> fast. At daytime when there is competition for both CPU and I-O,
> the lock-creating process is not fast enough to eat all available memory.
> Please enlighten me if I'm wrong.
> We have circumvented the problem by adding a WITH (TABLOCK) to the
> insert
> statement,
> but this is undesirable, if we for other reasons have to rerun a jobstep
> at
> daytime.
> Is this a known problem or as designed?
> Should I go into the tedious process of raising a support case with MS?
> By the way:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> \dg
> Dan van Ginhoven
> Sweden
>