Published on December 1st, 2014 | by Warner Chaves0
SQL Server 2014 New Features: Parallel SELECT INTO
Let’s continue our round up of the “under the radar” features of SQL Server 2014. For today, we’re going to talk about the improvement done to the SELECT INTO command.
What is SELECT INTO used for?
First, quick refresher on what SELECT INTO does. This operation allows us to materialize and populate a new table from the results of a query in one command. It’s commonly used for ETL pipelines, intermediate result tables or creation of temp tables inside stored procedures. Here’s the official Microsoft documentation.
What’s new about it?
Specifically for SQL Server 2014, parallelism support has been added so that the entire SELECT INTO process is done in a multi-threaded operation from end to end. This improvement is available on any edition as well. What this means is that we get a performance boost when we run our SELECT INTO commands because the entire operation will take advantage of the multiple cores in our server.
On previous versions, if the Cost of the execution plan was high enough then the SELECT portion could run in parallel, however, the materializing and INSERT portion into the new table was always single-threaded. This meant that we had a bottleneck on the execution plans because the data from the multiple threads pulled during the SELECT portion all had to get gathered into one stream to finish the work.
On this new implementation, there is not a serial zone in the plan that forces the different threads to gather into one stream, it operates on multiple threads (and thus multiple cores) from the moment the data is SELECTED all the way until the INSERT is done into the newly created table. The end result is faster execution.
Let’s see it in action
Here’s an example. I have a table called ImportedData with 20 million records on a database called ‘HR’. I imported this database from SQL 2012 to my SQL 2014 installation.
I’m running the following command:
select EmployeeID, sum(TotalHours) AccumulatedHours into AggregatedHourStats from ImportedData group by EmployeeID
This query will go through the entire set of 20 million records, group by the employee and tally up all the hours each one has worked. In this case, because I just imported it from SQL 2012, it has compatibility level 110. This means we will get a parallel zone while it’s SELECTING records and then a serial zone while it’s working on the result table. See this on the execution plan:
Now, to experience the new fully parallel version, we need to update the compatibility level to 120:
USE [master] GO ALTER DATABASE [HR] SET COMPATIBILITY_LEVEL = 120 GO
I reran the exact same query as above and got a new execution plan. Note how there’s parallelism END TO END now:
On my test server, the initial version ran for 4 seconds, this new one completes in 1. Now let’s imagine using this for larger tables with more complex SELECT statements and there can be really big benefits in processing time on your SELECT INTO operations. And the best part of it is, you don’t have to do any work at all! You set the database to compatibility level 120 and then the engine will take care of the rest.