I found that sys.dm_exec_requests has a Schedule_ID field in it.  You can join dm_io_pending_io_requests to it on that field.  Join to Exec_Requests and exec_sql_text and you can get what you...

TheSQLGuru (1/3/2017)

---

1) You have extra BEGIN TRAN's in the sproc. You should just have one at the very top and the very bottom.

2) You should have some explicit error checking...

I have attached the proposed changes to the table and to the InsertUpdate proc. Now that the Devs are in, I am hoping to confirm today that the TrackingNumber...

Overnight I do still see some deadlocks happening on the INSERT to UPSTRacking and on the IF NOT EXISTS(SELECT * FROM UPSTracking WHERE TrackingNumber = @TrackingNumber).

I am working on...

TheSQLGuru (12/31/2016)

---

Jeff Moden (12/30/2016)

---

TheSQLGuru (12/30/2016)

---

Jeff Moden (12/30/2016)

---

TheSQLGuru (12/30/2016)

---

Jeff: "Another potentially very serious problem with all of this is the idea that a lot of the rows will suffer a lot...

Sergiy (12/30/2016)

---

Then you need to fix the code.

Look what people told you:

Jeffrey Williams 3188 (12/29/2016)

---

You need to fix this issue:

...

Jeff Moden (12/30/2016)

---

What you included in the original post was a picture of the deadlock graph, not the text, which would have shown the actual code involved.

But, from...

Jeff Moden (12/30/2016)

---

TheSQLGuru (12/30/2016)

---

Jeff Moden (12/30/2016)

---

TheSQLGuru (12/30/2016)

---

Jeff: "Another potentially very serious problem with all of this is the idea that a lot of the rows will suffer a lot of...

TheSQLGuru (12/30/2016)

---

lmarkum (12/30/2016)

---

Sergiy (12/30/2016)

---

lmarkum (12/23/2016)

---

I also dropped the non-clustered index IX_UPSTracking on just TrackingNumber since that seemed redundant to the new clustered PK and another existing index...

Sergiy (12/30/2016)

---

lmarkum (12/23/2016)

---

I also dropped the non-clustered index IX_UPSTracking on just TrackingNumber since that seemed redundant to the new clustered PK and another existing index IX_UPSTracking on...

Jeff Moden (12/30/2016)

---

lmarkum (12/30/2016)

---

You should be able to easily figure that out on your own because you have an IDENTITY column....