Index - Total Fragmentation


I have been reading about fragmentation levels on indexes, but want to check with you lot first as I don't fully understand it.

I have a clustered index on a column in a table that is 99.2%. I think this is bad. Is this bad? Is it ever food to have a high fragmentation level? does it save disk space?

Also, if it is bad, how should I fix it? Rebuild, and then if that doesn't work Drop & Recreate? Is there any chance this will make things worse?


more ▼

asked Feb 21 at 05:16 PM in Default

avatar image

1k 3 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Yes, this is bad, Indexes can get fragmented from inserts, updates and deletions. Fragmentation causes performance issues Easily you can correct fragmentation by using ALTER INDEX command to either REORGANIZE or REBUILD the index. Have a look at this: http://sqltechtips.blogspot.in/2016/12/reorganizing-data-and-index-pages-in-sql-server.html

Hope this will help you

more ▼

answered Feb 22 at 04:10 AM

avatar image

421 3 7

(comments are locked)
10|1200 characters needed characters left

Fragmentation is generally bad. But before you consider dropping the index or re-building/ re-organising the index there are other things to take into consideration like the size of the table, statistics (updated or not), number of rows and also number of pages in the index. Defragmenting an index with less number of pages will make much of a difference in my opinion,Also consider the index usage.


more ▼

answered Feb 23 at 01:29 PM

avatar image

81 3 4 5

(comments are locked)
10|1200 characters needed characters left

are the stats up to date?

more ▼

answered Feb 23 at 12:17 PM

avatar image

722 1 4

I didn't notice because of any performance issues, I only noticed because I was clicking about and say a high fragmentation rate and thought "fragmentation doesn't sound good". I have rebuilt most of the indexes, but like Taseef said - the tables are pretty small, so it has only had a small performance improvement.

Feb 24 at 04:37 PM Gazz
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 21 at 05:16 PM

Seen: 96 times

Last Updated: Feb 24 at 04:37 PM

Copyright 2017 Redgate Software. Privacy Policy