Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: High address lock contention

    We just made a major change to our schema and now we are getting a high level of address lock contention but now real page level contention. Do we need to go row level locking to address this issue?

    ---------- --------------------------
    start time Mar 6 2008 12:00PM

    (1 row affected)
    ================================================== =============================
    Sybase Adaptive Server Enterprise System Performance Report
    ================================================== =============================

    Server Version: Adaptive Server Enterprise/12.5.3/EBF 12600 ESD#3/P/Linu
    Server Name: ross
    Run Date: Mar 06, 2008
    Statistics Cleared at: Mar 06, 2008 12:00:01
    Statistics Sampled at: Mar 06, 2008 12:01:02
    Sample Interval: 00:01:00

    ================================================== =============================

    Kernel Utilization
    ------------------

    Your Runnable Process Search Count is set to 2000
    and I/O Polling Process Count is set to 10

    Engine Busy Utilization CPU Busy I/O Busy Idle
    ------------------------ -------- -------- --------
    Engine 0 83.4 % 1.2 % 15.4 %
    Engine 1 81.3 % 1.5 % 17.2 %
    Engine 2 83.6 % 1.5 % 14.9 %
    Engine 3 78.9 % 1.3 % 19.7 %
    ------------------------ -------- -------- --------
    Summary Total 327.2 % 5.5 % 67.3 %
    Average 81.8 % 1.4 % 16.8 %

    CPU Yields by Engine per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Engine 0 2.4 8.9 143 8.0 %
    Engine 1 9.7 36.3 581 32.7 %
    Engine 2 6.0 22.6 361 20.3 %
    Engine 3 11.6 43.3 693 39.0 %
    ------------------------- ------------ ------------ ----------
    Total CPU Yields 29.6 111.1 1778

    Network Checks
    Non-Blocking 88523.2 331962.0 5311392 100.0 %
    Blocking 29.6 111.1 1778 0.0 %
    ------------------------- ------------ ------------ ----------
    Total Network I/O Checks 88552.8 332073.1 5313170
    Avg Net I/Os per Check n/a n/a 0.01532 n/a

    Disk I/O Checks
    Total Disk I/O Checks 93651.5 351193.3 5619092 n/a
    Checks Returning I/O 18023.4 67587.6 1081402 19.2 %
    Avg Disk I/Os Returned n/a n/a 0.00828 n/a


    ================================================== =============================

    Worker Process Management
    -------------------------
    per sec per xact count % of total
    ------------ ------------ ---------- ----------
    Worker Process Requests
    Requests Granted 1.7 6.5 104 100.0 %
    Requests Denied 0.0 0.0 0 0.0 %
    -------------------------- ------------ ------------ ----------
    Total Requests 1.7 6.5 104

    Requests Terminated 0.0 0.0 0 0.0 %

    Worker Process Usage
    Total Used 3.5 13.0 208 n/a
    Max Ever Used During Sample 3.1 11.5 184 n/a

    Memory Requests for Worker Processes
    Succeeded 38.0 142.5 2280 100.0 %
    Failed 0.0 0.0 0 0.0 %
    -------------------------- ------------ ------------ ----------
    Total Requests 38.0 142.5 2280

    Avg Mem Ever Used by a WP (in bytes) n/a n/a 151.8 n/a

    Tuning Recommendations for Worker Processes
    -------------------------------------------
    - Consider decreasing the 'memory per worker process'
    configuration parameter.


    ================================================== =============================

    Parallel Query Management
    -------------------------

    Parallel Query Usage per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Total Parallel Queries 1.8 6.6 105 n/a
    WP Adjustments Made
    Due to WP Limit 0.0 0.1 1 1.0 %
    Due to No WPs 0.0 0.0 0 0.0 %

    Merge Lock Requests per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Network Buffer Merge Locks
    Granted with no wait 58675.7 220033.8 3520540 91.2 %
    Granted after wait 5646.8 21175.4 338806 8.8 %

    Result Buffer Merge Locks
    Granted with no wait 0.9 3.4 55 0.0 %
    Granted after wait 0.0 0.0 0 0.0 %

    Work Table Merge Locks
    Granted with no wait 0.0 0.0 0 0.0 %
    Granted after wait 0.0 0.0 0 0.0 %
    ------------------------- ------------ ------------ ----------
    Total # of Requests 64323.4 241212.6 3859401

    Sort Buffer Waits per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Total # of Waits 0.0 0.0 0 n/a

    ================================================== =============================

    Task Management per sec per xact count % of total
    --------------------------- ------------ ------------ ---------- ----------

    Connections Opened 0.0 0.0 0 n/a

    Task Context Switches by Engine
    Engine 0 2712.7 10172.6 162762 27.9 %
    Engine 1 2231.6 8368.3 133893 22.9 %
    Engine 2 2714.3 10178.8 162860 27.9 %
    Engine 3 2077.8 7791.9 124670 21.3 %
    ------------------------- ------------ ------------ ----------
    Total Task Switches: 9736.4 36511.6 584185

    Task Context Switches Due To:
    Voluntary Yields 10.7 40.0 640 0.1 %
    Cache Search Misses 137.1 513.9 8223 1.4 %
    System Disk Writes 0.0 0.1 1 0.0 %
    I/O Pacing 0.1 0.3 5 0.0 %
    Logical Lock Contention 0.0 0.1 1 0.0 %
    Address Lock Contention 5646.8 21175.3 338805 58.0 %
    Latch Contention 0.0 0.0 0 0.0 %
    Log Semaphore Contention 0.0 0.0 0 0.0 %
    PLC Lock Contention 0.0 0.0 0 0.0 %
    Group Commit Sleeps 0.0 0.0 0 0.0 %
    Last Log Page Writes 0.2 0.6 10 0.0 %
    Modify Conflicts 0.1 0.3 5 0.0 %
    I/O Device Contention 0.0 0.0 0 0.0 %
    Network Packet Received 568.3 2131.0 34096 5.8 %
    Network Packet Sent 124.8 468.1 7489 1.3 %
    Other Causes 3248.5 12181.9 194910 33.4 %


    ================================================== =============================
    0)

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    You didnt mention what "major change" you did.

    Granted after wait 5646.8 21175.4 338806 8.8 %
    Address Lock Contention 5646.8 21175.3 338805 58.0 %

    If you see the above two, the count is almost the same. So my guess is you have issues with worker processes and network.

    1. Each worker process acquires an exclusive address lock on the network buffer while writing results to it.
    When the buffer is full, it is sent to the client, and the lock is held until the network write completes.

    Can you paste the following

    A) Network statistics of sysmon.
    B) sp_configure 'network'
    C) sp_configure 'worker'

  3. #3
    Join Date
    Mar 2008
    Posts
    2
    Thanks for the response! Here's the info you mentioned:

    Network I/O Management
    ----------------------

    Total Network I/O Requests 828.3 1057.4 49699 n/a
    Network I/Os Delayed 0.0 0.0 0 0.0 %


    Total TDS Packets Received per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Engine 0 157.6 201.2 9458 26.3 %
    Engine 1 197.5 252.1 11847 32.9 %
    Engine 2 114.1 145.7 6846 19.0 %
    Engine 3 130.8 166.9 7846 21.8 %
    ------------------------- ------------ ------------ ----------
    Total TDS Packets Rec'd 600.0 765.9 35997


    Total Bytes Received per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Engine 0 40405.3 51581.2 2424317 24.4 %
    Engine 1 57128.5 72930.0 3427708 34.5 %
    Engine 2 33578.6 42866.3 2014716 20.3 %
    Engine 3 34311.0 43801.3 2058662 20.7 %
    ------------------------- ------------ ------------ ----------
    Total Bytes Rec'd 165423.4 211178.8 9925403


    Avg Bytes Rec'd per Packet n/a n/a 275 n/a

    -----------------------------------------------------------------------------

    Total TDS Packets Sent per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Engine 0 355.9 454.4 21355 27.8 %
    Engine 1 413.1 527.4 24786 32.2 %
    Engine 2 249.5 318.5 14969 19.5 %
    Engine 3 263.1 335.8 15783 20.5 %
    ------------------------- ------------ ------------ ----------
    Total TDS Packets Sent 1281.6 1636.0 76893


    Total Bytes Sent per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Engine 0 146143.6 186566.3 8768615 27.8 %
    Engine 1 170546.6 217719.1 10232796 32.4 %
    Engine 2 104783.8 133766.6 6287030 19.9 %
    Engine 3 104890.5 133902.7 6293427 19.9 %
    ------------------------- ------------ ------------ ----------
    Total Bytes Sent 526364.5 671954.6 31581868


    Avg Bytes Sent per Packet n/a n/a 410 n/a

    1> sp_configure "network"
    2> go
    Msg 17411, Level 16, State 1:
    Server 'ross', Procedure 'sp_configure', Line 209:
    Configuration option is not unique.

    Parameter Name Default Memory Used Config Value Run Value Unit Type
    ------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
    additional network memory 0 1024 1048576 1048576 bytes dynamic
    default network packet size 512 #6920 8192 8192 bytes static
    max network packet size 512 0 14848 14848 bytes static
    max number network listeners 5 882 5 5 number dynamic
    Network Communication 0 0 0 0 not applicable NULL

    (1 row affected)
    (return status = 1)
    1>
    Parameter Name Default Memory Used Config Value Run Value Unit Type
    ------------------------------ ----------- ----------- ------------ ----------- -------------------- ----------
    memory per worker process 1024 512 8192 8192 bytes dynamic
    number of worker processes 0 5441 32 32 number dynamic

Posting Permissions

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