Auto Manage Indexes

DBA In A Box we save you money
SQL server database monitoring, troubleshooting, and analyzing software with tools designed to scan, alert, and investigate SQL server failures, performance, SOX compliance, replication failures and much more.

Home
About Us
Screen Shots
Contact Us
Downloads

SQL Scripts
Useful scripts
Auto Reindex
Database Mirroring
SQL Performance
Check Security

How to Auto Create Indexes and Drop un-used indexes from your databases in SQL Server 2005

Outlined here are the steps you need to take to create a scheduled job that will automatically create new indexes where needed, and drop obsolete indexes. The Data Management Views (DMV’s) will be used to determine what indexes to add and to drop. The DBA has a high level of control over how this is accomplished through variables being passed from the stored procedures you will create. As with all new stored procedures TEST TEST TEST, you may need to make alterations to the procedures to work in your environment.

Cautionary notes:
1.    Large indexes can take a while to build and slow performance.
2.    Setting the options incorrectly could cause someones favorite index to be dropped.

Recommendations on startup:
1.    Do several Test runs with the ListOnly flag set to True, this will tell you what will be added or created without actually doing the deed.
2.    Set your values high for minimal impact for the first few weeks until you feel you understand the impact of all the options.
3.    Run only after working hours and not right before everyone shows up for work.

The objects being created:
1.    Database AdminData: This contains one table.
2.    Table IDX_History: Used to record everything dropped and added.
3.    Function: MyCompany_split.
4.    Procedure: MyCompany_Build_New_Indexes.
5.    Procedure: MyCompany_Delete_UnUsed_Indexes.
6.    Schedule: Maint_Index_Optimize.

Step 1: Create an empty database AdminData or use any name you like the point is you need a location for the IDX_History table and dbo split function.

Step2: Replace the name "Acme" with your companies name in all the following code.

Step3: Create the IDX_History Table. This table records all indexes added and deleted should you need to resolve an issue.

CREATE TABLE [dbo].[IDX_History](
    [dbid] [smallint] NOT NULL,
    [IndexName] [nvarchar](150) NOT NULL,
    [TableName] [nvarchar](150) NOT NULL,
    [InsertDate] [smalldatetime] NULL,
    [DeleteDate] [smalldatetime] NULL,
    [Keys] [nvarchar](2000) NULL,
CONSTRAINT [PK_IDX_History] PRIMARY KEY CLUSTERED
(
    [dbid] ASC,
    [IndexName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 4: Create the Acme.dbo.split function: This is used to parse csv data.

USE [AdminData]
GO
/****** Object: UserDefinedFunction [dbo].[Acme_Split]     Script Date: 02/24/2012 15:44:54 *****
I do not take credit for this, originally labeled dbo.split from the web
*/
CREATE FUNCTION [dbo].[Acme_Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
    declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
            set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)

        set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end

Step 5: Create the Stored Procedure Acme_Build_New_Indexes in all databases where you will be running this procedure. It's probably possible to run from AdminData but I preferred this way.

USE [AdminData]
GO
/****** Object: StoredProcedure [dbo].[Acme_Build_New_Indexes]    Script Date: 02/24/2012 15:39:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[Acme_Build_New_Indexes]
@ListOnly                        bit,
@Statistical_Days_Min_Available        integer,    --Minumum amount in days that statistical data has been accumulated.
@Impact_GT_Amt                     Numeric(20,4),    -- Impact must be > that this number
@User_Seeks_GT_Amt                integer,    --User Seeks must be > than this number
@User_Scans_GT_Amt                integer,    --User Scans must be > than this numberAvgUserImpact
@Avg_User_Impact_GT_Amt            Numeric(20,4),    --Average User Impact must be > than this number.
@Avg_Tot_User_cost_GT_Amt        Numeric(20,4),    --Average Total User Impact must be > than this number
@Last_User_Seek_Min_Days        integer,    --Last User Access must be Less Than this number of days
@Max_Run_time_Minutes            integer,    --Maximum time this job should run.
@Max_Columns                    integer,    --Maximum amount of columns that can be included in the index.
@Min_Rows                        bigint,        --Minimum amount of rows that the table should have.
@SortInTemp                        varchar(3),    --Set the SortInTemp allowed flag ON or OFF    Default in "ON"
@FileGroup                        varchar(100) --Name of the Filegroup to create the index in Default is "Primary"

as

declare
@IndexScript as nvarchar(2000),

@statement as varchar(2000),
@impact as numeric(20,4),
@equality_columns as varchar(2000),
@inequality_columns as varchar(2000),
@covering_columns as varchar(2000),
@unique_compiles as integer,
@user_seeks as integer,
@user_scans as integer,
@last_user_seek as Datetime,
@avg_total_user_cost as numeric(20,4),
@avg_user_impact as numeric(20,4),
@rowcnt as bigint,

@LastBootTime as datetime,
@LastSeekTime as datetime,
@EndTime as datetime,

@Table as varchar(100),
@Database as varchar(100),
@period1 as integer,
@Period2 as integer,
@columnName as varchar(100),
@Column1_Name as varchar(100),
@Column2_Name as varchar(100),
@Column3_Name as varchar(100),
@Column4_Name as varchar(100),
@Column5_Name as varchar(100),
@Column6_Name as varchar(100),

@Column1B_Name as varchar(100),        -- Columns without brackets
@Column2B_Name as varchar(100),
@Column3B_Name as varchar(100),
@Column4B_Name as varchar(100),
@Column5B_Name as varchar(100),
@Column6B_Name as varchar(100),
@TableB as varchar(100),
@IndexName as varchar(150),
@IX_Name_Exist as varchar(150),

@MinDateToProcess as datetime,
@ColumnsUsed as integer,
@ColCounts as integer,
@KeyFoundCount as integer,
@IndexMatchCount as integer

If @SortInTemp is null set @SortInTemp = 'ON';
If @FileGroup is null set @FileGroup = 'PRIMARY';

declare cur_missing_Indexes cursor for
select statement,
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) as impact,
equality_columns, inequality_columns, included_columns as covering_columns, unique_compiles,
user_seeks, user_scans, last_user_seek, avg_total_user_cost, avg_user_impact, SI.rowcnt
FROM sys.dm_db_missing_index_details AS mid
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_handle = mid.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats as mis
on mis.group_handle = mig.index_group_handle
INNER JOIN sysindexes as SI on SI.Id = mid.object_id and si.indid in (0,1)
WHERE database_id = DB_ID()
ORDER BY left(statement,4), (avg_total_user_cost * avg_user_impact * (user_seeks + user_scans))DESC;

select @LastBootTime = min(login_time) from master..sysprocesses
select @MinDateToProcess = getdate() - @Statistical_Days_MIN_Available
select @LastSeekTime = getdate() - @Last_user_Seek_min_Days
select @EndTime = dateadd(mi,@Max_Run_time_Minutes,getdate())

Print 'LastBootTime:' + cast(@LastBootTime as varchar(30))
Print 'MinDateToProcess:' + cast(@MinDateToProcess as varchar(30))
Print 'LastSeektime:' + cast(@LastSeekTime as varchar(30))
Print 'Impact_GT_Amt:' + cast(@Impact_GT_Amt as varchar(30))
print 'User_Seeks_GT_Amt:' + cast(@User_Seeks_GT_Amt as varchar(30))
print 'User_Scans_GT_Amt:' + cast(@User_Scans_GT_Amt as varchar(30))
Print 'Avg_User_Impact_GT_Amt:' + cast(@Avg_User_Impact_GT_Amt as varchar(30))
print 'Max_Columns:' + cast(@Max_Columns as varchar(30))
print 'Min_Rows:' + cast(@Min_Rows as varchar(30))

if @LastBootTime > @MinDateToProcess GOTO EndOfRun;

Open cur_missing_Indexes

fetch next from cur_missing_Indexes
    into
@statement,@impact,@equality_columns,@inequality_columns,@covering_columns,
@unique_compiles,@user_seeks,@user_scans,@last_user_seek,@avg_total_user_cost,@avg_user_impact,@rowcnt

while @@fetch_status = 0
BEGIN
    select    @column1_Name = null,
            @column2_Name = null,
            @column3_Name = null,
            @column4_Name = null,
            @column5_Name = null,
            @column6_Name = null,
            @column1B_Name = null,
            @column2B_Name = null,
            @column3B_Name = null,
            @column4B_Name = null,
            @column5B_Name = null,
            @column6B_Name = null,
            @ColumnsUsed = 0

--print @statement
    if    @Impact >= @Impact_GT_Amt and
        @User_Seeks >= @User_Seeks_GT_Amt and
        @User_Scans >= @User_Scans_GT_Amt and
        @Avg_user_impact >= @Avg_User_Impact_GT_Amt and
        @Avg_total_user_cost >= @Avg_Tot_User_cost_GT_Amt and
        @Last_user_seek >= @LastSeekTime and
        @rowcnt >= @Min_rows
        Begin

        --Print @statement

        set @period1 = charindex('.',@statement,0)
        set @period2 = charindex('.',@statement,@Period1 + 1)
        set @Table = substring(@Statement,@Period2 + 1,len(@statement) - @Period2)
        set @Database = substring(@Statement,0,@Period1)

        select top 6 *
        into #Eq_columns
        from AdminData.dbo.Acme_split(@equality_Columns,',')

        --select * from #eq_columns    -- Testing

        select top 6 *
        into #Not_Eq_Columns
        from AdminData.dbo.Acme_split(@inequality_Columns,',')

        --select * from #Not_Eq_Columns    -- Testing

        declare Cur_EqColumns cursor for
        select * from #Eq_columns

        Open cur_EqColumns

        fetch next from cur_EqColumns
        into @ColumnName
        While @@Fetch_status = 0
            BEGIN
            set @ColumnName = rtrim(ltrim(@ColumnName))
            If @Column1_Name is null and @Max_Columns >= 1
            BEGIN
            set @column1_Name = @ColumnName
            set @ColumnsUsed = 1
            END
            else
                if @Column2_Name is null and @Max_Columns >= 2
                    BEGIN
                    set @Column2_Name = @ColumnName
                    set @ColumnsUsed = 2
                    END
                else
                    if @Column3_Name is null and @Max_Columns >= 3
                        BEGIN
                        set @Column3_Name = @ColumnName
                        set @ColumnsUsed = 3
                        END
                    else
                        if @Column4_name is null and @Max_Columns >= 4
                            BEGIN
                            set @Column4_name = @ColumnName
                            set @ColumnsUsed = 4
                            END
                        else
                            if @Column5_name is null and @Max_Columns >= 5
                                BEGIN
                                set @Column5_name = @ColumnName
                                set @ColumnsUsed = 5
                                END
                            else
                                If @Column6_name is null and @Max_Columns >= 6
                                    BEGIN
                                    set @Column6_name = @columnName
                                    set @ColumnsUsed = 6
                                    END
                                else
                                    GOTO Check_InEquality;


            fetch next from cur_EqColumns
            into @ColumnName
            END        -- End Fetch from @Cur_EqColumns

Check_InEquality:
        --set @ColumnsUsed = @Max_Columns - @ColumnsUsed
        close cur_EqColumns
        deallocate cur_eqColumns
        Drop Table #Eq_columns

        declare Cur_NEqColumns cursor for
        select * from #Not_Eq_Columns

        Open cur_NEqColumns

        fetch next from cur_NEqColumns
        into @ColumnName
While @@Fetch_status = 0
            BEGIN
            set @ColumnName = rtrim(ltrim(@ColumnName))
            If @Column1_Name is null and @Max_Columns >= 1
                BEGIN
                set @Column1_Name = @ColumnName
                set @ColumnsUsed = 1
                END
            else
                if @Column2_Name is null and @Max_Columns >= 2
                    BEGIN
                    set @Column2_Name = @ColumnName
                    set @ColumnsUsed = 2
                    END
                else
                    if @Column3_Name is null and @Max_Columns >= 3
                        BEGIN
                        set @Column3_Name = @ColumnName
                        set @ColumnsUsed = 3
                        END
                    else
                        if @Column4_name is null and @Max_Columns >= 4
                            BEGIN
                            set @Column4_name = @ColumnName
                            set @ColumnsUsed = 4
                            END
                        else
                            if @Column5_name is null and @Max_Columns >= 5
                                BEGIN
                                set @Column5_name = @ColumnName
                                set @ColumnsUsed = 5
                                END
                            else
                                If @Column6_name is null and @Max_Columns >= 6
                                    BEGIN
                                    set @Column6_name = @columnName
                                    set @ColumnsUsed = 6
                                    END
                                else
                                    GOTO BuildIndex;

            fetch next from cur_NEqColumns
            into @ColumnName

            END        -- End Fetch @Cur_NeqColumns

BuildIndex:

        close cur_NeqColumns
        deallocate cur_NeqColumns
        Drop Table #Not_Eq_Columns

        set @IndexScript = 'CREATE NONCLUSTERED INDEX [IX_' + replace(replace(@Table,'[',''),']','') + '_'
        set @IndexName = 'IX_' + replace(replace(@Table,'[',''),']','') + '_'

-- Create a name for the Index "IX_Table_Col1_Col2_Col3"
        if @Column1_name is not null
            BEGIN
                set @IndexScript = @IndexScript + '_' + replace(replace(@Column1_name,'[',''),']','')
                set @IndexName = @IndexName + '_' + replace(replace(@Column1_name,'[',''),']','')
                set @Column1B_name = replace(replace(@Column1_name,'[',''),']','')
            END
        if @Column2_name is not null
            BEGIN
                set @IndexScript = @IndexScript + '_' + replace(replace(@Column2_name,'[',''),']','')
                set @IndexName = @IndexName + '_' + replace(replace(@Column2_name,'[',''),']','')
                set @Column2B_name = replace(replace(@Column2_name,'[',''),']','')
            END
        if @Column3_name is not null
            BEGIN
                set @IndexScript = @IndexScript + '_' + replace(replace(@Column3_name,'[',''),']','')
                set @IndexName = @IndexName + '_' + replace(replace(@Column3_name,'[',''),']','')
                set @Column3B_name = replace(replace(@Column3_name,'[',''),']','')
            END
        if @Column4_name is not null
            BEGIN
                set @IndexScript = @IndexScript + '_' + replace(replace(@Column4_name,'[',''),']','')
                set @IndexName = @IndexName + '_' + replace(replace(@Column4_name,'[',''),']','')
                set @Column4B_name = replace(replace(@Column4_name,'[',''),']','')
            END
        if @Column5_name is not null
            BEGIN
                set @IndexScript = @IndexScript + '_' + replace(replace(@Column5_name,'[',''),']','')
                set @IndexName = @IndexName + '_' + replace(replace(@Column5_name,'[',''),']','')
                set @Column5B_name = replace(replace(@Column5_name,'[',''),']','')
            END
        if @Column6_name is not null
            BEGIN
                set @IndexScript = @IndexScript + '_' + replace(replace(@Column6_name,'[',''),']','')
                set @IndexName = @IndexName + '_' + replace(replace(@Column6_name,'[',''),']','')
                set @Column6B_name = replace(replace(@Column6_name,'[',''),']','')
            END

        set @IndexScript = @IndexScript + '] ON ' + @Table + '('

-- Add the tables to the script
        If @Column1_Name is not null    set @IndexScript = @IndexScript + @Column1_Name + ' ASC, '

        if @Column2_Name is not null    set @IndexScript = @IndexScript + @Column2_Name + ' ASC, '

        if @Column3_Name is not null    set @IndexScript = @IndexScript + @Column3_Name + ' ASC, '

        if @Column4_name is not null    set @IndexScript = @IndexScript + @Column4_Name + ' ASC, '

        if @Column5_name is not null    set @IndexScript = @IndexScript + @Column5_Name + ' ASC, '

        If @Column6_name is not null    set @IndexScript = @IndexScript + @Column6_Name + ' ASC, '

        set @IndexScript = substring(@indexScript,1,len(@indexScript) -1) -- remove last comma

        set @IndexScript = @IndexScript + ') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ' + @SortInTemp + ', IGNORE_DUP_KEY = OFF,
        DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [' + @FileGroup + '];'
        set @TableB = replace(replace(@Table,'[',''),']','')

        select @IndexMatchCount = isnull(max(A.Counter),0)    -- From the valid Indexes verify the 1st col matches at least one of the required columns
            from
             (select so.name as soname, si.name as siname, count(*) as counter
             from sysindexes si,sysobjects so, syscolumns sc, sysindexkeys sk
                     where(si.id = so.id)
             and sc.id = so.id
             and sk.id = so.id
             and sk.colid = sc.colid
             and sk.indid = si.indid
             and so.name = @TableB
            and sc.name in (@Column1B_Name, @Column2B_Name,@Column3B_Name,@Column4B_name,@Column5B_Name,@Column6B_Name)
             and si.root is not null
             and keyno = 1
            and si.name in(select sinameB -- get the names of all indexes that have all of the columns needed
                            from
                                (select so.name as sonameB, si.name as sinameB, count(*) as counterB
                                from sysindexes si,sysobjects so, syscolumns sc, sysindexkeys sk
                                where(si.id = so.id)
                                and sc.id = so.id
                                and sk.id = so.id
                                and sk.colid = sc.colid
                                and sk.indid = si.indid
                                and so.name = @TableB
                                and sc.name in (@Column1B_Name, @Column2B_Name,@Column3B_Name,@Column4B_name,@Column5B_Name,@Column6B_Name)
                                and si.root is not null
                                group by so.name, si.name) as B
                            where B.counterB >= @ColumnsUsed) -- Existing Index Columns must be >= Columns Needed
             group by so.name, si.name) as A

        set @IX_Name_Exist = null                -- verify the index does not already exist.
        select @IX_Name_Exist = IndexName
            from AdminData.dbo.IDX_History
                where dbid = DB_ID()
                and IndexName = @IndexName


        if @IndexMatchCount = 0 and @IX_Name_Exist is null -- if no matching existing indexes then create
        BEGIN
            if @ListOnly = 'TRUE'
            BEGIN
                print @IndexScript
            END
            ELSE
            BEGIN
                BEGIN TRY
                    print @IndexScript
                    exec sp_executesql @IndexScript
                    insert into AdminData.dbo.IDX_History
                        ([DBID],IndexName,TableName,InsertDate)
                        values(DB_ID(),@IndexName,@TableB,getdate())
                END TRY
                BEGIN CATCH
                    print 'Could not generate the following New Index'
                    print @IndexScript
                    print    'ERROR Line =    ' + cast(ERROR_LINE() as varchar(100))
                    print   'ERROR Message = ' + cast(ERROR_MESSAGE() as varchar(2000))
                    Print ' '
                    goto NextLine
                END CATCH
            END
        END
        ELSE
        BEGIN
            PRINT ''
            PRINT '*** This index was similar to an existing index and WAS NOT BUILT *** '
            PRINT @IndexScript
        END
NextLine:


-- ************ EXECUTE the SCript

-- ************ End Execute

    --print @IndexScript

    END        -- ****************** End If index meets all requirements to be built. *******

    if getdate() > @EndTime break    -- exceeded time limit.

    fetch next from cur_missing_Indexes
        into
    @statement,@impact,@equality_columns,@inequality_columns,@covering_columns,
    @unique_compiles,@user_seeks,@user_scans,@last_user_seek,@avg_total_user_cost,@avg_user_impact,@rowcnt
END        -- ****************** End Main Outer Loop **********************************
CLOSE cur_missing_indexes
DEALLOCATE cur_missing_indexes

EndOfRun:

STEP6: Create the ACME_Delete_UnUsed_Indexes Stored Procedure.

USE [AdminData]
GO
/****** Object: StoredProcedure [dbo].[ACME_Delete_UnUsed_Indexes]    Script Date: 02/24/2012 16:02:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[ACME_Delete_UnUsed_Indexes]
@ListOnly                bit,
@Statistical_Days_Min_Available    integer,        --Minumum amount in days that statistical data has been accumulated.
@TotalWrites_GT_Amt             integer,        -- Writes must be >= to this number
@TotalReads_LT_Amt            integer,        -- Reads must be <= to this number
@MinIDXAgeToDlet            integer,        -- Minimum number of days the index has been in existance
@Impact_LT_Amt            numeric(8,4)    -- Impact (ratio of reads vs writes) must be less that this number.
                            -- Example an impact of .5 means there are twice the number of writes to reads
                            -- an impact of 2.0 means there are twice the number of reads to writes
                            -- the Higher the impact the better the index is for the database
as

declare
@MinDateToProcess as datetime,
@LastBootTime as datetime,
@TableName as nvarchar(250),
@IndexName as nvarchar(250),
@SQL as nvarchar(2000),
@NewIndex as nvarchar(500),
@MinIDXDateToDlet as datetime

set @MinIDXDateToDlet = dateadd(day,-@MinIDXAgeToDlet,getdate())
--Print 'Minimum Index Age =' + cast(@MinIDXDateToDLET as varchar(30))
select @LastBootTime = min(login_time) from master..sysprocesses
select @MinDateToProcess = getdate() - @Statistical_Days_MIN_Available

if @LastBootTime > @MinDateToProcess GOTO EndOfRun;

declare cur_unused_Indexes cursor for
select obj.name, idx.name
from sys.indexes as idx
join sys.objects as obj on obj.object_id = idx.object_id
left outer join sys.dm_db_index_usage_stats as stats on stats.object_id = idx.object_id and stats.index_id = idx.index_id
WHERE stats.database_id = DB_ID()
and idx.index_id > 1
and idx.is_unique = 0
and idx.is_primary_key = 0
and idx.is_unique_constraint = 0
and obj.type = 'U'
and (((isnull(stats.user_seeks + stats.user_scans + stats.user_lookups,0) <= @TotalReads_LT_Amt
        and isnull(stats.user_updates,0) >= @TotalWrites_GT_Amt))
        and ((stats.user_seeks + stats.user_scans + stats.user_lookups + .0001) / (user_updates + .0001) <= @Impact_LT_Amt))

Open cur_unused_Indexes

fetch next from cur_UnUsed_Indexes
    into
@TableName, @IndexName

while @@fetch_status = 0
BEGIN

set @SQL = 'DROP INDEX ' + @TableName + '.' + @IndexName

set @NewIndex = null

-- Check if we're deleting a new index with no statistics
select @NewIndex = IndexName from AdminData.dbo.IDX_History
    where IndexName = @IndexName
    and dbid = DB_ID()
    and insertDate < @MinIDXDateToDlet

if @NewIndex is null -- No recent indexes were returned
BEGIN
    if @ListOnly = 'TRUE'
        BEGIN
            print @SQL
        END
        ELSE
        BEGIN
            BEGIN TRY
                print @SQL
                -- Save dropped index
                create table #indexes (indexname nvarchar(255), index_description nvarchar(1000), index_keys nvarchar(1000))

                insert #indexes exec sp_helpindex @TableName -- listing of indexes for this table

                if not exists (select IndexName
                                from AdminData.dbo.IDX_History
                                where IndexName = @IndexName
                                and dbid = DB_ID())
                BEGIN
                    insert into AdminData.dbo.IDX_History
                    ([dbid],IndexName,Tablename, DeleteDate, Keys)
                    select DB_ID(),@indexname, @TableName, getdate(), index_keys
                    from #indexes
                    where indexname = @IndexName
                END
                ELSE
                BEGIN
                    Update AdminData.dbo.IDX_History
                    set DeleteDate = getdate(),
                        Keys = (select index_Keys
                                        from #indexes
                                        where indexname = @IndexName)
                    where dbid = DB_ID()
                    and IndexName = @IndexName
                END
                drop table #indexes
                -- end save Dropped Index
                exec sp_executesql @SQL
            END TRY
            BEGIN CATCH
                print 'Could not drop the Index command: '
                print @SQL
                print    'ERROR Line =    ' + cast(ERROR_LINE() as varchar(100))
                print   'ERROR Message = ' + cast(ERROR_MESSAGE() as varchar(2000))
                Print ' '
                continue
            END CATCH
        END
END
fetch next from cur_unused_Indexes
    into
@TableName, @IndexName
END

CLOSE cur_unused_indexes
DEALLOCATE cur_unused_indexes

EndOfRun:

Step 7: Create the schedule: This is the most important part and here is where options are very important. you will need to create a scheduled event to fire and execute the code below. I will add a few additional notes here.
1. make some trial runs in a query window always make sure the @ListOnly flag is set to true, this will print out the new indexes that will be built or the indexes to be deleted, no updates will take place.
2. Adjust your options to what make sense for your company.
3. Remember after a restart of services the DMV's are reset and numbers will be very low. Setting the "Statistical_Days_Min_Available" to a high number (I recommend > 30) will prevent rarely used indexes that come into play during month end from being deleted.
4. Values should be tailored for each database not a one size fits all.
5. Big table = Big indexes You might want to run this late at night if you suspect large indexes will be built.
6. set the"Maximum time this job should run flag" to a number that will prevent the job from running into the working hours. This is checked at the beginning of each new index build. If the time has exceeded the job will stop.
7. If an existing index already exists that contains the same columns as the index to be built and at least one of the columns is in the 1st position the index will not be created.
8. New indexes may take a few days to accumulate statistics. The "MinIDXAgeToDlet" variable prevents this from happening. Set this value large enough to allow the new index to accumulate statistical data.

DECLARE    @return_value int

EXEC    @return_value = [dbo].[ACME_Delete_UnUsed_Indexes]
        @ListOnly = false,
        @Statistical_Days_Min_Available = 30,
        @TotalWrites_GT_Amt = 50,         --Number of writes should be GT > Number of reads
        @TotalReads_LT_Amt = 0,         -- Number of reads should be LT < than writes
        @MinIDXAgeToDlet = 30,        -- Number should be large enough to allow statistical data to be generated for new indexes.
        @Impact_LT_Amt = .25        -- ratio of reads vs writes. Lower = more writes than reads. exp. .5 = 2 writes to every 1 read.

/* in the setting above all indexes that have had a total of 0 seeks and scans and looksups and have had at least 50 writes and the index was not part of an auto create in the last 30 days, and the server has been running for at least 30 days, and there are less than 1 read to every 4 writes will be deleted.
*/

SELECT    'Return Value' = @return_value

GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[ACME_Build_New_Indexes]
        @ListOnly = FALSE,
        @Statistical_Days_Min_Available = 7,        --Minumum amount in days that statistical data has been accumulated.
        @Impact_GT_Amt = 10,            -- Impact must be > that this number
        @User_Seeks_GT_Amt = 10,            --User Seeks must be > than this number
        @User_Scans_GT_Amt = 0,            --User Scans must be > than this numberAvgUserImpact
        @Avg_User_Impact_GT_Amt = .02,        --Average User Impact must be > than this number.
        @Avg_Tot_User_cost_GT_Amt = .02,        --Average Total User Impact must be > than this number
        @Last_User_Seek_Min_Days = 7,        --Last User Access must be Less Than this number of days
        @Max_Run_time_Minutes = 90,        --Maximum time this job should run.
        @Max_Columns = 6,            --Maximum amount of columns that can be included in the index.
        @Min_Rows = 300,                --Minimum amount of rows that the table should have.
        @SortInTemp = N'ON',            --Set the SortInTemp allowed flag ON or OFF    Default in "ON"
        @FileGroup = N'PRIMARY'            --Name of the Filegroup to create the index in Default is "Primary"

SELECT    'Return Value' = @return_value

GO

Home | About Us | Screen Shots | Contact Us | Downloads