| Author |
Topic |
|
ahmeds08
Aged Yak Warrior
737 Posts |
 Posted - 2012-12-06 : 00:47:47
|
| Dear Experts,I have a column in a table which has around 12 million records.this columns gets updated daily in a ssis job.so,creating a index on this column will it be a good thing?Thanks,Javeed. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-06 : 02:03:53
|
| thats a very vague question. it depends would be best answerfew 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?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2012-12-06 : 02:51:04
|
quote:
Originally posted by visakh16
thats a very vague question. it depends would be best answerfew 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?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
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. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-06 : 08:48:27
|
| I wouldn't bother about adding index if it already has 15 indexes. Can you check index usage report if they are being used. Only create which is used and required |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-07 : 08:40:50
|
quote:
Originally posted by ahmeds08
quote:
Originally posted by visakh16
thats a very vague question. it depends would be best answerfew 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?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
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.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-12-07 : 11:40:10
|
| 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 awhileJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2012-12-17 : 06:33:15
|
quote:
Originally posted by visakh16
quote:
Originally posted by ahmeds08
quote:
Originally posted by visakh16
thats a very vague question. it depends would be best answerfew 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?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
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.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hi Visakh,thanks for the reply.Yes,all the other 15 indexes are created on foreign key columns which are very much used daily in the ssis lookups to pull the key value |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-19 : 01:09:07
|
| ok...then it makes sense for them to be there. Make sure you drop and recreate indexes in case of mass DML operations otherwise there can be performance hit in trying to update all these indexes------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
lopez
Starting Member
8 Posts |
Posted - 2012-12-26 : 07:37:33
|
| 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 |
|
|
|
|
Indexing