none
Defragment Sql 2008 System Databases

    Question

  • The query (for our sql 2008 master database)

    SELECT * FROM sys.dm_db_index_physical_stats(1, NULL, NULL, NULL, NULL);

    shows an average clustered index fragmentation of 75%.

    Isn't this number too high? If so, how can I reduce this index fragmentation? 

    TIA,

    edm2

    Wednesday, July 10, 2013 10:23 PM

Answers


  • I didn't know that. (Also, the page count for the index was about 18).  I ran DBCC CHECKDB on master and msdb and both completed without error.

    We just installed a network monitoring tool and it describes this very sql server as in the "critical" zone saying that the "Full Scans/sec"  performance counter for the master database is over "100% of where it should be.  (That's why I thought we may have to reindex this system DB).


    Hi,

    I dont recommend running Index defrag on Master DB,also since index has only 18 pages ,even if u rebuild it its most likely that fragmentation will not disappear Fragmentation with page count more than 1000 should cause u worry anything less than that i dont think its issue.

    You can run Index defrag on MSDB if it stores lot amount of backup history and job history.

    Also keep in mind do not always apply suggestions recommended by third party tool immediately.I have seen where TP tool recommending index on particular table has actually caused query plan to be more worse(just givin u ex).


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by edm2 Thursday, July 11, 2013 6:05 AM
    Thursday, July 11, 2013 5:13 AM
    Moderator

All replies

  • Hello,

    If avg_fragmentation_in_percent > 30%, it is recommended using ALTER INDEX REBUILD to correct the fragmentation.This is replacement for DBCC DBREINDEX to rebuild the index online or offline.
    Reference:http://technet.microsoft.com/en-us/library/ms189858.aspx

    However, for system database, there is no need to rebuild or reorganizse indexes for system databases. You only need to do consistency check (for example, DBCC CheckDB).
    You can refer to the following thread about similar issue:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ebc2d99d-6e91-49e5-99f7-2c4b92b6287b/how-often-should-system-databases-be-rebuilt-reorganized-

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support


    Thursday, July 11, 2013 1:48 AM
    Moderator
  • Hi,

    We need to consider the page count as well while rebuilding indexes. What is the page count for the index?


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Thursday, July 11, 2013 1:54 AM
    Answerer
  • Fanny,

    >>>> However, for system database, there is no need to rebuild or reorganizse indexes for system databases. You only need to do consistency check (for example, DBCC CheckDB).

    >>>>

    I didn't know that. (Also, the page count for the index was about 18).  I ran DBCC CHECKDB on master and msdb and both completed without error.

    We just installed a network monitoring tool and it describes this very sql server as in the "critical" zone saying that the "Full Scans/sec"  performance counter for the master database is over "100% of where it should be.  (That's why I thought we may have to reindex this system DB).

    I'll see what Profiler shows.

    edm2


    • Edited by edm2 Thursday, July 11, 2013 2:47 AM edit
    Thursday, July 11, 2013 2:47 AM

  • I didn't know that. (Also, the page count for the index was about 18).  I ran DBCC CHECKDB on master and msdb and both completed without error.

    We just installed a network monitoring tool and it describes this very sql server as in the "critical" zone saying that the "Full Scans/sec"  performance counter for the master database is over "100% of where it should be.  (That's why I thought we may have to reindex this system DB).


    Hi,

    I dont recommend running Index defrag on Master DB,also since index has only 18 pages ,even if u rebuild it its most likely that fragmentation will not disappear Fragmentation with page count more than 1000 should cause u worry anything less than that i dont think its issue.

    You can run Index defrag on MSDB if it stores lot amount of backup history and job history.

    Also keep in mind do not always apply suggestions recommended by third party tool immediately.I have seen where TP tool recommending index on particular table has actually caused query plan to be more worse(just givin u ex).


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by edm2 Thursday, July 11, 2013 6:05 AM
    Thursday, July 11, 2013 5:13 AM
    Moderator
  •  Hi,

    Yes you can but until unless if there is an specific reason that we need to be, but other Optimization like

    checkdb we may need to run to ensure for consistency, also purging & other things we can include for system DB(for ex- MSDB->cleanup task or purging or cleanup history -so here might be we can rebuild the index for MSDB if needed but be caution).

    Usually we have tebuild index job scheduled for system database's as well , as we didnt see any issues with it.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Thursday, July 11, 2013 5:21 AM