DB Reindexing script makes the system down | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DB Reindexing script makes the system down

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.
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.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |