Automate showcontig and defragment objects

• lugang

  Right there with Babe

  Points: 752

  More actions

  August 18, 2005 at 10:56 am

  #110591

  I have a database with over 900 tables. I want to monitor and defragment all the tables and index if necessary. I want to have the process in a scheduled job, run every Sunday.

  To do this I am planning to code a stored procedure, which runs DBCC SHOWCONTIG, saves the result in a table, scan the result table for Scan Desity, rebuild index wher Scan Density  <80%.

  The first problem is how to save dbcc showcontig result into a table. In Query Analyzer, this DBCC result is in the Message page, not in Result Grid page, therefore "insert into result_table exec .." does not work.

  The next question is on the best way to rebuld a fragmented table having no clustered index. One can drop and create the table, or create a clustered index and then drop it. Which is better ?

   

  Thanks

• MidwestDBA

  SSC Veteran

  Points: 299

  More actions

  August 18, 2005 at 11:07 pm

  #583397

  While your idea is valid, I think you might not be considering the *big* picture.

  Read the following article, this is good info:

  http://www.quest-pipelines.com/newsletter-v5/0204_B.htm

  There are two statements he makes that you may want to consider when doing your index analysis:

• Be more concerned with large tables/indexes with low scan densities or large drops in average page density than smaller tables/indexes. It takes more page splits to bring the percentages down on a larger index. Remember we're trying to reduce the page splits! A very large index that drops only 2 percent in scan density probably experienced more page splits than a small index that dropped 30 percent in scan density.

• Pay attention to more than just the scan density and average page density results from the SHOWCONTIG. There is a lot of relevant information here.

Although you have 900 tables in your database, I doubt every one of those tables has to be monitored so closely.  In fact I would venture to say that you can list your most highly used on a piece of paper.  As a database administrator, you should be very aware of your "problematic" or highly used tables.  Once you understand your tables you can build them to fulfill your needs.