Skip to Content
Author's profile photo Former Member

Key Features of DOL Table structure

I’m am rediscovering many features of the DOL data page and index page structures.

1. On non-leaf DOL index pages, only the page # and shortest distinguishable part of the key is written.

     If “Carl” was the last key on index leaf #2 and

          “Connie” was the first key on index leaf #3

          then the non-leaf page would hold “Co”,3 because “Co” was enough to distinguish Connie from Carl…

     This method possibly allows the non-leaf pages to maintain more entries than APL non-leaf pages.

2. On leaf DOL pages, the whole key is stored so that the index can retain the covered read features.

3. Leaf index pages store duplicate key values once per page with multiple rids.

4. Leaf index pages has a sorted offset table in the page; key+rids pairs are not sorts.

5. DOL Pages do not use the Address Lock Spinlock (added 7/28/2014)

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Bret Halford
      Bret Halford

      This method possibly allows the non-leaf pages to maintain more entries than APL non-leaf pages.

      It certainly does, and as a result, the indexes on larger tables have fewer levels  than they otherwise would have, which means fewer IO when accessing a row via index.


      Author's profile photo Jeff Tallman
      Jeff Tallman

      Yes - this was called "index suffix compression".    In 16.0, what was done for index LEAF page (which did have the full key) was "index prefix compression".

      Author's profile photo Mark Gearhart
      Mark Gearhart

      Hi Cory,

      Ah yes, about those DOL tables, I was wondering if you might have have any suggestions related to LWM and HWM lock promotion thresholds as they relate to DOL tables. Our DBA's have upped our number of locks to 7,000,000 (yes, 7 million), but our LWM and HWM numbers are still set to the default of 200.

      Mark Gearhart

      Author's profile photo Jeff Tallman
      Jeff Tallman

      Welll, I can speak from experience that the LWM and HWM numbers are far far too low by default - and most place using datarows locking (remember datapage locking still will use the "page" LWM & HWM) that I have been to have been escalating to table locks ALOT without them being aware of it - which has (of course) driven peformance issues.   My out-of-the-box suggestion for both of these is to set them to 50000 or 100000.

      Now an alternative that SAP uses is that they set both escalations to 2B and then tune the lockwait time to 30 minutes.   This prevents tablelocks - which is one of their main goals - but does result in running out of locks in some situations (e.g. large loads). 

      For non-SAP apps, I think the middle of the road approach is probably best - e.g. increase the row lock promotion LWM and HWM to something high enough to satisfy most requests (including those nightly set rowcount 2000 delete/update batches) and let it escalate to a tablelock when it is painfully obvious that is going to impact a good percentage of the avoid running out of locks.    So maybe 100000 for a LWM and 1000000 for a HWM and a percentage of 50 would be good for you at 7M locks (btw - many SAP systems run with 10M quickly learn not to use sp_lock...kinda like using sp_who on a system with 10000 connections - you just can't wait that long for all the output to scroll by).

      I would be tempted as well to reduce the lockwait to 30 minutes like SAP does - or even 1 hour.

      Author's profile photo Mark Gearhart
      Mark Gearhart

      Thank Jeff. Just as I thought. Those LWM and HWM settings are not good. We will adjust and monitor. Thanks.