CODEWITHSUNDEEP
sql-serversql-server-2012
ZedZip
ZedZip

Reputation: 6470

Why Clustered Index Scan in this query?

This query (SQL2012) execution plan shows me that Clustered Index Scan used in the internal sub-query on PK index:

  SELECT n3.id as node_id,x.id as id,
  (select xv.value from xv 
  --with(forceseek)  
  where xv.id=x.id) as [value]
  FROM x
  INNER JOIN n3
  ON x.[obj_id]=n3.id 
  AND n3.parent_id = '975422E0-5630-4545-8CF7-062D7DF72B6B'

The tables x and xv are master->details tables.

When I use hint forceseek then it shows Clustered Index Seek and query executes fast. Why there is Scan instead of Seek? How to change the query to have Index Seek without the hint FORCESEEK?

UPD: The full demo script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/*
DROP TABLE [dbo].[xv]
DROP TABLE [dbo].[x]
DROP TABLE [dbo].[n3]
*/

CREATE TABLE [dbo].[n3](
    [id] [uniqueidentifier] NOT NULL,
    [parent_id] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_n3] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
)

GO

CREATE TABLE [dbo].[x](
    [obj_id] [uniqueidentifier] NOT NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_x] PRIMARY KEY CLUSTERED 
(
    [id] ASC
))

GO

ALTER TABLE [dbo].[x]  WITH CHECK ADD  CONSTRAINT [FK_x_n3] FOREIGN KEY([obj_id])
REFERENCES [dbo].[n3] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[x] CHECK CONSTRAINT [FK_x_n3]
GO

CREATE TABLE [dbo].[xv](
    [id] [int] NOT NULL,
    [value] [sql_variant] NOT NULL,
 CONSTRAINT [PK_xv] PRIMARY KEY CLUSTERED 
(
    [id] ASC
))

GO

ALTER TABLE [dbo].[xv]  WITH CHECK ADD  CONSTRAINT [FK_xv_x] FOREIGN KEY([id])
REFERENCES [dbo].[x] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[xv] CHECK CONSTRAINT [FK_xv_x]
GO

INSERT INTO n3(id,parent_id)
select newid(), '975422E0-5630-4545-8CF7-062D7DF72B6B'
GO 10

INSERT INTO n3(id,parent_id)
select newid(), '805422E0-5630-4545-8CF7-062D7DF72B6B'
GO 5

INSERT INTO x([obj_id]) 
select id from n3 where parent_id='975422E0-5630-4545-8CF7-062D7DF72B6B';


insert into xv (id, value) 
select id, cast(RAND(1) as sql_variant) from x 

--select * from x
--select * from n3

  SELECT n3.id as node_id,x.id as id,
  (select xv.value from dbo.xv 
  --with(forceseek)  
  where xv.id=x.id
  ) as [value]
  FROM dbo.x
  INNER JOIN dbo.n3
  ON x.[obj_id]=n3.id
  AND n3.parent_id = '975422E0-5630-4545-8CF7-062D7DF72B6B'


/*
DROP TABLE [dbo].[xv]
DROP TABLE [dbo].[x]
DROP TABLE [dbo].[n3]
*/

--Update statistics xv with fullscan

Upvotes: 2

Views: 367

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

I suspect the statistics of xv table might be out of date. Update the statistics of xv and try running the query again.

Update statistics xv with fullscan

Update :

After looking at the data setup and query, For the given parent_id input it is very clear that all the records in both x and xv match so it is obvious that optimizer chooses index scan instead of seek because it has fetch all the records from both x and xv table

Also the number of records is less so optimizer will prefer scan instead of seek

Upvotes: 1

Related Questions