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.
| Author |
Topic |
|
prashant_d26@yahoo.com
Starting Member
1 Post |
 Posted - 2009-03-10 : 12:46:45
|
| I have a database having a single flat table. It's size is around 30GB. I have created approximately 75 indexes on this table. everyday new data gets loaded in this table. daily batch size is around 100K records. I have to drop the indexes before inserting the new data and re-create all indexes after the load is finished. My problem is the consistency of performance while re-creating these indexes. Sometimes it happens quickly (4 hours) and sometimes it takes lot of time ( goes around 20 hours). I am not sure what is going wrong here. I would like to know your thoughts on this issue. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-10 : 12:53:43
|
| Dont think you need to drop/recreate the inserts for 100k rows.. just re-index all ..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-10 : 13:10:14
|
| Why are there 75 indexes on your table? That seems to me an absurd amount of overhead. How many columns total on your table?Mike"oh, that monkey is going to pay" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-10 : 14:43:29
|
quote:
Originally posted by mfemenel
Why are there 75 indexes on your table? That seems to me an absurd amount of overhead. How many columns total on your table?Mike"oh, that monkey is going to pay"
I guess indexes are overlapping same columns which makes query engine to confuse. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-10 : 15:05:52
|
| I'm just trying to fathom the overhead of updating that many indexes on a table that is 30GB. It would be interesting to see in the DMV's which ones actually are used. My guess is 90% of the indexes have never been touched or touched infrequently enough to be thrown out.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
performance issue while re-creating indexes