deadlock issue post migrations

Sam 1,476

Hi All,

We are seeing this deadlock quite often after migrating from sql 2012 EE to sql 2017 RTM CU22.
Deadlock is happening on the same table on 2 different indexes. One spid is holding an 'X' lock and other spid is holding 'U' lock and both are requesting opposite locks.
Looking for some suggestions on fixin41788-deadlockgraph1.xmlg this deadlock. Will the index rebuild fix the deadlock?

Attaching the deadlock.xml file.

Regards,
Sam

Dirk Hondong 871 Reputation points

2020-11-23T11:32:37.833+00:00

Another thing which comes into my mind (besides my answer):
did you change the compatibility level after moving from 2012 to 2017?
If yes, maybe some of your queries are now not that fast anymore.
Query regression can happen after such a change. So maybe one task is a bit slower than before and now the DLs occur.
Sam 1,476 Reputation points

2020-11-23T13:03:33.85+00:00

Hi Dirk, Yes, we did changed the compatibility mode to 140.
If we change the compatability mode back to 110 (i.e. sql 2012) will it work?
Sam 1,476 Reputation points

2020-11-23T13:06:23.353+00:00

In short, what is Query regression? u mean does sql generates a totally new exection plans ?
Shashank Singh 6,251 Reputation points

2020-11-23T13:17:50.823+00:00
Yes that is one way to say it. And the plan generated is sub optimal leading to choosing of bad query plan and this slowing the query. IF that is really the cause and IF this is the only query creating problem I would suggest to use QUERY HINT to force optimizer to use old CE. Example

SELECT CustomerId, OrderAddedDate FROM OrderTable WHERE OrderAddedDate >= '2016-05-01' OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

The query hint is OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Let me know how it goes
Dirk Hondong 871 Reputation points

2020-11-23T13:34:09.08+00:00

I think there are quite a few articlces out there which explain a little bit.

Normally you would do the following:
Stay with the DB in the old compatibility level.

Then, with a recent SSMS (e.g. 18.6 and higher) you can right click on your database and choose the last point "Database upgrade".

It will enable and utilize query store.
So, you run with the old compat level and gather some query information.
After one or two weeks you switch to the actual compat level and let query store gather the information for another week or two.
Then you go back to the database upgrade wizard and you'll get a bunch of information where queries improved or where queries may regressed.

Look here:
https://learn.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-ver15
Sam 1,476 Reputation points

2020-11-23T13:43:07.983+00:00

Okay Dirk. Will try this.
Cris Zhan-MSFT 6,631 Reputation points

2020-11-30T01:25:23.43+00:00

Hi @Sam ,
Is there any update on this case?

1 answer

Dirk Hondong 871 Reputation points

2020-11-23T11:28:42.177+00:00

Hi Sam,

I don't think that an index rebuild will solve your problem permanently, e.g. it can occur again.
The thing is: what processes are accessing the table, e.g. is it from an application, a SSIS package?
Solving DL issues is often a combination of app design and database access / design.

I have a similiar case with a deadlock during a ssis package run.
Long story short: the data flow needs to be adjusted a bit because there's a multicast involved which is the "troublemaker".
Please sign in to rate this answer.

1 person found this answer helpful.
Sam 1,476 Reputation points

2020-11-23T13:05:14.703+00:00

Here in my case, it is not an SSIS package, it's a java application.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

deadlock issue post migrations

1 answer

Your answer