Getting Daily Index fragmentation alerts? Need Solution?

Question

Hello Folks,

I have a scheduled job for rebuilding indexes on my server XXX however i am daily getting the alerts to rebuild the indexes on this table.

can any one suggest me why this happens. and what are consideration points..

Thanks in advance.

 

 

---

Yangamuni Prasad M

Answer 1

Hi,

One what conditions you have scheduled your job

---

Thanks and regards, Rishabh

Answer 2

we are using SQL Diagnostic Manager tool for monitoring the server so this tool will send us the alerts.

I dont know on what conditions this tool will send the alerts..

---

Yangamuni Prasad M

Answer 3

Do not blindly rebuild indexs, mostly UPDATE statistics will help for performance

Take a look at http://ola.hallengren.com/ script deal with that

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

Hi Prasad,

  Index should be rebuilt or reorganized based on the fragmentation percentage and the number of data pages in the tables.  Please check the settings in the tool ..

If fragmentation is between 5 - 30 % then index re-organize

If fragmentation is above 30 % then index re-built .. Some options like this will be there . Please check and change accordingly

---

-- Reddy Balaji C. ##Mark as answer if it solves the issue

Answer 5

Then possibly the tool is not precise enough for your envorinment. Say you have a table with an index, you many many times per day insert data into this table. If the index in question isn't over a monotonically increasing column, you will get page splits. And if you have relatively few pages (perhaps you empty or trim this table every day or week), then the fragmentation caused by the insert's page splits will probably be enough to "tip the scale" for the Diagnostic Manager's check. That doesn't mean it is somehting wrong in your database, it is just the way it is. A good tool will allow you to exclude tables/indexes for these things and/or also adjust threshold levels.

---

Tibor Karaszi, SQL Server MVP | web | blog