Row Locking in ASE 11.9.2 & 12.0

Row Locking in ASE 11.9.2 & 12.0

Post by mmoura.. » Tue, 16 May 2000 04:00:00



Greeings;

I recently tried enabling row locking "lock DataRows" in ASE11.9.2 and
it works like a charm. Much better concurrency than old page locking
although the table/index takes more storage space (I don't know why)

Is there any reason not to take full advantage of this new great feature
that Sybase added to join the rest of the world (Oracle, DB2, Informix)
with Row Locking? I still see resistence from some dba old-timers who
are afraid to try this new feature.

Thanks

Maher

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by David L. Cheri » Tue, 16 May 2000 04:00:00


Quote:> Is there any reason not to take full advantage of this new great feature
> that Sybase added to join the rest of the world (Oracle, DB2, Informix)
> with Row Locking? I still see resistence from some dba old-timers who
> are afraid to try this new feature.

When you turn on DOL (data only locking), it turns your tables into heaps.
Clustered indexes, which in APL tables, were efficient because the data was
stored on the leaf level are now structured like non-clustered.  This means
that accessing them will be slower and less efficient because (i) extra I/Os
will be incurred to read the heap/data page from the clustered index's leaf
page and (ii) the heap is not sorted in the order of the clustered key
column(s).  To compensate for this short-coming, other improvements were
made such as heaps are now table scanned using the OAM pages instead of
prev/next page pointers.  Next, DOL tables require more maintenance because
fragmentation grows at a much faster pace.  Overall, depending on the design
of your table/indexes and how your application accesses them, you can see
performance degradation.  I think the safest rule of thumb is to only use
DOL if you need it -and- make sure you do some load testing prior to moving
it into production.

IMHO, newer doesn't always mean better.

David L. Cherin
White Sands Technology, Inc.

 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Bob Densmor » Tue, 16 May 2000 04:00:00


Find a "Powering up with ASE 11.9.2" class.  You are in for much more manual
maintenance activity than before if you blindly convert everything to
datarows locking.

Bob Densmore


> Greeings;

> I recently tried enabling row locking "lock DataRows" in ASE11.9.2 and
> it works like a charm. Much better concurrency than old page locking
> although the table/index takes more storage space (I don't know why)

> Is there any reason not to take full advantage of this new great feature
> that Sybase added to join the rest of the world (Oracle, DB2, Informix)
> with Row Locking? I still see resistence from some dba old-timers who
> are afraid to try this new feature.

> Thanks

> Maher

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Norri » Wed, 17 May 2000 04:00:00



Quote:>> Is there any reason not to take full advantage of this new great feature
>> that Sybase added to join the rest of the world (Oracle, DB2, Informix)
>> with Row Locking? I still see resistence from some dba old-timers who
>> are afraid to try this new feature.
> When you turn on DOL (data only locking), it turns your tables into heaps.
> Clustered indexes, which in APL tables, were efficient because the data was
> stored on the leaf level are now structured like non-clustered.  This means
> that accessing them will be slower and less efficient because (i) extra I/Os
> will be incurred to read the heap/data page from the clustered index's leaf
> page and (ii) the heap is not sorted in the order of the clustered key
> column(s).  To compensate for this short-coming, other improvements were
> made such as heaps are now table scanned using the OAM pages instead of
> prev/next page pointers.  Next, DOL tables require more maintenance because
> fragmentation grows at a much faster pace.  Overall, depending on the design
> of your table/indexes and how your application accesses them, you can see
> performance degradation.  I think the safest rule of thumb is to only use
> DOL if you need it -and- make sure you do some load testing prior to moving
> it into production.

So, you don't recommend to set the server default lock setting to DOL, am I right?
 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Ken H » Wed, 17 May 2000 04:00:00




> >> Is there any reason not to take full advantage of this new great feature
> >> that Sybase added to join the rest of the world (Oracle, DB2, Informix)
> >> with Row Locking? I still see resistence from some dba old-timers who
> >> are afraid to try this new feature.

> > When you turn on DOL (data only locking), it turns your tables into heaps.
> > Clustered indexes, which in APL tables, were efficient because the data was
> > stored on the leaf level are now structured like non-clustered.  This means
> > that accessing them will be slower and less efficient because (i) extra I/Os
> > will be incurred to read the heap/data page from the clustered index's leaf
> > page and (ii) the heap is not sorted in the order of the clustered key
> > column(s).  To compensate for this short-coming, other improvements were
> > made such as heaps are now table scanned using the OAM pages instead of
> > prev/next page pointers.  Next, DOL tables require more maintenance because
> > fragmentation grows at a much faster pace.  Overall, depending on the design
> > of your table/indexes and how your application accesses them, you can see
> > performance degradation.  I think the safest rule of thumb is to only use
> > DOL if you need it -and- make sure you do some load testing prior to moving
> > it into production.

> So, you don't recommend to set the server default lock setting to DOL, am I right?

Right.. You have to do your own test to justisfy the change of locking scheme.

Ken
Certfied Adaptive Server Administrator Professional.

 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Norri » Wed, 17 May 2000 04:00:00





>> >> Is there any reason not to take full advantage of this new great feature
>> >> that Sybase added to join the rest of the world (Oracle, DB2, Informix)
>> >> with Row Locking? I still see resistence from some dba old-timers who
>> >> are afraid to try this new feature.

>> > When you turn on DOL (data only locking), it turns your tables into heaps.
>> > Clustered indexes, which in APL tables, were efficient because the data was
>> > stored on the leaf level are now structured like non-clustered.  This means
>> > that accessing them will be slower and less efficient because (i) extra I/Os
>> > will be incurred to read the heap/data page from the clustered index's leaf
>> > page and (ii) the heap is not sorted in the order of the clustered key
>> > column(s).  To compensate for this short-coming, other improvements were
>> > made such as heaps are now table scanned using the OAM pages instead of
>> > prev/next page pointers.  Next, DOL tables require more maintenance because
>> > fragmentation grows at a much faster pace.  Overall, depending on the design
>> > of your table/indexes and how your application accesses them, you can see
>> > performance degradation.  I think the safest rule of thumb is to only use
>> > DOL if you need it -and- make sure you do some load testing prior to moving
>> > it into production.

>> So, you don't recommend to set the server default lock setting to DOL, am I right?
> Right.. You have to do your own test to justisfy the change of locking scheme.

How to test the behaviour of of a new Sybase Server?
 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by David L. Cheri » Wed, 17 May 2000 04:00:00



Quote:> So, you don't recommend to set the server default lock setting to DOL, am

I right?

Depending on your table schema and application's use, it may be a benefit.
Remember, the trade-off is performance of table scans vs. more required
database maintenance and larger table sizes.  I would say that you'd have to
do a lot of testing prior to making that decision in the following areas:
(i) does it increase performance to use DOL because of lock concurrency and
(ii) at the rate we insert/update data, how often will I be required to
defragment the database and can I afford the required downtime.

The rule of thumb for P&T in Sybase is "If you turn one knob to improve
performance in one area of your database, another knob someplace else will
also turn that will degrade it".  Each time you make a P&T change, there are
always side-effects and performance costs elsewhere associated with it. As
long as you understand this and fully know about the consequences, you're
okay.

David L. Cherin
White Sands Technology, Inc.

 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Norri » Thu, 18 May 2000 04:00:00





>> So, you don't recommend to set the server default lock setting to DOL, am
> I right?
> Depending on your table schema and application's use, it may be a benefit.
> Remember, the trade-off is performance of table scans vs. more required
> database maintenance and larger table sizes.  I would say that you'd have to
> do a lot of testing prior to making that decision in the following areas:
> (i) does it increase performance to use DOL because of lock concurrency and
> (ii) at the rate we insert/update data, how often will I be required to
> defragment the database and can I afford the required downtime.
> The rule of thumb for P&T in Sybase is "If you turn one knob to improve
> performance in one area of your database, another knob someplace else will
> also turn that will degrade it".  Each time you make a P&T change, there are

I found that it would be difficult for me to convert a 4GB all-pages lock table to data-only lock table. Any suggestion?
 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Eric Mine » Thu, 18 May 2000 04:00:00


Quote:>"If you turn one knob to improve
>performance in one area of your database, another knob someplace else will
>also turn that will degrade it"

For your 'rule of thumb' to be beleivable you'll have to be more specific. It
has not been my experience that tweaking a knob to aid performance will result
in an adverse effect of performance.

Could you be more specific about the adverse side effects you refer to? Again,
this has not been my experience.

There are very few rules of thumb that stand up to analysis. Most performance
issues are directly dependant on the dataset.

Later,

Eric Miner
ASE Engineering
Optimzier Group




> > So, you don't recommend to set the server default lock setting to DOL, am
> I right?

> The rule of thumb for P&T in Sybase is "If you turn one knob to improve
> performance in one area of your database, another knob someplace else will
> also turn that will degrade it".  Each time you make a P&T change, there are
> always side-effects and performance costs elsewhere associated with it. As
> long as you understand this and fully know about the consequences, you're
> okay.

> David L. Cherin
> White Sands Technology, Inc.

 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Brian Blan » Sat, 20 May 2000 04:00:00


Hi,

I am assuming that Sybase is giving you problems about convert converting a 4GB table from allpages to a DOL locking
scheme.  In order to go from allpages to DOL or vice-versa, you must have tablesize+20% extra free.  So, in your case, you
need about 5 GB free in the database.  Maybe, it would be easier to BCP the data out, truncate the table, change the locking
scheme, then BCP the data back into the database.

Hope that helps.

- Brian Blank





> >> So, you don't recommend to set the server default lock setting to DOL, am
> > I right?

> > Depending on your table schema and application's use, it may be a benefit.
> > Remember, the trade-off is performance of table scans vs. more required
> > database maintenance and larger table sizes.  I would say that you'd have to
> > do a lot of testing prior to making that decision in the following areas:
> > (i) does it increase performance to use DOL because of lock concurrency and
> > (ii) at the rate we insert/update data, how often will I be required to
> > defragment the database and can I afford the required downtime.

> > The rule of thumb for P&T in Sybase is "If you turn one knob to improve
> > performance in one area of your database, another knob someplace else will
> > also turn that will degrade it".  Each time you make a P&T change, there are
> I found that it would be difficult for me to convert a 4GB all-pages lock table to data-only lock table. Any suggestion?

 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by Norri » Sat, 20 May 2000 04:00:00


Do you have any idea of the time required to complete the tasks of
bcp-out, bcp-in and re-index on a Pentium III 550 MHz?


> Hi,

> I am assuming that Sybase is giving you problems about convert converting a 4GB table from allpages to a DOL locking
> scheme.  In order to go from allpages to DOL or vice-versa, you must have tablesize+20% extra free.  So, in your case, you
> need about 5 GB free in the database.  Maybe, it would be easier to BCP the data out, truncate the table, change the locking
> scheme, then BCP the data back into the database.

> Hope that helps.

> - Brian Blank





>> >> So, you don't recommend to set the server default lock setting to DOL, am
>> > I right?

>> > Depending on your table schema and application's use, it may be a benefit.
>> > Remember, the trade-off is performance of table scans vs. more required
>> > database maintenance and larger table sizes.  I would say that you'd have to
>> > do a lot of testing prior to making that decision in the following areas:
>> > (i) does it increase performance to use DOL because of lock concurrency and
>> > (ii) at the rate we insert/update data, how often will I be required to
>> > defragment the database and can I afford the required downtime.

>> > The rule of thumb for P&T in Sybase is "If you turn one knob to improve
>> > performance in one area of your database, another knob someplace else will
>> > also turn that will degrade it".  Each time you make a P&T change, there are
>> I found that it would be difficult for me to convert a 4GB all-pages lock table to data-only lock table. Any suggestion?

--
http://www.cooper.com.hk
 
 
 

Row Locking in ASE 11.9.2 & 12.0

Post by daihungk.. » Wed, 24 May 2000 04:00:00


in answer to your question. i dont think anyone but you can answer that.

however, if you were to say bcp out a 100mb table like your 4gb one and
then reverse the process you will get an idea of how long it takes on
your system. bear in mind that if you have a lot of indices on the table
it will take longer than you think to push it in due to index management
considerations.

dave






> >> So, you don't recommend to set the server default lock setting to
DOL, am
> > I right?

> > Depending on your table schema and application's use, it may be a
benefit.
> > Remember, the trade-off is performance of table scans vs. more
required
> > database maintenance and larger table sizes.  I would say that you'd
have to
> > do a lot of testing prior to making that decision in the following
areas:
> > (i) does it increase performance to use DOL because of lock
concurrency and
> > (ii) at the rate we insert/update data, how often will I be required
to
> > defragment the database and can I afford the required downtime.

> > The rule of thumb for P&T in Sybase is "If you turn one knob to
improve
> > performance in one area of your database, another knob someplace
else will
> > also turn that will degrade it".  Each time you make a P&T change,
there are
> I found that it would be difficult for me to convert a 4GB all-pages

lock table to data-only lock table. Any suggestion?

Sent via Deja.com http://www.deja.com/
Before you buy.