SQL Server Performance

DB Reindexing script makes the system down

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by rahul_in05, Apr 14, 2011.

  1. rahul_in05 New Member

    Hi,I am running the following scripts to reindex my database tablessp_MSforeachtable @command1="print '?' dbcc dbreindex ('?')"thensp_msforeachtable 'update statistics? with fullscan'to update the stats.During the execution of first script it hangs down my production server for about 1 hour.Can anyone tell me any alternate for this so that my production sever does not stop responding during re indexing?Thanks in advance.
  2. Luis Martin Moderator

    Welcome to the foruma!!
    Did you run dbreindex on working hours?.
    If yes, that is why your server hangs (locking).
    You can run that in windows time or you can use from BOL:
    To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause. This statement replaces the DBCC INDEXDEFRAG statement. To reorganize a single partition of a partitioned index, use the PARTITION clause of ALTER INDEX.
    Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.
    Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.
    The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates.
    Reorganize an index when the index is not heavily fragmented. See the previous table for fragmentation guidelines. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index.
    Also if you use rebuild option, then update statistics is unnecesary.

Share This Page