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)

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'

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