Index Seek not happening

Question

Index Seek not happening

vignesh.ms

Hall of Fame

Points: 3456
More actions
December 3, 2014 at 11:29 pm

#313522

Hi There,
I'm having a table to hold the photo details of a business, BusinessPhotoId is the primary key column.
Now I require to find photos of a business so I used the following query,

select * from businessphoto where businessid = 2

CI - Clutered Index
NCI - Non-Clutered Index
It leads a CI scan, So I decided to create an NCI on BusinessId column
Though I created a NCI, execution plan shows only CI scan.
1. What is the problem, why the optimizer goes with the index seek?
2. Do you need any other Info from me about the table to ans ?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

viggneshwar Newbie Points: 6 More actions · Answer 1

Hi,

It's not necessary, plan always should be index seek. Based on volume of table data, index scan will be considered as best plan by the compiler.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Without seeing the execution plan, vague guess. Index is not covering and too many rows are being returned to make index seek + all the key lookups efficient.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

vignesh.ms (12/3/2014)
Though I created a NCI, execution plan shows only CI scan.
1. What is the problem, why the optimizer goes with the index seek?
2. Do you need any other Info from me about the table to ans ?
Thanks

You are doing a SELECT *. Chances are very big your index is not covering.

Table definition, index definition and execution plan might be a help to troubleshoot this.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

And because you're selecting everything, that's all stored with the clustered index, hence the scan.

How many rows is it returning out of how many rows in the database?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

If you (almost) always do lookups on the table based on "businessid", then cluster the table on businessid. If you need to keep the PK, make the PK nonclustered .

ALTER TABLE businessphoto DROP CONSTRAINT [PK_businessphoto]

--chg fillfactor and filegroup name as needed

CREATE CLUSTERED INDEX CL_businessphoto ON dbo.businessphoto ( businessid ) WITH ( FILLFACTOR = 98 ) ON [PRIMARY]

ALTER TABLE businessphoto ADD CONSTRAINT [PK_businessphoto] PRIMARY KEY NONCLUSTERED ( BusinessPhotoId ) WITH ( FILLFACTOR = 100 ) ON [PRIMARY]

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 6

ScottPletcher (12/4/2014)
If you (almost) always do lookups on the table based on "businessid", then cluster the table on businessid. If you need to keep the PK, make the PK nonclustered .
[/code]

Thanks dude.. it works. good thinking