Cluster Index - Huge Table - 0 Fragmentation - Clustered Index Scan???

  • Maybe Gail or someone else more knowledgeable can correct me, but creating a nonclustered index that contains every column is more or less identical (for selecting) to a clustered index.

    It seems in this case, you might be better off restructuring the table, and along with adjusting data sizes if possible, create the clustered index on the test_regid field (if it's unique and fits requirements/etc).

  • Derrick Smith (9/7/2010)


    Maybe Gail or someone else more knowledgeable can correct me, but creating a nonclustered index that contains every column is more or less identical (for selecting) to a clustered index.

    Yup mostly. It's not exactly the same, it doesn't affect all the nonclustered indexes and it's a separate structure from the table unlike the cluster.

    It seems in this case, you might be better off restructuring the table, and along with adjusting data sizes if possible, create the clustered index on the test_regid field (if it's unique and fits requirements/etc).

    Impossible to say without more knowledge of the other queries that run against this table and what the data in that column looks like. test_regid is not unique. An equality match returns over 500 rows. That said, a clustered index does not have to be defined unique, it's merely a very good idea to do so.

    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
  • Hi Derrick,

    Clustered index can be created on a single column and it is not equivalent to creating a Nonclustered index with all the columns in a table.

    Also PK by default creates a clustered index and if the table already has a clustered index we cannot create a second clustered index on the table.

    Also sometimes changing the table structure might not be feasible for the sake of a single query and neither is it possible to create a clustered index based on the individual queries.

    Best Regards,

    SQLBuddy

  • Thanks Gail & SQLBuddy. I'm still working on learning the internals of indexing.

    sqlbuddy123 (9/7/2010)


    Hi Derrick,

    Clustered index can be created on a single column and it is not equivalent to creating a Nonclustered index with all the columns in a table.

    Also PK by default creates a clustered index and if the table already has a clustered index we cannot create a second clustered index on the table.

    Also sometimes changing the table structure might not be feasible for the sake of a single query and neither is it possible to create a clustered index based on the individual queries.

    Best Regards,

    SQLBuddy

    True. I was mostly asking to see if the PK/ID field he has now served any purpose...if this column isn't used for anything, isn't relied upon for any queries, and has no relevancy to the uniqueness of the other records, then there's no reason to have it. If there is no reason to have it, the regid field might be a better candidate, which may also resolve this query issue. Looking at this though..if they're using select * for this query, I imagine it's not the only occurrence so any schema changes would probably break the entire app.

    I'm not convinced that there's no better way to get this query to run than having an all-inclusive index..will wait for more info.

  • sqlbuddy123 (9/7/2010)


    Clustered index can be created on a single column and it is not equivalent to creating a Nonclustered index with all the columns in a table.

    If you create a clustered index on a specific column and a nonclustered index on the same column with every other column in the table as an include column, the two indexes will be near-identical in structure. The main (but not only) differences will be that the nonclustered index is a separate structure from the table and that it does not affect any of the other nonclustered indexes.

    Also PK by default creates a clustered index and if the table already has a clustered index we cannot create a second clustered index on the table.

    If there isn't a clustered index already. Nothing requires that the pk be enforced by a clustered index though.

    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
  • GilaMonster (9/7/2010)


    Derrick Smith (9/7/2010)


    Maybe Gail or someone else more knowledgeable can correct me, but creating a nonclustered index that contains every column is more or less identical (for selecting) to a clustered index.

    Yup mostly. It's not exactly the same, it doesn't affect all the nonclustered indexes and it's a separate structure from the table unlike the cluster.

    It seems in this case, you might be better off restructuring the table, and along with adjusting data sizes if possible, create the clustered index on the test_regid field (if it's unique and fits requirements/etc).

    Impossible to say without more knowledge of the other queries that run against this table and what the data in that column looks like. test_regid is not unique. An equality match returns over 500 rows. That said, a clustered index does not have to be defined unique, it's merely a very good idea to do so.

    Gail..i have seen a very strange thing....i just replicated the table that had issue, changed the column names since i had to send you the execution plan and now i see the same query is running within 1 sec . I didn't apply the index through DTA just used whatever was there. I will create a another test copy and send you. One more thing now in the execution plan i see it is using clustered index and is also doing a look up?

  • Stale statistics. I suspected that might be the case. That's why I wanted to see the full exec plan

    Please run UPDATE STATISTICS <Table name> WITH FULLSCAN on the original table, see if it fixes things.

    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
  • Are you sure this isn't a result of the data already being in the buffer cache?

    If you ran the query without the index and it took 50 seconds, then added the index, reran it, and it took 1 second..I'm fairly sure that unless you did an sp_recompile on the table/proc, it would just grab that data right from the cache. That may have been skewing the results heavily into making it look like that index was neccessary.

  • Derrick Smith (9/8/2010)


    I'm fairly sure that unless you did an sp_recompile on the table/proc, it would just grab that data right from the cache.

    DBCC FREEPROCCACHE. sp_recompile will force a new plan, but will use data already in cache.

    Thing is, data in cache/not in cache won't result in a different execution plan. It'll cause the second and subsequent executions to be faster, but the plan will be the same.

    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
  • GilaMonster (9/8/2010)


    Stale statistics. I suspected that might be the case. That's why I wanted to see the full exec plan

    Please run UPDATE STATISTICS <Table name> WITH FULLSCAN on the original table, see if it fixes things.

    Gail:

    Update stats on the original table didnt work, but i created a copy of the table on other server and query ran within a sec? Data,structure everything is same. I am little hesitant to post the execution plan here and if i rename the columns query will run fine. Any idea what is going on here?

  • GilaMonster (9/8/2010)


    Stale statistics. I suspected that might be the case. That's why I wanted to see the full exec plan

    Please run UPDATE STATISTICS <Table name> WITH FULLSCAN on the original table, see if it fixes things.

    Alright Gail, now i have a good solution but still i need your input on this please. I realized that the option under the properities of index "Use Index" was unchecked for the bad one and checked for the good one. After i checked the box query ran fine. I am attaching the two exec plans with and without checking the option of use re-index, please let me know how should i analyse this.

Viewing 11 posts - 16 through 25 (of 25 total)

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