December 3, 2014 at 11:29 pm
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
December 4, 2014 at 1:16 am
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.
December 4, 2014 at 1:30 am
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
December 4, 2014 at 2:56 am
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
December 4, 2014 at 4:17 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 4, 2014 at 10:02 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
December 4, 2014 at 10:27 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply