Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Question Unanswered: SQL Server DeadLock problem

    Please help me solve deadlock in SQL server!!
    the scenario:
    We have a database server running SQL server 2000 Win Server 2003 which manages couple of huge databases and hundreds of simultaneously connected clients to them. Number of transactions done by multiple large size applications on these databases are enormous and we did not have much problems for a long period with partially same data and transaction volume.
    Recently, server has encountered lots of blockings in its processes and finally deadlocks which made the server halt… the number of deadlocks is increasing day after day and server halts are becoming a serious headache: before we had once a month, now we have twice a day.
    We have set multiple trace flags on server to generate and capture logs, used profiler and installed advanced SQL analysis monitoring tools. Finally, we found out that deadlocks are mostly caused by similar simultaneous queries blocking each other’s in table access, but the queries and the tables are different for each deadlock and each query may come from a different application or client. Following is a typical blocking session that caused deadlock and server halt:
    - Blocking Process A ran at 9:36:11, Waiting time(ms): 469313, SQL Query: UPDATE TableX
    - Blocked Processes B ran at 1: 9:36:12, Waiting time (ms): 436094, SQL Query: INSERT INTO TableX
    The SELECT statements are locked and unfortunately we cannot change them by adding 'nolock' access modifier since they are all called from third party applications. The waiting times for most of blocked processes are above 400,000 mille seconds (more than 6 minutes!). Our servers are high performance, wealthy in CPU and Memory resources and the SQL has a huge memory allocated to itself that never reached to its limit! All we can do is to set changes directly on database, SQL Server settings, network and hardware.
    So far, we have come up with the following solutions (which are not the ultimate cure) and we still did not apply them since the database is huge in size and design detail and we better get sure before any action:
    1- reduce the LOCK_TIMEOUT in db that applications do not wait a long time
    2- Index all fields in blocking tables to shorten query times

    If anybody can suggest an avenue of search or an alternative to prevent or minimize this error. Thanks...
    A.Oveissian

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by amir1
    database server running SQL server 2000 Win Server 2003 which manages couple of huge databases
    Quote Originally Posted by amir1
    wealthy in CPU and Memory resources and the SQL has a huge memory allocated to itself that never reached to its limit!
    On the surface, these two statements don't really agree. Can you say what the amount of physical memory on the server is, and what the amount used by SQL Server is? Also, what is the amount of free physical memory available? It would also be good to know what the Page Life Expectancy for the instance is (this is a perfmon counter under Buffer manager, if I recall).

    It almost sounds like you may have a machine with say 8GB of physical memory, but have only installed a 32 bit instance of SQL Server, which can only take up to about 2 GB of that memory.

    On another front, adding indexes to every column may have actually hurt your situation, as any update/insert/delete operation will now not only have to make their changes to the table data, but maintain all of the additional indexes (including adding/removing rows and re-ordering the new entries).

  3. #3
    Join Date
    Sep 2010
    Posts
    4

    Post

    thank you for feedback,

    Actually there is a huge amount of free available memory exists for both os and SQL server. For OS about 2GB, for SQLserver more than 30GB allocated and lots of it free left.

    Resource statistic is as following:
    Page life: 300 to 1200 secs
    Number of pages: 2600
    Paging per sec.: 1 to 6
    CPU usage: mostly 0 to 20% sometimes 100 %
    Disk busy: 2%
    Server CPU is XENON 32 core

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It does not look like a memory bottleneck, then.

    I would look at removing any index that can not be proven to help in some fashion. As mentioned above, they simply add drag on the system, and furnish more objects that need to be locked, and potential deadlock points.

  5. #5
    Join Date
    Sep 2010
    Posts
    15
    - Blocking Process A ran at 9:36:11, Waiting time(ms): 469313, SQL Query: UPDATE TableX
    - Blocked Processes B ran at 1: 9:36:12, Waiting time (ms): 436094, SQL Query: INSERT INTO TableX
    The SELECT statements
    are locked and unfortunately we cannot change them by adding 'nolock' access modifier since they are all called from third party applications. The waiting times for most of blocked processes are above 400,000 mille seconds (more
    Could you please post the query/storedProcedure/Select statements up?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First and foremost, the 32 bit implementation of SQL Server 2000 can't effectively use that much memory. Anything unmanaged over 8Gb is a handicap, and managed or not anything over 12 Gb will degrade performance.

    SQL 2000 is no longer supported, so I wouldn't invest any significant time or effort into trying to upgrade it "as is" but would move toward current generation hardware and software. I would recomment Windows Server 2008 R2 64 bit, and SQL 2008 R2 64 bit.

    No two apps or installations are identical in performance, but I'd be shocked if you needed to do anything more than this upgrade to make the problem a non-issue, as well as increase performance of everything you do on that server immensely. Just an observation, but I would set the SQL Server memory governer to leave at least 2 Gb for the Operating System. Some poorly written applications can cause SQL to use memory very agressively, so I'd leave that limit in place "just in case" some query or queries might run amok.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2010
    Posts
    4
    Quote Originally Posted by namman View Post
    Could you please post the query/storedProcedure/Select statements up?

    There are many queris blocking each other and cause deadlock. All are simple as following:

    UPDATE EMPS01 SET fileds

    SELECT
    GETDATE() AS LastUpdate,
    NUM,
    CNAME, ...
    FROM
    EMPS01

    But the above statements work independently. The issue is when they are runing together.

  8. #8
    Join Date
    Sep 2010
    Posts
    15
    Code:
    So far, we have come up with the following solutions (which are not the ultimate cure) and we still did not apply them since the database is huge in size and design detail and we better get sure before any action:
    1- reduce the LOCK_TIMEOUT in db that applications do not wait a long time
    2- Index all fields in blocking tables to shorten query times
    As you said that is like tylenol, just relieving pain, but not cure the wound.

    Deadlock is about processes blocking each other. So the real solution is to find out which processes involve "blocking each other", then fix them. The main question is : when the 2 processes block each other?

    Code:
    There are many queris blocking each other and cause deadlock. All are simple as following:
    
    UPDATE EMPS01 SET fileds
    
    SELECT
    GETDATE() AS LastUpdate,
    NUM,
    CNAME, ...
    FROM
    EMPS01
    
    But the above statements work independently. The issue is when they are runing together.
    Suppose we have 2 processes running concurrently, "runing together".

    CASE 1:
    P1
    UPDATE EMPS01 SET fileds = 'something'
    P2
    SELECT GETDATE() AS LastUpdate, NUM, CNAME, ... FROM EMPS01

    CASE 2:
    P1
    begin tran
    UPDATE EMPS01 SET fileds = 'something'
    SELECT GETDATE() AS LastUpdate, NUM, CNAME, ... FROM EMPS01
    commit tran
    P2
    begin tran
    UPDATE EMPS01 SET fileds = 'something'
    SELECT GETDATE() AS LastUpdate, NUM, CNAME, ... FROM EMPS01
    commit tran

    With that structure, P1 and P2 will never block each other in either case.

    In your application, P1 or P2 may be the deadlock victim but they are victim of other processes, not P1 or P2. I ask you to post some main queries/storedprocedure/transactino ... to see the structure of the process which may involve the deadlock. Just try to help.

    I know it is impossible to post ALL queries on a forum. I just add a little suggest, hope you solve the problem.

  9. #9
    Join Date
    Sep 2010
    Posts
    4

    Thanks

    Thanks for Help,

    Yes, we have too many scheduled processes runing hourly and daily on our server. Finally we ended up with finding time-costy scheduled processes and deactivated them. However, I still do not have any other solution in mind to prevent this problem before occuring, but monitoring the server all the time. Hard to do... Please let me know if you still have other suggestions to predict deadlocks in a proactive resolution.

    Thanks Again

    Quote Originally Posted by namman View Post
    Code:
    So far, we have come up with the following solutions (which are not the ultimate cure) and we still did not apply them since the database is huge in size and design detail and we better get sure before any action:
    1- reduce the LOCK_TIMEOUT in db that applications do not wait a long time
    2- Index all fields in blocking tables to shorten query times
    As you said that is like tylenol, just relieving pain, but not cure the wound.

    Deadlock is about processes blocking each other. So the real solution is to find out which processes involve "blocking each other", then fix them. The main question is : when the 2 processes block each other?

    Code:
    There are many queris blocking each other and cause deadlock. All are simple as following:
    
    UPDATE EMPS01 SET fileds
    
    SELECT
    GETDATE() AS LastUpdate,
    NUM,
    CNAME, ...
    FROM
    EMPS01
    
    But the above statements work independently. The issue is when they are runing together.
    Suppose we have 2 processes running concurrently, "runing together".

    CASE 1:
    P1
    UPDATE EMPS01 SET fileds = 'something'
    P2
    SELECT GETDATE() AS LastUpdate, NUM, CNAME, ... FROM EMPS01

    CASE 2:
    P1
    begin tran
    UPDATE EMPS01 SET fileds = 'something'
    SELECT GETDATE() AS LastUpdate, NUM, CNAME, ... FROM EMPS01
    commit tran
    P2
    begin tran
    UPDATE EMPS01 SET fileds = 'something'
    SELECT GETDATE() AS LastUpdate, NUM, CNAME, ... FROM EMPS01
    commit tran

    With that structure, P1 and P2 will never block each other in either case.

    In your application, P1 or P2 may be the deadlock victim but they are victim of other processes, not P1 or P2. I ask you to post some main queries/storedprocedure/transactino ... to see the structure of the process which may involve the deadlock. Just try to help.

    I know it is impossible to post ALL queries on a forum. I just add a little suggest, hope you solve the problem.

Tags for this Thread

Posting Permissions

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