Fragmentation

Question

Hi Team 

In one of database some table index Fragmentations are utilizing above 90%. for this i have rebuild the Index that purticular indexes. Again after two hours its repeating some another tables.

Kindly  please suggest me what is the long term solution for this.

---

subu

Answer 1

Decreasse the Fill factor is one option...

---

Please click "Mark As Answer" if my post helped. Tony C.

Answer 2

Tony,

Thanks for your reply.

But its not recommended I saw in some of the links. Is there any other please suggest me.

 

---

subu

Answer 3

"In one of database some table index Fragmentations are utilizing above 90%. for this i have rebuild the Index that purticular indexes. Again after two hours its repeating some another tables"

I am not really sure what your problem is. If you are saying that for different tables the fragmentation is high at different points then its fine. it is supposed to be.

If yu are saying the same tables/indexes have high after reindexing then...

what is the page count of these indexes? If it is less than 1000 then you can safely ignore. You dont have to reindex them.

If the page count is greater than 1000, then check what is the fillfactor. You need to finetune the fill factor.

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 4

You've been given the same basic response to your many variations of this question.  Don't blindly rebuild indexes.  Ignore small tables and indexes.  Choose the appropriate strategy to rebuild your indexes to minimize the burden on your server.  And most importantly, figure out why this index becomes so fragmented and respond accordingly.  The long term solution will depend on understanding that last point.

Answer 5

Hi Scoot,

You are 100% Correct i stick there only. Why this index becomes so fragmented and How can we check kindly suggest me on the same.

Thank for your reply.

---

subu

Answer 6

You didn't answeres the question yet; how many data pages do the index / table have?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

Hi Olaf,

Just now i have checked one more index was showing 99% fragmention. For this i have checked with the count only 

SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(''),OBJECT_ID(''),NULL,NULL,'DETAILED')
GO

Using above script but one table went 1107 so for that perticulat index only fragmentation make it as 85% after then rebuild the index now its every thing was fine.

For next time on-wards i will check page count then i will take action occurdingly. Thanks for your suggestion.

I any thing wrong please correct me.

---

subu

Answer 8

Subbu,

You need to learn few basics about fragmentation and below article will help you

Why is index still fragmented after rebuild

>In one of database some table index Fragmentations are utilizing above 90%

There is nothing like index utilization 90 % this is totally wrong.

>Again after two hours its repeating some another tables.

What does this mean, repeating ? Please take time and study the basics

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Technet Wiki Article
MVP

Answer 9

Hello

See the MSDN articel :Reorganize and Rebuild Indexes

https://msdn.microsoft.com/en-us/library/ms189858.aspx

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 10

You may have index maintenance in place. You can use Ola's great script to implement the same.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
 [Blog]