Sunday, May 28, 2017
 
SQL Server Script Repository
Facebook Twitter LinkedIn Google+ Addthis
 
     

SQL Script - Index Fragmentation Management

By Saleem Hakani on 8/24/2014

This procedure allows you to check and fix fragmentation in your databases. Alternatively, it can also provide you with recommendations without fixing fragmentation issues. Please review the help from the Procedure to learn more about various parameters/options it supports.

This Script Provides the following information:
1. Database name in which fragmentation was found, Table Name, Index Name that is fragmented, Percentage of Fragmentation,
2. Recommendation on what to do about fragmentation and script that you can use to individually and manually defrag or rebuild the index.
3. This script is generated based on the recommendations provided by SQLCOMMUNITY. Note: This script does not generate any scripts for heaps.

--Author: Saleem Hakani (Http://sqlcommunity.com)

Create Procedure [dbo].[SQLOPS_DeFragger]

(

@Cmd Varchar(10)=null,

@DBName Varchar(255)=null,

@Action Varchar(10)='Report',

@PercentCheck Int=30,

@MAXDOP int=0,

@Online Varchar(10)='ON'

)

as

Set nocount on

If (@Cmd is null or @Cmd <> 'Execute')

Begin

Print 'SQLOPS_FragCHECK (Author: Saleem Hakani)'

Print '========================================'

Print ' '

Print 'SQLOPS_FragCHECK provides you with Index and Table Fragmentation information from specific or all databases.'

Print ' '

Print 'It Provides the following information:'

Print 'Database name in which fragmentation was found, Table Name, Index Name that is fragmented, Percentage of Fragmentation,'

Print 'Recommendation on what to do about fragmentation and script that you can use to individually and manually defrag or rebuild the index.'

Print 'This script is generated based on the recommendations provided by SQLOPS. Note: This script does not generate any scripts for heaps'

Print ' '

Print 'Syntax: Exec SQLOPS_DeFragger @cmd or @DBName, @Action, @Percent>'

Print '@cmd: ''Help'' for obtaining help for this stored procedure'

Print '@DBName: Can be the name of a valid database or ''ALL'' for all databases'

Print '@Action= ''Report'' to report fragmentation level and all the details'

Print '@Action=''Fix'' to automatically fix fragmentation'

Print '@Percent= by default this SPROC checks for fragmentation over 30%. You can supply a differnt value to check based on supplied percentage'

Print '@Maxdop = Allows you to configure MAX DEGREE OF PARALLELISM VALUE (0 - 16) for INDEX REBUILD by passing a numeric value. (0 = unlimited, 1=serial)'

Print '@Online = Allows you to perform index rebuild operation either offline or online. (Default is Online) (Supported values are: ON or OFF)'

Print ' '

Print 'To Report Index Fragmentation for a Specific Database (ex: SQLOPS database)'

Print 'Exec SQLOPS_DeFragger @Cmd=''Execute'',@DBName=''SQLOPS'''

Print ' '

Print 'To Fix Index Fragmentaiotn for a specific database (ex: SQLOPS database)'

Print 'Exec SQLOPS_DeFragger @Cmd=''Execute'',@DBName=''SQLOPS'', @Action=''Fix'''

Print ' '

Print 'To Fix Index Fragmentaiotn for a specific database with MAXDOP=1'

Print 'Exec SQLOPS_DeFragger @Cmd=''Execute'',@DBName=''SQLOPS'', @Action=''Fix'',@MAXDOP=1'

Print ' '

Print 'To REPORT Index Fragmentation for all databases on the local SQL Server instance (Excludes System Databases)'

Print 'Exec SQLOPS_DeFragger @Cmd=''Execute'',@DBName=''ALL'''

Print ' '

Print 'To Fix Index Fragmentaiotn for ALL databases (excluding system databases)'

Print 'Exec SQLOPS_DeFragger @Cmd=''Execute'',@DBName=''ALL'', @Action=''Fix'''

Print ' '

Print 'To Fix Index Fragmentaiotn OFFLINE for ALL databases (excluding system databases)'

Print 'Exec SQLOPS_DeFragger @Cmd=''Execute'',@DBName=''ALL'', @Action=''Fix'',@Online=''OFF'''

Print ' '

Print 'To get help:'

Print 'Exec SQLOPS_DeFragger ''Help'';'

RETURN

End

Declare @FragTable Table

(

DBName Varchar(255),

TableName Varchar(300),

IndexName Varchar(255),

Fragmented Varchar(4),

Total_Pages BigInt,

IndexType Varchar(50),

SQLOPS_Recommendation Varchar(50),

Script Varchar(4000)

)

If (@Maxdop > 16) or (@Maxdop is null)

Begin

Print 'Invalud @Maxdop Value. Please retry the Stored Procedure by entering valid @MAXDOP value.'

Print 'Valid values are between 0 and 16. (Note: 1=Serialized, 0=Parallelized). Default value is 0.'

Return

End

If (@Online not in ('ON', 'OFF'))

Begin

Print 'Invalid @Online value. Please retry by passing either ''ON''(For Online Operation) or ''OFF''(For Offline operation). Default value is ''ON''.'

Return

End

If (@PercentCheck < 1 or @PercentCheck > 100) or (@PercentCheck is null)

Begin

Print 'Invalid @PercentCheck value. Please retry by passing values between 1 and 100. Default value is 30.'

Return

End

If (@Action not in ('Report', 'Fix'))

Begin

Print 'Invalid @Action value. Please retry by passing either ''REPORT''(To report fragmentation information) or ''FIX''(To Fix Fragmentation). Default Value is ''Report''.'

Return

End

If (@DBName is null)

Begin

Print ' @DBName value is NULL. Please supply a Valid database name or supply ''ALL'' for all databases.'

Return

End

If (@Cmd='Execute' and @DBName='ALL')

Begin

Declare DBNameCur Cursor for Select Name from Master.Sys.Databases where Database_ID > 4 and State_Desc='Online'

Open DBNameCur

Fetch Next from DBNameCur into @DBName

While (@@FETCH_STATUS=0)

Begin

Insert @FragTable

Exec('Use ['+@DBName+']; SELECT DB_Name() as ''DBName'', ss.name+''.''+OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ISNULL(ind.name,''--INDEX NOT AVAILABLE--'') AS IndexName, Convert(varchar,round(indexstats.avg_fragmentation_in_percent,0))+''%'' as ''% Fragmented'',

indexstats.Page_Count, indexstats.index_type_desc AS IndexType,

(Case

when indexstats.index_type_desc <> ''Heap'' and indexstats.avg_fragmentation_in_percent < 30 then ''Defrag Index''

When indexstats.index_type_desc <> ''Heap'' and indexstats.avg_fragmentation_in_percent > 30 then ''Rebuild Index''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent > 30 then ''Create Index''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent < 30 then ''Create Index After 30% Fragmentation''

End) as ''Recommendation'',

(Case

when indexstats.index_type_desc <> ''Heap'' and Indexstats.avg_fragmentation_in_percent < 30 then ''USE ['+@DBName+']; ALTER INDEX [''+ Ind.Name +''] ON [''+ss.name+''].[''+Object_Name(Ind.Object_ID)+''] REORGANIZE WITH (MAXDOP = '+@MAXDOP+'); ''

When indexstats.index_type_desc <> ''Heap'' and Indexstats.avg_fragmentation_in_percent > 30 then ''USE ['+@DBName+']; ALTER INDEX [''+Ind.Name+''] ON [''+ss.name+''].[''+Object_Name(Ind.Object_ID)+''] REBUILD WITH (ONLINE='+@ONLINE+', MAXDOP = '+@MAXDOP+'); ''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent > 30 then ''USE ['+@DBName+']; --CREATE CLUSTERED INDEX ON [''+ss.name+''.''+UPPER(Object_Name(Ind.Object_ID))+''] TABLE AND THEN DROP IT''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent < 30 then ''USE ['+@DBName+'];--CREATE CLUSTERED INDEX ON [''+ss.name+''.''+UPPER(Object_Name(Ind.Object_ID))+''] TABLE AND THEN DROP IT''

End) as ''Script''

FROM sys.dm_db_index_physical_stats(DB_ID('''+@DBName+'''), NULL, NULL, NULL, ''DETAILED'') indexstats

INNER JOIN '+@DBName+'.sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id

INNER JOIN sys.objects SysObjects ON indexstats.object_id = SysObjects.object_id

INNER JOIN sys.schemas ss ON ss.[schema_id] = SysObjects.[schema_id]

WHERE indexstats.avg_fragmentation_in_percent > '+@PercentCheck+'

ORDER BY indexstats.avg_fragmentation_in_percent DESC')

Fetch Next from DBNameCur into @DBName

End

Close DBNameCur

Deallocate DBNameCur

End

If (@DBName <> 'ALL')

Begin

If Not Exists (Select Name from sys.databases where name=@DBName)

Begin

Print 'Database ['+Upper(@DBName)+'] does not exist. Please enter a valid database name.'

Return

End

If Exists (Select Name from sys.databases where name=@DBName)

Begin

Insert @FragTable

Exec('Use ['+@dbName+']; SELECT DB_Name() as ''DBName'', ss.name+''.''+ OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ISNULL(ind.name,''--INDEX NOT AVAILABLE--'') AS IndexName, Convert(varchar,round(indexstats.avg_fragmentation_in_percent,0))+''%'' as ''% Fragmented'',

indexstats.Page_Count, indexstats.index_type_desc AS IndexType,

(Case

when indexstats.index_type_desc <> ''Heap'' and indexstats.avg_fragmentation_in_percent < 30 then ''Defrag Index''

When indexstats.index_type_desc <> ''Heap'' and indexstats.avg_fragmentation_in_percent > 30 then ''Rebuild Index''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent > 30 then ''Create Index''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent < 30 then ''Create Index After 30% Fragmentation''

End) as ''Recommendation'',

(Case

when indexstats.index_type_desc <> ''Heap'' and Indexstats.avg_fragmentation_in_percent < 30 then ''USE ['+@DBName+']; ALTER INDEX [''+Ind.Name+''] ON [''+ss.name+''].[''+Object_Name(Ind.Object_ID)+''] REORGANIZE WITH (MAXDOP = '+@MAXDOP+'); ''

When indexstats.index_type_desc <> ''Heap'' and Indexstats.avg_fragmentation_in_percent > 30 then ''USE ['+@DBName+']; ALTER INDEX [''+Ind.Name+''] ON [''+ss.name+''].[''+Object_Name(Ind.Object_ID)+''] REBUILD WITH (ONLINE='+@ONLINE+', MAXDOP = '+@MAXDOP+'); ''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent > 30 then ''USE ['+@DBName+']; --CREATE CLUSTERED INDEX ON [''+ss.name+''.''+UPPER(Object_Name(Ind.Object_ID))+''] TABLE AND THEN DROP IT''

When indexstats.index_type_desc = ''Heap'' and indexstats.avg_fragmentation_in_percent < 30 then ''USE ['+@DBName+']; --CREATE CLUSTERED INDEX ON [''+ss.name+''.''+UPPER(Object_Name(Ind.Object_ID))+''] TABLE AND THEN DROP IT''

End) as ''Script''

FROM sys.dm_db_index_physical_stats(DB_ID('''+@DBName+'''), NULL, NULL, NULL, ''DETAILED'') indexstats

INNER JOIN '+@DBName+'.sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id

INNER JOIN sys.objects SysObjects ON indexstats.object_id = SysObjects.object_id

INNER JOIN sys.schemas ss ON ss.[schema_id] = SysObjects.[schema_id]

WHERE indexstats.avg_fragmentation_in_percent > '+@PercentCheck+'

ORDER BY indexstats.avg_fragmentation_in_percent DESC')

End

End

If (@Action='Report')

Begin

If (Select Count(*) from @FragTable) = 0

Begin

Print '>= '+Convert(Varchar,@PercentCheck)+'% Index Fragmentation Not Found in ['+@DBName+'] Database. Please try a different database or change @PercentCheck value to a lower value.'

Return

End

Else

Begin

Select * from @FragTable

Return

End

End

If (@Action='Fix')

Begin

Declare @cnt int, @FragStr nVarchar(4000)

Select @cnt=count(*) from @FragTable

Print 'Processing Fragmentation for ['+ltrim(Str(@cnt))+'] Indexes'

Declare CheckFragTable Cursor For Select Script from @FragTable Order by Fragmented

Open CheckFragTable

Fetch Next from CheckFragTable into @FragStr

While (@@FETCH_STATUS=0)

Begin

Print 'EXECUTING: '+@FragStr

Exec SP_ExecuteSQL @FragStr

Fetch Next from CheckFragTable into @FragStr

End

If (@cnt > 0)

Begin

Print 'Completed processing above ['+Ltrim(str(@cnt))+'] indexes'

Print 'Note: SQL Server may prefer to not defrag/rebuild a fragmented index if the size of the index is small.'

End

Close CheckFragTable

Deallocate CheckFragTable

Return

End

GO

 

Alter Index
Index Fragmentation
Index Rebuild
Remove Index Fragmentation
Minimize
Script Categories
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM