Adding a Clustered index to a HEAP table on someone else's app

Question

We're using a proprietary application based on SQL Server 2005, which has many HEAP based tables (that is, no Clustered index). Over the years, these tables have grown badly fragmented (e.g. 99% fragmentation). I need to defragment them.

Now, in SQL Server 2005, there's no way to do that directly. I can either:

1. Create a Clustered index on a prexisting field, and keep it
2. Create that index, rebuild the table, and then drop it
3. Add my own field (eg autoincrement key)

Now, this is a large app, written by a vendor - a giant black box. I'm not eager to mess around with their stuff. I don't know a lot about how the tables are used. What's the least impact way to do this?

And, as a follow up: There are many of these HEAP based tables, over a dozen databases used by the application. Is there a way to automate choice #2 or #3? Or, how should I pick which tables to modify?

---

UPDATE:
To answer the questions posed by the (very helpful) responses:

• Performance has been unacceptable. The vendor told my client that they this is because the tables are extremely fragmented, and it's the client's responsibility to defragment them regularly
• We have two instances of the application: a test one and a production one
• I first defragmented all of the Clustered tables, which greatly improve performance
• The vendor's support team has been very unhelpful. They've told us it's our responsibility to defragment the tables. When I've asked them how they recommend defragmenting HEAP based tables - should I add a Clustered Index? - they've only responded "That's a Microsoft question".

In short, the customer support team has made it clear: You must defrag the tables, how you do it is your business.

As for future versions: Yes, new versions are being developed, and they will eventually migrate to SQL Server 2012. But they need performance solutions today.

Finally, as for the defragmenting taking too long: It doesn't matter. They have giant tables with 99% fragmentation; the application isn't used at night; I can easily spend hours at a time defragging them.

Answer 1

Fragmented heaps matter. The larger the heap, the harder it is to walk the data. Performance suffers.

Creating and dropping the CI will reorder the rows in the table as you are aware. If the vendor offers no solution and it is your responsibility to address the fragmentation, that's exactly what I have done in the past. I wouldn't leave the CI if it were me, I'd drop it. I would avoid adding a column since that does alter the function of the table permanently, and the same argument could be made if you leave the CI in place.

If you do this be sure that you have appropriate storage for creating the CI since it will require storage at least equal to the size of the heap, and also consider when you do it re: your service level requirements.

Answer 2

I'm going to go on the assumption that you've identified a problem that needs to be fixed... though I'm not entirely convinced of that yet.

Similar to my answer here, there are two cases when it comes to vendor applications.

1. The application is currently under license and/or the vendor/owner doesn't want you meddling.

   In this case, definitely do not change the schema in any way, including adding indexes of any kind.

   Making schema changes may in fact void your license contract, but this can also cause problems for the vendor if they decide to fix the issues in a future schema update. Speaking from personal experience from the point of view of the vendor, we sometimes have to very actively tell clients to stop creating objects in our databases, because it impacts our ability to successfully apply schema changes. (I'm not just saying that -- we had a case a couple months ago where a schema update failed for one client because of an unexpected dependency from a client-created object. Slightly different scenario than adding an index, but still, it does come up.)

   There are rare cases where database-level changes are okay, but you need to make absolutely certain the vendor is okay with what you're planning to do.

   The best option for this scenario is... actually, not to make any changes yourself. Put together a compelling case to prove why it's important these changes be made, and present it to the vendor for them to implement in a future release.

   From a vendor's point of view, ideally suggestions are specific, simple to implement and test, and have a high positive impact for all clients. In a large application, suggesting that all tables that are heaps be converted to clustered indexes may be (and probably is) a good idea, but it's a complete non-starter. Instead, figure out the top 5-10 most important tables or 2-3 application areas that would benefit from this type of change in your environment.

2. The application is out of license or the vendor/owner doesn't care what you do with it.

   In this case, you can add any index you want, but objects should remain in place with the same names (note I didn't say the same objects). There are many options here, depending on how the application and database works internally. If you still plan to apply future vendor-supplied schema changes, tread very carefully and make sure you create undo scripts for all your changes.

   The key to successfully making changes is to maintain application functionality... easier said than done, of course, if you plan to make significant changes.

   That said, simply adding clustered indexes is pretty safe. The challenge will be selecting an appropriate key for each index, and this should be done by hand, not automated. The only way to do this part well is by familiarizing yourself with a table's access patterns and purpose.

   Come to think of it, you could go through the same process as in the other case: identify the 5-10 tables that would have the highest overall impact, and fix those first. You may find that's all that needs to be done.