Answered by:
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
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.
- Edited by spaghettidbaMVP Monday, August 29, 2016 9:31 AM
- Proposed as answer by Shanky_621MVP, Editor Monday, August 29, 2016 12:35 PM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, September 9, 2016 1:58 AM
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
- Proposed as answer by Enric Vives Monday, August 29, 2016 10:48 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, September 9, 2016 1:58 AM
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- Proposed as answer by Teige Gao Tuesday, August 30, 2016 1:16 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, September 9, 2016 1:58 AM
Monday, August 29, 2016 12:37 PMAnswerer
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.
- Edited by spaghettidbaMVP Monday, August 29, 2016 9:31 AM
- Proposed as answer by Shanky_621MVP, Editor Monday, August 29, 2016 12:35 PM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, September 9, 2016 1:58 AM
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
- Proposed as answer by Enric Vives Monday, August 29, 2016 10:48 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, September 9, 2016 1:58 AM
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- Proposed as answer by Teige Gao Tuesday, August 30, 2016 1:16 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, September 9, 2016 1:58 AM
Monday, August 29, 2016 12:37 PMAnswerer -
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.aspxwith 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