1

I've noticed that we have a few heap tables on our database, they all have a non clustered index on the primary key (which is an Int). The biggest table has over 2 million rows, a forwarded record count of 663,479 and page count of 609,028. Also noticed that the forwarded records per second are 972 on the server.

From my limited knowledge and a bit of googling this all looks pretty bad so i'm looking into adding a clustered index to this table.

  1. Would you agree this is the right thing to do?
  2. If so what are the best practices for adding a clustered index to this table, i assume i will have to remove the existing non clustered index on the primary key (and any foreign keys referencing it) and then add the clustered index and recreate the foreign keys? Are there any scripts to assist with dropping and recreating the foreign keys as there are quite a few?
  3. Would there be any benefit to rebuilding the heap as a short term fix while waiting for a chance to sort out the indexes?

Sorry if i haven't provided enough information, let me know if you need to know anything else to be able to help, thanks!

  • 1
    You should give us more information. For example,if PK is composed of 10 fields, it may be better to not create clustered index on it. Or if it's a guid, maybe it's a way to avoid clustered index fragmentation – sepupic Sep 17 '19 at 13:25
  • Thanks @sepupic, have updated the question, the primary key is just an int column. – rbz Sep 17 '19 at 13:46
1

Would you agree this is the right thing to do?

Yes, especially if (1) the table is looked up using the Primary Key, and (2) you are noticing performance issues.

Note that if the table is primarily looked up using a set of data other than the PK (Example: Order Lines may be looked up by the OrderID and sorted by the OrderLineId), then you may consider a clustered index based on the most common lookup(s), especially if they select sequences of rows. For example, on the Order Lines example above, having a Clustered index like:

CREATE CLUSTERED INDEX Idx_OrderID_OrderLineId
ON OrderLines (OrderId, OrderLineId)

would be optimal for retrieving all of the order lines for a given order.

Also, there are cases where having a non-clustered covering index may be optimal. You will need to keep an eye on performance whatever you do with these tables.

If so what are the best practices for adding a clustered index to this table, i assume i will have to remove the existing non clustered index on the primary key (and any foreign keys referencing it) and then add the clustered index and recreate the foreign keys? Are there any scripts to assist with dropping and recreating the foreign keys as there are quite a few?

Best practices depends on the answers to the above questions. If you actually want a clustered PK (because that is how the table is commonly referenced, or because there is no clear clusterable index that could be created), then you will need to perform all of the steps you specify above. If you decide to create a clustered index that is identical to your PK (and yes, people do this... ) you won't need to do all these steps. And if you decide your optimum clustered index is some combination of fields not identical to the PK, then you will not need to do all of these steps either.

If you do replace the PK with a clustered PK, then there are scripts that can help with identifying the foreign keys. I tend to use the View Dependencies functionality inside to SSMS to identify possible locations for FKs to my table, then manually script them out and drop them myself (because I had some bad experiences with scripts that should handle FKs), so I can't recommend any specific choices right now.

Would there be any benefit to rebuilding the heap as a short term fix while waiting for a chance to sort out the indexes?

Not really, no.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.