The total execution time of SqlPackage (DacFx) publish is overly dependent on SQL Server network latency.

[PetrPospisil]

• SqlPackage or DacFx Version: 162.5.57 latest
• .NET Framework (Windows-only) or .NET Core: 8.0.11 latest
• Environment (local platform and source/target platforms): Windows 11 OS, Azure MSSQL database

Steps to Reproduce:

1. Create SQL server in azure
2. Create EMPTY SQL database in the SQL server
3. (optional) Run ping on the SQL server
4. Use some DACPAC with a lot of objects to create (in our case it is around 3000 tables, constraints, procedures and so on)
5. Run SQLPackage to deploy the DACPAC
6. Read time elapsed

Did this occur in prior versions? If not - which version(s) did it work in?
Not version related.

(DacFx/SqlPackage/SSMS/Azure Data Studio)
DacFx and SqlPackage.

Hi,
We have encountered a performance issue with SQLPackage (DacFx) when publishing a DACPAC containing a large number of SQL objects (approximately 3,000 tables, constraints, procedures, etc.) to an empty database. We tested this on the WUS3 and WE data centers.

Observations:

• Low Latency (1 ms):
  When running SQLPackage Publish on an Azure SQL Server with a 1 ms ping (a virtual machine in the same data center as the Azure SQL Server), the deployment of the DACPAC takes approximately 1 minute and 30 seconds.
• Moderate Latency (30 ms):
  When running SQLPackage Publish on an Azure SQL Server with a 30 ms ping (e.g., from my PC in Central Europe publishing to an Azure SQL Server in the WE data center), the deployment takes approximately 4 minutes.
• High Latency (140 ms):
  When running SQLPackage Publish on an Azure SQL Server with a 140 ms ping (e.g., a virtual machine in the WE data center publishing to an Azure SQL Server in the WUS2 data center, or vice versa), the deployment takes approximately 20 minutes.

Root Cause:
We identified the issue within DacFx during the execution of the generated publish script. For each database object, a request is sent to the SQL Server (140 ms) and a error is checked (140 ms). This process occurs sequentially in a single thread, without any batching of operations.

This approach significantly increases the total execution time. In our worst-case scenario, with 3,000 objects, the estimated additional time is:
3,000objects×(140msrequest+140mserrorcheck)= 14minutes

Request for Advice:
We hope this provides a clear understanding of the issue. Is there any advice or optimization we could try to speed up the DACPAC deployment process?

Thank you!

Sincerely,
PP

[ErikEJ]

@PetrPospisil

For each database object, a request is sent to the SQL Server (140 ms)

Can you share an example of the query executed?

[PetrPospisil]

Hello,
here is some sample project with 3000 generated tables:
DatabaseTest.zip

here are publish test files
DeployTest.zip

here is sample output from SQLPackage:
sql-17447-customers-ci-001.database.windows.net.output.txt
It is from my PC (Central Europe) against WE SQL server run with ping 30ms. It took about 3+mins to complete.

Here is screen from Azure Data Studio:

You can see that tables are created one at the time. For every table there is one call to create the table and second call to check errors. Every batch run adds 30ms to total execution time. So 2x 30ms per table due to server latency.

Is there anything else you need?

Sincerely,
PP

[ErikEJ]

@PetrPospisil

Is there anything else you need?

Yeah, DacFX to be open source 😆 - but thanks for the detailed repro

[llali]

@PetrPospisil what is the maxdop? and how many vCore you db has? here's some recommendations:

Compute Utilization can be viewed on Azure portal under Overview --> Monitoring. This will help to see the database resource utilization while export is in-progress and accordingly allocate required resources to Azure Sql like vCores or memory.
Increase query parallelism by increasing MAXDOP in database. Refer Server configuration: max degree of parallelism - SQL Server | Microsoft Learn for finding the right MAXDOP value for specific Sql Server configuration
If needed, increase sqlpackage parallelism by passing in /MaxParallelism:###. The default value is set to 8, which should be sufficient in most cases. However, if the Azure SQL server has ample compute resources (vCores), a higher value can be configured to enhance parallelization.

[PetrPospisil]

Hello,
this issue is not related to the performance of the SQL server. The speed of execution plans do not matter here. The issues is rather about how the DacFx executes generated insert script for the database. It does it in single thread (as the SQL server requires it in docs), but rather then doing it in batches, like creating multiple resources (like using EXECUTE or sp_executesql) per one "GO", it does everything as one resource per one GO which is extremely dependent on network latency because every resource is created using two network requests (create resource + check errors) as seen in screenshot from data studio.