Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Location
    London
    Posts
    3

    Unanswered: sp_configure "number of locks" - what value is recommended?

    Hello,

    I have the following stats from sp_sysmon.
    I know I should be increasing the configuration on the server.
    But what is the recommended setting for
    "number of locks"
    when our lock requests per second is 9000 plus..?

    Cheers
    L

    ================================================== ========
    Sybase Adaptive Server Enterprise System Performance Report
    ================================================== ========

    Server Version: Adaptive Server Enterprise/11.5.1/P/NT/OS 4.00/1/fbu/Fri
    Server Name: Server is Unnamed
    Run Date: Feb 03, 2006
    Statistics Cleared at: 17:37:07
    Statistics Sampled at: 17:37:38
    Sample Interval: 00:00:31

    ================================================== ========
    Lock Management
    ---------------

    Lock Summary per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Total Lock Requests 9601.8 297655.0 297655 n/a
    Avg Lock Contention 0.0 0.0 0 0.0 %
    Deadlock Percentage 0.0 0.0 0 0.0 %

    Lock Detail per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------

    Exclusive Table
    Granted 0.0 1.0 1 100.0 %
    Waited 0.0 0.0 0 0.0 %
    ------------------------- ------------ ------------ ---------- ----------
    Total EX-Table Requests 0.0 1.0 1 0.0 %

    Shared Table
    Total SH-Table Requests 0.0 0.0 0 n/a

    Exclusive Intent
    Total EX-Intent Requests 0.0 0.0 0 n/a

    Shared Intent
    Granted 0.2 7.0 7 100.0 %
    Waited 0.0 0.0 0 0.0 %
    ------------------------- ------------ ------------ ---------- ----------
    Total SH-Intent Requests 0.2 7.0 7 0.0 %

    Exclusive Page
    Total EX-Page Requests 0.0 0.0 0 n/a

    Update Page
    Total UP-Page Requests 0.0 0.0 0 n/a

    Shared Page
    Granted 5087.9 157725.0 157725 100.0 %
    Waited 0.0 0.0 0 0.0 %
    ------------------------- ------------ ------------ ---------- ----------
    Total SH-Page Requests 5087.9 157725.0 157725 53.0 %


    Exclusive Address
    Granted 0.1 2.0 2 100.0 %
    Waited 0.0 0.0 0 0.0 %
    ------------------------- ------------ ------------ ---------- ----------
    Total EX-Address Requests 0.1 2.0 2 0.0 %

    Shared Address
    Granted 4513.5 139920.0 139920 100.0 %
    Waited 0.0 0.0 0 0.0 %
    ------------------------- ------------ ------------ ---------- ----------
    Total SH-Address Requests 4513.5 139920.0 139920 47.0 %


    Last Page Locks on Heaps
    Total Last Pg Locks 0.0 0.0 0 n/a

    Deadlocks by Lock Type per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Total Deadlocks 0.0 0.0 0 n/a


    Deadlock Detection
    Deadlock Searches 0.0 0.0 0 n/a


    Lock Promotions
    Total Lock Promotions 0.0 0.0 0 n/a

    (return status = 0)

  2. #2
    Join Date
    May 2005
    Location
    Paris
    Posts
    46
    Hi,

    The number of locks parameter sets the total number of available locks for all users on Adaptive Server.

    The total number of locks needed by Adaptive Server depends on the number and nature of the queries that are running. The number of locks required by a query can vary widely, depending on the number of concurrent and parallel processes and the types of actions performed by the transactions. To see how many locks are in use at a particular time, use sp_lock.

    For serial operation, we suggest that you can start with an arbitrary number of 20 locks for each active, concurrent connection.

    Parallel execution requires more locks than serial execution. For example, if you find that queries use an average of five worker processes, try increasing, by one-third, the number of locks configured for serial operation.

    If the system runs out of locks, Adaptive Server displays a server-level error message. If users report lock errors, it typically indicates that you need to increase number of locks; but remember that locks use memory. See "Number of locks" for information.

    Datarows locking may require that you change the value for number of locks. See the Performance and Tuning Guide for more information.


    All processes in Adaptive Server share a pool of lock structures. As a first estimate for configuring the number of locks, multiply the number of concurrent user connections you expect, plus the number of worker processes that you have configured, by 20. The number of locks required by queries can vary widely. See "number of locks" for more information. For information on how worker processes use memory, see "Worker processes".


    Globally a value like 10 000 or 20 000 could be good.

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    just run,

    exec sp_countmetadata "number of locks"

    increase only if the free desriptors is very low.

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The original post mentions 11.5.x - sp_countmetadata won't return any data for "number of locks" in that version. I just checked:

    [22] AN_OLD_SERVER.master.1> select @@version;

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Adaptive Server Enterprise/11.5.1.1/P/HP9000-800/HP-UX 10.0/SWR 9085 ESD 7/opt/Sat Jun 24 00:51:41 PDT 2000

    (1 row affected)
    [23] AN_OLD_SERVER.master.1> sp_countmetadata 'number of locks';
    Msg 18283, Level 16, State 1
    , Procedure 'sp_countmetadata', Line 107
    Configuration parameter 'number of locks' is not supported in this system stored procedure.
    (return status = 1)

    Michael

  5. #5
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    opps!

    countmetadata came in 12.5.x In earlier releases there is a convoluted way to get it from the dbcc's.

    I'm not sure what is available in 11.5.1 for lock exhaustion. In ASE 11.9.2, there is lock escalation mechanism that can set thresholds to prevent lock exhaustion.

    In 1151 you simply have to bump the locks or re-code for smaller transations with more vapoints.

Posting Permissions

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