InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 10:03:24 GMT20http://www.sqlservercentral.com/Forums/Topic1384481-391-1.aspxI have a table TabA with a column that is a foreign key to the primary key of table TabB.I have also an index on this column from TabA.I have a stored procedure that selects some data from a table TabC and insert into TabA:[code="sql"]CREATE TABLE TabA(ColA1 int,ColA2 int,.............);ALTER TABLE TabA ADD CONSTRAINT PK_TabA PRIMARY KEY CLUSTERED (ColA1);ALTER TABLE TabA ADD CONSTRAINT FK_TabA_TabB FOREIGN KEY (ColA2) REFERENCES TabB(ColB1);CREATE NONCLUSTERED INDEX IX_ColA2 ON TabA(ColA2);CREATE TABLE TabB(ColB1 int,ColB2 int,.............);ALTER TABLE TabB ADD CONSTRAINT PK_TabB PRIMARY KEY CLUSTERED(ColB1);CREATE TABLE TabC(ColC1 int,ColC2 int,.............);INSERT INTO TabA (ColA1, ColA2,.....)SELECT ColC1, ColC2......FROM TabCWHERE .....[/code]This query causes deadlocks in application. The deadlocks are on the primary key of table TabB.Looking inside the query plan I see that an index scan is made on primary key of TabB PK_TabB (although TabB does not take part in the query!) .How can I remove the index scan on the primary key of table TabB from the query plan?How can I force the an index scan/seek on index IX_ColA2 from TabA instead of index scan on primary key of TabB?ThanksWed, 14 Nov 2012 02:26:40 GMTioani