Esat Erkec

How to use parallel insert in SQL Server 2016 to improve query performance

December 8, 2017 by

Introduction

In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time.

  • SQL Server can execute queries in parallel
  • SQL Server creates a path for every query. This path is execution plan
  • The SQL Server query optimizer creates execution plans
  • SQL Server query optimizer decides the most efficient way for create execution plan

Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed.

In the SQL Server Engine, there is a parameter to set up a limit aka governor for CPU usage. This setting name is MAXDOP (maximum degree of parallelism). We can set this parameter in T-SQL or SQL Server Management Studio under the properties of the server. “0” means SQL Server can use all processors if they are necessary

We can change this option using the following T-SQL script


Parallel execution plans, MAXDOP and ENABLE_PARALLEL_PLAN_PREFERENCE

The Query optimizer analyzes possible execution plans and then chooses the optimal execution plan. This selection is based on the value of query estimated cost. In some cases, the SQL Server query optimizer chooses a parallel execution plan, primarily because the SQL Server query optimizer decides a parallel execution plan cost is more optimum than a serial execution plan.

Now we will look at some examples of parallel execution plans and properties. This query will generate a parallel execution plan.


The screenshot is from ApexSQL Plan, a free tool to view and analyze SQL Server query execution plans

In the previous example, we can see parallel operators and as has been highlighted, Degree of Parallelism show us how many threads are used in this query.

In this step, we will look at the MAXDOP query hint. We can dictate to the SQL Server query optimizer how many threads will run in parallel. This hint specifies the number of threads for the query. We will apply this query hint to the following query and we will change our degree of parallelism to 2


A new query hint is supported in SQL Server 2016 SP1 or above versions. This hint is ENABLE_PARALLEL_PLAN_PREFERENCE. It allows us to force the SQL Server query optimizer to select parallel plan instead of serial plan.

We will use ENABLE_PARALLEL_PLAN_PREFERENCE in this query and the Query Optimizer will generate a parallel plan.

  • Parallel execution plan with “ENABLE_PARALLEL_PLAN_PREFERENCE” query hint

  • Parallel execution plan without ENABLE_PARALLEL_PLAN_PREFERENCE query hint

To this point, we have discussed SQL Server query optimizer parallel processing decision, mechanism, and usage. Next, we will discuss SQL Server 2016 parallel insert and performance impact.

Parallel insert

In SQL Server 2016, Microsoft has implemented a parallel insert feature for the INSERT … WITH (TABLOCK) SELECT… command. The parallel insert functionality has proven to be a really useful tool for ETL / data loading workloads which will result in great improvements for data loading. This is important because, in data loading, performance and time is a key metric.

We will look for answers to the following questions in the next section of the article

  • How parallel insert work
  • How parallel insert improves performance
  • How parallel insert generates execution plans

Requirements

  1. SQL Server 2016 installed
  2. WideWorldImporters Database (Microsoft new sample database for SQL Server)

Create a sample for parallel insert

We will use [Warehouse].[StockItemTransactions] table. To enlarge the data, we will create dummy source destination and insert records

This number defines how many times the record will be added.

In this step we will create destination table

We will look at parallel and serial insert query differences.

Parallel execution:

Tip:

TABLOCK hint provides lock escalation on the table level for source tables. Table level exclusive locks reduce concurrency, which means another sessions cannot insert or update a table when the parallel insert is running

In previous query create parallel execution plan. Left to right it contains:

Parallel table scan operator: this operator indicates SQL Server reads the whole table row by row because we don’t have any index on source table. In the operator properties we can see number of rows read option. This option tells us which thread read how many rows and the parallel option tell us this operator run in parallel

Parallel table insert: this operator indicates parallel insert operations. If we look at the properties of this operator, we can see insert operation distributed to 4 threads

Gather streams: collect parallel operators and convert these operators to single, serial stream. In this operator properties we cannot see any thread distribution

Now we will examine SQL Server execution times.

Cpu time: total processor time which is spent by all processors. We can see this option main execution plan operators.

Elapsed time: total time of query.


Query Name CPU Time (ms) Elapsed Time (ms) Number of Threads
With parallel insert 4 threads 65031 23032 4

Tip:

Query estimated subtree cost: this is a unit of measurement system for execution plans.

Cost threshold for parallelism: this option indicates when a query has an estimated cost greater than this value, this query may run in parallel.

Our previous query estimated subtree cost is 1640. Now we will change cost threshold over 1640 and SQL Server query optimizer generates a serial plan because this value affects the SQL Server query optimizer choice and whether a query execution plan will be parallel or serial.

Now we will change cost threshold option value



If we will run previous query. We could not see the parallel operators

Cost threshold for parallelism option value directly affect SQL Server query optimizer choice.

This query will generate serial execution plan


Query Name CPU Time (ms) Elapsed Time (ms) Number of Threads
With parallel insert 1 thread 107078 111581 1

This query will generate a 2 thread parallel plan


Query Name CPU Time (ms) Elapsed Time (ms) Number of Threads
With parallel insert 2 threads 59203 33648 2

This query will generate a 3 thread parallel plan


Query Name CPU Time (ms) Elapsed Time (ms) Number of Threads
With parallel insert 3 threads 66249 24348 3


The chart above tells us that parallel insert option creates a significant performance improvement. It reduces the execution time of query.

Limitations

  • If the target is a heap table (a table without clustered indexes), a parallel insert execution plan is not created by the query optimizer
  • Table variables do not allow parallel insert execution plans
  • Database compatibility level must be 130 or above

Conclusions

SQL Server parallel insert feature provides high performance on bulk insert operations and ETL process. This feature provides the opportunity for significant performance improvements.

See more

To view and analyze SQL Server query execution plans for free, check out ApexSQL Plan.

References

Esat Erkec

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
Esat Erkec
Query analysis, SQL Server 2016

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

581 Views
  • Thanks for sharing.
    Interesting information of SQL parallelism…