![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_sqlserver2014.jpg\")
<\/p>\n<\/p>\n
A couple of weeks ago, I had to provide some best practices in term of architecture for a “heavy writes” environment that consists in importing data from different sources into SQL Server tables. At the customer place, I was asked about interesting new SQL Server features that could be used to increase the performance speed of the data import process. Indeed, SQL Server 2014 provides an interesting enhancement of SELECT INTO statement that is often used in ETL environments. It is now possible to run in parallel insert operations by using the SELECT INTO command.<\/p>\n
First, let me say that for versions prior to SQL Server 2014, inserting data in parallel into a single table is possible by using SSIS packages (for instance with several import data tasks in parallel), but the destination must exist. However, it is the creation of the destination table \u201cin-fly\u201d that counts here.<\/p>\n
After the release of SQL Server 2014 RTM I decided to test this new enhancement on my lab environment. The laptop I used for my tests is a Lenovo T530 with the following specifications:<\/p>\n
In addition I used two databases to simulate my data import workload process:<\/p>\n
<\/p>\n
| \n database_name<\/strong><\/p>\n<\/td>\n file_id<\/strong><\/p>\n<\/td>\n type_desc<\/strong><\/p>\n<\/td>\n logical_name<\/strong><\/p>\n<\/td>\n Drive<\/strong><\/p>\n<\/td>\n size_MB<\/strong><\/p>\n<\/td>\n<\/tr>\n AdventureWorks2012_DW<\/p>\n<\/td>\n 1<\/p>\n<\/td>\n ROWS<\/p>\n<\/td>\n AdventureWorks2012_DW<\/p>\n<\/td>\n C:<\/p>\n<\/td>\n 6252<\/p>\n<\/td>\n<\/tr>\n AdventureWorks2012_DW<\/p>\n<\/td>\n 2<\/p>\n<\/td>\n LOG<\/p>\n<\/td>\n AdventureWorks2012_DW_log<\/p>\n<\/td>\n D:<\/p>\n<\/td>\n 8000<\/p>\n<\/td>\n<\/tr>\n AdventureWorks2012_DW<\/p>\n<\/td>\n 65537<\/p>\n<\/td>\n FILESTREAM<\/p>\n<\/td>\n AdventureWorks2012_DW_xtp_ckpt<\/p>\n<\/td>\n C:<\/p>\n<\/td>\n 0<\/p>\n<\/td>\n<\/tr>\n AdventureWorks2012<\/p>\n<\/td>\n 1<\/p>\n<\/td>\n ROWS<\/p>\n<\/td>\n AdventureWorks2012_Data<\/p>\n<\/td>\n C:<\/p>\n<\/td>\n 6413<\/p>\n<\/td>\n<\/tr>\n AdventureWorks2012<\/p>\n<\/td>\n 2<\/p>\n<\/td>\n LOG<\/p>\n<\/td>\n AdventureWorks2012_Log<\/p>\n<\/td>\n I:<\/p>\n<\/td>\n 8000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n The C: drive is a solid-state Samsung 840 EVO\u00a0drive with SATA 600. I decided to place both database mdf files on this disk because solid-state drives perform well for random IO. Unfortunately, I don\u2019t have a second one to isolate each mdf database file, but in my context, it will be sufficient.<\/p>\n The D: drive is an mechanic disk with 7200 RPM and SATA 600. The AdventureWorks2012_DW transaction log file is placed here.<\/p>\n Finally, the I: drive is a USB drive with 5200 RPM only. It hosts the AdventureWorks2012 source database. In my context, the rotate speed of the I: drive is not important because during the test, we will only read the bigTransactionHistory table.<\/p>\n The hardware description of the environment is important here because the performance gain depends on many factors like the number of available processors, the amount of memory, the performance of the overall storage, the location of the database files, etc. For example, I did not notice a positive performance impact during my first tests because my mdf database files were located on slow disks, which was the main bottleneck regardless of the possibility to use (or not) the select into command in parallel.<\/p>\n |
![\"blog_7_-_bigTransactionHistoryTable_configuration\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_bigTransactionHistoryTable_configuration.jpg\")
<\/p>\n![\"blog_7_-_bigTransactionHistoryTable_to_bigTransactionHistory2\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_bigTransactionHistoryTable_to_bigTransactionHistory2.jpg\")
<\/p>\n![\"blog_7_-_tableresulttest\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_tableresulttest.jpg\")
<\/p>\n![\"blog_7_-_disk_ssd_througput_nonparallel\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_disk_ssd_througput_nonparallel.jpg\")
<\/span><\/p>\n![\"blog_7_-_disk_ssd_througput_parallel\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_disk_ssd_througput_parallel.jpg\")
<\/span><\/p>\n![\"blog_7_-_bigTransactionHistory2_plan_nonparallel\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_bigTransactionHistory2_plan_nonparallel.jpg\")
<\/span><\/p>\n![\"blog_7_-_scheduler_task_nonparallel\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_scheduler_task_nonparallel.jpg\")
<\/span><\/p>\n![\"blog_7_-_bigTransactionHistory2_plan_parallel\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_7_-_bigTransactionHistory2_plan_parallel.jpg\")
<\/span><\/p>\n