locked
Redundant indexes RRS feed

  • Question

  • There are following redundant indexes:

    1. 

    CREATE NONCLUSTERED INDEX [first_index] ON [dbo].[TRANSACTION] ([Kword] ,[Created_Date] ,[Status] )
    INCLUDE ( [Transaction_ID],[Source_ID],[Dest_ID],[Amount],[Fee])

    2. 

    CREATE NONCLUSTERED INDEX [second_index] ON [dbo].[TRANSACTION] ([Kword], [Dest_ID]) INCLUDE ([Source_ID], [Created_Date])

    A query when run with second index shows index seek in the plan. The same query when run with first index shows index seek but with parallelism and also suggests the second index with 99% improvement.

    Please explain this situation and can I or not remove the second index?







    • Edited by Curendra Thursday, April 23, 2020 5:52 AM
    Thursday, April 23, 2020 5:39 AM

Answers

  • There are following redundant indexes:

    No, those indexes are not redundant. Whether both are needed, I cannot tell, since I don't knnow anything about your system. But a query like:

    SELECT * FROM [Transaction] WHERE Kword= @keyword AND created_Date > @yesterday

    benefits from the first index, whereas this query has use for the second index:

    SELECT * FROM [Transaction] WHERE Kword= @keyword AND Dest_ID = @destid

    If you would drop one of the indexes, it is possible that the query that used this index will start using the other one, but it will be less efficient. How much more inefficient depends on how selective KWord is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 23, 2020 9:38 PM

All replies

  • See Greg's sp to see index usage

    create procedure usp_helpindexusagestats
      @tablename varchar(1000)
    as

    set nocount on

    if object_id('tempdb..#helpindex') > 0 drop table #helpindex

    create table #helpindex (
       index_name varchar (1000) not null primary key
     , index_description varchar (1000) null
     , index_keys varchar (1000) null
    )

    insert #helpindex
    exec sp_helpindex @tablename

    alter table #helpindex add inccols varchar(1000) null

    declare cr cursor for
    select si.name, sc.name
    from sysobjects so
    join sysindexes si on so.id = si.id
    join sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id
    join sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id
    where so.xtype = 'U'
      and so.name = @tablename
      and ic.is_included_column = 1
    order by si.name, ic.index_column_id

    declare @siname varchar(1000), @scname varchar(1000)

    open cr

    fetch next from cr into @siname, @scname

    while @@fetch_status = 0
     begin

      update #helpindex set inccols = isnull(inccols , '') + @scname + ', ' where index_name = @siname

      fetch next from cr into @siname, @scname
     end

    update #helpindex set inccols = left(inccols, datalength(inccols) - 2)
    where right(inccols, 2) = ', '

    close cr
    deallocate cr

    select hi.index_name, hi.index_description, hi.index_keys, hi.inccols as included_columns, ius.index_id, user_seeks, user_scans, user_lookups, user_updates
    , last_user_seek, last_user_scan, last_user_lookup
    from sys.dm_db_index_usage_stats ius
    join sysindexes si on ius.object_id = si.id and ius.index_id = si.indid
    join sysobjects so on si.id = so.id
    join #helpindex hi on si.name = hi.index_name collate database_default
    where ius.database_id = db_id()
      and so.name = @tablename

    drop table #helpindex
    go


    exec usp_helpindexusagestats 'yourtb'

    See which index is much more in use...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 23, 2020 7:11 AM
  • Hi Curendra,

    would you please show the query statement and execution plan screenshot.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 23, 2020 9:53 AM
  • Without seeing your WHERE or JOIN clause it is impossible to guess.

    However, the indexes are not the same.  So different WHERE clauses are going to choose different indexes.

    Thursday, April 23, 2020 11:59 AM
    Answerer
  • There are following redundant indexes:

    No, those indexes are not redundant. Whether both are needed, I cannot tell, since I don't knnow anything about your system. But a query like:

    SELECT * FROM [Transaction] WHERE Kword= @keyword AND created_Date > @yesterday

    benefits from the first index, whereas this query has use for the second index:

    SELECT * FROM [Transaction] WHERE Kword= @keyword AND Dest_ID = @destid

    If you would drop one of the indexes, it is possible that the query that used this index will start using the other one, but it will be less efficient. How much more inefficient depends on how selective KWord is.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 23, 2020 9:38 PM
  • Following is the query.

    SELECT T.Source_ID,T.Created_Date
    FROM dbo.TRANSACTION T
    WHERE T.Kword='top' AND T.Dest_ID=1199



    • Edited by Curendra Friday, April 24, 2020 3:29 AM
    Friday, April 24, 2020 3:28 AM
  • Following is the query.

    SELECT T.Source_ID,T.Created_Date
    FROM dbo.TRANSACTION T
    WHERE T.Kword='top' AND T.Dest_ID=1199



    Can you please create one consolidated index as below and see the queries you used to which index is being picked? if for all cases, it picks the consolidated, you can eliminate the other two indexes. But do not forget to run a performance test.

    CREATE NONCLUSTERED INDEX [consolidated_index] ON [dbo].[TRANSACTION] ([Kword] ,[Dest_ID],[Created_Date] ,[Status] )
    INCLUDE ( [Transaction_ID],[Source_ID],[Dest_ID],[Amount],[Fee],[Created_Date])

    I would further remove TRANSACTION_ID from INCLUDE if Transaction_Id is a clustered index key for your table.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]  [twitter]

    Friday, April 24, 2020 5:30 AM
    Answerer
  • Following is the query.

    the query? Surely you are running more than one query against a table named Transaction?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, April 24, 2020 9:30 PM