Killing Fragmentation

Top Five SSWUG TV Episodes of 2011
SSWUG TV was introduced in 2011 extending the already popular SSWUG media content. We added new segments on top of the regular interviews with thought leaders around the IT field. We picked our top 5 episodes in this new format, recaping some of the great content provided by our team and guests.

1. Interview with SQL Server instructor Kalen Delaney about her upcoming book and with Tom Trainer from Gluster about capacity utilization in the Hadoop environment.

If you have any ideas on improving SSWUG’s programming or if you simply want to praise the work we’ve done, feel free to let us know!

Killing Fragmentation
Here is a fragmentation scenario for which I have not found a satisfactory solution. I have some large data mart tables that take as much as 50gig for a months worth of data. The data becomes fragmented quickly, and the indexes must be re-built nightly to maintain optimal performance. And that is the problem.

I only have 20gig space available in tempdb. Therefore, when these large tables re-index the clustered index, it is performed in the database itself. When re-indexing is completed I have a large amount of unallocated space equal to the size of the largest table that was re-indexed. That’s no surprise. However, if I wish to remove some of this un-necessary space, the benefit of re-indexing is lost. Simon Facer wrote a concise blog on the issue. So, what options are available to me?

• Don’t re-index the clustered index
• Shrink the file but use Truncate only (see Simon’s article)
• Re-index the clustered index onto a new file

Your first question should be, why do you care about the un-used space in the first place? You are going to need it again tomorrow when you re-index again. That’s a great question. I’m going to avoid answering the question in that my answer won’t really help anyone else. But, I brought the question up because it is something you should consider for your situations.

In my situation I chose the third option. I have a rolling file I use for this mart data. Each day, the data is re-indexed pointing to a different file allocated specifically for this purpose. In my process, I do the following steps:

1. I first determine the size of the tables being de-fragmented
2. I size the next file space so it doesn’t have to automatically grow
3. I move and defragment the files by rebuilding the clustered index pointing to the new file space
4. I shrink the original file space to zero, and leave it as part of the database for a future cycle

When the process is complete I end up with a trim database that has no fragmentation.

Of course this is all SQL Server specific, and is necessary based on the techniques used by SQL Server to shrink allocated space in a database file. I can’t speak to other data engines as to if this is even an issue. So, for you MySQL, Oracle, DB2, Sybase, Informix, Posgres jockeys, drop me a note and share you experience with defragmenting your data for performance. Send your response to btaylor@sswug.org.

Cheers,

Ben

$$SWYNK$$

Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ

SQL supports a concept called all-at-onc… (read more)