Is it bad to disable Page locking for a table?

Question

Is it bad to disable Page locking for a table?

SQLSalas

Ten Centuries

Points: 1327
More actions
November 16, 2010 at 7:07 pm

#226762

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!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

SQLSalas Ten Centuries Points: 1327 More actions · Answer 2

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>

<process-list>

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>

unknown </frame>

</executionStack>

(@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>

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>

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>

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

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</pagelock>

</resource-list>

</deadlock>

</deadlock-list>

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

SQLSalas Ten Centuries Points: 1327 More actions · Answer 4

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

SQLSalas Ten Centuries Points: 1327 More actions · Answer 6

Temecula, California is where I'm at.

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

Thanks a lot!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

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

tfifield SSCrazy Eights Points: 9655 More actions · Answer 8

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