Lesson Learned #223:Hands-On-Labs: How to improve a bulk insert process

[Jose_Manuel_Jurado]

We used to have cases where our customers reported that they need more throughput using a bulk insert process for their database located on Azure SQL Database, Azure SQL Managed Instance or On-Premises.  In this video below we going to provide us some insights about how to improve this throughput.

Review at Network level

• Network device bandwidth.
• Old network drives.

How to improve?

• Increase Packet Size if possible and test.
• Run in parallel the process.
• if you are running in Azure review the accelerated networking feature in your Virtual Machine.
• Review - TCP/IP performance tuning for Azure VMs | Microsoft Docs
• Download the neccesary data
• Try to avoid to use MARS.
• Use parametrized query if posible to avoid roundtrips.
• Review Behind the Scenes on OPTIMIZE_FOR_SEQUENTIAL_KEY - Microsoft Tech Community
• Delete the indexes, constraints and re-create it also consider statistics.
• Use Business Critical/Premium/Hyperscale.

Enjoy!