Columnstore Index: Parallel load into clustered columnstore index from staging table
'SQL Server has supported parallel data load into a table using BCP, Bulk Insert and SSIS. The picture below shows a typical configuration of a Data Warehouse where data is loaded from external files either using BCP or SSIS. SQL Server supports parallel data load.
Another common scenario of loading data is via a staging table. Data is first loaded into one or more staging tables where it is cleansed and transformed and then it is moved to the target table as shown below.
While this works well, one challenge is that though loading data into staging tables can done in parallel, the data load from staging table into target tables is single-threaded which can slowdown the overall ETL process. To give you an extreme example, let us say you are running on a 64-core machine and the last step in your ETL is to move data from staging table to target table. If INSERT is single threaded, you will notice that only one of 64 CPUs are being used. Ideally, you want all 64 cores to be used for faster migration of data to target table. With SQL Server 2016, you can move data from staging table into a target table in parallel which can reduce the overall data load time significantly.'...
Trackbacks
Die Kommentarfunktion wurde vom Besitzer dieses Blogs in diesem Eintrag deaktiviert.
Kommentare
Ansicht der Kommentare: Linear | Verschachtelt