March 29, 2012 at 11:47 am
I was given a DB by the client. I've noticed that almost for all tables there are duplicated indexes, i.e. for the same field there may be 2 indexes one clustered and another one non-clustered. Is there any particular reason for it? Also sometimes there are 2 non-clustered indexes on the same fields?
March 29, 2012 at 12:42 pm
WaitingWonder2 (3/29/2012)
I was given a DB by the client. I've noticed that almost for all tables there are duplicated indexes, i.e. for the same field there may be 2 indexes one clustered and another one non-clustered. Is there any particular reason for it? Also sometimes there are 2 non-clustered indexes on the same fields?
Are they identical in all ways? i.e. no additional columns in the index? Did you check for included columns?
Can you post the DDL for your table and all indexes? change the object names to protect the guilty.
BTW fields are for UI developers and Excel users, we say columns around here 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 29, 2012 at 11:58 pm
Just noticed that include is different.
March 30, 2012 at 8:04 am
If you post the DDL we can help you identify indexes worthy of consolidation or elimination.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 9:07 am
One other advantage I have seen with having a clustered index on a specific column and then also have an non clustered index on the same column is that the non clustered index is typically smaller in size (depends on how many additional columns and included columns also fillfactor) and in turn uses less memory to read which makes things faster. While still having the same column as the clustered index helps non index covered queries (such as ad-hoc queries) or provides better organization for additional indexes, you don't need to load a large table into memory to scan the column in question.
March 30, 2012 at 9:16 am
DBA_Dom (3/30/2012)
One other advantage I have seen with having a clustered index on a specific column and then also have an non clustered index on the same column is that the non clustered index is typically smaller in size (depends on how many additional columns and included columns also fillfactor) and in turn uses less memory to read which makes things faster. While still having the same column as the clustered index helps non index covered queries (such as ad-hoc queries) or provides better organization for additional indexes, you don't need to load a large table into memory to scan the column in question.
Nice point. I had not heard that argument before. Have you had experience with this use-case? Does the optimizer regularly choose the nonclustered index when it's less overhead (covering) or have you had to issue index hints to get it to look towards the nonclustered instead of the clustered?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 9:27 am
opc.three (3/30/2012)
DBA_Dom (3/30/2012)
One other advantage I have seen with having a clustered index on a specific column and then also have an non clustered index on the same column is that the non clustered index is typically smaller in size (depends on how many additional columns and included columns also fillfactor) and in turn uses less memory to read which makes things faster. While still having the same column as the clustered index helps non index covered queries (such as ad-hoc queries) or provides better organization for additional indexes, you don't need to load a large table into memory to scan the column in question.Nice point. I had not heard that argument before. Have you had experience with this use-case? Does the optimizer regularly choose the nonclustered index when it's less overhead (covering) or have you had to issue index hints to get it to look towards the nonclustered instead of the clustered?
I've found a nice article on the topic. http://www.databasejournal.com/features/mssql/article.php/3787021/Exploring-SQL-Servers-Index-INCLUDEs.htm
March 30, 2012 at 9:38 am
Hel (3/30/2012)
opc.three (3/30/2012)
DBA_Dom (3/30/2012)
One other advantage I have seen with having a clustered index on a specific column and then also have an non clustered index on the same column is that the non clustered index is typically smaller in size (depends on how many additional columns and included columns also fillfactor) and in turn uses less memory to read which makes things faster. While still having the same column as the clustered index helps non index covered queries (such as ad-hoc queries) or provides better organization for additional indexes, you don't need to load a large table into memory to scan the column in question.Nice point. I had not heard that argument before. Have you had experience with this use-case? Does the optimizer regularly choose the nonclustered index when it's less overhead (covering) or have you had to issue index hints to get it to look towards the nonclustered instead of the clustered?
I've found a nice article on the topic. http://www.databasejournal.com/features/mssql/article.php/3787021/Exploring-SQL-Servers-Index-INCLUDEs.htm%5B/quote%5D
Thanks. In looking through the section "Duplicating the Clustered Index or a Part of It" it lost me when it started talking about INCLUDES. I was aware of the benefits of replicating an index'es leading columns plus adding an INCLUDE to create additional coverage, but that is a slightly different variation to replicating a clustered index in every way as a nonclustered index in hopes of having the optimizer use it to cover a query rather than using the clustered index, which would naturally be wider than the nonclustered index.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2012 at 10:20 am
Yes, I have experience with creating a nonclustered index with the same column as a clustered index. For example:
table1 (col1, col2, col3, col4, text1, text2, text3, date1, date2)
clustered index idx_clustered on table1(col1)
nonclustered index idx_nonclustered on table1(col1)
with the above it really depends on how wide the table is (mine actually has about 30 columns and millions or rows) if you were to run the following:
select col1,col2,text1,date2
from table1
where col1 = 254
It may use the clustered index depending on a various amount of things such as uniqueness of the col1 again size of the table and number of expected rows to return.
if you only were to select col1 and no other it would absolutely use the nonclustered index. I have both situation on different tables.
Since in the first one there is joining multiple tables using col1 from table1 to join it prefers to use the nonclustered index and is less costly to do an index scan with a bookmark lookup versus a full table scan and even more so for other queries to do an index seek with a rowid lookup. Again if it decides that the table is small enough that loading it all into memory for a full table scan versus constant (meaning 100K+) lookups it will take that route.
I would only suggest a nonclustered index as a duplicate clustered index (without the table data overhead) depending on size of the table and the need of the query. No solution works for every situation.
P.S. I do not recommend Index Hints unless you have no other choice and it has been tested thoroughly. Lower cost in execution plans does not always mean faster results.
March 30, 2012 at 10:33 am
DBA_Dom (3/30/2012)
Yes, I have experience with creating a nonclustered index with the same column as a clustered index. For example:table1 (col1, col2, col3, col4, text1, text2, text3, date1, date2)
clustered index idx_clustered on table1(col1)
nonclustered index idx_nonclustered on table1(col1)
For completeness, I assume if you're clustered index is unique (preferred) then you would also choose to make the nonclustered unique.
with the above it really depends on how wide the table is (mine actually has about 30 columns and millions or rows) if you were to run the following:
select col1,col2,text1,date2
from table1
where col1 = 254
It may use the clustered index depending on a various amount of things such as uniqueness of the col1 again size of the table and number of expected rows to return.
if you only were to select col1 and no other it would absolutely use the nonclustered index. I have both situation on different tables.
That's what I was looking for, thanks for confirming.
Since in the first one there is joining multiple tables using col1 from table1 to join it prefers to use the nonclustered index and is less costly to do an index scan with a bookmark lookup versus a full table scan and even more so for other queries to do an index seek with a rowid lookup. Again if it decides that the table is small enough that loading it all into memory for a full table scan versus constant (meaning 100K+) lookups it will take that route.
I would only suggest a nonclustered index as a duplicate clustered index (without the table data overhead) depending on size of the table and the need of the query. No solution works for every situation.
P.S. I do not recommend Index Hints unless you have no other choice and it has been tested thoroughly. Lower cost in execution plans does not always mean faster results.
I could not agree more about table hints...judicious use only. I was asking because if table hints were a somewhat necessary way to compel the optimizer to use the nonclustered index that would setup a serious pitfall. Say someone was reviewing indexes and found the supposed-duplicate and did not know the benefits of having such an index in place. If the nonclustered index were dropped that would constitute a breaking change.
Thanks for the reply
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply