Rebuild index for system database

• juliava

  SSC-Addicted

  Points: 444

  More actions

  August 29, 2023 at 7:55 pm

  #4275066

  I do check database integrity for system db,is there need to do system rebuild index if yes what is fragmentation?

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  August 30, 2023 at 5:23 am

  #4275444

  The only time you might want to do a rebuild on a system (or any other database) is if you need to recover a lot of disk space.  You check for that by looking at the "avg_page_space_used_in_percent" column of sys.dm_db_index_physical_stats.  Using the amount of logical fragmentation to determine when an index needs to be rebuilt is a fallacy EXCEPT for "evenly distributed" indexes, in which case, you need to look for anything over 1% logical fragmentation.

  I'll also add that except for some very special conditions that are too long to get into here, I strongly recommend that you almost never use REORGANIZE.  Using REORGANIZE on most indexes that fragment will perpetuate fragmentation and actually cause the fragmentation to be much worse.

   

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Summer90

  SSC-Dedicated

  Points: 32891

  More actions

  September 20, 2023 at 12:28 pm

  #4290308

  I would not touch tables in master at all personally.   MSDB can get quite large for job history and backup history.

  I use this for msdb:

   

  use msdb

  go

  ALTER INDEX ALL ON [dbo].[sysmail_mailitems]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  GO

  ALTER INDEX ALL ON [dbo].[sysmail_log]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  GO

  ALTER INDEX ALL ON [dbo].[backupset]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  go

  ALTER INDEX ALL ON [dbo].[backupmediafamily]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  go

  ALTER INDEX ALL ON [dbo].[backupmediaset]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  go

  ALTER INDEX ALL ON [dbo].[sysjobhistory]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  GO

  ALTER INDEX ALL ON [dbo].[sysssislog]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  go

  ALTER INDEX ALL ON [dbo].[sysmaintplan_logdetail]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  go

  ALTER INDEX ALL ON [dbo].[backupfilegroup]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  go

  ALTER INDEX ALL ON [dbo].[backupfile]

  REBUILD WITH (SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

  go

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  September 20, 2023 at 8:42 pm

  #4290603

  Summer90 wrote:

  I would not touch tables in master at all personally.   MSDB can get quite large for job history and backup history.

  I wouldn't rebuild indexes that don't need it.  I'm pretty sure I wouldn't  use  STATISTICS_NORECOMPUTE = ON in such a blanket fashion, either.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)