August 23, 2011 at 6:51 am
Hi All,
We have a data warehouse where tables are joined on multiple criteria.
Non clustered indexes were created with a combination of as many as 10 columns (all 10 columns are used in where clause).
The table does not have a clustered index.
Please let me know whether it is effective to have such huge non clustered index?
Will a usage of clustered index in this case going to be effective?
August 24, 2011 at 12:24 am
Hello Everybody,
Can anybody help?
Do you have any sugesstions or similiar experience?
I shall be highly obliged if you can please share it.
August 24, 2011 at 2:58 pm
If all the columns are used together in where clause then it is likely a good index. You can check sys.dm_db_index_usage_stats to see if it is used and how often.
Since it's a data warehouse you want to have more indexes than in an OLTP system.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 26, 2011 at 8:47 am
Just checking but is this installed on a SQL 2000 instance?
Joie Andrew
"Since 1982"
August 26, 2011 at 9:36 pm
Hi Joie.
It's a SQL 2000 instance
August 27, 2011 at 1:59 am
Ah, okay. I ask because you won't be able to use sys.dm_db_index_usage_stats then since dynamic management objects were not introduced until SQL 2005. You could try using the Index Tuning Wizard. Check this article out:
Index Tuning Wizard SQL Server 2000
Joie Andrew
"Since 1982"
August 29, 2011 at 7:00 am
Joie Andrew (8/27/2011)
Ah, okay. I ask because you won't be able to use sys.dm_db_index_usage_stats then since dynamic management objects were not introduced until SQL 2005. You could try using the Index Tuning Wizard. Check this article out:
Oops, didn't notice the forum we were in. Always assume it is a 2005 or later version at this point.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply