Is it bad to disable Page locking for a table?

  • It probably will be the classic answer, It depends... 🙂

    The thing is that, unfortunately, there are so many deadlocks ongoing and when i have captured the deadlock graph when it happens. Looking at most, the resource are pointing to page locks on two of the big tables in the DB. Is the table below, the right way to disable page locks? Would there be negative effects?

    SP_INDEXOPTION 'tblName', 'AllowPageLocks', FALSE

    THANKS a lot!

  • If you do it for the wrong reasons, yes it is bad.

    Disable page locks and you may just get table locks instead. It may fix the deadlocks, but the concurrency will not improve.

    The vast majority of deadlocks are due to poor indexing, poorly written code or both. I would suggest you look for and fix the root cause and not just try and hide the symptoms.

    Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • fortunately (or unfortunately), i already have that flag turned on, and already have the deadlock graph ready to be posted here. Here goes (one of the many deadlocks...:( )

    <deadlock-list>

    <deadlock victim="process84ad48">

    <process-list>

    <process id="process84ad48" taskpriority="0" logused="0" waitresource="PAGE: 20:1:24395174" waittime="843" ownerId="13258910998" transactionname="SELECT" lasttranstarted="2010-11-16T16:31:24.080" XDES="0x3152ae00" lockMode="S" schedulerid="1" kpid="8808" status="suspended" spid="59" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2010-11-16T16:31:23.750" lastbatchcompleted="2010-11-16T16:31:23.750" clientapp=".Net SqlClient Data Provider" hostname="APSVC-01" hostpid="2800" loginname="DOMAIN\DomainUser" isolationlevel="read committed (2)" xactid="13258910998" currentdb="20" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128057">

    <executionStack>

    <frame procname="adhoc" line="1" stmtstart="92" sqlhandle="0x02000000366850121335a25ee37df179849055e29bf971d9">

    SELECT [t10].[TransactionID] AS [ID], [t10].[Indicator] AS [RecordIndicator], [t10].[ReferenceNumber] AS [Reference], [t10].[TrackingNumber], [t10].[Name1], [t10].[Name2], [t10].[Address1], [t10].[Address2], [t10].[Address3], [t10].[City], [t10].[State], [t10].[Zip], [t10].[UserFields], [t10].[value] AS [RecordIndicatorGroupID3], [t10].[value2] AS [FieldSetID], [t10].[value3] AS [ImportBatchID2], [t10].[value4] AS [PrintBatchID2], [t11].[SubLetterID] AS [ID2], [t12].[SubLetterIndicator] AS [RecordIndicator2], COALESCE([t12].[RecordIndicatorGroupID],0) AS [RecordIndicatorGroupID4], [t11].[UserFields] AS [UserFields2], COALESCE([t11].[Order],0) AS [Order], (

    SELECT COUNT(*)

    FROM [dbo].[tblSubLetter] AS [t13]

    LEFT OUTER JOIN [dbo].[tblSubLetterIndicator] AS [t14] ON [t14].[SubLetterIndicatorID] = [t13].[SubLetterIndicatorID]

    WHERE [t13].[TransactionID] = [t10].[TransactionID]

    ) AS [value], [t10].[IMBDest], [t10].[IMBReturn], [t10].[Verbiage] AS [Indicia], [t10].[ServiceID], [t10].[Report </frame>

    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">

    unknown </frame>

    </executionStack>

    <inputbuf>

    (@p0 datetime,@p1 int,@p2 int,@p3 int,@p4 int)SELECT [t10].[TransactionID] AS [ID], [t10].[Indicator] AS [RecordIndicator], [t10].[ReferenceNumber] AS [Reference], [t10].[TrackingNumber], [t10].[Name1], [t10].[Name2], [t10].[Address1], [t10].[Address2], [t10].[Address3], [t10].[City], [t10].[State], [t10].[Zip], [t10].[UserFields], [t10].[value] AS [RecordIndicatorGroupID3], [t10].[value2] AS [FieldSetID], [t10].[value3] AS [ImportBatchID2], [t10].[value4] AS [PrintBatchID2], [t11].[SubLetterID] AS [ID2], [t12].[SubLetterIndicator] AS [RecordIndicator2], COALESCE([t12].[RecordIndicatorGroupID],0) AS [RecordIndicatorGroupID4], [t11].[UserFields] AS [UserFields2], COALESCE([t11].[Order],0) AS [Order], (

    SELECT COUNT(*)

    FROM [dbo].[tblSubLetter] AS [t13]

    LEFT OUTER JOIN [dbo].[tblSubLetterIndicator] AS [t14] ON [t14].[SubLetterIndicatorID] = [t13].[SubLetterIndicatorID]

    WHERE [t13].[TransactionID] = [t10].[TransactionID]

    ) AS [value], [t10].[IMBDest], [t10].[IMBReturn], [t10].[Verbiage] </inputbuf>

    </process>

    <process id="process3317978" taskpriority="0" logused="1691524" waitresource="PAGE: 20:1:24675800" waittime="531" ownerId="13258805509" transactionname="user_transaction" lasttranstarted="2010-11-16T16:31:18.407" XDES="0x4e1cc250" lockMode="IX" schedulerid="15" kpid="7532" status="suspended" spid="248" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-11-16T16:31:18.390" lastbatchcompleted="2010-11-16T16:31:18.390" clientapp=".Net SqlClient Data Provider" hostname="PRODTS-01" hostpid="0" loginname="SQLUser" isolationlevel="read uncommitted (1)" xactid="13258805509" currentdb="20" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128057">

    <executionStack>

    <frame procname="CertifiedPro.dbo.trg_tblTransaction" line="81" stmtstart="6578" stmtend="11840" sqlhandle="0x03001400bb680a7aaa922001d79d00000000000000000000">

    UPDATE tblActiveTransaction SET

    ImportBatchID = i.ImportBatchID,

    RecordIndicatorID = i.RecordIndicatorID,

    PrintBatchID = i.PrintBatchID,

    MailBatchID = i.MailBatchID,

    GroupDate = i.GroupDate,

    CommitmentDate = i.CommitmentDate,

    RequestedPrintDate = i.RequestedPrintDate,

    PageCount = i.PageCount,

    Weight = t.Weight,

    TrayID = i.TrayID,

    RecipientID = i.RecipientID,

    AutomatedTransaction = i.AutomatedTransaction

    FROM tblTransaction t

    JOIN tblActiveTransaction at on t.TransactionID = at.TransactionID

    JOIN inserted i on at.transactionid = i.transactionid

    JOIN deleted d on i.transactionid = d.transactionid

    WHERE

    i.ImportBatchID != d.ImportBatchID OR

    (i.ImportBatchID is null AND d.ImportBatchID is not null) OR

    (i.ImportBatchID is not null AND d.ImportBatchID is null) OR

    i.RecordIndicatorID != d.RecordIndicatorID OR

    (i.RecordIndicatorID is null AND d.RecordIndicatorID is not null) OR

    (i.RecordIndicatorID is not null AND d.RecordIndicatorID is null) </frame>

    <frame procname="CertifiedPro.dbo.odi_usp_CreatePrintBatch_v2" line="205" stmtstart="17852" stmtend="18872" sqlhandle="0x03001400b04b7701b57c18002a9e00000100000000000000">

    update tbltransaction set

    printbatchid = @PrintBatchID,

    trackingnumber = dbo.fn_BuildTrackingNumber(@TrackingNumberType, @USPSCode, @Duns, t.seq + @StartCounter, t.transactionid),

    alternatetrackingnumber = case when @DunsID = 3 then cast(t.seq + @StartCounter as varchar(50)) else null end

    from tbltransaction

    join @t t on tblTransaction.transactionid = t.transactionid

    --------------------------------------------------------------------------------------------------------------------- </frame>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 20 Object Id = 24595376] </inputbuf>

    </process>

    </process-list>

    <resource-list>

    <pagelock fileid="1" pageid="24675800" dbid="20" objectname="DBName.dbo.tblActiveTransaction" id="lock1a99ce40" mode="SIU" associatedObjectId="72057596838281216">

    <owner-list>

    <owner id="process84ad48" mode="S" />

    </owner-list>

    <waiter-list>

    <waiter id="process3317978" mode="IX" requestType="convert" />

    </waiter-list>

    </pagelock>

    <pagelock fileid="1" pageid="24395174" dbid="20" objectname="DBName.dbo.tblActiveTransaction" id="lock4292fa80" mode="IX" associatedObjectId="72057596838281216">

    <owner-list>

    <owner id="process3317978" mode="IX" />

    </owner-list>

    <waiter-list>

    <waiter id="process84ad48" mode="S" requestType="wait" />

    </waiter-list>

    </pagelock>

    </resource-list>

    </deadlock>

    </deadlock-list>

  • You have a 100 or so line long trigger on that table. That's part of the cause of the deadlock.

    Is that trigger necessary?

    Is it written as optimally as possible? Remember that a trigger all runs within the same transaction as the statement that fired it.

    There's a whole lot of ORs in that update. Are your indexes optimal for that? It's different from indexing for AND predicates. My guess is that update is table scanning.

    You have a udf in an update. Does it access data? If so, it's likely non-optimal. Consider rewriting.

    You have a join to a table variable. Is the lack of statistics causing a sub-optimal plan for the update (and hence making it run longer than necessary)?

    In short, there's a lot you need to look at and fix before considering something like lock levels. If you don't know where to start, or it's urgent, get a specialist in to help out.

    p.s. That did not look like the output of the traceflag, it looks like profiler's deadlock graph event. The traceflag (which writes into the error log) doesn't have < > around the elements.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail,

    Is the trigger necessary? Unfortunately, the answer is yes. And it's even cascading... (Cringes) 🙁

    Currently, that's one of the goals I am hoping to accomplish, being able to eliminate the deadlocks (I don't think the business sees it as urgent as they had been dealing with it prior to me being here, but its something that I am hoping to resolve). The company (prior to me), have dealt with more deadlocks before (and put read uncommitted in a lot of stuff - uhhh, i see and hear a lot more cringing).

    As for the deadlock graph (You're right, it was not from the error log, although it's also there). I have created a job that logs any deadlock event (and sends out an email) everytime it happens. The deadlock XML graph is also saved on a table so it can easily be queried on. Easier than getting it from the Error log especially with the amount of deadlocks we get).

    So far, i have disabled the pagelock, but monitoring for any possible escalation to a table level lock. Has been good so far, at least giving sometime to troubleshoot the main issue (which as you have already pointed out, the trigger, the query, the temporary table, and probably a lot more).

    As for a specialist, what type of specialist would you recommend? Database architect? Or a Database developer?

    Thank you so much again!

  • SqlN00bie (11/17/2010)


    As for a specialist, what type of specialist would you recommend? Database architect? Or a Database developer?

    Right now I would suggest a performance tuning specialist. Someone who can help you fix this and all the other problems that likely exist.

    Whereabouts in the world are you?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Temecula, California is where I'm at.

    I'll send a message to 'TheSQLGuru' per your high recommendation.

    Thanks a lot!

  • SqlN00bie (11/17/2010)


    Temecula, California is where I'm at.

    I'll send a message to 'TheSQLGuru' per your high recommendation.

    Thanks a lot!

    I am not a performance specialist - and Kevin is probably one of your better options. However, I am local as I live in Murrieta just up the street. Work in Anaheim 3 days a week (yeah, the commute $#!@#...) - and from home the other 2 days.

    Good luck...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey,

    I live in Lake Arrowhead and have to commute to Gardena 3 times a week. I can understand your comments on the commute.

    Todd Fifield

Viewing 9 posts - 1 through 9 (of 9 total)

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