Unused Indexes

  • Hello,

    I have been running the DBTA on a database to get some recommendations, I have the results, nothing has been applied.

    Afterwards I ran the script below that I got from the web, I first ran it in dev (where DBTA had not been run) to test it, I then ran it in live to compare the output. The script appears to have also listed all the indexes listed in the DBTA results as well. I have checked to make sure these were not applied in live, why have all the dta indexes also been listed? Where are they and how can I prevent them from being listed? I only want to list the indexes currently in use. After the code I have a another question.

    SET NOCOUNT ON;

    DECLARE @First[smallint]

    ,@Last[smallint]

    ,@IsUnique[smallint]

    ,@HasNonKeyCols[char](1)

    ,@TableName[varchar](256)

    ,@IndexName[varchar](256)

    ,@IndexType[varchar](13)

    ,@IndexColumns[varchar](1000)

    ,@IncludedColumns[varchar](1000)

    ,@IndexColsOrder[varchar](1000)

    ,@IncludedColsOrder[varchar](1000)

    DECLARE @Indexes TABLE ([RowNo][smallint] IDENTITY(1, 1)

    ,[TableName][varchar](256)

    ,[IndexName][varchar](256)

    ,[IsUnique][smallint]

    ,[IndexType][varchar](13))

    DECLARE @AllIndexes TABLE ([RowNo][smallint] IDENTITY(1, 1)

    ,[TableName][varchar](256)

    ,[IndexName][varchar](256)

    ,[IndexType][varchar](13)

    ,[KeyColumns][varchar](512)

    ,[NonKeyColumns][varchar](512)

    ,[KeyColumnsOrder][varchar](512)

    ,[NonKeyColumnsOrder][varchar](512)

    ,[IsUnique][char](1)

    ,[HasNonKeyColumns][char](1))

    IF OBJECT_ID('Tempdb.dbo.#Temp') IS NOT NULL

    DROP TABLE #Temp

    SELECT o.[object_id] AS [ObjectID]

    ,OBJECT_NAME(o.[object_id]) AS [TableName]

    ,i.[index_id] AS [IndexID]

    ,i.[name] AS [IndexName]

    ,CASE i.[type]

    WHEN 0 THEN 'Heap'

    WHEN 1 THEN 'Clustered'

    WHEN 2 THEN 'Non-Clustered'

    WHEN 3 THEN 'XML'

    ELSE 'Unknown'

    END AS [IndexType]

    ,ic.[column_id] AS [ColumnID]

    ,c.[name] AS [ColumnName]

    ,ic.[is_included_column] [IncludedColumns]

    ,i.[is_unique] AS [IsUnique]

    INTO #Temp

    FROM sys.indexes i

    INNER JOIN sys.objects o

    ON i.object_id = o.object_id

    AND o.type = 'U' AND i.index_id > 0

    INNER JOIN sys.index_columns ic

    ON i.index_id = ic.index_id

    AND i.object_id = ic.object_id

    INNER JOIN sys.columns c

    ON c.column_id = ic.column_id

    AND c.object_id = ic.object_id

    INSERT INTO @Indexes

    SELECT DISTINCT

    [TableName]

    ,[IndexName]

    ,[IsUnique]

    ,[IndexType]

    FROM #Temp

    SELECT @First = MIN([RowNo]) FROM @Indexes

    SELECT @Last = MAX([RowNo]) FROM @Indexes

    WHILE @First <= @Last

    BEGIN

    SET @IndexColumns=NULL

    SET @IncludedColumns=NULL

    SET @IncludedColsOrder=NULL

    SET @IndexColsOrder=NULL

    SELECT @TableName = [TableName]

    ,@IndexName = [IndexName]

    ,@IsUnique = [IsUnique]

    ,@IndexType = [IndexType]

    FROM @Indexes

    WHERE [RowNo] = @First

    SELECT @IndexColumns = COALESCE(@IndexColumns + ', ', '') + [ColumnName]

    FROM #Temp

    WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0

    ORDER BY [IndexName], [ColumnName]

    SELECT @IncludedColumns = COALESCE(@IncludedColumns+ ', ', '') + [ColumnName]

    FROM #Temp

    WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1

    ORDER BY [IndexName], [ColumnName]

    SELECT @IndexColsOrder = COALESCE(@IndexColsOrder + ', ', '') + [ColumnName]

    FROM #Temp

    WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0

    SELECT @IncludedColsOrder = COALESCE(@IncludedColsOrder + ', ', '') + [ColumnName]

    FROM #Temp

    WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1

    SET @HasNonKeyCols = 'N'

    IF @IncludedColumns IS NOT NULL

    BEGIN

    SET @HasNonKeyCols = 'Y'

    END

    INSERT INTO @AllIndexes ([TableName]

    ,[IndexName]

    ,[IndexType]

    ,[IsUnique]

    ,[KeyColumns]

    ,[KeyColumnsOrder]

    ,[HasNonKeyColumns]

    ,[NonKeyColumns]

    ,[NonKeyColumnsOrder] )

    SELECT @TableName

    ,@IndexName

    ,@IndexType

    ,CASE @IsUnique

    WHEN 1 THEN 'Y'

    WHEN 0 THEN 'N'

    END

    ,@IndexColumns

    ,@IndexColsOrder

    ,@HasNonKeyCols

    ,@IncludedColumns

    ,@IncludedColsOrder

    SET @First = @First + 1

    END

    SELECT 'Listing All Indexes' AS [Comments]

    SELECT [TableName]

    ,[IndexName]

    ,[IndexType]

    ,[KeyColumns]

    ,[HasNonKeyColumns]

    ,[NonKeyColumns]

    ,[KeyColumnsOrder]

    ,[NonKeyColumnsOrder]

    ,[IsUnique]

    FROM @AllIndexes

    SELECT 'Listing Duplicate Indexes' AS [Comments]

    SELECT DISTINCT

    a1.[TableName]

    ,a1.[IndexName]

    ,a1.[IndexType]

    ,a1.[KeyColumns]

    ,a1.[HasNonKeyColumns]

    ,a1.[NonKeyColumns]

    ,a1.[KeyColumnsOrder]

    ,a1.[NonKeyColumnsOrder]

    ,a1.[IsUnique]

    FROM @AllIndexes a1

    JOIN @AllIndexes a2

    ON a1.[TableName] = a2.TableName

    AND a1.[IndexName] <> a2.[IndexName]

    AND a1.[KeyColumns] = a2.[KeyColumns]

    AND ISNULL(a1.[NonKeyColumns], '') = ISNULL(a2.[NonKeyColumns], '')

    WHERE a1.[IndexType] <> 'XML'

    SET NOCOUNT OFF;

    Is it bad practice to have a non clusted index on top of a clusted index?

    Thanks for any help.

    Regards,

    D.

  • Duran i guess you'd have to show us an example or two from the results of the query, instead of the query itself.

    for example, maybe the recommendation is an index with INCLUDE columns, but we can't see what you were seeing, so everything would be wild guesses.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hello Lowell,

    Thanks for getting back, here is an example of what came back, as well as these the indexes that are actually in the table also came up, but mixed in with these.

    _dta_index_Assigned_Tests_9_29959183__K3_K2_1040 Non-Clustered

    _dta_index_Assigned_Tests_9_29959183__K3_K2_4_5_6_7_8_10 Non-Clustered

    _dta_index_Assigned_Tests_9_29959183__K3_K2_4_5_6_7_8_10_8337 Non-Clustered

    _dta_index_Assigned_Tests_9_29959183__K3_K2_9 Non-Clustered

    What do you think? Is the script picking the indexes out of a cache rather than taking the information from the database?

    Regards,

    D.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply