Monday, January 16, 2017

Take advantage of parallel inserts in SQL 2016

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.