Post reply

166 days to create index

  • April 30, 2014 at 8:20 am

    #1709856

    Edit: Based on the index missing and index usage stats you posted (thanks!) I would say: /Edit.

    JID, not DID, seems like the best clustering index key for SELECTs. We can add LID to make it a unique key, if needed. [Of course, then the nonclus indexes on JID will be removed.]

    But, we have to make sure it doesn't cause too much fragmentation during INSERTs.

    How sequential are JID values?

    What is the min count, max count and avg count of rows per JID?

    If those results aren't too bad, it's definitely worth testing.

    What is the min count, max count and avg count of rows per DID?

    We can reduce the number of nonclus indexes on DID, but we'll have to see to how few.

    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!

  • May 1, 2014 at 7:48 am

    #1710103

    Your calculation is a bit off, you said "13m record reads per hours / 2,163,568,622 rows = 166 days to create this index !"

    That's 166 hours not days, still 7 days is a lot.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

Viewing 2 posts - 16 through 16 (of 16 total)

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