SQL Server Performance

Question about lock memory

Discussion in 'Performance Tuning for DBAs' started by Chappy, Feb 26, 2003.

  1. Chappy New Member

    This refers to SQL 7.0.

    Having run out of memory for locks once again (im currently inspecting each and every query to avoid this, but im in it for the long haul; theres thousands), I am struggling to gain enough diagnostic info from client tools to help me concentrate my efforts on the worst queries.

    As soon as the lock error occurred, I ran select lock_cnt = count(*), tran_cnt = @@trancount from master..syslocks
    which shows me that 74 locks were active.
    This doesnt seem particularly high, does this refer only to table locks?

    How can I get more information about the sort of lock allocations currently active, and how much memory is currently allocated to locks.
    Ive checked BOL, this site, and several other web sources but I didnt find my answer.
  2. sqljunkie New Member

    There is a 'type' column in the syslocks table that will tell you what type of locks they are. Perhaps you could modify your query to give you a distinct aggregation of the different lock types in the table.
  3. Chappy New Member

    I did look into this, but the type of all locks in syslocks was 8, also the id of each was 0.

    I saved dumps of syslocks and syslockinfo to study, but neither has given me much information it seems.
  4. sqljunkie New Member

    I think type 8 is an Extent lock. Also isn't id actually the ECID or the execution context? Was the spid different for most of the entries?
  5. Chappy New Member

    Yes, most of the locks belonged to different connections, with some connections having 2 of these locks (different databases).

    I havent found much in the way of documentation for syslocks table (even in BOL!), so Im not sure what the id is used for.
  6. Chappy New Member

    Also, I think im fairly safe to say that locks are dynamically configured in sql 7.0?
    Ive read a website stating that sql 7.0 uses the value from sp_configure, but im fairly sure this is not correct, and locks memory is dynamically allocated as needed (up to a certain percentage of total RAM). Someone please correct me if im wrong, thanks!
  7. sqljunkie New Member

    I haven't seen much either...
    Are you able to isolate what queries a few of the spids are running? Perhaps there is a way "tune" some of those queries.
    One suggestion that I heard somewhere was to try locking hints. If you're selecting from a large table it could make sense to use the paglock hint to do page locking instead of row-level locking...
    I don't know if this makes any sense to what you're trying to do, its only an idea.
  8. Chappy New Member

    Unfortunately I was not doing a profile at the time when the server went belly up. The problem I face is that we run several large systems of this server, and most of the database activity is ad hoc queries. Ive converted and improved a lot of these queries over time, but have so far been unable to identify the set of queries which bump up the locks to bursting point (this happens about once every two months). <br /><br />Also I would have expected the system to support way more than 74 locks before running out of memory. Im wondering if the dump i took gives an accurate representation of the locks at the time of the error, or whether the error occurring managed to abort some transaction somewhere which in turn caused a lot of locks to be released (hence only 74).<br /><br />very confused <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  9. Chappy New Member

    I tend to avoid using locking hints if I can, even though it would probably reduce my lock memory problem, it would also reduce concurrency if i was hint at pagelocks. Thanks for the tips though, I think those hints are useful in some situations.
  10. bradmcgehee New Member

    In the book, Inside Microsoft SQL Server 2000, by Kalen Delaney, starting on page 800, is a very good discussion of locks.

    Here's a brief summary.

    --Locks are internal memory structure that reside in memory (never on disk)
    --Each type of lock requires 64 bytes of memory, plus each process holding a lock uses 32 bytes called alock owner block. And, each process waiting for a lock needs 32 bytes.
    --Locks are tracked in a hash table, which is limited in size.
    --If a SQL Server has over 1GB of RAM, then 8MB of memory is assigned to this hash table, and it can track up to 512,000 different locks. If you have less than 1GB of memory, then the hash table is smaller.
    --If locking is set to dynamic (the default), then SQL Server 2500 lock blocks.
    --When there is a need for more locks, but there are any lock blocks allocated, the lock manage will automatically allocate new lock blocks. The lock manager is limited to 60% of the buffer manager's committed target size allocation. So if this number is reached, no more locks can be allocated and you will get an error.
    --You can use Trace Flag 1200 to get more details about locks.

    Based on all of this,the 74 active locks you found must be way underestimated. I don't know why. But if you try the Trace Flag 1200, you may get more detail.

    Also, based on the above, if you can add more RAM to your server, this should help.

    Brad M. McGehee

Share This Page