Sql-server – How to speed up rebuilding a database from scratch/scripts

powershellscriptingsql server

Our Database exists in many different scripts within our source control. i.E. every table has it's own scripts for creating the table, the indexes and so on.
When needed, we can rebuild the entire dev-database by starting a powershell script which will run through every script by feeding it to sqlcmd.exe.

This process recently took about 30 to 40 minutes. We discovered that we were using the old 2008R2 Version of sqlcmd, thanks to a mistake in the local path variables, which pointed to the old version.
After fixing this the entire process was reduced to about 8 minutes.

I am now wondering if there are any "switches" I might have overlooked which could speed up this process even more.

The Database is currently set to compat level 100 (for stupid reasons), multi_user mode and simple recovery model. The database runs locally and no user will be connecting during the rebuild process.

I did try setting it to single_user mode but ran into an issue. Apperently the sequential process of firering one sql-script after another seems to be too quick(?) for it's own good. Somehow one connection is not closed fast enough before the next one is established.

Are there any other options either on query or database level that could speed up a database rebuild process in this scenario?

Best Answer

Applying schema scrips ends up being a lot of small transactions, and that can cause lots of log file waits. You can try running the scripts in a transaction, but not all changes are compatible with transactions.

So an easy way make the scripts never wait on log flushes is to to turn on Delayed Durability while running the scripts.

EG

ALTER DATABASE current SET DELAYED_DURABILITY = disabled;

go

declare @i int = 0
while @i < 10000
begin

    exec ('
    drop table if exists mt;
    create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
    ')
    set @i += 1;

end

go

ALTER DATABASE current SET DELAYED_DURABILITY = forced;

go

declare @i int = 0
while @i < 10000
begin

    exec ('
    drop table if exists mt;
    create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
    ')
    set @i += 1;

end
Related Question