Parallel Insert Select in SQL Server

With SQL Server 2016, we are now able to use parallelism in the query using INSERT INTO… SELECT with WITH (TABLOCK).

In order for the query to run in parallel, the compatibility level of the database must be at least 130.

You may want to read the article “What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level“.

Let’s examine how it works by making an example.

First, create the Person.Person table’s create script in the AdventureWorks database as follows.

Then, using this script, let’s create a table named Person.Person2 with the same structure as Person.Person. Remember to change the table and constraint names before running the script. Otherwise you will receive errors like the following.

Msg 2714, Level 16, State 5, Line 27

There is already an object named ‘DF_Person_NameStyle’ in the database.

Msg 1750, Level 16, State 1, Line 27

Could not create constraint or index. See previous errors.

The script you will execute should be as follows.

After creating the table, we remove the Primary Key from the Person2 table and run the insert into clause with the following script without parallelism.

When we look at the execution plan, we see that Clustered Index Scan and Table Insert operations are performed without parallelism.

Execute the INSERT INTO statement with WITH (TABLOCK) as follows and look at the execution plan again.

As you can see the query worked this time using parallelism.

Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *