SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Indexing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ahmeds08
Aged Yak Warrior

India
625 Posts

Posted - 12/06/2012 :  00:47:47  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

India
52317 Posts

Posted - 12/06/2012 :  02:03:53  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 12/06/2012 02:04:11
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
625 Posts

Posted - 12/06/2012 :  02:51:04  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/06/2012 :  08:48:27  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/07/2012 :  08:40:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2032 Posts

Posted - 12/07/2012 :  11:40:10  Show Profile  Visit jackv's Homepage  Reply with Quote
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

India
625 Posts

Posted - 12/17/2012 :  06:33:15  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

India
52317 Posts

Posted - 12/19/2012 :  01:09:07  Show Profile  Reply with Quote
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

USA
8 Posts

Posted - 12/26/2012 :  07:37:33  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000