Saturday, December 2, 2017

SQL Server - Best Practices in SSIS 2012

Following are some of the best practices in SSIS which is helpful in performance optimization

  • Hardware Infrastructure: Disks, RAM, CPU, Network
  • SQL Infrastructure: File Groups, Indexing, Partitioning
  • Retrieve only the data that you need
      • With NOLOCK hints to avoid locking overhead
      • Perform simple logic at Source
      • Don’t use excessive data conversions
  • Choose most suitable connection
  • Tune Source if possible
  • Heap insert is typically faster than Cluster index
  • Avoid blocking components in large datasets
  • Choose wisely between SSIS and T-SQL
  • Do minimally logged operations
  • Partitioning the table
  • Smartly use the transactions in package
  • Use Package Configuration for managing packages easily
  • Standard Naming Convention
  • Use Annotaions in Packages
  • Use Memory not Disk
  • Use stored procedures instead of in-line SQL

No comments:

Post a Comment