April 30, 2014 at 8:20 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 1, 2014 at 7:48 am
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