Showing posts with label nc-index. Show all posts
Showing posts with label nc-index. Show all posts

Friday, February 10, 2012

change nc-index to c-index?

Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?

thanx,Is update executed against facts_id itself or against the table "where facts_id=<whatever>"? If it's the first, then you have a lot of page splits in your index pages, but rebuilding non-clustered index with so many rows is much less destructive than if you had a clustered index there.|||the updates are like:

update facts
set name = value
where facts_id = @.facts_id

so I can expect only few page splits (is there a way to count 'em?).
There's no update on the facts_id itself and it's unlikely it'll happen in the future which, I guess, makes the facts_id even more attractive for a clustered index.
I'll have to rebuild the other indexes as well though but I guess that's okay.

I saw the facts has a bunch of statistics, my dev does not have this nor do the other warehouses that are derived from prod. Could this be a problem? If I'd throw 'em out (delete 'em), would sql rebuild them by itself?|||It will if Auto Create Statistics is on. It's a database setting.