Is It Possible to Upload Multiple Statements of Non-performance to Dir at One Time

Introduction

In the first part of this article, we volition discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a large chore into multiple processors. This model is meant to reduce processing fourth dimension.

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

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

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

Nosotros can change this choice using the post-obit T-SQL script

EXEC sp _configure 'max degree of parallelism' , four ; GO RECONFIGURE WITH OVERRIDE ; GO EXEC sp _configure 'max caste of parallelism'

Parallel execution plans, MAXDOP and ENABLE_PARALLEL_PLAN_PREFERENCE

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

At present we will expect at some examples of parallel execution plans and properties. This query will generate a parallel execution program.

SELECT SOD . [ SalesOrderID ] , [ SalesOrderDetailID ] , [ CarrierTrackingNumber ] , [ OrderQty ] , [ ProductID ] , [ SpecialOfferID ] , [ UnitPrice ] , [ UnitPriceDiscount ] , [ LineTotal ] FROM [ Sales ] . [ SalesOrderDetail ] SOD INNER Join SALES . [ SalesOrderHeader ] SOH ON SOD . SalesOrderID = SOH . SalesOrderID ORDER BY SOD . ModifiedDate DESC , SOH . rowguid DESC

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

In the previous case, we can run across parallel operators and as has been highlighted, Degree of Parallelism testify 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 post-obit query and we will change our degree of parallelism to 2

SELECT SOD . [ SalesOrderID ] , [ SalesOrderDetailID ] , [ CarrierTrackingNumber ] , [ OrderQty ] , [ ProductID ] , [ SpecialOfferID ] , [ UnitPrice ] , [ UnitPriceDiscount ] , [ LineTotal ] FROM [ AdventureWorks2014 ] . [ Sales ] . [ SalesOrderDetail ] SOD INNER JOIN SALES . [ SalesOrderHeader ] SOH ON SOD . SalesOrderID = SOH . SalesOrderID Society BY SOD . ModifiedDate DESC , SOH . rowguid DESC OPTION ( MAXDOP two )

A new query hint is supported in SQL Server 2016 SP1 or above versions. This hint is ENABLE_PARALLEL_PLAN_PREFERENCE. It allows usa to forcefulness the SQL Server query optimizer to select parallel program instead of series plan.

We volition 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

SELECT * FROM [ Sales ] . [ vSalesPersonSalesByFiscalYears ] OPTION ( Utilise HINT ( 'ENABLE_PARALLEL_PLAN_PREFERENCE' ) )

• Parallel execution programme without ENABLE_PARALLEL_PLAN_PREFERENCE query hint

SELECT * FROM [ Sales ] . [ vSalesPersonSalesByFiscalYears ]

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 affect.

Parallel insert

In SQL Server 2016, Microsoft has implemented a parallel insert characteristic for the INSERT … WITH (TABLOCK) SELECT… command. The parallel insert functionality has proven to be a actually useful tool for ETL / data loading workloads which volition 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 side by side section of the commodity

• 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 overstate the data, we will create dummy source destination and insert records

1 2 3 4 5 six 7 8 9 10 eleven 12 13 14 15 sixteen 17 xviii 19 twenty 21 22 23 24 25 26 27 28 29 30 31 32

USE WideWorldImporters GO Drop TABLE IF EXISTS [ DummySource_Table ] CREATE TABLE [ DummySource_Table ] ( [ Id_Source ] [ int ] IDENTITY ( 1 , 1 ) , [ StockItemID_Source ] [ int ] Not Zilch , [ TransactionTypeID_Source ] [ int ] NOT NULL , [ CustomerID_Source ] [ int ] NULL , [ InvoiceID_Source ] [ int ] NULL , [ SupplierID_Source ] [ int ] NULL , [ PurchaseOrderID_Source ] [ int ] NULL , [ TransactionOccurredWhen_Source ] [ datetime2 ] ( seven ) Non Nada , [ Quantity_Source ] [ decimal ] ( 18 , 3 ) NOT Cipher , [ LastEditedBy_Source ] [ int ] NOT Nada , [ LastEditedWhen_Source ] [ datetime2 ] ( 7 ) Not Zilch ) Become INSERT INTO [ DummySource_Table ] SELECT [ StockItemID ] , [ TransactionTypeID ] , [ CustomerID ] , [ InvoiceID ] , [ SupplierID ] , [ PurchaseOrderID ] , [ TransactionOccurredWhen ] , [ Quantity ] , [ LastEditedBy ] , [ LastEditedWhen ] FROM [ Warehouse ] . [ StockItemTransactions ] GO 200

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

In this step we volition create destination table

1 2 3 4 5 six vii 8 ix 10 11 12 thirteen 14 fifteen xvi 17

Driblet TABLE IF EXISTS [ ParallelInsert_Destination ] Go CREATE TABLE [ dbo ] . [ ParallelInsert_Destination ] ( [ Id_Source ] [ int ] Non Zilch , [ StockItemID_Source ] [ int ] NOT Naught , [ TransactionTypeID_Source ] [ int ] Not NULL , [ CustomerID_Source ] [ int ] Nada , [ InvoiceID_Source ] [ int ] NULL , [ SupplierID_Source ] [ int ] NULL , [ PurchaseOrderID_Source ] [ int ] NULL , [ TransactionOccurredWhen_Source ] [ datetime2 ] ( 7 ) NOT Cipher , [ Quantity_Source ] [ decimal ] ( xviii , iii ) Non NULL , [ LastEditedBy_Source ] [ int ] Non Aught , [ LastEditedWhen_Source ] [ datetime2 ] ( seven ) NOT Goose egg )

Nosotros will look at parallel and serial insert query differences.

Parallel execution:

Set up STATISTICS Time ON TRUNCATE Table [ ParallelInsert_Destination ] INSERT INTO [ ParallelInsert_Destination ] WITH ( TABLOCK ) SELECT * FROM [ DummySource_Table ]

Tip:

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

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

Parallel table scan operator: this operator indicates SQL Server reads the whole table row by row because we don't take any index on source tabular array. In the operator properties we can run across number of rows read option. This option tells us which thread read how many rows and the parallel selection tell the states 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 run into insert operation distributed to iv threads

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

Now we volition examine SQL Server execution times.

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

Elapsed time: full time of query.

Query Name	CPU Time (ms)	Elapsed Time (ms)	Number of Threads
With parallel insert four 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 price 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 option and whether a query execution program will be parallel or serial.

Now we volition change cost threshold option value

exec sp_configure 'advanced' , 1 Go RECONFIGURE Get exec sp_configure 'price threshold for parallelism' , 5000 ; Go RECONFIGURE Go exec sp_configure 'cost threshold for parallelism'

SET STATISTICS TIME ON TRUNCATE TABLE [ ParallelInsert_Destination ] Set up STATISTICS TIME ON INSERT INTO [ ParallelInsert_Destination ] WITH ( TABLOCK ) SELECT * FROM [ DummySource_Table ] SET STATISTICS Fourth dimension OFF

If we volition run previous query. We could not run across the parallel operators

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

This query will generate serial execution program

Gear up STATISTICS Fourth dimension ON TRUNCATE Table [ ParallelInsert_Destination ] Fix STATISTICS Fourth dimension ON INSERT INTO [ ParallelInsert_Destination ] SELECT * FROM [ DummySource_Table ] Ready STATISTICS TIME OFF

Query Proper noun	CPU Time (ms)	Elapsed Fourth dimension (ms)	Number of Threads
With parallel insert 1 thread	107078	111581	ane

This query will generate a two thread parallel program

SET STATISTICS Fourth dimension ON TRUNCATE TABLE [ ParallelInsert_Destination ] SET STATISTICS Fourth dimension ON INSERT INTO [ ParallelInsert_Destination ] WITH ( TABLOCK ) SELECT * FROM [ DummySource_Table ] OPTION ( MAXDOP 2 ) SET STATISTICS Fourth dimension OFF

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

This query will generate a 3 thread parallel plan

Gear up STATISTICS Time ON TRUNCATE Tabular array [ ParallelInsert_Destination ] Gear up STATISTICS TIME ON INSERT INTO [ ParallelInsert_Destination ] WITH ( TABLOCK ) SELECT * FROM [ DummySource_Table ] OPTION ( MAXDOP 3 ) Set STATISTICS TIME OFF

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

The chart higher up tells united states that parallel insert option creates a significant functioning improvement. Information technology 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 created by the query optimizer
• Table variables do not allow parallel insert execution plans
• Database compatibility level must be 130 or higher up

Conclusions

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

• Author
• Recent Posts

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

Most of his career has been focused on SQL Server Database Assistants and Development. His current interests are in database administration and Concern Intelligence. You lot can find him on LinkedIn.

View all posts past Esat Erkec

walkerscolon1963.blogspot.com

Source: https://www.sqlshack.com/use-parallel-insert-sql-server-2016-improve-query-performance/

Share this post