Automate INDEX rebuild based on fragmentation results?

Question

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.

You must rebuild indexes fragmented higher than 30%. Yes it very possible using the sql dmv. Not at my pc now will post a script later. — Namphibian, May 08 '12 at 08:51

Namphibian · Accepted Answer · 2012-05-08T10:14:06.567

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.

If you use SAMPLED, the nonleaf levels of indexes are not processed. form BOL: Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Results in SAMPLED mode should be regarded as approximate — Diego, May 08 '12 at 09:54
meaning that if you use SAMPLED you must be aware that you may not get a accurate result — Diego, May 08 '12 at 09:55
Indeed but 1% of a 500 000 000 row table is 5 000 000. Which in some cases is good enough. It really depends on the size of the index and the window you have to do defragmentation. — Namphibian, May 08 '12 at 10:10
I'm using this with Azure SQL, which supports ALTER INDEX REBUILD WITH (ONLINE=ON) in every edition. I also removed the the second part of the case statement that uses REORGANIZE. The REORGANIZE modifier for ALTER INDEX is not currently supported by Azure SQL. You may also want to add a try/catch block so that those tables that do not support online indexing don't cause the entire operation to fail. — NovaJoe, Jan 19 '15 at 19:01

score 3 · Answer 2 · edited Dec 08 '15 at 10:20

In case, you were thinking of avoiding to create any temp tables and parsing the string to create a list of SQL strings. Here is an efficient way of accomplishing it:

USE databasename

GO

DECLARE @Queryresult NVARCHAR(4000)

SET @Queryresult=''

SELECT

  @Queryresult=@Queryresult + 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON '
  + QUOTENAME('dbo') + '.'
  + QUOTENAME(OBJECT_NAME(i.OBJECT_ID)) + ' REBUILD;'

 FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ss

  INNER JOIN sys.indexes i ON i.OBJECT_ID = ss.OBJECT_ID AND i.index_id = ss.index_id

  INNER JOIN sys.objects o ON ss.object_id = o.object_id

WHERE ss.avg_fragmentation_in_percent > 50

AND ss.record_count > 0 

AND o.is_ms_shipped = 0 --Excludes any objects created as a part of SQL Server installation

AND ss.index_id > 0     --Excludes heap indexes

EXEC sp_executesql @Queryresult

score 1 · Answer 3 · answered May 08 '12 at 08:52

1

yes, you can.

You can get the fragmented indexes using this query:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

and based on the result just build a command to recreate them

I would wrap everything on a Stored Procedure and call it from a SQL Server Job

FYI, 50% is a very big fragmentation. I would go with less.

answered May 08 '12 at 08:52

Diego

34,802
21
91
134

the sys.dm_db_index_physical_stats function called with the 'DETAILED' parameter can be a bit of a killer to run once you have data in the 500 000 000+ row count. When dealing with much bigger tables the SAMPLED parameter can help reduce the time spent analysing the index and you can actually finish the index rebuild on time. Just a note I like the fact that you actually know the DMV's it is a black belt kung fu art! – Namphibian May 08 '12 at 09:18
If you use SAMPLED, you may not get a accurate result because you will be scaning only the leaf levels of the index: Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Results in SAMPLED mode should be regarded as approximate – Diego May 08 '12 at 09:56
Indeed but if you need to defrag a 100 000 000 row table in a 2 hour window sometimes you need make a trade off. Just a note that if you are defragmenting large tables that need to remain online and available this might be the solution. Note that using SAMPLED also has the following effect If the index or heap has fewer than 10,000 pages a DETAILED parameter is used. I just found that most of the time this actually allows you to get things done during a maintenance window. – Namphibian May 08 '12 at 09:59

score 1 · Answer 4 · answered May 08 '12 at 08:52

1

You can use sys.dm_db_index_physical_stats to get information about your index fragmentation (see the avg_fragmentation_in_percent column). Then you can do an alter index with the rebuild clause whenever your threshold is reached.

answered May 08 '12 at 08:52

David Brabant

41,623
16
83
111

Automate INDEX rebuild based on fragmentation results?

4 Answers4

Linked