database import
1 TopicWhy Your BACPAC Import Is Slow: Finding the Real Bottleneck
A slow BACPAC import is not automatically an Azure SQL performance problem. When an import runs for hours or days, people usually blame the target database. Often, they're wrong. The import could be waiting on the workstation running it, the network between that machine and Azure, SqlPackage settings, or the target database. The progress bar won't tell you which one it is. Before you scale up or restart a two-day import, work out where the slowdown actually is. Where the work actually happens A BACPAC import is not a server-side restore. A client tool (SqlPackage, SSMS, or the Import/Export service acting as the client) does this: Opens and unpacks the BACPAC file Builds the schema model in memory and creates the objects in the target Reads the table data from the file and bulk-inserts it across the network Rebuilds indexes and enables constraints Any of these steps can be the slow one. That's why you need to look at four things: the client, the network, the target, and the method itself. Use SqlPackage, not the portal For a database of any real size, run the import with SqlPackage rather than the portal. You get control over parameters and proper logs. Use the latest .NET version, because performance improvements ship regularly. A good baseline command with logging turned on: ./SqlPackage /Action:Import \ /sf:"C:\bacpac\mydb.bacpac" \ /tsn:"myserver.database.windows.net" \ /tdn:"MyDb" \ /ua:true /tid:"contoso.onmicrosoft.com" \ /df:"C:\bacpac\import.log" \ /DiagnosticsLevel:Verbose If you're investigating performance, also turn on performance tracing before you run it: Set-Item -Path Env:DACFX_PERF_TRACE -Value true It makes the log much bigger, so only use it while you're diagnosing. 1. Check the client The machine running SqlPackage does more work than people expect. Memory: the schema model is built in memory, and a large schema can use a lot of it. Disk: the BACPAC file has to be unpacked, so make sure there's enough fast local disk space. CPU: check whether any cores are maxed out during the data phase. Watch Task Manager or Performance Monitor while the import runs. If the client is maxed out while the target database is idle, scaling up the database won't help. 2. Check the network Running an import from an on-premises laptop over a VPN is the most common reason it's slow. High latency and connections that drop now and then slow down every batch of rows. Watch how much the client is sending over the network. If it stays at a low, steady level while the client and target both have spare capacity, the network is probably the limit. The quickest test is to copy the BACPAC file to an Azure VM in the same region as the target and run SqlPackage from there. Use SSD storage and enable accelerated networking on the VM. If the import gets much faster, you've found the cause. 3. Check the target Run this on the target database while the import is running: SELECT TOP (30) end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC; How to read it: What you see What it means avg_log_write_percent near 100% Log throughput is the limit. Scale up for the import. avg_data_io_percent near 100% Data I/O is the limit. Scale up for the import. avg_cpu_percent high Compute is the limit, especially during index rebuilds. Everything low the target isn't the problem. Look at the client and network or try increasing parallelism. Microsoft's guidance is to temporarily scale the target to a higher service tier for the import and scale back down afterwards. Business Critical or Premium gives the best import performance. Treat the scale-up as an experiment: record the numbers before and after so you know whether it helped. If the target is barely busy, SqlPackage may simply not be sending enough work in parallel. The default is 8. You can raise it with MaxParallelism:16. Increase it gradually and keep watching the target. For large tables with many indexes, p:RebuildIndexesOfflineForDataPhase=True can also speed up the index rebuild phase. 4. Is it slow, or stuck? A progress bar that hasn't moved doesn't mean nothing is happening. Run these checks on the target. Is there still activity? SELECT r.session_id, s.program_name, r.status, r.command, r.wait_type, r.wait_time, r.total_elapsed_time FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id WHERE r.session_id <> @@SPID; Are row counts going up? Run this twice, a few minutes apart: SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, OBJECT_NAME(object_id) AS table_name, SUM(row_count) AS rows_loaded FROM sys.dm_db_partition_stats WHERE index_id IN (0, 1) GROUP BY object_id ORDER BY rows_loaded DESC; If rows are still being added, the import is slow but alive. If you see no sessions and no rows being added, the client has probably lost its connection. Scaling up won't fix that. You'll need to fix the connection and run the import again into an empty database. 5. Should you be using a BACPAC at all? A BACPAC is a logical copy of your schema and data. That works well for small and medium databases, but it gets painful for large ones that can only be offline for a short time. If your pilot shows the import won't fit your downtime window even with the best setup, look at a migration service such as Azure Database Migration Service instead. Run a pilot before you promise a time Don't promise a fixed speed like "X GB per hour". Results depend on schema complexity, the number and size of indexes, data types, the network and the target tier. Instead: Import a representative sample, or the full BACPAC into a non-production target. Try it from a remote workstation and from an Azure VM close to the target. Try it on the current tier and on a temporarily higher tier. Try it with the default parallelism and with a higher value. Record how long each run takes, how long the largest tables take (you can get this from the log), and what the target's resource use was. Base your cutover estimate on these measurements and allow some extra time. After the import Check for disabled indexes. If an import was interrupted after the data loaded but before the index rebuild finished, some indexes can be left disabled: SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, OBJECT_NAME(object_id) AS table_name, name AS index_name FROM sys.indexes WHERE is_disabled = 1; Compare row counts between the source and the target. Update statistics. Scale the database back down to its normal production size. Quick checklist Latest SqlPackage (.NET version), not the portal Diagnostic log enabled (/df), plus the perf trace if you're investigating Client CPU, memory and disk checked Network ruled in or out (test from an Azure VM in the same region) sys.dm_db_resource_stats checked during the import Target scaled up for the import, with numbers recorded before and after Parallelism tuned only if the target is underused Slow vs stuck confirmed with row counts Indexes, row counts and statistics checked afterwards, and the tier scaled back down When an import is slow, find out which part is slow before you change anything. References: Troubleshoot issues and performance with SqlPackage Import a BACPAC file to Azure SQL Database