Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Automate INDEX rebuild based on fragmentation results?

Is it possible to add a maintenance job to check indexes fragmentation. If greater than 50% then rebuild those indexes automatically ?

Index size can vary from 100MB to 10GB. SQL 2005.

Thank you.

like image 321
user1372635 Avatar asked May 08 '12 08:05

user1372635


People also ask

How do I automate an index rebuild in SQL Server?

The option is under the Management node of SQL Server management studio. Right-click on Maintenance Plans and select New Maintenance Plan… Provide the appropriate name of the maintenance plan. Drag and drop the index rebuild task from the maintenance plan toolbox.

Does Azure automatically rebuild indexes?

Azure does not support rebuilding indexes using SQL Server Management Studio. It also does not support maintenance plans and does not use a SQL Server Agent. Therefore, a manual approach to rebuilding indexes is required.

Does rebuild index remove fragmentation?

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

How do you calculate index rebuild?

Determining if an Index Needs to Be Rebuilt In Oracle, you can get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command. Here's some sample output from the INDEX_STATS Table: SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE; Statement processed.


1 Answers

I use this script . Please note I would advise you reading up about the dmv I am using here they are a hidden gem in SQL2005+.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes
(
 DatabaseName SYSNAME
 , SchemaName SYSNAME
 , TableName SYSNAME
 , IndexName SYSNAME
 , [Fragmentation%] FLOAT
)

INSERT INTO #FragmentedIndexes
SELECT
 DB_NAME(DB_ID()) AS DatabaseName
 , ss.name AS SchemaName
 , OBJECT_NAME (s.object_id) AS TableName
 , i.name AS IndexName
 , s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
 @RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
 WHEN [Fragmentation%] > 30
   THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
      + QUOTENAME(SchemaName) + '.'
      + QUOTENAME(TableName) + ' REBUILD;'
 WHEN [Fragmentation%] > 10
    THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
    + QUOTENAME(SchemaName) + '.'
    + QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
 PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes

Also keep in mind that this script can run a while and block access to your tables. Unless you have Enterprise editions SQL can LOCK the table when rebuilding the index. This will block all queries to that table using the index till the index defrag is finished. Thus it is not advised to run index rebuild during operational hours only during maintenance windows. If you are running enterprise edition you can use the ONLINE=ON option to defrag indexes online. This will use more space but your tables wont be blocked/locked during the defrag operation.

Shout if you need more information.

UPDATED:

If you are running this query on a smaller database you can probably use the 'DETAILED' parameter in the call to sys.dm_db_index_physical_stats. This is probably a more detailed examination of the indexes. The discussion in the comments will also point out that on much larger tables it is probably worth doing a SAMPLED scan as this will help reduce the time needed to do the index scan.

like image 181
Namphibian Avatar answered Sep 27 '22 02:09

Namphibian