Improving ALTER FRAGMENT ATTACH performance

You can take advantage of the performance optimizations for the ALTER FRAGMENT ATTACH statement if your database meets certain requirements.

To take advantage of the performance optimization, you must meet all of the following requirements:
  • Formulate appropriate distribution schemes for your table and index fragments.
  • Ensure that no data movement occurs between the resultant partitions due to fragment expressions.
  • Update statistics for all the participating tables.
  • Make the indexes on the attached tables unique if the index on the surviving table is unique.
Important: Only logging databases can benefit from the performance improvements for the ALTER FRAGMENT ATTACH statement. Without logging, the database server works with multiple copies of the same table to ensure recoverability of the data when a failure occurs. This requirement prevents reuse of the existing index fragments.