none
Index defrag and Update stats for CONTENT DATABASE

    Question


  • Hi,

    We have Share Point 2007 SP1 databases on SQL Server 2005 EE x64 with SP3. Currently, we are doing Index defrag on weekly basis.

    We did the Index defrag analysis for one week daily and the below indexes are defragmenting daily for the Content database.

    Here, we have considered page>500 & avg_fragmentation_in_percent>10



    Query used to find Index defrag:

    [quote]SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats (7, NULL, NULL, NULL, 'limited')
    where avg_fragmentation_in_percent>10 AND INDEX_ID>0 AND page_count>500[/quote]


    7    53575229    1    CLUSTERED INDEX            28.7469287469287    5291
    7    53575229    2    NONCLUSTERED INDEX            66.4449371766445    2706
    7    277576027    1    CLUSTERED INDEX            77.4237288135593    1475
    7    277576027    2    NONCLUSTERED INDEX            77.6606954689146    949
    7    277576027    3    NONCLUSTERED INDEX            77.1008403361345    952
    7    277576027    4    NONCLUSTERED INDEX            78.1052631578947    950
    7    485576768    1    CLUSTERED INDEX            11.632860040568      9860
    7    485576768    2    NONCLUSTERED INDEX            22.694087403599            9725
    7    1058102810    CLUSTERED INDEX            13.4649910233393    1114
    7    1365579903        CLUSTERED INDEX            21.6931831378469        8611

    The  tables getting fragmented on daily are as below:


    select object_name(53575229) --AllDocs

    select object_name(277576027) --AllDocStreams

    select object_name(485576768) --AllLinks

    select object_name(1365579903) --AllUserdata

    select object_name(1058102810) --EventCache

    Because we are facing  blocking problem for the content database and it might be due to index fragmentation right?

    Is that blocking is due to index fragmentation? please advice?

    In this case, do we need to Index defrag on daily basis?

    After Index defrag on daily basis, do we need to also Update the statistics?

    or it's OK to perform Index defrag weekly even if the indexes are getting defragged daily as above?

    Thanks
    Wednesday, August 04, 2010 4:54 AM

All replies

  • Could you plz advice me..

     

    Thanks

    Friday, August 06, 2010 5:12 PM
  • Can you upgrade to SharePoint 2007 SP2?  The database timer job includes an index defrag in it.  Also, reading or writing to SharePoint databases will throw you into an unsupported state.


    http://sharepoint.nauplius.net
    Friday, August 06, 2010 5:35 PM
  • How would you know whether the indexes are got fragmented or not without querying the fragmentation level using the below query:

    SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count
    FROM sys.dm_db_index_physical_stats (7, NULL, NULL, NULL, 'limited')
    where avg_fragmentation_in_percent>10 AND INDEX_ID>0 AND page_count>500


    Question:

    Using the above query to find the Fragmentation level for Share point databases is UNSUPPORTED by Microsoft??

    Please advice

     

    Thanks

    Friday, August 06, 2010 5:51 PM
  • ANY query to a SharePoint database outside of the SharePoint API/WebService can throw you into an unsupported state.  Please see: http://msdn.microsoft.com/en-us/library/bb861829%28office.12%29.aspx

    Correct in that you would not know the fragmentation level if you did not run the query.


    http://sharepoint.nauplius.net
    Friday, August 06, 2010 5:53 PM
  • Trevor S. _  is correct.  Technically you should not be monkeying with your SharePoint DBs.  That said it has always been fully supported to run SQL 2005 maintenance plans.  If you think you have fragmentation, then run a reindex job.  Better yet, again Trevor is correct that there is now a timer job in SP2 that will handle this for you

    Sunday, August 08, 2010 12:04 AM
  • That means we need to blindly perform Index reorg/rebuild for all Share point databases regardless of whether  the indexes are really got fragmented or not?

     

    And blindly schedule  Index defrag daily or weekly without knowing/having Index fragmentation values?

     

    Is that Microsoft wants do? This NOT makes sense for me..

     

    I appreciate, the DBA's who maintaining Share point databases  respond to this thread and share their experience on how they are dealing with Index fragmentation

     

    Many thanks

    Sunday, August 08, 2010 8:49 AM
  • I appreciate, the DBA's who maintaining Share point databases  respond to this thread and share their experience on how they are dealing with Index fragmentation

    Hi Ereti,

    I wrote a blog post a while back which details some information regarded supported operations on MOSS databases at http://mossblogger.blogspot.com/2010/06/administration-supported-database.html

    Microsoft published a very useful white paper back in November 2009 called "Database Maintenance for Office SharePoint Server 2007" which on the whole is still relevant.

    WRT your question above I would recommend following the advice that others have provided and upgrade to MOSS SP2 - the included timer job should settle most of your concerns around DB fragmentation.


    Benjamin Athawes
    Twitter
    SharePoint Blog

    • Proposed as answer by Matt E Thursday, August 12, 2010 11:06 PM
    Monday, August 09, 2010 12:59 AM
  • Thanks Banjiman,

    We are also using Quest Spotlight SQL Server Monitoring tool, to monitor the SQL Server instance hosting the Share point databases.

    In this case, obviously any 3rd party monitoring tool will query the databases to get the up to date performance data and its continuously queries.

    So does using a monitoring tool to monitor Share point SQL instance also UNSUPPORTED by Microsoft?

    Please advice?

    Thanks

    Thursday, August 12, 2010 3:55 PM
  • The support policy is there as a means of strongly discouraging changes to the MOSS content databases.

    In practise, reading the database is not likely to cause any issues but it is still not generally recommended.

    Your support query around the Quest tool is probably best answered by them. 

    Given Quest's good reputation within the SharePoint community I doubt you will have any problems around support.

    HTH.


    Benjamin Athawes
    Twitter
    SharePoint Blog

    Thursday, August 12, 2010 4:10 PM
  • Benjamin is right.  Quest works with Microsoft on guidance when building features in it's products.  Administrators and Developers should stay out of the database, that's definitely clear.  Microsoft has provided protocol documentation for vendors which provides an added layer of working with the product with tools including front end and backend guidance.  It's still extremely important for customers to avoid the database.

    If you have any issues Quest support is there for any of the Quest tools.  Quest is a Microsoft Gold Partner and works very closely with Microsoft to provide the best solutions.

    If you do ever need access to a database you should make a copy of it.  Obviously in the case of fragmentation that doesn't make as much sense, but I agree with the thread above.  Upgrading to post SP2 is the best way to address what you're seeing as you'll get the benefit of the built in timer jobs.  You'll also enjoy other fixes and features in SP2 which relate to fixes in SQL.

    Joel

    • Proposed as answer by Matt E Thursday, August 12, 2010 11:06 PM
    Thursday, August 12, 2010 5:06 PM
  • Joel, i appreaciated both your post on your site and the comment here. It seems to be right on target ( and why wouldnt it be, from an Ex. :) ).

    But i have a further question for you or others about the maintenance jobs referred by both you and Benjamin. On the technet site for the database manintence jobs there were a couple of posts stating that it is not a good practice to AUTOGROW a database. this seems to be in line with SQL best practices.

    What is your take/viewpoint on this?

    Thursday, August 12, 2010 11:06 PM
  • It all depends on your needs.  In many situations autogrow can be fine, in some situations (e.g. heavy disk workload), it can be unacceptable.
    http://sharepoint.nauplius.net
    Thursday, August 12, 2010 11:07 PM
  • But i have a further question for you or others about the maintenance jobs referred by both you and Benjamin. On the technet site for the database manintence jobs there were a couple of posts stating that it is not a good practice to AUTOGROW a database. this seems to be in line with SQL best practices.

    When you consider the default autogrowth options for MOSS content databases you will likely realise why it is not best practise to leave them as-is.

    Note that you should NOT turn autogrowth off for safety reasons, but you should pre-grow for performance reasons.

    The default for the DB itself it 1MB with unrestricted growth. The log will grow in 10% increments.

    Even in a low usage collaboration environment, you are likely to hit 1MB very quickly, facilitating an autogrowth during which time data cannot be written to the content DB. If you have ever wondered why the "server is processing files" for so long in MOSS after a multi-file upload this is one of the reasons.

    As recommended by MS, leave autogrowth enabled (for safety reasons), but pre-grow your database (and log) based on anticipated disk usage.

    Physical storage recommendations (Office SharePoint Server)


    Benjamin Athawes
    Twitter
    SharePoint Blog

    Thursday, August 12, 2010 11:20 PM
  • Thanks, i see your point....

    its not a bad idea, its just a bad idea to use it as the only mechanism for growth, especially for a large environment.

    I love the SharePoint Community!

    Thursday, August 12, 2010 11:45 PM