Clustered Index Scan Nightmare

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Clustered Index Scan Nightmare

Author

Topic

vladibo
Starting Member

11 Posts

Posted - 2014-09-16 : 07:27:06

I have table Table1 with primary key Id, field Name and field with index OtherId


CREATE TABLE [dbo].[Table1] (
	[Id] UNIQUEIDENTIFIER NOT NULL,
	[Name] [varchar](255) NOT NULL,
	[OtherId] UNIQUEIDENTIFIER NOT NULL,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD 
	CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	)  ON [PRIMARY] 
GO
CREATE NONCLUSTERED INDEX [IX_Table1_OtherId] 
	ON [dbo].[Table1]([OtherId])
GO
INSERT INTO [Table1] VALUES('FAD89C30-0EA4-47D8-B5DE-514180C6178E', 'One', '96F11BBA-24AC-4F92-82D8-F65BE6BF0DCF')
INSERT INTO [Table1] VALUES('E16174B5-B0EC-4F85-AF98-0342FD2E49E9', 'Two', '417AB911-1C03-42DE-B7A6-EC19C6AFA370')
INSERT INTO [Table1] VALUES('6B420B24-40F7-4A25-B76E-23733910940D', 'Three', '9CF525E9-8ED0-4C98-A17F-04E9AF086AC0')

Then when I run


SELECT * FROM [Table1] WHERE OtherId = '417AB911-1C03-42DE-B7A6-EC19C6AFA370'

I get in the execution plan "Clustered Index Scan"

Why Oh Why? I have index on OtherId, shouldn't that make it index seek?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-09-16 : 08:11:51

because the table is small, the database engine could use index scan with low cost.

Javeed Ahmed

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-16 : 09:12:48

To add a bit more color to what ahmeds08 said, if you turn on XML query plan using

SET SHOWPLAN_XML ON

and then run your select statement, it will show you the XML query plan. If you click on it to view it and look through, you will find something like optimization level = trivial, or Reason for Early Optimizer Termination = "Good Enough Plan Found" (I don't recall the exact wording, but something along those lines).

Query optimizer does not try to find the absolute best query plan. It tries to find a "good enough" plan in a "reasonable amount of time". For a small table as in your example, it determined that a scan resulted in a good enough plan. Try inserting 1,000 or so rows into your table and look at the execution plan again. It would use your non-clustered index.

vladibo
Starting Member

11 Posts

Posted - 2014-09-16 : 12:17:11

Thank you guys I was beginning to freak out

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-16 : 16:49:47

All this happens because of your "SELECT *".
Change your select to

SELECT * FROM [Table1] WITH (INDEX ([IX_Table1_OtherId]))
WHERE OtherId = '417AB911-1C03-42DE-B7A6-EC19C6AFA370'

and you will see that the optimizer is forced to use a KEY LOOKUP to get all columns out.
The reason is the name column which is not indexed. The ID column is included in the nonclustered index by default.

Change your index [IX_Table1_OtherId] to this

CREATE NONCLUSTERED INDEX [IX_Table1_OtherId] 
	ON [dbo].[Table1]([OtherId])
	INCLUDE (name)

and you will get the expected behaviour.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources