Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run (as an apprentice DBA!) to maximize the performance of the database?

For instance, I executed EXEC sp_updatestats but it reported that no indexes required updating.

Is there a list of things to do once large amounts of data has been loaded or does SQL Server 2008 just take care of all that for you?

share|improve this question

migrated from stackoverflow.com Nov 2 '11 at 12:25

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 8 down vote accepted

If you're loading to an empty database, you could/should take steps to avoid requiring any additional maintenance steps post load. Fragmentation is the enemy, that's what you're trying to avoid.

  • Drop all NC indexes before loading.
  • Post load, add the NC indexes for each table in sequence i.e. don't add an index to TableA, then TableB, then back to TableA. This is has no effect on fragmentation but it can improve the time taken to add the indexes on very large datasets (reduces buffer pool churn).
  • If you can insert data to a table in clustered index order, leave the clustered index in place. If you can't, dump the data into a heap and rebuild in to a clustered index post load.

Rather than copy and paste, I'll point you toward the very comprehensive list of references for ETL optimisation @Marian put together in an answer to a question I asked on BCP. Many of these will be equally applicable to your scenario.

share|improve this answer
2  
Interesting, on the link to the videos from SQLCAT and SSIS their finding was never drop a NC index. Guess this is yet another case of "it depends" –  billinkc Nov 2 '11 at 15:15
2  
@billinkc was that not for incremental loads, rather than the empty database this question refers too? Either way, SQLCAT testing does tend to be involve a class of hardware rarely seen in typical organisations. –  Mark Storey-Smith Nov 2 '11 at 15:23

It depends on how much of a window you have but generally updating statistics and rebuilding/reorganizing indexes is a good step to take. Other than that there shouldn't be anything else you need to do really.

The statistics tell the query optimizer how many rows are likely affected by an operation and that in turn tells SQL which approach to take to run your queries. Inserting data can skew the distribution and if you haven't inserted enough rows to trigger an update of statistics in auto update mode the manual one should help. If you have the window, I say keep the stats update portion.

Indexes can become fragmented as data is added. Rebuilding or Reorganzing helps reduce this which can lead to improved performance when actually accessing your data.

share|improve this answer
4  
Along the lines of Indexes, in one of the SSIS videos from SQLCAT, they also suggest a rule of thumb that if your data growth is > 100% and there's a single NCI, drop and recreate them. If it's more than 10% and there are 2+ NCI, drop and recreate will yield better performance. –  billinkc Nov 2 '11 at 14:23
2  
Just to clarify Bill's comment - I think he is saying the suggestion is for the times to drop-create they mean Drop Index, Do your data load, Then Recreate your index.. Rather than leave the index(es) there during the load and rebuild after. I think so anyway :-) and yeah, great point, I +1 that comment. –  Mike Walsh Nov 2 '11 at 14:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.