Help in analyzing deadlock and index recommendation - all definitions included

Question

Help in analyzing deadlock and index recommendation - all definitions included

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
October 11, 2017 at 10:41 am

#339817

Hi,
There is a procedure and a trigger that are involved in deadlock.
The table had no indexes or primary keys. I created a non-clustered index on EmailAddress when the deadlock started to occur.
That index somewhat reduced the deadlock occurrence,
However, we now again have the same issue even with the index.
I have attached the deadlock xml, table ddl, procedure and trigger definition.
Can i get some assistance in looking at why the deadlock happening despite the index?
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Have you looked at the execution plan of the stored proc to see if the index is actually used?
Also, what other queries are executed against this table UO_MList? If they all use EmailAddress as part of the WHERE clause, then maybe it makes more sense to make that the CLUSTERED index instead of NON-CLUSTERED? You need to take all usage into consideration, not just this deadlock.

DesNorton SSC-Insane Points: 24386 More actions · Answer 2

You have 2 updates to the same table, with a trigger firing on each update.

I would try doing a single update
UPDATE [dbo].[UO_MList] SET GeneralSales_HolidaySales_OptIn = ISNULL(@GeneralSales_HolidaySales_OptIn, GeneralSales_HolidaySales_OptIn) , NewProducts_NewVendors_OptIn = ISNULL(@NewProducts_NewVendor_OptIn, NewProducts_NewVendors_OptIn) , SplAnnouncements_OptIn = ISNULL(@SplAnnouncements_OptIn, SplAnnouncements_OptIn) , Equipments_OptIn = ISNULL(@Equipments_OptIn, Equipments_OptIn) , ThirdParty_OptIn = ISNULL(@ThirdParty_OptIn, ThirdParty_OptIn) , ProductAlerts_OptIn = ISNULL(@ProductAlerts_OptIn, ProductAlerts_OptIn) , News_Projects_OptIn = ISNULL(@News_Projects_OptIn, News_Projects_OptIn) , Promotions_Sales_OptIn = ISNULL(@Promotions_Sales_OptIn, Promotions_Sales_OptIn) , LastModified = GETDATE() , Source = ISNULL(@Source, Source) , OptIn = COALESCE(@GeneralSales_HolidaySales_OptIn, GeneralSales_HolidaySales_OptIn, 0) & COALESCE(@NewProducts_NewVendor_OptIn, NewProducts_NewVendors_OptIn, 0) & COALESCE(@SplAnnouncements_OptIn, SplAnnouncements_OptIn, 0) & COALESCE(@Equipments_OptIn, Equipments_OptIn, 0) & COALESCE(@ThirdParty_OptIn, ThirdParty_OptIn, 0) & COALESCE(@News_Projects_OptIn, News_Projects_OptIn, 0) & COALESCE(@Promotions_Sales_OptIn, Promotions_Sales_OptIn, 0) WHERE EmailAddress = @EmailAddress;

How to post data/code on a forum to get the best help.

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 3

DesNorton - Wednesday, October 11, 2017 12:39 PM
You have 2 updates to the same table, with a trigger firing on each update.
I would try doing a single update
UPDATE [dbo].[UO_MList] SET GeneralSales_HolidaySales_OptIn = ISNULL(@GeneralSales_HolidaySales_OptIn, GeneralSales_HolidaySales_OptIn) , NewProducts_NewVendors_OptIn = ISNULL(@NewProducts_NewVendor_OptIn, NewProducts_NewVendors_OptIn) , SplAnnouncements_OptIn = ISNULL(@SplAnnouncements_OptIn, SplAnnouncements_OptIn) , Equipments_OptIn = ISNULL(@Equipments_OptIn, Equipments_OptIn) , ThirdParty_OptIn = ISNULL(@ThirdParty_OptIn, ThirdParty_OptIn) , ProductAlerts_OptIn = ISNULL(@ProductAlerts_OptIn, ProductAlerts_OptIn) , News_Projects_OptIn = ISNULL(@News_Projects_OptIn, News_Projects_OptIn) , Promotions_Sales_OptIn = ISNULL(@Promotions_Sales_OptIn, Promotions_Sales_OptIn) , LastModified = GETDATE() , Source = ISNULL(@Source, Source) , OptIn = COALESCE(@GeneralSales_HolidaySales_OptIn, GeneralSales_HolidaySales_OptIn, 0) & COALESCE(@NewProducts_NewVendor_OptIn, NewProducts_NewVendors_OptIn, 0) & COALESCE(@SplAnnouncements_OptIn, SplAnnouncements_OptIn, 0) & COALESCE(@Equipments_OptIn, Equipments_OptIn, 0) & COALESCE(@ThirdParty_OptIn, ThirdParty_OptIn, 0) & COALESCE(@News_Projects_OptIn, News_Projects_OptIn, 0) & COALESCE(@Promotions_Sales_OptIn, Promotions_Sales_OptIn, 0) WHERE EmailAddress = @EmailAddress;

Thank you for your response.
Updated the procedure to combine 2 updates into one as suggested. The deadlocks are still happening with same data as in deadlock xml attached.

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 4

Chris Harshman - Wednesday, October 11, 2017 11:03 AM
Have you looked at the execution plan of the stored proc to see if the index is actually used?
Also, what other queries are executed against this table UO_MList? If they all use EmailAddress as part of the WHERE clause, then maybe it makes more sense to make that the CLUSTERED index instead of NON-CLUSTERED? You need to take all usage into consideration, not just this deadlock.

Yes, all queries uses EmailAddress in WHERE clause.
I am going to create clustered index and see if that helps.

Should I be setting the fill factor to somewhere around 70 to 80 to avoid page splits?
Note: We have a weekly job to degrag the indexes above 50 percent fragment.

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 5

Interestingly all the deadlocks (around 70+) from past 2 days are showing
ridlock fileid=4 pageid 165303 dbid=6

how to see what data is in the page?
Since this is production box, I am not willing to run DBCC PAGE !!
Is there any other way to find this?

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Santhoshkumar KB - Friday, October 13, 2017 7:38 AM
Since this is production box, I am not willing to run DBCC PAGE !!

I think it's fairly safe to do this in production, but nevertheless I admire your caution! Why not restore the database on a different server and perform your analysis there instead?

John

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 7

John Mitchell-245523 - Friday, October 13, 2017 7:59 AM
Santhoshkumar KB - Friday, October 13, 2017 7:38 AM
Since this is production box, I am not willing to run DBCC PAGE !!
I think it's fairly safe to do this in production, but nevertheless I admire your caution! Why not restore the database on a different server and perform your analysis there instead?
John

Thank you for the suggestion, I am restoring on another server.

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Santhoshkumar KB - Friday, October 13, 2017 7:38 AM
Interestingly all the deadlocks (around 70+) from past 2 days are showing
ridlock fileid=4 pageid 165303 dbid=6
how to see what data is in the page?
Since this is production box, I am not willing to run DBCC PAGE !!
Is there any other way to find this?

When's the last time you ran DBCC CHECKDB?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)