Improve SQL insert latency?

Hi @Adam Weitzman

Your observed latency is expected with Azure SQL Hyperscale for small transactional workloads—Hyperscale is optimized for massive scale and throughput, not low-latency micro-inserts.

• Log write throughput limits: Hyperscale caps log write throughput around 100–150 MB/s per database, which can introduce delays for frequent small commits.
• Distributed architecture overhead: Hyperscale uses multiple replicas and page servers, so even trivial operations like TRUNCATE and small inserts incur coordination costs.
• Latency sensitivity: It’s designed for workloads with large datasets and analytical queries, not latency-critical OLTP micro-benchmarks.

To improve performance, you need to adjust your workload design or consider a different service tier.

• Change Service Tier
  • For latency-sensitive OLTP workloads, General Purpose or Business Critical tiers often outperform Hyperscale for small inserts.
  • Business Critical uses local SSDs and synchronous replicas, reducing commit latency.
• Batch Inserts
  • Instead of inserting 10 rows individually, batch them into a single INSERT statement or use table-valued parameters.
  • This reduces round trips and log flushes.
• Avoid Frequent Truncates
  • TRUNCATE in Hyperscale is metadata-heavy due to distributed storage.
  • Consider using DELETE with minimal logging or staging tables instead.
• Network Optimization
  • Ensure your Azure VM and SQL Database are in the same region and availability zone.
  • Use Accelerated Networking on the VM to reduce network jitter.
• Diagnostics & Monitoring
  • Use Query Store and Hyperscale performance diagnostics to identify bottlenecks.
  • Check if latency is dominated by log flushes or network hops.

https://learn.microsoft.com/en-us/azure/azure-sql/database/hyperscale-architecture?view=azuresql
https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching?view=azuresql

https://github.com/MicrosoftDocs/sql-docs/blob/live/azure-sql/database/hyperscale-performance-diagnostics.md?utm_source=copilot.com

I think the lag’s just hyperscale’s remote‑log hop biting ur tiny TRUNCATE→10 inserts… maybe just keep the table warm (no TRUNCATE) + send the rows once in a small batch… if it still drags, try the same load on BC to rule out the fixed hop…

Hi @Adam Weitzman

What you’re seeing is a common pattern when testing very small operations against Azure SQL Database, especially on the Hyperscale tier. In your scenario (a TRUNCATE followed by inserting 10 rows), the actual data work is minimal, so the total time is dominated by end-to-end latency rather than raw SQL insert performance.

On-prem SQL Server runs on a single machine with local storage. Azure SQL Hyperscale, on the other hand, uses a distributed architecture that includes a compute node, page servers, a log service, and Azure storage. While this architecture provides very high throughput and scalability, small operations can make fixed overheads more visible. For very small write operations, this fixed overhead (including commit and round-trip cost) becomes noticeable.

In addition, each T-SQL call from your application is a network round trip. A TRUNCATE followed by multiple individual INSERT statements introduces multiple round trips and commits. Even when the client VM and the database are in the same region, cloud latency is higher than an on-premises LAN, so a chatty pattern can produce the multi-second results you’re seeing.

How to Improve performance
The most effective improvement is to reduce the number of round trips:

• Send the 10 rows as a single batch, for example: INSERT … VALUES (…),(…),(…)
• Or pass the rows as a table-valued parameter (TVP)
• Wrap TRUNCATE and INSERT inside one transaction

Batching is a recommended best practice in Azure SQL because it minimizes client-server communication overhead and improves insert performance.

It’s also a good idea to confirm that your SQL server is using the Redirect connection policy. In Redirect mode, the client connects directly to the database node, which reduces latency compared to Proxy mode.

For more realistic benchmarking, test with a single batched insert or execute the logic in a single stored procedure call. This helps isolate database performance from network and application overhead.

If latency remains higher than expected after batching, you can check for server-side waits such as WRITELOG (commit latency) or NETWORK_IO using Query Store or DMVs. However, in most cases like this, optimizing the test pattern resolves the issue.

Helpful References:
https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching?view=azuresql
https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture?view=azuresql
https://learn.microsoft.com/en-us/azure/azure-sql/database/hyperscale-architecture?view=azuresql

Hope this helps, Please let us know if you have any questions and concerns.