Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index scan on foreign key reference Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 3:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:17 AM
Points: 295, Visits: 402
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
Post #1378173
Posted Monday, October 29, 2012 5:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1378200
Posted Monday, October 29, 2012 6:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:17 AM
Points: 295, Visits: 402
Yes, it is a cluster index scan of PK_Tab2.
The DDL of the two tables:
CREATE TABLE [Tab1] 
(
[ClientContextID] INT IDENTITY (1, 1) NOT NULL,
[ClientID] UNIQUEIDENTIFIER NOT NULL,
[ContextID] INT NOT NULL,
[SessionID] INT NULL,
[ToRefresh] BIT NOT NULL
);

ALTER TABLE [Tab1]
ADD CONSTRAINT [PK_Tab1] PRIMARY KEY CLUSTERED ([ClientContextID] ASC);

ALTER TABLE [Tab1]
ADD CONSTRAINT [FK_Tab1_Tab2] FOREIGN KEY ([SessionID])
REFERENCES [Tab2] ([SessionID]) ON DELETE CASCADE ON UPDATE NO ACTION;

CREATE INDEX [IX_Tab1]
ON [Tab1] (ClientID ASC, SessionID ASC) INCLUDE (ClientContextID, ToRefresh, ContextID);


CREATE TABLE [Tab2]
(
[SessionID] INT IDENTITY (1, 1) NOT NULL,
[PatientID] CHAR (12) NOT NULL,
[Active] BIT NOT NULL,
[Start] DATETIME NOT NULL,
[End] DATETIME NULL,
...............................................
);

ALTER TABLE [Tab2]
ADD CONSTRAINT [PK_Tab2] PRIMARY KEY CLUSTERED ([SessionID] ASC);


Post #1378206
Posted Monday, October 29, 2012 6:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:49 AM
Points: 7,234, Visits: 13,719
That looks fine to me. Ensure statistics are up to date for indexes on Tab2, and check for an index seek in the CI of Tab2 when you insert a single row into Tab1.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1378214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse