locked
Information about indexes RRS feed

  • Question

  • Hi all,

    i'm using sql server 2008r2, i have found a table which is having 35 indexes and this is our main transaction table. Here i found a job which creates missing indexes automatically.what im getting confused is it is having multiple indexes on a single column with different columns in include clause. can i create a single index with all that including columns?

    can u please help me to know the concept of include clause in indexes.

    Thanks 

    Vinesh M

    Monday, August 29, 2016 9:22 AM

Answers

  • Creating all suggested missing indexes automatically is one of the worst ideas ever.

    Suggested Missing indexes are just a clue that a single statement would benefit from that index, with no correlation to how often the statement is issued, how heavily the table is written to. Indexes should be created taking the whole workload into consideration.

    Moreover, missing idexes suggestions often contain duplicate indexes and can be merged into a single index.

    Included columns are somewhat "cheaper" than index keys: the data is not stored at intermediate level in the B-Tree, it is stored only at leaf level. However, Included columns are not for free: they're still copies of the data in the table (heap or clustered index), so they contribute to the index size.

    That said, 35 indexes on a single table seems utterly ridiculous to me.

    Monday, August 29, 2016 9:28 AM
  • See what indexes are in use. (Scan, Seek info)

    -------------------------how often the indexes  are being accessed----------------
    --Heres an index analysis script I put together recently which combines 
    --output from sp_helpindex (also with included columns, which the regular sp_helpindex lacks) 
    --and the dm_db_index_usage_stats() DMV.

    --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


    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

    Monday, August 29, 2016 10:36 AM
  • I agree what Spaghettidba is suggesting. Running a job to blindly create index is worst thing to do on such an important table. Use Uri's script to find unused indexes and remove them carefully

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, August 29, 2016 12:37 PM
    Answerer

All replies

  • Creating all suggested missing indexes automatically is one of the worst ideas ever.

    Suggested Missing indexes are just a clue that a single statement would benefit from that index, with no correlation to how often the statement is issued, how heavily the table is written to. Indexes should be created taking the whole workload into consideration.

    Moreover, missing idexes suggestions often contain duplicate indexes and can be merged into a single index.

    Included columns are somewhat "cheaper" than index keys: the data is not stored at intermediate level in the B-Tree, it is stored only at leaf level. However, Included columns are not for free: they're still copies of the data in the table (heap or clustered index), so they contribute to the index size.

    That said, 35 indexes on a single table seems utterly ridiculous to me.

    Monday, August 29, 2016 9:28 AM
  • See what indexes are in use. (Scan, Seek info)

    -------------------------how often the indexes  are being accessed----------------
    --Heres an index analysis script I put together recently which combines 
    --output from sp_helpindex (also with included columns, which the regular sp_helpindex lacks) 
    --and the dm_db_index_usage_stats() DMV.

    --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


    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

    Monday, August 29, 2016 10:36 AM
  • I agree what Spaghettidba is suggesting. Running a job to blindly create index is worst thing to do on such an important table. Use Uri's script to find unused indexes and remove them carefully

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, August 29, 2016 12:37 PM
    Answerer
  • FYI......the below from the link already suggested by spa but you will get complete details further w.r.t to design

    can u please help me to know the concept of include clause in indexes.
    >go through this link https://msdn.microsoft.com/en-us/library/ms190806.aspx

    with the help of Uri scripts - schedule an job to pull the information's  about indexes how it being used,advisingyou dont take the immediate decision rather see for an week/month based on your criteria &  ok as well = so this will give the good hint further.

    so when you are going to remove ensure you first did in test (incase if needed take the current execution plan prior &post -compare it).

    Be aware-

    sys.dm_db_index_usage_stats
    https://msdn.microsoft.com/en-us/library/ms188755.aspx

    Note:- (BOL)The counters are initialized to empty whenever the SQL Server  service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
    When an index is used, a row is added to sys.dm_db_index_usage_stats if a row does not already exist for the index. When the row is added, its counters are initially set to zero.


    Regards, S_NO "_"


    • Edited by S_NO Saturday, September 3, 2016 2:14 PM update
    Saturday, September 3, 2016 2:13 PM