Thursday, January 19, 2006

SSIS Performance Tips

Not that I am an experienced SSIS developer but I'll try to give some general guidelines to optimize the performance of bulk import operations.

  • Try to get your flat files sorted by the clustered index of the destination table
  • Use the 'Fast Parse' option; there are some limitations for date (time) and integer data
  • For OLEDB Connections use the Fast Load setting
  • Use a SQL Server Destination if possible (can be up to 25% faster!)
  • Use BULK_LOGGED recovery model for your SQL Server destinations
  • The MaxConcurrentExecutables package setting defines how many tasks can run concurrently (default number of logical cpus + 2)
  • Change the EngineThreads property of a task (defaults to 5 but could support more on multi-processor servers - testing is the key)
  • Run parallel import steps if possible
  • Use the right isolation level for your package and container
  • Import into a heap from multiple files in parallel and then recreate the indexes (clustered first then the non-clustered indexes)

No comments: