Post reply

Index Seek not happening

  • 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

  • December 4, 2014 at 1:16 am

    #1762597

    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

    #1762601

    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

  • December 4, 2014 at 2:56 am

    #1762635

    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

    #1762659

    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

    #1762775

    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

    #1762862

    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