locked
Index AND Clustered Index on the Primary Key RRS feed

  • Question

  • Accidental DBA here - I've inherited a SQL database and app and I'm trying to go through and learn it and find possible problems...

    There are a lot of indexes created with the _dta prefix - so these were created by the tuning adviser I guess...

    anyway I find a bunch of duplicate indexes and also indexes that are also clustered and non clustered for the primary key for certain tables - so I look at one table for instance and it has an ID column, that's int and autoincrementing, there's a unique clustered index for it and also a non clustered index for it

    should I delete the non clustered index? (again this index is prefixed with the _dta so it was created by the performance tuning )



    Check out my CNC (and more) projects at http://www.backyard-workshop.com

    Friday, March 7, 2014 7:31 PM

Answers

  • Yes, duplicate indexes may hit the performance, I have been using Greg's stored procedure to see what indexes are in use in order to get idea which one to remove,

    I use this script primarily to identify which indexes are sub-sets of each other
    & can therefore be consolidated into a single index with the added information
     about usage patterns so I also have an understanding of how often the indexes are being accessed by queries.

    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

    This usp_ should first be created within the user database & can then be called simply as follows:
    exec usp_helpindexusagestats 'MyTableName'
    Output includes some rich information about the indexes
     on the table including column names (keys & included columns) and also usage stats information about how often seeks, scans & lookups are being performed on each index.


    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

    • Marked as answer by JamieC7 Sunday, March 9, 2014 1:29 PM
    Saturday, March 8, 2014 8:18 AM

All replies

  • You can also the below link to find duplicate index 

    http://gallery.technet.microsoft.com/SQL-Identify-Duplicate-ca8be9eb

    In your case delete the non-clustered index. 

    For more information you can refer the below link and understand the significance of index deletion

    http://www.confio.com/logicalread/duplicate-indexes-and-sql-server-performance/#.Uxog0WJdWbM

    -Prashanth

    Friday, March 7, 2014 7:47 PM
  • Yes, duplicate indexes may hit the performance, I have been using Greg's stored procedure to see what indexes are in use in order to get idea which one to remove,

    I use this script primarily to identify which indexes are sub-sets of each other
    & can therefore be consolidated into a single index with the added information
     about usage patterns so I also have an understanding of how often the indexes are being accessed by queries.

    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

    This usp_ should first be created within the user database & can then be called simply as follows:
    exec usp_helpindexusagestats 'MyTableName'
    Output includes some rich information about the indexes
     on the table including column names (keys & included columns) and also usage stats information about how often seeks, scans & lookups are being performed on each index.


    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

    • Marked as answer by JamieC7 Sunday, March 9, 2014 1:29 PM
    Saturday, March 8, 2014 8:18 AM
  • I usually do not consider these two indexes as duplicate indexes. There are chances that who created index would have some reason for it to balance the load on the index and its usage. Two non-clustered indexes with same keys including the same columns can be considered as duplicate indexes. Certain cases, if there is any change in the columns in including, then you drop one index and can go for a covered index.

    But, in your case you see or compare clustered and non-clustered. May be the reason to balance the usage, someone should create the same. So please analyze and do it. DO NOT DROP or CONSIDER as its a duplicate index.

    Saturday, March 8, 2014 12:42 PM
    Answerer
  • Hi

    I think it is exactly duplicate index. None duplicate I would consider the different order of the columns even though they are the same columns or different columns in INCLUDE clause..


    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

    Sunday, March 9, 2014 9:45 AM
  • Hello Uri,

        Why not an engineer created a non-clustered index with one column that used for a particular query to avoid the usage of CLUSTERED index which is huge for a huge table with multiple columns? May be a situation where non-clustered index has been created to avoid a clustered scan. At the time, it wont be a good idea to DROP the non clustered index just because its a DUPLICATE index. And I think its not a duplicate index also.

    Please share your thoughts.

    Sunday, March 9, 2014 5:06 PM
    Answerer