Post reply

SQL Server Locks

  • January 10, 2008 at 9:21 am

    #69694

    Hello all,

    our SQL server (Navision). keeps locking up and we're ont sure why.

    when it happens, in the current activity log, I can see there are quite a few users with the exact same lock, each of their processes are different.

    I did a quick scan on google for a way to solve this issue. but this is all i could find.

    description:

    Possible transaction management issue.

    · For shared locks, check Isolation level for transaction.

    Soultion:

    · Lock Wait Time (ms)

    Check for memory pressure, which causes more physical I/O, thus prolonging the duration of transactions and locks.

    but i'm not sure what that means..

    i've checked the CPU's and there not being hammered, the memory usage is quite low,

    I'm just not sure whats causing the lock.

    If we leave them for 15 minutes, they clear them selves,

    any ideas, or anything that i can do to resolve it?

    cheers

    Dave

  • January 10, 2008 at 3:52 pm

    #766921

    For the shared locks, you may modify your queries with no_lock or the isolation level.

  • February 4, 2008 at 4:12 am

    #774682

    Yeah, I would if i could.. 🙁

    its not my system, its Navision and I don't have anything to do with the Application side of things.

    I could alter the SP's to see if this resolves the issue.

    I'll experiment and see what happens..

    cheers

    Dave

  • February 5, 2008 at 5:04 am

    #775061

    Hi,

    Navision in versions below at least 4.0 is hard on the sql server... it is not optimized for sql server.

    We are using Navision 3.7 - and blocking is quite usual.

    On our main server ( 200 users, about 140 GB data) we execute a stored procedure which detects blocking, gets the blocking spid, reports on it, sends mails to the user and the admin, and kills the process if the blocking exeeds a specified time.

    In Navision the user is able to set filters on any data on the fly. these filters get then translated into sql without any regard to indexing. that way each user can execute queries on hundreds of thousends rows without using any index at all. this results in a lot of table locking, which blocks usually a lot of users.

    You need to spend a lot of time on performance tuning, in sql server and in the application!

    regards

    karl

    Best regards
    karl

  • March 22, 2008 at 2:31 am

    #792787

    Try deleting ZERO SIFT it should alteast reduce your locking time.

  • March 23, 2008 at 11:51 am

    #792886

    i just want to know about shared lock ,exclusive lock, updatelock,....... of sql .

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply