Row Deletion vs. Logical Reads
-
We hit some totally inexplicable activity yesterday, I couldn't find any references on Google (couldn't even figure the search phrase), so it's time to rely on the true source of all key information related to SQL Server.
Background:
- SQL Server 2000sp3a
- Large table (1,200,000+ rows across 48000+ pages)
- Clustered primary key on integer column set with identity(1,1)
- 6 additional indexes
- Contains 11 foreign keys referencing other tables
- Primary key is referenced by 21 foreign keys
- Has 1 foreign key referencing itself
- Sits in the middle of our system like a spider in its web
- Is used continuously throughout the day
We're trying to delete some old chaff data from this guy, and hitting major time and blocking issues. (It isn't relational integrity--we already checked and these rows can be deleted.) In researching the issue I ran code like
- SET STATISTICS IO ON
- DELETE <table> where <primaryKey> = <singleValue>
- SET STATISTICS IO OFF
The results showed a single table scan and 0 to 3 logical reads against all the depended foreign key tables, which is what you'd expect... but for the table itself, it performed 2 scans and 46,000+ logical reads! (You'll note it wasn't all the pages, just most of them.)
So what I'd like to know is: why, if I'm attempting to delete one row in a (this?) clustered table where the row is explicitly identified by it's primary key, does it perform what would appear to be a table scan? I'm totally mystified.
Philip
-
I'd imagine that it's because of the one foreign key which is referencing itself. Before deleting the row, SQL Server has to check that it's not violating integrity, and therefore has to check all of the foreign key values in your table.
-
Son of a gun, that's it. Thanks! The self-referential constraint has to be checked, and the parent column (as stored in the table) is not indexed. Index that column and (duh) the table scan goes away.
I tested this by building and selectively implementing a quick set of tables. I've copied it below, for posterity.
Philip
(P.S. I previewed this post, and for some reason it turned some of my closing parenthesis into goofy faces. Oh well.)
SET NOCOUNT ON
/*
DROP TABLE SON_OF_FOO
DROP TABLE FOO
DROP TABLE FRIEND
*/
-- Create tables
CREATE TABLE FRIEND
(
Friend_ID int not null identity(1,1)
constraint PK_FRIEND
primary key clustered
,Name varchar(50) not null
 
CREATE TABLE FOO
(
Foo_ID int not null identity(1,1)
constraint PK_FOO
primary key clustered
,Some_Data int not null
,More_Data varchar(50) not null
,No_Data real null
,Friend_ID int null
constraint FK_FOO__FRIEND
foreign key references FRIEND (Friend_ID)
,Alt_Foo_ID int null
constraint FK_FOO__FOO
foreign key references FOO (Foo_ID)
 
CREATE nonclustered INDEX IX_FOO__Alt_Foo_ID
on FOO (Alt_Foo_ID)
CREATE TABLE SON_OF_FOO
(
Son_Of_Foo_ID int not null identity(1,1)
constraint PK_SON_OF_FOO
primary key clustered
,Foo_ID int not null
constraint FK_SON_OF_FOO__FOO
foreign key references FOO (Foo_ID)
 
GO
-- Make 10,000 friends
DECLARE @Loop int
SET @Loop = 0
WHILE @Loop < 10000
BEGIN
SET @Loop = @Loop + 1
INSERT FRIEND (Name)
values ('Friend ' + CAST(@Loop as varchar(10)))
END
GO
-- 100,000 FOO entries
-- The first 10,000 have friends
-- Every thousandth has an fkey to the prior (x999th) entry
DECLARE @Loop int
SET @Loop = 0
WHILE @Loop < 100000
BEGIN
SET @Loop = @Loop + 1
INSERT FOO (Some_Data, More_Data, Friend_ID, Alt_Foo_ID)
values (@Loop, 'Row ' + CAST(@Loop as varchar(10))
,case when @Loop <= 10000 then @Loop else null end
,case when @Loop%1000 = 0 then @Loop - 1 else null end)
END
GO
-- Every thousandth FOO has a child
DECLARE @Loop int
SET @Loop = 0
WHILE @Loop < 100000
BEGIN
SET @Loop = @Loop + 1000
INSERT SON_OF_FOO (Foo_ID)
values (@Loop)
END
GO
/*
-- Check out the size of the main table
DBCC SHOWCONTIG(FOO)
-- Test it
SET STATISTICS IO on -- And set "Show Execution Plan" on as well
DELETE FOO
where Foo_ID = 56788
SET STATISTICS IO off
*/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply