Hello Group,

Here is some further information that might be of interest.

I created a 10GB test table database with a big for loop in an exec block statement with various indices, however interestingly this database did not exhibit the same "go slow" behaviour. What I mean by that is that obviously it was slower to activate the indices then restore the data pages, but the process was always using a substantial percentage of the CPU in this time and a reasonable level of disk I/O (relative to the capacity of the VM) and finished in expected time.

I then took the real database that took 11+ hours to restore, picked a largish child table (~20 million records ) and its parent table (~75000 records) and dropped everything else from the database, all views, procedures, triggers, udfs, etc except these two tables, their associated constraints and indices.

Here are the restore measurements

FBK size 2.5GB

Data pages ~3 minutes
activate PK child table ~2 minutes
activate 2 indices on child table ~1 minute each
activate PK parent table ~a few seconds
activate FK from child table to parent table ~7 minutes
activate FK from child table to parent table (different child table field) also ~7 minutes

FDB Size 3GB

I then created another index that mimicked the index created by one of the FK from child table to parent table (ascending index on the same field).

In my next test, this new index tool ~1 minute to activate (vs ~7 minutes for the equivalent FK).

Whilst there does need to be a constraint check as well with the FK (ie does the inserted value exist in the parent table PK index), I am quite surprised that the impact is so great. Note this is just a theory, I am still working on making a shareable database that illustrates the problem.

I need to kick off that full 11 hour restore again to confirm whether the specific slow to activate indices were indeed the FKs, but I thought I would post an interim update in case anyone has noticed the same.

Thanks
Adam