Post reply

Newly created Index and execution plan

  • June 17, 2015 at 11:01 pm

    #297985

    Hi,

    I run a query

    select col1, col2, col3, col4

    from Table

    where col2=5

    order by col1

    I have a primary key on the column.

    The execution plan showing the clustered index scan cost 30% & sort cost 70%

    When I run the query I got missing index hint on col2 with 95% impact.

    So I created the non clustered index on col2.

    The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.

    After creating the index also I am seeing same execution plan

    The execution plan showing the clustered index scan cost 30% & sort cost 70%

    but I can see the total time is reducing & Logical reads on that table is reducing.

    I am sure that index is useful but why there is no change in the execution plan?

  • June 18, 2015 at 2:31 am

    #1806287

    If the index is not in the execution plan, then it's not useful for that query and is not been used.

    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

  • June 18, 2015 at 8:58 am

    #1806414

    Hi,

    When we can create new index.

    One of my table in subscriber db requires new index. The table in subsciber db always be in use for Reporting.

    Is it ok to create index if the usage of table and no of records in the table are high?

  • June 18, 2015 at 11:02 am

    #1806473

    Maybe. Test, and see if there's any impact.

    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

  • June 21, 2015 at 10:22 pm

    #1806927

    Please confirm

    If one of the query started executing and we create index that query finish without index

  • June 22, 2015 at 2:35 am

    #1806956

    Yes, the query's already running, the index create will, depending on locks, have to wait until that query has finished. If it does run at the same time, it can't affect the currently executing query.

    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

  • June 22, 2015 at 6:17 am

    #1807003

    Thank you

  • June 24, 2015 at 9:16 pm

    #1807930

    What will happen if we drop the index in the middle of the day?

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

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