When DML(Insert, Update,Delete) operations are performed on a table then table fragmentation occurs, and this fragmentation degrades the performance of database. So it is necessary to rebuild all indexes to improve the performances of indexes. We will use the "DBCC DBRINDEX" to drop and rebuild all the indexes.
Query:
- EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
- GO
- EXEC sp_updatestats
- GO
Output:
Pankaj Kumar ChoudharyPosted Apr 27, 2016, 6:01 AM
Thanks Manish Kumar for your kind information, i know rebuild the index is not a small task, it is very critical task and also depend upon several other concepts like fragmentation and defragmentation of index but in this blog i only explained how to rebuild the index......
Manish KumarPosted Apr 27, 2016, 5:15 AM
Hi , Nice article. i guess rebuilding an index is a heavy duty process in database means let's say a table has millions of records and with an index. When we rebuild an index it will delete the index and recreated from scratch with entirely new structure .so for a millions of records this will block database resource. Rebuilding of index can be determine with system define function "sys.dm_db_index_physical_stats" which tells us fragmentation % of index. If % falls between 0-10 % then nothing is required. if % falls between 10-30 % then index reorganize is required and if % more then 30 % then we have to do rebuild. More over we have another option for rebuild we can put ONLINE/OFFLINE concept. Regards Manish