October 29, 2012 at 3:52 am
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
October 29, 2012 at 5:33 am
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
October 29, 2012 at 6:01 am
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] UNIQUEIDENTIFIERNOT NULL,
[ContextID] INTNOT NULL,
[SessionID] INTNULL,
[ToRefresh] BITNOT 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);
October 29, 2012 at 6:27 am
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.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply