multiple index on same column
-
why would you have multiple indexes on the same column?
we have a bought in database and looking at one table it has a clustered primary key involving two columns
ALTER TABLE [dbo].[ins_yps] ADD CONSTRAINT [ins_yps_pk] PRIMARY KEY CLUSTERED(
[yps_ayrc] ASC,
[yps_pslc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
and two other indexes, one for each of the fields in the PK
CREATE NONCLUSTERED INDEX [ins_ypsI3] ON [dbo].[ins_yps](
[yps_pslc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ins_ypsI2] ON [dbo].[ins_yps](
[yps_ayrc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
is this just poor design, or possibly migration from a previous version of SQL Server?
-
andrew_dale (4/30/2015)
why would you have multiple indexes on the same column?we have a bought in database and looking at one table it has a clustered primary key involving two columns
ALTER TABLE [dbo].[ins_yps] ADD CONSTRAINT [ins_yps_pk] PRIMARY KEY CLUSTERED(
[yps_ayrc] ASC,
[yps_pslc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
and two other indexes, one for each of the fields in the PK
CREATE NONCLUSTERED INDEX [ins_ypsI3] ON [dbo].[ins_yps](
[yps_pslc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ins_ypsI2] ON [dbo].[ins_yps](
[yps_ayrc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
is this just poor design, or possibly migration from a previous version of SQL Server?
I believe that the index "[ins_ypsI2]" is not needed but the index "[ins_ypsI3]" may be needed as yps_psic is the lead column.
-
Yeah, to expand on what djj said, the trick is that those two columns are a composite key in the clustered index, and the order of the columns makes a difference.
To use the most hackneyed example possible, think of a phone book.
The clustered index in this case would be like the usual sorting of a phone book, by last name, and then by first name.
The second index would be an ordering of phone numbers by first name alone, and the third index would be an ordering of phone numbers by last name alone.
The index of numbers by last name alone would be redundant, since anything you would use it to do you could just use the clustered index, as its primary sort is also by last name.
However, using the clustered index to get a list of phone numbers for people with the first name Jacob would be really inefficient. Even though first name is a column in the key, it's just the secondary sort, so you'd still end up having to just scan the whole phone book looking for people with the first name Jacob.
Because of that, the second index, having as its primary (and only) sorting key the column that is only the secondary sorting key for the clustered index, could be useful for some queries that wouldn't benefit from the clustered index.
Hopefully that helped clarify and didn't just obscure things even more. I haven't even had coffee today, so I'll fall back on that excuse if this didn't help. 🙂
EDIT: Fixed a typo.
-
many thanks for the replies, they do make sense.
checking the execution plan, if I run a simple query and include one of the index fields in the where clause then I get Clustered Index Seek if the where is on yps_ayrc and a Clustered Index Scan if the where is on yps_pslc
If I write a more complicated query, with a group by on yps_pslc, then it does a NonClustered Index Scan on ins_ypsI3
-
Quick thought, it might be helpful to look at the sys.dm_db_index_usage_stats view.
😎
SELECT*
FROM sys.dm_db_index_usage_stats IUS
WHERE IUS.object_id = OBJECT_ID(N'[SCHEMA_NAME.TABLE_NAME]');
-
Hi Andrew,
The problem with redundant indexes, like ins_ypsI2, is that there is additional overhead for data updates but no benefit for selects.
The script below will find all redundant indexes in the current database. It lists the columns in the redundant index and superior index so you can compare them. It doesn't take INCLUDE columns into account, but these are also listed in the result set.
Just because an index is listed doesn't mean it's not needed, especially if one is clustered and another is non-clustered. As Eirikur has said, check index usage stats before making any decision.
Here's the script:
set nocount on;declare @i table (
obj_id int,
obj_name nvarchar(255),
sch_name nvarchar(255),
index_id int,
index_name nvarchar(255),
index_cols nvarchar(max),
included_cols nvarchar(max),
index_type int,
is_primary_key int
)
insert @i
select i.object_id, object_name(i.object_id), object_schema_name(i.object_id), i.index_id, i.name,
(select x.cols
from (
select cast(column_id as nvarchar(10)) + ',' as [text()]
from sys.index_columns
where object_id = i.object_id and index_id = i.index_id
and is_included_column = 0
order by key_ordinal asc
for xml path('')
) x(cols)) index_cols,
isnull((select x.cols
from (
select cast(column_id as nvarchar(10)) + ',' as [text()]
from sys.index_columns
where object_id = i.object_id and index_id = i.index_id
and is_included_column = 1
order by column_id asc
for xml path('')
) x(cols)), 'd'),
i.[type], i.is_primary_key
from sys.indexes i
where objectproperty(i.object_id, 'IsSystemTable') = 0
andisnull(indexproperty(i.object_id, i.name, 'IsHypothetical'), 0) = 0
andisnull(indexproperty(i.object_id, i.name, 'IsStatistics'), 0) = 0
andisnull(indexproperty(i.object_id, i.name, 'IsAutoStatistics'), 0) = 0
and i.[type] < 3
select r.sch_name, r.obj_name, r.index_name redundant_index, s.index_name superior_index,
(select x.cols from (
select name + ', ' as [text()]
from sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
where ic.object_id = r.obj_id and ic.index_id = r.index_id
and is_included_column = 0
order by ic.key_ordinal asc
for xml path('')
) x(cols)) redundant_index_cols,
(select x.cols from (
select name + ', ' as [text()]
from sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
where ic.object_id = r.obj_id and ic.index_id = r.index_id
and is_included_column = 1
order by c.name asc
for xml path('')
) x(cols)) redundant_included_cols,
(select x.cols from (
select name + ', ' as [text()]
from sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
where ic.object_id = s.obj_id and ic.index_id = s.index_id
and is_included_column = 0
order by ic.key_ordinal asc
for xml path('')
) x(cols)) superior_index_cols,
(select x.cols from (
select name + ', ' as [text()]
from sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id
where ic.object_id = s.obj_id and ic.index_id = s.index_id
and is_included_column = 1
order by c.name asc
for xml path('')
) x(cols)) superior_included_cols
from @i r
join @i s on r.obj_id = s.obj_id
where charindex(r.index_cols, s.index_cols) = 1
and r.index_cols <> s.index_cols
and r.index_id <> s.index_id
and r.index_type = s.index_type
and r.is_primary_key = 0
order by 1, 2, 3;
-
Hi Richard
thanks for the code. It mostly works, but does not pick up the index that I thought was redundant because it is a nonclustered index which was duplicating a clustered one, so your condition r.index_type = s.index_type throws out the comparison. Removing this check does bring back my spurious index.
regards
Andrew
-
andrew_dale (4/30/2015)
many thanks for the replies, they do make sense.checking the execution plan, if I run a simple query and include one of the index fields in the where clause then I get Clustered Index Seek if the where is on yps_ayrc and a Clustered Index Scan if the where is on yps_pslc
If I write a more complicated query, with a group by on yps_pslc, then it does a NonClustered Index Scan on ins_ypsI3
Don't forget to exclude from your query any columns which are not in the index. SELECT * FROM ... WHERE .. will almost always require reads from the clustered index. SELECT 1 FROM ... WHERE ... would be appropriate to use for this test, or better still, add only the columns which appear in the WHERE clause to the SELECT list.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply