In SQL 2016
it’s possible to insert in parallel. I was asked to do some test about this and
I had no clue how good this was. But after those tests aim very excited about
how fast it is.
I had a quite
large table with 220 million rows and ca 75GB in size. It had one clustered
index on it and recovery set to simple. I tested first an INSERT and then SELECT
INTO and INSERT WITH TABLOCK. The last two take advantage of the parallel
insert.
Hardware
setup was a HP ProLiant BL460 G9 with 256GB RAM, 24 core and Windows 2016. SQL 2016 did run with Max degree
of parallelism set to four. SQL had
128GB as Max memory sett.
A query plan
compare show the different. As you can see we have a parallel insert in the
second query plan.
The
difference in time was amazing. 4,2 minutes compared to 18,2 minutes. A reduce
in time by ca 76%, that’s cool. So, when possible remember to evaluate if you
can use TABLOCK. I think it´s possible in many ETL processes. Also, its quite
common that you do select into any temporary table, then it run a parallel
insert anyhow.
