Information about indexes

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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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