IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_DefragSearchIndexes')
	BEGIN
		DROP  Procedure  dbo.proc_DefragSearchIndexes
	END

GO

CREATE PROCEDURE [dbo].[proc_DefragSearchIndexes]
(
	@maxdopLevel int = 0, -- If your system is experiencing high I/O latencies setting this to may help
	@defragThreshold float = 10.0
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @objectid int
    DECLARE @indexid int
    DECLARE @command varchar(8000)
    DECLARE @baseCommand varchar(8000)
    DECLARE @schemaname sysname
    DECLARE @objectname sysname
    DECLARE @indexname sysname
    DECLARE @currentDdbId int
    SELECT @currentDdbId = DB_ID()

    PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Defraging with defragThreshold of:' + CAST(@defragThreshold as nvarchar)
    PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Defraging with maxdopLevel of:' + CAST(@maxdopLevel as nvarchar)
    PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Calculating fragmentation'

	declare @IndexesToDefrag table (objectid int, indexid int, objectname sysname)

	insert into @IndexesToDefrag (objectid, indexid, objectname)
	select 
        i.object_id, 
        i.index_id, 
        i.name
    FROM 
        sys.indexes AS i
    INNER JOIN 
        sys.objects AS o
    ON
        i.object_id = o.object_id
    WHERE 
        i.index_id > 0 AND
        o.type = 'U' and 
		i.index_id in
		(
		SELECT st.index_id 
		FROM sys.dm_db_index_physical_stats (@currentDdbId, NULL, NULL , NULL, 'SAMPLED') st
		WHERE st.object_id = i.object_id and 
		st.avg_fragmentation_in_percent > @defragThreshold)

	DECLARE indexesToDefrag CURSOR FOR 
	select objectid, indexid, objectname
	from @IndexesToDefrag

    OPEN indexesToDefrag
    -- Loop through the partitions.
    FETCH NEXT
    FROM
        indexesToDefrag
    INTO 
        @objectid, 
        @indexid,
        @indexname
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Lookup the name of the index
        SELECT 
            @schemaname = s.name
        FROM 
            sys.objects AS o
        JOIN 
            sys.schemas AS s
        ON
            s.schema_id = o.schema_id
        WHERE
            o.object_id = @objectid

	    PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Working with index: ' + @indexname

		IF @indexname IN ('IX_MSSDocProps'
						, 'IX_MSSDocSdids'
						, 'IX_AlertDocHistory'
						, 'IX_MSSDEFINITIONS_DOCID'
						, 'IX_MSSDEFINITIONS_TERM'
						, 'PK_Sdid'
						, 'IX_SDHash'
                        -- Uncomment either of the indexes below if you know that
                        -- common queries in your environment are property based 
                        -- and will use these indexes. 
						, 'IX_Int'
						--, 'IX_Str'
						, 'IX_DOCID')
		begin
			-- Fragmentation is bad enough that it will be more efficient to rebuild the index
			SELECT @baseCommand = 
				' ALTER INDEX ' + 
					@indexname +
				' ON ' + 
					@schemaname + '.' + object_name(@objectid) + 
				' REBUILD WITH (MAXDOP = ' + CAST(@maxdopLevel as nvarchar) + ', FILLFACTOR = 80, ONLINE = '

			-- Use dynamic sql so this compiles in SQL 2000
			SELECT @command =
				' BEGIN TRY ' + 
				   @baseCommand + 'ON) ' +
				' END TRY ' +
				' BEGIN CATCH ' +
				   -- Indices with image-like columns can't be rebuilt online, so go offline
				   @baseCommand + 'OFF) ' +
				' END CATCH '

			PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Rebuilding'
			EXEC (@command)
			PRINT CONVERT(nvarchar, GETDATE(), 126) + ': Done'
		end
        FETCH NEXT FROM indexesToDefrag INTO @objectid, @indexid, @indexname
    END
    CLOSE indexesToDefrag
    DEALLOCATE indexesToDefrag
    RETURN 0
END