For testing purposes, I created 15 different .mdf files that are essentially the same file (300MB) but with different names. When I use CREATE DATABASE FOR ATTACH for a single database, it takes about 2 seconds.
When I run it in parallel via C# code(SqlCommand), I would expect it to take a couple of seconds, but it takes 35 seconds. It seems to me there is a lock somewhere, but I am not sure why and where, as it does not use the model database because of the FOR ATTACH option.
I'm using Docker for running SQL Server. Ext4 file system is used and there is only one .mdf & .ldf file per database.
Here is a link to the logs captured via SQL Profiler: Log file
1 Answer 1
Running a loop on sys.dm_tran_locks while attaching a database shows, unsurprisingly, that it requires X-locks on rows in various system tables.:
- sysdbfrag
- syspru
- sysbrickfiles
- sysdbreg
Some of these are documented, and they've been blogged about in various places as being the sources for sys.databases among others.
Quite why you would want to parallelize these CREATE DATABASE commands is a different question: it shouldn't even take 2 seconds to attach them so perhaps your hardware needs looking at, and it's not clear why 30 seconds to attach 15 databases is so onerous.