Post reply

98% Clustered Index Scan

  • November 19, 2009 at 2:53 am

    #228035

    Hi All

    I have a performance issue on one of my databases. I viewed the Execution plans for the queries that have a high number of reads on database; and got the some of following results that needs some expert explanation:

    A Clustered index Scan on a 1000 row table is 98%. Primary key(id column), Foreign key, one non-clustered and one clustered index on table

    Argument in Execution plan:

    It shows the Clustered index but prefer the index to be on the id column

    Should/can I change the primary key column to be a non-clustered index as well?

    Is a 98% Clustered index scan good or bad based on above criteria?

    Thanks and Regards

    Anchelin

  • November 19, 2009 at 3:11 am

    #1081423

    A clustered index scan is a table scan, so that's usually a bad thing.

    Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

  • November 19, 2009 at 3:44 am

    #1081441

    Thank you for your reply.

    Will you be able to tell me what I can do to optimize the table?

    Thanks

    Anchelin

  • November 19, 2009 at 4:17 am

    #1081458

    GilaMonster (11/19/2009)

    Please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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