few questions1. what index are you planning to create? clustered or non clustered?2. will column be a target for data retrieval frequently (ie WHERE clause in queries)3. What would be amount of data you add to table each day through ssis job?4. Are there other indexes in tables?5. Is the column unique valued? Are there too many duplicates? whats the distribution % of values inside it?

few questions1. what index are you planning to create? clustered or non clustered?2. will column be a target for data retrieval frequently (ie WHERE clause in queries)3. What would be amount of data you add to table each day through ssis job?4. Are there other indexes in tables?5. Is the column unique valued? Are there too many duplicates? whats the distribution % of values inside it?

Hi Visakh,Thanks for your reply.1.I am planning to create a non-clustered index.2.yes,this column is used in many queries in the where clause.3.Its a incremental load,on an average 10000 records are loaded daily.4.Yes, there are around 15 non clustered indexes on this table already.5.This column has duplicate values.There are only 6 different function types used to update the function type column on the fact table which has around 12 million records.

few questions1. what index are you planning to create? clustered or non clustered?2. will column be a target for data retrieval frequently (ie WHERE clause in queries)3. What would be amount of data you add to table each day through ssis job?4. Are there other indexes in tables?5. Is the column unique valued? Are there too many duplicates? whats the distribution % of values inside it?

Hi Visakh,Thanks for your reply.1.I am planning to create a non-clustered index.2.yes,this column is used in many queries in the where clause.3.Its a incremental load,on an average 10000 records are loaded daily.4.Yes, there are around 15 non clustered indexes on this table already.5.This column has duplicate values.There are only 6 different function types used to update the function type column on the fact table which has around 12 million records.

so are you sure you need those 15 other indexes? An index has to be added only if you can get a good improvement on queries which are frequently fired.

Maintaining the extra indexes (if not being used) just creates extra overhead , regarding disk and maintenace. As indicated , review usage - one way to achieve the review is to report of writes and reads on an index. A high level of writes - and a very low level - maybe 0 - indicates data being written and NC updated , but low reads indicates limited or no usage on the index.Don't just delete them - monitor for awhile

few questions1. what index are you planning to create? clustered or non clustered?2. will column be a target for data retrieval frequently (ie WHERE clause in queries)3. What would be amount of data you add to table each day through ssis job?4. Are there other indexes in tables?5. Is the column unique valued? Are there too many duplicates? whats the distribution % of values inside it?

Hi Visakh,Thanks for your reply.1.I am planning to create a non-clustered index.2.yes,this column is used in many queries in the where clause.3.Its a incremental load,on an average 10000 records are loaded daily.4.Yes, there are around 15 non clustered indexes on this table already.5.This column has duplicate values.There are only 6 different function types used to update the function type column on the fact table which has around 12 million records.

so are you sure you need those 15 other indexes? An index has to be added only if you can get a good improvement on queries which are frequently fired.

For better Performance you need to update statistics for your SQL Server ..there might be a problem of fragmentation so u need to defragment your database also u can recognize your indexes for improved performance