I am cleaning up a database and there are performance issues with heavy traffic. Reading and writing. But the heavy traffic is condensce to a few days annually.
I suspect one problem is a lot of heavily-used tables don't have a clustered index. But they do have a primary key and a unique, non-clustered index tied to it that is just on the primary key (no includes).
Is this a beneficial approach anytime?
My guess is someone thought it would be faster if the db didn't have to insert all the columns from a clustered index (I wouldn't do this ever myself so I don't know the madness behind the method).
I am starting the task of applying clustered indexes to these tables, but wanted to survey smarter minds before I got too far to see if there is a scenario where a strategy like this would help?
I would think the loss of connections to the clustered index would outweigh any gain this might have?
I feel fairly confident in saying that no, this is not a beneficial approach, ever. If a table has only nonclustered indexes, it's a heap table, and aside from bulk insert operations, there is no good reason for having a heap table. That's not just me talking, that's Microsoft's opinion as well.
I have maintained a big warehouse where it was a good idea to not have a primary key at all (because the index for it took up a huge amount of space without being used), but only a unique clustered index on a DATETIME plus an ID. But you'll note that, primary key or not, it did have a clustered index, because not having one is just a bad idea.