Deadlock Analysis Results from trace flag 1222

Question

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

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

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 1

I think there is a problem in usp_CALLMAN_UPDATE_ADVISERS first look at this query execution plan and if it need any index. And

it is a query like this

Maybe you can use with(nolock) hint in this select and then update and check index fragmentation

AND ASD.[Status] IN (2<c/>4);

03/26/2013 12:20:01,spid23s,Unknown,AND ASD.DateTo > @CurrentDate

03/26/2013 12:20:01,spid23s,Unknown,WHEREASD.DateFrom < @CurrentDate

03/26/2013 12:20:01,spid23s,Unknown,ASD.AdviserID = ADV.AdviserID

03/26/2013 12:20:01,spid23s,Unknown,INNER JOINCallManager.dbo.CallMan_AdviserStatusDatesASDON

03/26/2013 12:20:01,spid23s,Unknown,FROMCallManager.dbo.AdvisersAvailabilityADV

03/26/2013 12:20:01,spid23s,Unknown,SETADV.[Status] = ASD.[Status]

03/26/2013 12:20:01,spid23s,Unknown,UPDATEADV

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 2

Thanks very much for your input.

I have taken a look at that sp and notice that the isolation level is set to Serializable ! I also notcied this in the error log provided.

Would it be advisable to change it to Read Comiited ?

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 3

I can t say any think about that what is your app is need?

PearlJammer1 SSCrazy Eights Points: 8477 More actions · Answer 4

I dont think the updates that the SP handles are critical - I will need to speak with the developers about this i think. Thanks for your help.:-)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Note there are 2 additional parts to this blog series.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service