Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
5  
Why do they need to be defragmented? Are they particularly large? Have you identified them as the cause of a performance issue? Do you have a support contract with the vendor (which may be voided if you alter the database)? –  Mark Storey-Smith Nov 7 '12 at 1:46
1  
In 2005 you can build a clustered index online if you are using Enterprise Edition. Also in 2008+ you can rebuild the table without creating a clustered index. Not sure how long you are staying on 2005, or why the vendor doesn't believe in clustered indexes, but creating a clustered index and leaving it there should not harm their application. As Mark suggested, though, you should verify this in any case. –  Aaron Bertrand Nov 7 '12 at 1:54
3  
Building a clustered index only to drop it seems quite wasteful to me, especially if you can't do it online (e.g. Standard Edition). What is the point? You'll get temporary short term relief but before long you'll be right back where you started. The last thing I would consider is (3) - changing the schema at all. –  Aaron Bertrand Nov 7 '12 at 1:55
2  
@S.RobertJames From your update, it sounds like the vendor is covering their backside and avoiding addressing the problem. "That's a Microsoft question" stinks as a response. They designed the tables as heaps, they should have designed appropriate maintenance. Preferable would be to add the CIX and leave it, if there is a suitable candidate column. Alternatively if it must remain a heap, copy the data to a new table, drop old, rename. –  Mark Storey-Smith Nov 7 '12 at 20:06
1  
@EricHiggins You're missing the point. If the vendor design necessitated a heap, they should of had a reason for it and they should have mitigation for it. More likely, it's a mistake/omission/fubar. And no, creating and dropping a CIX is not "the right path". –  Mark Storey-Smith Nov 8 '12 at 0:30
show 12 more comments

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
I generally dislike solutions that don't solve the root problem, but I'm going to go ahead and +1 this because I think it will help in the meantime. Good answer. –  Jon Seigel Nov 8 '12 at 3:39
    
Thanks Jon and I agree with everyone about poorly written vendor apps believe me, but we can't fix that in this context, and the OP was asking for advice on how to manage the current situation. That's what I was aiming to give him. –  Eric Higgins Nov 8 '12 at 3:46
add comment

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.

Edit: this answer, in a more general sense, is now available in video form.

share|improve this answer
    
Thank you for the thought out reply. Given that I have permission from the vendor, but no involvement, I want to do the least invasive fix I can do. It still seems to me that that would be: Create a CI, Rebuild, Drop the CI. –  S. Robert James Nov 7 '12 at 18:37
1  
You do not need to "rebuild". The create of the CI will reorder the heap as a part of the CI create operation. –  Eric Higgins Nov 7 '12 at 23:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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