question

Gazz avatar image
Gazz asked

Index - Total Fragmentation

Hello, 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? Thanks
indexingfragmentation
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

perrywhittle avatar image
perrywhittle answered
are the stats up to date?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Tauseef_jan avatar image
Tauseef_jan answered
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. T
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.