Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    5

    Lightbulb Unanswered: Max row per page

    Hi all


    I have ASE 11.5, and I am going to upgrade to 12.0 , my current index and table definition contain max_row_per_page=1, I am welling to change this one by do the following:

    1- drop and create the indexes with no max_row_per_page=0
    2- alter tables and indexes on lock_schema to be data_rows

    my question is : is that the right wayto change this option or there is somwthing wrong in my way.

    2nd question is there is configuration parameters called "lock schem" in Sybase 12.0 , is that affect the way where the ASE do the table lock to make the chnages useful for query execution.

    Please advice me with the correct way to migrate from max_row_per_page=1 to max_row_per_page=0.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Max row per page

    Hi,

    You method of changing the max rows per page to data rows locking is OK. Where was the contention originally, on the data pages or the index pages. If it was on the index pages consider using data pages locking rather than data rows. Data rows locking may increase the number of locks you need to allocate to the server.

    The "lock scheme" configuration parameter sets the server wide default locking scheme. By default ASE creates tables with the "All Pages Locking" lock scheme. This is the traditional (eg 11.5) locking mechanism. The parameter can be set to "datapages" or "datarows" and only affects tables created after the parameter change.

    Assuming the server is configured for All Pages locking (the default), consider the following...

    create table T1 ( a int, b varchar(25))
    go
    sp_configure 'lock scheme', 0, 'datarows'
    go
    create table T2 ( a int, b varchar(25))
    go

    Table T1 is has the all pages locking scheme and table T2 has datarows. Changing the parameter does not change the locking scheme for T1.

    Table locks continue to be managed with the HWM and LWM thresholds. These apply to both page and row level locks. Page locks continue to be promoted to table locks - just as before. Row locks are promoted to table locks. They are *NOT* promoted to page locks first. There is now a set of HWM and LWM parameters for controlling the lock promotion for row and page level locks separately.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •