ioani (10/29/2012)
I have a table Tab1 with a column that is a foreign key to the primary key of table Tab2.I have a stored procedure that selects some data from a table Tab 3 and insert into Tab1:
INSERT INTO Tab1 (Col1, Col2,.....)
SELECT Col1, Col2......
FROM Tab3
WHERE .....
This query causes deadlocks in application. The deadlocks are on the primary key of table Tab2.
Looking inside the query plan I see that an index scan is made on primary key of Tab2 (although Tab2 does not take part in the query!).
How can I remove the index scan on the primary key of table Tab2 from the query plan?
Thanks,
ioani
Each time you insert a row into Tab1, SQL Server checks Tab2 to ensure that the value you are adding to the FK column actually exists there - that's what DRI is for.
Is it a clustered index scan you see in the plan? Do you get an index scan if you add a single row to table Tab1? If so, it sounds like you don't have an appropriate index on the PK of Tab2. A clustered index scan is a table scan.
Post the DDL for Tab1, including all indexes. Getting indexes just right can be quite fiddly.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden