SQL Server Performance

Writelog and deadlock

Discussion in 'General DBA Questions' started by california6, Jul 19, 2008.

  1. california6 New Member

    My SQL Server performance was very poor and when enable deadlock trace, i see the following in the errorlog file:
    2008-07-19 00:00:10.99 spid1 Node:1
    2008-07-19 00:00:10.99 spid1
    2008-07-19 00:00:10.99 spid1 Wait-for graph
    2008-07-19 00:00:10.99 spid1
    2008-07-19 00:00:10.99 spid1 ...
    2008-07-19 00:00:11.01 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:160 ECID:0 Ec:(0x6F5DF530) Value:0x79
    2008-07-19 00:00:11.01 spid1 Victim Resource Owner:
    2008-07-19 00:00:11.01 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:160 ECID:0 Ec:(0x6F5DF530) Value:0x79
    2008-07-19 00:00:11.01 spid1 Requested By:
    2008-07-19 00:00:11.01 spid1 Input Buf: Language Event: SELECT COUNT(*) FROM WF_QUEUE WHERE PROCESSID = 2 an
    2008-07-19 00:00:11.01 spid1 SPID: 159 ECID: 0 Statement Type: SELECT Line #: 1
    2008-07-19 00:00:11.01 spid1 Owner:0x3a0e0c60 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:159 ECID:0
    2008-07-19 00:00:11.01 spid1 Grant List 0::
    2008-07-19 00:00:11.01 spid1 KEY: 11:459148681:3 (cc01513261ee) CleanCnt:2 Mode: X Flags: 0x0
    2008-07-19 00:00:11.01 spid1 Node:2
    2008-07-19 00:00:11.01 spid1
    2008-07-19 00:00:11.01 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:159 ECID:0 Ec:(0x6F87F530) Value:0x7e
    2008-07-19 00:00:11.01 spid1 Requested By:
    2008-07-19 00:00:11.01 spid1 Input Buf: Language Event: SELECT COUNT(*) FROM WF_QUEUE WHERE PROCESSID = 2 an
    2008-07-19 00:00:11.01 spid1 SPID: 160 ECID: 0 Statement Type: SELECT Line #: 1
    2008-07-19 00:00:11.01 spid1 Owner:0x7974e500 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:160 ECID:0
    2008-07-19 00:00:11.01 spid1 Grant List 1::
    2008-07-19 00:00:11.01 spid1 KEY: 11:459148681:3 (d0015f16d5a0) CleanCnt:2 Mode: X Flags: 0x0
    2008-07-19 00:00:17.98 backup Log backed up: Database: FlexTrack, creation date(time): 2005/03/10(12:20:18),
    2008-07-19 00:00:27.84 backup Log backed up: Database: Context, creation date(time): 2004/05/21(10:11:32), fi
    2008-07-19 00:00:27.99 backup Log backed up: Database: eCaptureASIA, creation date(time): 2004/07/21(09:44:46
    2008-07-19 00:00:28.31 backup Log backed up: Database: OptikaAuditASIA, creation date(time): 2008/07/18(14:59
    2008-07-19 00:00:48.57 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:160 ECID:0 Ec:(0x6F5DF530) Value:0x79
    2008-07-19 00:00:48.57 spid1 Victim Resource Owner:
    2008-07-19 00:00:48.57 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:160 ECID:0 Ec:(0x6F5DF530) Value:0x79
    2008-07-19 00:00:48.57 spid1 Requested By:
    2008-07-19 00:00:48.57 spid1 Input Buf: Language Event: SELECT COUNT(*) FROM WF_QUEUE WHERE PROCESSID = 2 an
    2008-07-19 00:00:48.57 spid1 SPID: 159 ECID: 0 Statement Type: SELECT Line #: 1
    2008-07-19 00:00:48.57 spid1 Owner:0x1c906400 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:159 ECID:0
    2008-07-19 00:00:48.57 spid1 Grant List 0::
    2008-07-19 00:00:48.57 spid1 KEY: 11:459148681:3 (7f01a4bd437f) CleanCnt:2 Mode: X Flags: 0x0
    2008-07-19 00:00:48.57 spid1 Node:2
    2008-07-19 00:00:48.57 spid1
    2008-07-19 00:00:48.57 spid1 ResType:LockOwner Stype:'OR' Mode: S SPID:159 ECID:0 Ec:(0x6F87F530) Value:0x1c
    2008-07-19 00:00:48.57 spid1 Requested By:
    2008-07-19 00:00:48.57 spid1 Input Buf: Language Event: SELECT COUNT(*) FROM WF_QUEUE WHERE PROCESSID = 2 an
    2008-07-19 00:00:48.57 spid1 SPID: 160 ECID: 0 Statement Type: SELECT Line #: 1
    2008-07-19 00:00:48.57 spid1 Owner:0x7974ee00 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:160 ECID:0
    2008-07-19 00:00:48.57 spid1 Grant List 1::
    2008-07-19 00:00:48.57 spid1 KEY: 11:459148681:3 (7a010025ba6e) CleanCnt:2 Mode: X Flags: 0x0
    2008-07-19 00:00:48.57 spid1 Node:1
    2008-07-19 00:00:48.57 spid1
    2008-07-19 00:00:48.57 spid1 Wait-for graph
    2008-07-19 00:00:48.57 spid1
    Could somebody please tell as to whats going on? I see the trace keep growing rapidly with the same error.
    california6, Jul 19, 2008
    #2

  2. Mahmoud_H New Member

    two process acces wf_queue in the same time
    proc 160: SELECT COUNT(*) FROM WF_QUEUE WHERE PROCESSID = 2 an....
    proc 159: SELECT COUNT(*) FROM WF_QUEUE WHERE PROCESSID = 2 an.....
    try to modify code to avoid using count(*) in the same time
    Mahmoud_H, Jul 20, 2008
    #3

  3. MohammedU New Member

    If are worried about the result count(*) try using the isolation level READ UNCOMMITTED...by default SQL is uses READ COMMITTED isolation...
    And make your query is using the index if there is one if not create one to improve perfomance...
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT COUNT(*) FROM WF_QUEUE WHERE PROCESSID = 2 an.....
    OR
    SELECT COUNT(*) FROM WF_QUEUE (NOLOCK) WHERE PROCESSID = 2 an.....
    MohammedU, Jul 20, 2008
    #4

  4. california6 New Member

    Thanks a much for the contribution. Is there any other way to avoid this deadlock happening? The reason is - this is a propriety application (Oracle) and we do not have the leverage to modify the code with Oracle's intervention.
    Any suggestion?
    california6, Jul 20, 2008
    #5

  5. california6 New Member

    When i run select * from sysprocesses - i see the following for atleast 10 connections:
    LCK_M_SCH_S TAB: 11:459148681
    When checked the above object - its WF_QUEUE. what can i do to avoid table locking? also i see quiet few WRITELOG in the lastwaittype.
    any suggestions what can be done here?
    california6, Jul 20, 2008
    #6

Share This Page