Index Defragmentation! 1

[Antzz]

Hi All,
I am having a weird issue.
I have overnite database optimization jobs which do re-indexing(we do reorgs as opposed to rebuilds). What I have seen lately is that the fragmentation does not decrease to the extent it should in case of a set of tables. For eg. if before the job, fragmentation was 80%, after de-fragmentation(reindexing), it would drop to only 75% as opposed to a lower number.

Any ideas why this is the case?

A

 

[ptheriault]

I have found in my experience that if an index is fragmented more than 30% it is better to rebuild the index instead of defrag.

Also,
Here is a quote right from BOL

DBCC INDEXDEFRAG shuffles index leaf pages in place. Therefore, if an index is interleaved with other indexes on disk, running DBCC INDEXDEFRAG against that index does not make all leaf pages in the index contiguous. To improve the clustering of pages, rebuild the index.

And if this is a 2005 box you should use the ALTER INDEX Statement as Defrag will not be supported going forward.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Antzz]

I tried doing a rebuild but unless I change the fillfactor considerably, the result is the same(70% defrag).

 

[Antzz]

Oh an important thing - I also found out recently that SP2 on SQL Server(2005) was not applied completely. This was by our System Administrator. I am not sure to what extent SP2 was successful.

Just wondering if this can have an impact on index rebuilding).

 

[ptheriault]

Is there a clusted index on the table in question? If not then it is a heap table. There are 3 ways to correct fragmentation on a heap.

1. create a clustered index ( and keep it)
2. select you data out into a temp table, truncate table and insert the data backin with an order by clause

3. create a clustered index, then drop it.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Antzz]

That does make sense. Some of these tables might not have had clustered indexes and I think I had created them. But I did not transferred the data to a temp table and moved it back in.

I had assumed that creating a clustered index on an identity column would correct the fragmentation. Is that not true?

 

[ptheriault]

If you don't have a clustered index then defrag and reindex will not work. You can create a clustered index in an identity column but you will have to re-index or defrag the other indexes on the table.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Antzz]

Paul,
I detected the missing clustered indexes but had re-created them. The problem exists even after I had made the changes and rebuilt indexes on the other columns.

In fact a couple of tables where rebuild did not work(clustered indexes exists on these tables), I dropped the clustered index and re-created it. The fragmentation was still present - unless I changed the fill factor drastically. This condition was also prevalent on indexes with less than 50 pages. I am not sure what is the impact of fragmentation as far as performance on something like this.

Roopam

 

[olahallengren]

I think that tables with few pages always report a high level of
fragmentation. It's also so that fragmenation is not a problem in tables with
few pages. Kalen Delaney wrote a blog post about this recently.

http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx

I have a stored procedure that could help you with this. The stored procedure does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Ola Hallengren

http://ola.hallengren.com