SQL Server Optimize Insert Performance In 5 Steps. What are the possible reasons of the slow table insert? What are ways to identify this bottleneck without the execution plan?
- DROP ALL triggers and constraints on the table
- DROP ALL indexes (except the ones that are needed by the insert)
- Prevent page splits: make sure you use a clustered index that new records will be inserted at the end of the table. Use an IDENTITY column as the primary key.
- Don’t leave empty space in the table: set the fill factor to 0 or 100 (same), reducing the number of pages that the data is spread across.
- Reduce transaction log overhead: change the recovery model of the database to Simple