SQL Server 2014: SELECT INTO is parallel March 7th, 2014

Vinod Kumar

Recently I was performance tuning and optimization for a customer and working on SELECT INTO statements code blocks. I saw a weird behavior and a self learning hit when I saw something interesting. In SQL Server 2014, this command seems to be running in parallel. Cool and a must from performance point of view. To reproduce this simple learning, I executed the below query on my SQL 2014 CTP2 box:

Looking at the execution plan revealed the following. Look at the Parallelism and Gather Stream to confirm the same.

Sometimes, there are these nifty tiny features that get added to the product that we stumble upon by accident. Since my TempDB is in compatibility of 120 (SQL 2014), I thought of making an interesting test. I changed my compatibility level to 100 for AdventureWorks and did the test again. To my surprise I saw the Parallelism is gone !!! Woot, that was quite a learning.

Do let me know if you get the same behavior on a pre-SQL 2014 box.

Share this article

Tags: , , , , , , , , , ,

This entry was posted on Friday, March 7th, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply