Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Indexing

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 answer

few questions
1. 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 answer

few questions
1. 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 MVP
http://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.
Go to Top of Page

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
Go to Top of Page

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 answer

few questions
1. 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 MVP
http://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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 awhile

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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 answer

few questions
1. 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 MVP
http://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 MVP
http://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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -