Maintaining Index on SQL SERVER 2005 - Part 2

If your database has a lot of transaction, the index is fragmented obviosly. And then your database performance is going to slow. So, to overcome the problem, you must maintain your index periodically. Rebuild and Re-organize index might help you to goback to top performance of your database. I have tried and experienced for altering index. That was amazing, cause it helped to boost my database performance.

Rebuild vs Re-organize

What's the difference of these two methods? they are look like the same things but they don't. We use Rebuild Index when the index has more than 30% data is fragmented, and we use Re-organize index when the index has less than 30% data is fragmented. You can see the fragmented index at DBCC SHOWCONTIG (@TABLENAME) or SELECT *FROM sys.dm_db_index_physical_stats(db_id(),Object_ID(N'contracts'),NULL, NULL, 'limited')
For example :

-- Rebuild Index
ALTER INDEX IX_ACCRUED_INCOMES_3 ON
ACCRUED_INCOMES
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = OFF, STATISTICS_NORECOMPUTE = ON)

-- Reorganize Index
ALTER INDEX IX_ACCRUED_INCOMES_3 ON ACCRUED_INCOMES REORGANIZE

Now, I have scheduled the index maintenance daily (i created a job that run at night -SQL Server Idle-), but if you wanna run (index maintenance) the job at the busy time , i suggest you to add options ONLINE = ON. therefore your table can be accessed although the table is locking by maintenance index performance.