Answered by:
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 MThursday, March 3, 2011 7:02 AM
Answers
-
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- Marked as answer by Prasad Yangamuni Thursday, March 3, 2011 9:48 AM
Thursday, March 3, 2011 7:40 AM
All replies
-
Hi,
One what conditions you have scheduled your job
Thanks and regards, RishabhThursday, March 3, 2011 7:10 AM -
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 MThursday, March 3, 2011 7:19 AM -
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/- Proposed as answer by Naomi N Thursday, March 3, 2011 10:34 PM
Thursday, March 3, 2011 7:36 AMAnswerer -
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- Marked as answer by Prasad Yangamuni Thursday, March 3, 2011 9:48 AM
Thursday, March 3, 2011 7:40 AM -
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 | blogThursday, March 3, 2011 9:23 AM