locked
Getting Daily Index fragmentation alerts? Need Solution? RRS feed

  • 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
    Thursday, 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
    Thursday, March 3, 2011 7:40 AM

All replies

  • Hi,

    One what conditions you have scheduled your job


    Thanks and regards, Rishabh
    Thursday, 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 M
    Thursday, 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 AM
    Answerer
  • 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
    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 | blog
    Thursday, March 3, 2011 9:23 AM