Sql-server – Options for Index Defragmentations For Critical Tables

fragmentationsql server

I wonder if this is a suitable solution for index defragmentations.
scenario is like this;

There is a table named 'Events' which is very critical and has 4 indexes which are not done maintenance before and fragmentation rates are around 95. Also table has 450 million rows. There are very much transactions in a minutes and Board can not tolerate to cuts for long time like 30 minute or higher.

I want to create a new table named 'Events_2' and create same indexes and statistics after all process done, I want to drop 'Events' table, and rename 'Events_2' to 'Events'.

Is it a solution for this situation or Index rebuild or Reorganize is the only solution?

Thanks For Reply.

Best Answer

I would not recommend the procedure that you mention in your question. If you did that, the newly built table would end up suffering the same amount of fragmentation as the original and you would be back to square one.

I would recommend using Ola Hallengren's Index and Maintenance scripts to reduce the fragmentation of your indexes:-
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Ola's script are tried and tested, I've used them for a while now and have had absolutely no problems with them.

So, taking example H from Ola's page.

Rebuild or reorganize all indexes with fragmentation on the table Production.Product in the database AdventureWorks:-

EXECUTE dbo.IndexOptimize
@Databases = 'AdventureWorks',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'AdventureWorks.Production.Product

This will REORGANIZE all indexes on the Production.Product table with fragmentation > 5% and < 30%. It will then REBUILD_ONLINE all indexes on the table with fragmentation > 30%.

The values are based on Microsoft's recommendation here:-
http://msdn.microsoft.com/en-us/library/ms189858.aspx

REORGANIZE is an online operation, leaving the table available for read-write operations.

REBUILD ONLINE can be run as you have Enterprise Edition. Quoting from BOL:-

"The ONLINE option allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during these index operations. For example, while a clustered index is being rebuilt by one user, that user and others can continue to update and query the underlying data"

But be aware, it should be called REBUILD NEARLY ONLINE as a S-lock or Sch-M lock is held at the end of the operation which can block certain operations. Also online rebuilds cannot be performed on indexes containing the following data types:- image, ntext, and text.

Have a read through of Microsoft's guidelines for online operations:-
http://msdn.microsoft.com/en-us/library/ms190981.aspx

Once you get familiar with the scripts on a DEVELOPMENT system you can look at implementing on your PRODUCTION server. A proper schedule should be setup in order to keep the amount of REORGANIZE/REBUILD that have to be run to a minimum.

Related Question