0

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.

sql profiler logs

Here is a link to the logs captured via SQL Profiler: Log file

8
  • There is no actual question here... just statements. Commented Jul 10 at 10:31
  • What type of storage are you running on? How many NDFs and LDFs does each database have? Are they physically located on the same server as where the databases are being created? "When I run it in parallel" - What does this mean...what methodology are you using to run it in parallel?
    – J.D.
    Commented Jul 10 at 13:02
  • 1
    Have you already looked at the wait stats during the attaching of the databases?
    – Peter
    Commented Jul 10 at 13:02
  • @J.D. I've just updated my question with more info.
    – Obscure_33
    Commented Jul 10 at 14:09
  • 1
    The symptoms suggest the operation acquires blocking locks so parallel commands will not reduce duration compared to single-threaded.
    – Dan Guzman
    Commented Jul 10 at 18:05

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.