SQL Server INSERT Performance (SQL, Azure SQL Database)

The query runtime appears to be dominated by IO waits.

Here are the wait stats

PAGEIOLATCH_EX is a wait to write to disk, PAGEIOLATCH_SH is a wait to read from disk, and LOG_RATE_GOVERNOR is essentially also an IO wait, waiting to write to the log file. The IO and Log write limits on a 20DTU database are quite small, and the standard tier DTU model provisions only 1-4 IOPS/DTU, so that's under 100 IOPS.

So you can either

1. Write less data

-by eliminating columns, especially the nvarchar(max) column if it's large

-by compressing the data using Page Compression or a Clustered Columnstore index, or by using the COMPRESS TSQL function for the nvarchar(max) column if it is large

or

2. Provide more resources

-by scaling to a higher DTU, or VCore configuration, or a Serverless configuration with elastic scale

-by moving to Hyperscale which provides 100MB/S log throughput at every service level

-Moving this database into an Elastic Pool where it can share a larger pool of resources with other databases.

Table partioning won't reduce the amount of writes. And In-Memory OLTP is only available in the Premium/Business Critical Tier, which already has higher IOPS.