I agree with Scott Rankin that there are many ways to peel this onion.

What's I've posted below has actually been ripped out of a more complex and permanent solution that I wrote so if you see variables that are hard-coded and only used once that's because those variables would have been set by configuration tables that my system runs off of.

Either way, the positives about this solution are:

-- This version is done with temporary stored procedures so no permanent code needs to be added to your server if you don't want to. (can just as easy remove the ##'s if you want them permanent)

-- Two procs are offered here, one to gather statistics and another to selectively perform maintenance based on flexible standards.

-- Fragmentation level is not the only metric here, this solution also includes page density and will check for old statistics (in case you don't do auto update statistics or if the stats are old but haven't hit your fragmentation thresholds yet).

-- The proc will decide (based on the passed in thresholds) whether to use REORGANIZE or REBUILD.

-- At the end of the script it will return the stats table for you which shows what the indexes looked like before maintenance and there is a IndexStatus table which lets you know which ones had maintenance performed on them. This gives you a glimpse of how bad things were and how much work was accomplished.

/*Author: Derik Hammer - http://www.sqlhammer.com - 01/01/2013*/

IF OBJECT_ID('tempdb..##usp_UpdateIndexStatistics') IS NOT NULL

DROP PROCEDURE ##usp_UpdateIndexStatistics

GO

CREATE PROCEDURE ##usp_UpdateIndexStatistics

(

@FragLimit TINYINT,

@PageSpaceLimit TINYINT,

@StatsExpiration tinyint

)

As

BEGIN

--Set NOCOUNT to improve performance

SET NOCOUNT ON;

--Declare variables

DECLARE @ForceCheckBIT

DECLARE @IndexMaintOptionsID INT

DECLARE @DatabaseName varchar (128)

DECLARE @CheckPeriodicity tinyint

IF OBJECT_ID('tempdb..##Statistics') IS NOT NULL

DROP TABLE ##Statistics

CREATE TABLE ##Statistics

(

[IndexStatisticID] [int] IDENTITY(1,1) NOT NULL,

[DatabaseName] [nvarchar](128) NOT NULL,

[SchemaName] [sysname] NOT NULL,

[TableName] [nvarchar](128) NOT NULL,

[IndexName] [sysname] NOT NULL,

[IndexID] [int] NOT NULL,

[IndexDepth] [tinyint] NULL,

[IndexLevel] [tinyint] NULL,

[PartitionNumber] [int] NULL,

[IndexTypeDesc] [nvarchar](60) NULL,

[AllocUnitTypeDesc] [nvarchar](60) NULL,

[AvgFragmentationPercent] [float] NULL,

[AvgPageSpaceUsedPercent] [float] NULL,

[FillFactor] [tinyint] NOT NULL,

[IsDisabled] [bit] NULL,

[PageCount] [bigint] NULL,

[RecordCount] [bigint] NULL,

[IndexStatus] [varchar](50) NOT NULL,

[InsertDate] [datetime] NULL,

[UpdateDate] [datetime] NULL

)

ALTER TABLE ##Statistics ADD CONSTRAINT PK_IndexMaint_Statistics_IndexStatisticID PRIMARY KEY CLUSTERED

(

[IndexStatisticID] ASC

)

ALTER TABLE ##Statistics ADD CONSTRAINT [DF_IndexMaint_Statistics_IndexStatus] DEFAULT ('Index Maintenance Required') FOR [IndexStatus]

ALTER TABLE ##Statistics ADD CONSTRAINT [DF_IndexMaint_Statistics_InsertDate] DEFAULT (GETDATE()) FOR [InsertDate]

DECLARE Databases_cur CURSOR FAST_FORWARD

FOR

SELECT sysDBs.name

FROM sys.databases sysDBs

WHERE sysDBs.[state] = 0 --Ensure the database is ONLINE to continue.

OPEN Databases_cur

FETCH NEXT FROM Databases_cur INTO @DatabaseName

WHILE ( SELECT fetch_status FROM sys.dm_exec_cursors(@@SPID) WHERE name = 'Databases_cur' ) = 0

BEGIN

--Retrieve index check periodicity settings

SELECT @CheckPeriodicity = NULL

IF @CheckPeriodicity > 0 AND @CheckPeriodicity IS NOT NULL

BEGIN

--Set the periodicity to a negative number for use in the DATEADD function.

SET @CheckPeriodicity = 0 - @CheckPeriodicity

END

IF @CheckPeriodicity IS NULL

BEGIN

--Set ForceCheck when the check periodicity is left NULL indicating a complete recheck on each run.

SET @ForceCheck = 1

END

-- Remove the temp table used to store the index info during the run

IF OBJECT_ID('tempdb..##FragmentationInfo') IS NOT NULL

Begin

EXEC ('Drop Table ##FragmentationInfo')

End

-- Switch to the database to pull the index info from

EXEC ('USE [' + @DatabaseName + ']')

-- Create the table to store the dm_db_index_physical_stats results

EXEC ('CREATE TABLE ##FragmentationInfo

(

[DatabaseName] [nvarchar](128) NULL,

[SchemaName] [sysname] NULL,

[TableName] [nvarchar](128) NULL,

[IndexName] [sysname] NULL,

[IndexID] [int] NULL,

[IndexDepth] [tinyint] NULL,

[IndexLevel] [tinyint] NULL,

[PartitionNumber] [int] NULL,

[IndexTypeDesc] [nvarchar](60) NULL,

[AllocUnitTypeDesc] [nvarchar](60) NULL,

[AvgFragmentationPercent] [float] NULL,

[AvgPageSpaceUsedPercent] [float] NULL,

[FillFactor] [tinyint] NOT NULL,

[IsDisabled] [bit] NULL,

[PageCount] [bigint] NULL,

[RecordCount] [bigint] NULL

)')

-- Load stats for current database from sys.dm_db_index_physical_stats

--- Ensure only base tables are scanned

EXEC ('USE [' + @DatabaseName + '];

Insert Into ##FragmentationInfo

SELECTDB_NAME(INX_Stat.Database_ID) AS [DatabaseName],

INFO_Schema.TABLE_SCHEMA AS [SchemaName],

OBJECT_NAME(INX_Stat.Object_Id) AS [TableName],

INX.name AS [IndexName],

INX_Stat.index_id AS [IndexID],

INX_Stat.index_depth AS [IndexDepth],

INX_Stat.index_level AS [IndexLevel],

INX_Stat.partition_number AS [PartitionNumber],

INX_Stat.index_type_desc AS [IndexTypeDesc],

INX_Stat.alloc_unit_type_desc AS [AllocUnitTypeDesc],

INX_Stat.avg_fragmentation_in_percent AS [AvgFragmentationPercent],

INX_Stat.avg_page_space_used_in_percent AS [AvgPageSpaceUsedPercent],

INX.fill_factor AS [FillFactor],

INX.[is_disabled] AS [IsDisabled],

INX_Stat.page_count AS [PageCount],

INX_Stat.record_count AS [RecordCount]

FROM sys.dm_db_index_physical_stats(DB_ID(''' + @DatabaseName + '''), NULL, NULL, NULL, ''SAMPLED'') INX_Stat

INNER JOIN sys.indexes INX ON (INX.object_id = INX_Stat.object_id) AND (INX.index_id = INX_Stat.index_id)

INNER JOIN INFORMATION_SCHEMA.TABLES INFO_Schema ON OBJECT_ID(INFO_Schema.TABLE_SCHEMA + ''.'' + INFO_Schema.TABLE_NAME) = INX_Stat.object_id

WHERE INX.name IS NOT NULL

AND INFO_Schema.TABLE_TYPE = ''BASE TABLE''

AND INFO_Schema.TABLE_NAME <> ''dtproperties''

ORDER BY INX_Stat.avg_fragmentation_in_percent DESC, INX_Stat.avg_page_space_used_in_percent ASC')

-- Migrate the scan results into the [IndexMaint].[Statistics] table

EXEC ('Insert Into ##Statistics ([DatabaseName],[SchemaName],[TableName],[IndexName],[IndexID]

,[IndexDepth],[IndexLevel],[PartitionNumber],[IndexTypeDesc]

,[AllocUnitTypeDesc],[AvgFragmentationPercent],[AvgPageSpaceUsedPercent]

,[FillFactor],[IsDisabled],[PageCount],[RecordCount])

Select INFO.[DatabaseName],INFO.[SchemaName],INFO.[TableName],INFO.[IndexName],INFO.[IndexID]

,INFO.[IndexDepth],INFO.[IndexLevel],INFO.[PartitionNumber],INFO.[IndexTypeDesc],INFO.[AllocUnitTypeDesc]

,INFO.[AvgFragmentationPercent],INFO.[AvgPageSpaceUsedPercent],INFO.[FillFactor],INFO.[IsDisabled],INFO.[PageCount],INFO.[RecordCount]

From ##FragmentationInfo INFO

-- Only add new indexes to the table

Left Join ##Statistics INDX

On (INDX.DatabaseName = ''' + @DatabaseName + '''

And INFO.TableName = INDX.TableName

And INFO.IndexName = INDX.IndexName)

Where LEN(INFO.IndexName) > 0

And (INFO.IndexID > 0 And INFO.IndexID < 255)

And INDX.IndexStatisticID IS NULL')

-- Cleanup the temp table used to store the information

EXEC ('Drop Table ##FragmentationInfo')

-- Check the indexes and set the IndexStatus based on the settings, set the good indexes

Update ##Statistics

Set IndexStatus = 'Index is Optimal'

WHERE DatabaseName = @DatabaseName

And (

(

AvgFragmentationPercent <= @FragLimit --Flag for optimal if the fragmentation is below the limit.

And

((AvgPageSpaceUsedPercent / (CASE [FillFactor] WHEN 0 THEN 100 ELSE [FillFactor] END) * 100) >= @PageSpaceLimit) -- Only flag as optimal if the space used percentage is above the limit adjusted for fill factor.

AND

AvgPageSpaceUsedPercent <= (CASE [FillFactor] WHEN 0 THEN 100 ELSE [FillFactor] END) --REBUILD if the space used is above the fill factor. This is the only way to allocate the space.

)

OR RecordCount = 0-- If there are no rows, there''s no need to reindex

OR

(

AvgFragmentationPercent <= @FragLimit

AND

[PageCount] <= 5

) --Don't perform maintenance on extremely small tables unless they are fragmented. AvgPageSpaceUsedPercent is not a useful metric with such small tables.

)

And IndexStatus = 'Index Maintenance Required'

-- Check the statistics last updated date and set the IndexStatus based on the settings

EXEC ('USE [' + @DatabaseName + '];

Update ##Statistics

Set IndexStatus = ''Statistic Maintenance Required''

WHERE DatabaseName = ''' + @DatabaseName + '''

AND IndexName IN (SELECTINDX.name AS IndexName

FROMsys.indexes INDX

INNER JOIN sys.objects OBJS ON INDX.object_id = OBJS.object_id

INNER JOIN ##Statistics IXSTAT ON IXSTAT.IndexName = INDX.name

AND OBJS.object_id = OBJECT_ID(( QUOTENAME(LTRIM(RTRIM(IXSTAT.SchemaName)))

+ ''.''

+ QUOTENAME(LTRIM(RTRIM(IXSTAT.TableName)))))

WHEREOBJS.type = ''U'' --Ensure they are user tables only.

AND DATEDIFF(dd, ISNULL(STATS_DATE(INDX.OBJECT_ID, index_id), OBJS.create_date), GETDATE()) > ' + @StatsExpiration + ') --Ensure that their stats are out of date based on configuration setting.

AND IndexStatus != ''Index Maintenance Required'' --Do not mark for stats update when already marked for index maintenance because we use auto-update stats as a policy

--and index maintenance will do a better job updating the statistics than UPDATESTATISTICS will due to the sample size.

AND RecordCount != 0');

FETCH NEXT FROM Databases_cur INTO @DatabaseName

END

CLOSE Databases_cur

DEALLOCATE Databases_cur

SET NOCOUNT OFF

End

GO

/**************************************************************************************/

/**************************************************************************************/

/**************************************************************************************/

EXEC ##usp_UpdateIndexStatistics

@FragLimit = 20

, @PageSpaceLimit = 80

, @StatsExpiration = 7

--SELECT * FROM ##Statistics

/**************************************************************************************/

/**************************************************************************************/

/**************************************************************************************/

IF OBJECT_ID('tempdb..##usp_SelectiveReindex') IS NOT NULL

DROP PROCEDURE ##usp_SelectiveReindex

GO

CREATE PROCEDURE ##usp_SelectiveReindex

(

@ExecuteWindowEnd TINYINT = NULL -- End hour, for when to stop processing

, @MaxDefrag TINYINT -- Maximum fragmentation before defraging will become rebuild

, @FragLimit TINYINT

, @PageSpaceLimit TINYINT

)

As

Begin

SET NOCOUNT ON

DECLARE @DatabaseIDint

DECLARE @ExecStatementvarchar(4000)

DECLARE @ExecMessagevarchar(4000)

DECLARE @IndexStatisticIDint

DECLARE @ObjectIDint

DECLARE @TableNamesysname

DECLARE @IndexIDint

DECLARE @IndexNamesysname

DECLARE @FillFactortinyint

DECLARE @Fragmentationtinyint

DECLARE @PageSpaceUsedtinyint

DECLARE @LastErrorint

DECLARE@DatabaseNamesysname

DECLARE @IndexMaintOptionsID INT

DECLARE @IndexStatusVARCHAR(50)

DECLARE Databases_cur CURSOR FAST_FORWARD

FOR

SELECT sysDBs.name

FROM sys.databases sysDBs

WHERE sysDBs.[state] = 0 --Ensure the database is ONLINE to continue.

OPEN Databases_cur

FETCH NEXT FROM Databases_cur INTO @DatabaseName

WHILE ( SELECT fetch_status FROM sys.dm_exec_cursors(@@SPID) WHERE name = 'Databases_cur' ) = 0

BEGIN

-- delete the temp table if it still exists

IF OBJECT_ID('tempdb..#IndexCheck') IS NOT NULL

Begin

DROP TABLE #IndexCheck

End

-- Create the table to store the results of checking to see if the index exists

CREATE TABLE #IndexCheck ([id] int, [type] varchar(50))

--Loop through each record to perform maintenance, until there are no more indexes needing maintenance or the execution window ends.

-- -- A while loop was added instead of a cursor because a cursor's data set is static and in this case a single index would have

-- -- been reorged or rebuilt more than once. In this case when maintenance has been conducted on an index all of it's associated

-- -- records are updated with 'Index Optimized' and the WHILE loop will do another SELECT TOP 1 which won't include the duplicate rows.

-- -- The duplicate rows weren't removed from the result set because each index level has different frag levels and page density.

WHILE EXISTS(

Select TOP 1 ISTAT.IndexStatisticID

From ##Statistics ISTAT

--Inner Join (Select MAX(IndexStatisticID) As IndexStatisticID

--From [IndexMaint].[Statistics]

--Where DatabaseName = @DatabaseName

--Group By OBJECT_ID(TableName), TableName, IndexID, IndexName) UIDX

--On (ISTAT.IndexStatisticID = UIDX.IndexStatisticID)

Where DatabaseName = @DatabaseName

And (IndexStatus = 'Index Maintenance Required'

OR IndexStatus = 'Statistic Maintenance Required')

And LEN(LTRIM(RTRIM(IndexName))) > 0 -- Needs to have an index name...

And (IndexID > 0 And IndexID < 255)

And RecordCount > 0-- If there are no rows, there's no need to reindex

)

Begin

-- clear the index check table

Delete From #IndexCheck

Select TOP 1

@IndexStatisticID = ISTAT.IndexStatisticID

, @ObjectID = OBJECT_ID(TableName), @TableName = (QUOTENAME(LTRIM(RTRIM(SchemaName))) + '.' + QUOTENAME(LTRIM(RTRIM(TableName))))

, @IndexName = LTRIM(RTRIM(IndexName)), @FillFactor = [FillFactor]

, @Fragmentation = [AvgFragmentationPercent], @PageSpaceUsed = AvgPageSpaceUsedPercent

, @IndexStatus = IndexStatus

From ##Statistics ISTAT

--Inner Join (Select MAX(IndexStatisticID) As IndexStatisticID

--From [IndexMaint].[Statistics]

--Where DatabaseName = @DatabaseName

--Group By OBJECT_ID((QUOTENAME(LTRIM(RTRIM(SchemaName))) + '.' + QUOTENAME(LTRIM(RTRIM(TableName))))), TableName, IndexID, IndexName) UIDX

--On (ISTAT.IndexStatisticID = UIDX.IndexStatisticID)

Where DatabaseName = @DatabaseName

And (IndexStatus = 'Index Maintenance Required'

OR IndexStatus = 'Statistic Maintenance Required')

And LEN(LTRIM(RTRIM(IndexName))) > 0 -- Needs to have an index name...

And (IndexID > 0 And IndexID < 255)

And RecordCount > 0-- If there are no rows, there's no need to reindex

-- Verify the index exists by retrieving the ids from the source database

Insert Into #IndexCheck ([id], [type])

EXEC ('USE [' + @DatabaseName + '];

-- verify the table exists (Row 1)

Select [id], ''Table''

From dbo.sysobjects obj

Where id = OBJECT_ID(RTRIM(''' + @TableName + '''))

And OBJECTPROPERTY(id, N''IsUserTable'') = 1

Union All

-- verify the index exists (row 2)

Select [object_id]

, Case When allow_page_locks = 0 Then ''Index - No Page Lock'' Else ''Index'' End

From sys.indexes

Where name = RTRIM(''' + @IndexName + ''')

And [object_id] = OBJECT_ID(RTRIM(''' + @TableName + '''));')

-- Verify the IndexCheck table has two records

IF (Select COUNT([id]) From #IndexCheck) = 2

BEGIN

IF @IndexStatus = 'Statistic Maintenance Required'

BEGIN

SET @ExecStatement = 'UPDATE STATISTICS ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' ' + QUOTENAME(@IndexName) + ' WITH FULLSCAN;'

SET @ExecMessage = '-- Executing UPDATE STATISTICS ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' ' + QUOTENAME(@IndexName) + ' WITH FULLSCAN;'

END

IF @IndexStatus = 'Index Maintenance Required'

BEGIN

-- Determine if the index should be defragmented or reindexed

IF (@Fragmentation > @MaxDefrag) OR (((@PageSpaceUsed / (CASE @FillFactor WHEN 0 THEN 100 ELSE @FillFactor END) * 100) > 100))

Or EXISTS(Select 1 From #IndexCheck Where [type] = 'Index - No Page Lock')

Begin

SET @ExecStatement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REBUILD;';

SET @ExecMessage = '-- Executing ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REBUILD;'

End

ELSE

Begin

SET @ExecStatement = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REORGANIZE;';

SET @ExecMessage = '-- Executing ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@DatabaseName) + '.' + @TableName + ' REORGANIZE;'

End

END

-- See if the statement should be displayed or executed

--IF @Debug = 1

--Begin

--Select @ExecMessage As ExecMessage, @ExecStatement As ExecStatement

--End

--ELSE

--Begin

-- Execute the statment for the index

EXEC (@ExecStatement)

Set @LastError = @@ERROR

-- Update the Index Statistics table with the new status.

-- -- Update all rows for the given index so that maintenance isn't performed excessively.

Update ##Statistics Set

IndexStatus = CASE When Not @LastError = 0 Then 'Error: ' + CAST(@LastError AS NVARCHAR(8))

Else 'Index Optimized' End

, UpdateDate = GETDATE()

WHERE IndexName = @IndexName

AND DatabaseName = @DatabaseName

AND (QUOTENAME(LTRIM(RTRIM(SchemaName))) + '.' + QUOTENAME(LTRIM(RTRIM(TableName)))) = @TableName

--End

End

ELSE

Begin

-- print a message explaining the current index counldn't be found

print 'Index doesn''t exist!'

-- Log that the index couldn't be found

Update ##Statistics Set

IndexStatus = 'Index doesn''t exist!'

Where IndexStatisticID = @IndexStatisticID

End

-- Check to see if the process should exit due to time

IF Not @ExecuteWindowEnd = Null

Begin

IF DATEPART(hour, GETDATE()) >= @ExecuteWindowEnd

Begin

Goto EXECUTION_WINDOW_END

End

End

END -- While Loop

-- End of execution

EXECUTION_WINDOW_END:

-- delete the temp table if it still exists

IF OBJECT_ID('tempdb..#IndexCheck') IS NOT NULL

Begin

DROP TABLE #IndexCheck

End

FETCH NEXT FROM Databases_cur INTO @DatabaseName

END

CLOSE Databases_cur

DEALLOCATE Databases_cur

SET NOCOUNT OFF

End

GO

/**************************************************************************************/

/**************************************************************************************/

/**************************************************************************************/

EXEC ##usp_SelectiveReindex

@ExecuteWindowEnd = NULL -- End hour, for when to stop processing

, @MaxDefrag = 50 -- Maximum fragmentation before defraging will become rebuild

, @FragLimit = 20

, @PageSpaceLimit = 80

SELECT * FROM ##Statistics

/**************************************************************************************/

/**************************************************************************************/

/**************************************************************************************/

IF OBJECT_ID('tempdb..##Statistics') IS NOT NULL

DROP TABLE ##Statistics

IF OBJECT_ID('tempdb..##usp_SelectiveReindex') IS NOT NULL

DROP PROC ##usp_SelectiveReindex

IF OBJECT_ID('tempdb..##usp_UpdateIndexStatistics') IS NOT NULL

DROP PROCEDURE ##usp_UpdateIndexStatistics