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?
asked Feb 21, 2017 at 05:16 PM in Default
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
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.
answered Feb 23, 2017 at 01:29 PM
are the stats up to date?
answered Feb 23, 2017 at 12:17 PM