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 |
|
Acedude
Starting Member
3 Posts |
 Posted - 2008-02-06 : 13:20:30
|
| Hi,Quick question regarding creating/dropping indexes in SQL Server 2005.I am processing a fact table in a data warehouse that churns 4-5 million rows on a daily basis. This table has 3-4 indexes. I tried dropping indexes before pumping data into the fact table and then recreated them in the end after all the data had been loaded. This surprisingly takes more than the usual time which i don't understand why! Can anyone explain this please? Conventionally, this is supposed to cut the time remarkably, but it is not the case.The loading time is usually an hour and ten minutes. Since this strategy does not work, i am looking for other performance boosting measures.Any suggestions?Thank you! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-06 : 23:48:22
|
| Spread table to multiple luns, load data in multipe threwads, ... |
 |
|
|
Acedude
Starting Member
3 Posts |
Posted - 2008-02-07 : 16:06:42
|
| I am sure you meant something by that.Could you please elaborate. I didn't quite get that. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-02-07 : 16:09:50
|
| What rmiao meant, I think, is to spread the filegroup/files across multiple drives. this will improve performance.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-02-07 : 16:17:50
|
| Also, make sure that if your table has a CLUSTERED index, then that should be the last one dropped and first one created. And hopefully you can feed the data into the table in the order of the clustered index.Be One with the OptimizerTG |
|
|
|
Qualis
Posting Yak Master
145 Posts |
Posted - 2008-02-07 : 16:20:55
|
| What is the Fill factor on your indexes? How many fields are covered by the indexes? What is the speed if you use:Alter Index All On MyTable Rebuild --<-- Performs a drop and createOrAlter Index All On MyTable Reorganize --<-- DefragmentsLook at the following link to see what you might try to optimize:http://msdn2.microsoft.com/en-us/library/ms188388.aspx |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-02-07 : 19:06:52
|
| To Echo the spreading up data across multiple: drives, luns, mount points (take your pick) if you put the indexes on separate disk from the data, that ususaly helps.Also, You might try batching the data in chunks. Sometimes this is faster as SQL will spool up a bunch for a rollback. |
|
|
|
|
|
|
|
|
Dropping/creating indexes