Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Massive locks from an update of a single row

5 posts in ,  Windows NTAdministration General Discussion Last posting was on 2000-04-19 15:40:47.0Z
Eric T. J. Hogue Posted on 2000-04-18 13:22:30.0Z
From: "Eric T. J. Hogue" <Eric.Hogue@wpafb.af.mil>
Subject: Massive locks from an update of a single row
Date: Tue, 18 Apr 2000 09:22:30 -0400
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <bg87LoTq$GA.211@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: 137.245.195.62
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3208 sybase.public.sqlserver.general:663 sybase.public.sqlserver.nt:362
Article PK: 1065370

We are running Sybase 11.9.2 on a Win NT4.0 box.

I have a stored proc that is frequently called, whose function is to replace
a null value in a field of a single row with a datetime value. I believe
the fillfactor for this table is 0, but since it starts out everyday with
with a few dozen rows, grows to hundreds over the course of the day, and
gets emptied at night, I didn't think adding a fillfactor would help.

In any case, the problem is that from time to time, these updates lock up
literally hundreds of pages.

The table has a primary index on an identity field which is used to specify
the update location.

Any help would be greatly appreciated.

Eric


Stefan Goebel Posted on 2000-04-18 14:15:46.0Z
From: "Stefan Goebel" <goebel@dsdi.de>
References: <bg87LoTq$GA.211@forums.sybase.com>
Subject: Re: Massive locks from an update of a single row
Date: Tue, 18 Apr 2000 16:15:46 +0200
Lines: 33
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <cPGC$DUq$GA.298@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: 62.159.143.62
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3207 sybase.public.sqlserver.general:662 sybase.public.sqlserver.nt:361
Article PK: 1065369

Hi Eric,

have you checked whether the update statement performes
a table scan instead of using the metioned index ?

If so, try to force the optimizer to use the index

Bye, Stefan

Eric T. J. Hogue wrote in message ...
>We are running Sybase 11.9.2 on a Win NT4.0 box.
>
>I have a stored proc that is frequently called, whose function is to
replace
>a null value in a field of a single row with a datetime value. I believe
>the fillfactor for this table is 0, but since it starts out everyday with
>with a few dozen rows, grows to hundreds over the course of the day, and
>gets emptied at night, I didn't think adding a fillfactor would help.
>
>In any case, the problem is that from time to time, these updates lock up
>literally hundreds of pages.
>
>The table has a primary index on an identity field which is used to specify
>the update location.
>
>Any help would be greatly appreciated.
>
>Eric
>
>


Eric T. J. Hogue Posted on 2000-04-18 17:22:19.0Z
From: "Eric T. J. Hogue" <Eric.Hogue@wpafb.af.mil>
References: <bg87LoTq$GA.211@forums.sybase.com> <cPGC$DUq$GA.298@forums.sybase.com>
Subject: Now it takes too long.
Date: Tue, 18 Apr 2000 13:22:19 -0400
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <#RDtLuVq$GA.298@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: 137.245.195.62
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3206 sybase.public.sqlserver.general:661 sybase.public.sqlserver.nt:360
Article PK: 1065368

Yes, it was doing a table scan. However, with the table scan it was
reporting about 12,000 logical reads, and with the forced
index 179,000. It seems to take several times as long. Could the deferred
update be causing this effect?

Eric

However, after forcing the index, it seems to run at about 4 sec per
iteration, in

"Stefan Goebel" <goebel@dsdi.de> wrote in message
news:cPGC$DUq$GA.298@forums.sybase.com...
> Hi Eric,
>
> have you checked whether the update statement performes
> a table scan instead of using the metioned index ?
>
> If so, try to force the optimizer to use the index
>
> Bye, Stefan
>
>
> Eric T. J. Hogue wrote in message ...
> >We are running Sybase 11.9.2 on a Win NT4.0 box.
> >
> >I have a stored proc that is frequently called, whose function is to
> replace
> >a null value in a field of a single row with a datetime value. I believe
> >the fillfactor for this table is 0, but since it starts out everyday with
> >with a few dozen rows, grows to hundreds over the course of the day, and
> >gets emptied at night, I didn't think adding a fillfactor would help.
> >
> >In any case, the problem is that from time to time, these updates lock up
> >literally hundreds of pages.
> >
> >The table has a primary index on an identity field which is used to
specify
> >the update location.
> >
> >Any help would be greatly appreciated.
> >
> >Eric
> >
> >
>
>


Eric T. J. Hogue Posted on 2000-04-18 17:50:14.0Z
From: "Eric T. J. Hogue" <Eric.Hogue@wpafb.af.mil>
References: <bg87LoTq$GA.211@forums.sybase.com> <cPGC$DUq$GA.298@forums.sybase.com> <#RDtLuVq$GA.298@forums.sybase.com>
Subject: Re: Now it takes too long.
Date: Tue, 18 Apr 2000 13:50:14 -0400
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <PYzpx9Vq$GA.211@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: 137.245.195.62
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3205 sybase.public.sqlserver.general:660 sybase.public.sqlserver.nt:359
Article PK: 1065367

Never mind, it just shortened itself to 4 logical reads.

It somehow decided a direct update was acceptable.

AAAAgh! :)

Eric

"Eric T. J. Hogue" <Eric.Hogue@wpafb.af.mil> wrote in message
news:#RDtLuVq$GA.298@forums.sybase.com...
> Yes, it was doing a table scan. However, with the table scan it was
> reporting about 12,000 logical reads, and with the forced
> index 179,000. It seems to take several times as long. Could the
deferred
> update be causing this effect?
>
> Eric
>
> However, after forcing the index, it seems to run at about 4 sec per
> iteration, in
> "Stefan Goebel" <goebel@dsdi.de> wrote in message
> news:cPGC$DUq$GA.298@forums.sybase.com...
> > Hi Eric,
> >
> > have you checked whether the update statement performes
> > a table scan instead of using the metioned index ?
> >
> > If so, try to force the optimizer to use the index
> >
> > Bye, Stefan
> >
> >
> > Eric T. J. Hogue wrote in message ...
> > >We are running Sybase 11.9.2 on a Win NT4.0 box.
> > >
> > >I have a stored proc that is frequently called, whose function is to
> > replace
> > >a null value in a field of a single row with a datetime value. I
believe
> > >the fillfactor for this table is 0, but since it starts out everyday
with
> > >with a few dozen rows, grows to hundreds over the course of the day,
and
> > >gets emptied at night, I didn't think adding a fillfactor would help.
> > >
> > >In any case, the problem is that from time to time, these updates lock
up
> > >literally hundreds of pages.
> > >
> > >The table has a primary index on an identity field which is used to
> specify
> > >the update location.
> > >
> > >Any help would be greatly appreciated.
> > >
> > >Eric
> > >
> > >
> >
> >
>
>


Ray DiMarcello Posted on 2000-04-19 15:40:47.0Z
From: "Ray DiMarcello" <rdimarcello@ingva.com>
References: <bg87LoTq$GA.211@forums.sybase.com> <cPGC$DUq$GA.298@forums.sybase.com> <#RDtLuVq$GA.298@forums.sybase.com> <PYzpx9Vq$GA.211@forums.sybase.com>
Subject: Re: Now it takes too long.
Date: Wed, 19 Apr 2000 11:40:47 -0400
Lines: 74
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <F$dowahq$GA.298@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.administration,sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: border.goldenamericanlife.com 207.245.124.67
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.administration:3204 sybase.public.sqlserver.general:659 sybase.public.sqlserver.nt:358
Article PK: 1065366

It won't always do a direct update if the column definition allows nulls.
The deferred update most likely is the culprit and I would look into
defining it as not null ...

RD

Eric T. J. Hogue wrote in message ...
>Never mind, it just shortened itself to 4 logical reads.
>
>It somehow decided a direct update was acceptable.
>
>AAAAgh! :)
>
>Eric
>
>"Eric T. J. Hogue" <Eric.Hogue@wpafb.af.mil> wrote in message
>news:#RDtLuVq$GA.298@forums.sybase.com...
>> Yes, it was doing a table scan. However, with the table scan it was
>> reporting about 12,000 logical reads, and with the forced
>> index 179,000. It seems to take several times as long. Could the
>deferred
>> update be causing this effect?
>>
>> Eric
>>
>> However, after forcing the index, it seems to run at about 4 sec per
>> iteration, in
>> "Stefan Goebel" <goebel@dsdi.de> wrote in message
>> news:cPGC$DUq$GA.298@forums.sybase.com...
>> > Hi Eric,
>> >
>> > have you checked whether the update statement performes
>> > a table scan instead of using the metioned index ?
>> >
>> > If so, try to force the optimizer to use the index
>> >
>> > Bye, Stefan
>> >
>> >
>> > Eric T. J. Hogue wrote in message ...
>> > >We are running Sybase 11.9.2 on a Win NT4.0 box.
>> > >
>> > >I have a stored proc that is frequently called, whose function is to
>> > replace
>> > >a null value in a field of a single row with a datetime value. I
>believe
>> > >the fillfactor for this table is 0, but since it starts out everyday
>with
>> > >with a few dozen rows, grows to hundreds over the course of the day,
>and
>> > >gets emptied at night, I didn't think adding a fillfactor would help.
>> > >
>> > >In any case, the problem is that from time to time, these updates lock
>up
>> > >literally hundreds of pages.
>> > >
>> > >The table has a primary index on an identity field which is used to
>> specify
>> > >the update location.
>> > >
>> > >Any help would be greatly appreciated.
>> > >
>> > >Eric
>> > >
>> > >
>> >
>> >
>>
>>
>
>