How to speed up rebuilding a database from scratch/scripts

Question

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?

Answer 1

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

Answer 2

The scope on the question is pretty wide, so the answer is going to be pretty wide-ranging and generic too, so bear with me.

You could have a few different problems:

• The setup queries might be doing a lot of work (like creating a database and growing its log file)
• The SQL Server may be underpowered
• The app server may be running a whole lot of queries, one at a time, waiting on network round trips

To figure it out, I'd let a deployment run, and then use sp_BlitzCache from the open source First Responder Kit. (Disclaimer: I'm involved with the project too.) sp_BlitzCache can analyze your plan cache for long-running queries like this:

EXEC sp_BlitzCache @SortOrder = 'duration', @MinutesBack = 10;

That'll give you a list of the top 10 longest-running queries from your setup script (assuming it started within the last 10 minutes.) That'll tell you if maybe one of the queries is running much longer than the others.

Look at the Duration columns in sp_BlitzCache's output. Take the longest-running query, and try that one in isolation. If it's creating a database and growing its log file, for example, then you can work on making that query run faster. Or, if none of the long-running queries are more than, say, 5 seconds long, you might start looking at the sheer number of queries in the deployment script: are we talking hundreds, thousands, tens of thousands?

That's the thought process I'd use to narrow down where the root cause is.

Answer 3

We have now been able to reduce the execution time for our databse build process to an acceptable level.

Special Thanks to Brent Ozar and David Brown who’s anwers helped in identifying problematic areas: @Brent: I was able to shave of some time by deactivating Forced Parameterization @David: DELAYED_DURABILITY is an option but we are currently testing on SQL Server 2012 which does not offer that. But we will use it once we switch to our "main branch".

I would like to post what we have done:

Compatibility Level

We were setting the compatibility level to 90 at the beginning of our creation scripts. Changing this to our target (100) (don’t ask) reduced the building time by about 10 seconds.

Indexing

Our “build order” created the indexes at a very early step. Almost right after creating the tables. I moved this so the indexes are now being created after most data is inserted. This reduced building time by another 10 seconds.

Switching from sqlcmd.exe to PowerShell module SqlServer

One of our developers changed the PowerShell script which controls the build process. We were using sqlcmd.exe to execute every sql-script. We now switched to the powershell SqlServer. So instead of calling the sqlcmd.exe we do this:

        Invoke-Sqlcmd -OutputSqlErrors $true `
            -ServerInstance $global:DbServer `
            -Database "master" `
            -InputFile $createDatabaseScript `
            -Variable "catalog=$global:dbName" `
            -Verbose:$false

With this we were able to reduce the build process by whopping 50%.

These steps and our solution are by no means perfect but the build time is now at an acceptable level. Thank you everyone!