SQL Server 2014 – SELECT INTO exécuté en parallèle

Encore une petite nouveauté qui n’est pas franchement mise en avant dans SQL Server 2014, vu les enjeux que représentent Hekaton et autres fonctionnalités majeures.

Cependant, j’imagine que bon nombre d’entre vous utilisent fréquemment l’instruction SELECT INTO.

Jusqu’à présent, cette opération n’était pas parallélisée. SQL Server 2014 apporte enfin un plan d’exécution parallèle.

Tout d’abord, après avoir restauré la base ContosoRetail me permettant de travailler sur une table de 13 M d’enregistrement, je créé deux bases destination (version SQL2012 et version SQL2014) afin de mesurer le gain de performance.


CREATE DATABASE SelectInto110;
GO
ALTER DATABASE [SelectInto110] SET COMPATIBILITY_LEVEL = 110;
GO
ALTER DATABASE [SelectInto110]
	MODIFY FILE ( NAME = N'SelectInto110', SIZE = 2560000KB , FILEGROWTH = 524288KB )
GO
ALTER DATABASE [SelectInto110] 
	MODIFY FILE ( NAME = N'SelectInto110_log', SIZE = 2097152KB , FILEGROWTH = 131072KB )
GO




CREATE DATABASE SelectInto120;
GO
ALTER DATABASE [SelectInto120] SET COMPATIBILITY_LEVEL = 120;
GO
ALTER DATABASE [SelectInto120]
	MODIFY FILE ( NAME = N'SelectInto120', SIZE = 2560000KB , FILEGROWTH = 524288KB )
GO
ALTER DATABASE [SelectInto120] 
	MODIFY FILE ( NAME = N'SelectInto120_log', SIZE = 2097152KB , FILEGROWTH = 131072KB )
GO

SET STATISTICS TIME ON
GO

Ensuite, j’exécute la requête SELECT INTO.

Compatibilité SQL SERVER 2012 :

imageimage

Compatibilité SQL SERVER 2014 :

imageimage

Effectivement, le plan d’exécution est bien parallélisé lorsque l’on est en mode de compatibilité SQL 2014. Au niveau du temps d’exécution on passe de 1:40 minute à 37 secondes. Pas mal.

Faisons grossir la table d’origine pour voir ce que cela donne avec beaucoup plus de données.


ALTER DATABASE [ContosoRetailDW] 
SET COMPATIBILITY_LEVEL = 120;
GO

USE [ContosoRetailDW]
GO

INSERT INTO [FactOnlineSales]  WITH (TABLOCK)
(
	 [DateKey]
	,[StoreKey]
	,[ProductKey]
	,[PromotionKey]
	,[CurrencyKey]
	,[CustomerKey]
	,[SalesOrderNumber]
	,[SalesOrderLineNumber]
	,[SalesQuantity]
	,[SalesAmount]
	,[ReturnQuantity]
	,[ReturnAmount]
	,[DiscountQuantity]
	,[DiscountAmount]
	,[TotalCost]
	,[UnitCost]
	,[UnitPrice]
	,[ETLLoadID]
	,[LoadDate]
	,[UpdateDate]
)
SELECT [DateKey]
      ,[StoreKey]
      ,[ProductKey]
      ,[PromotionKey]
      ,[CurrencyKey]
      ,[CustomerKey]
      ,[SalesOrderNumber]
      ,[SalesOrderLineNumber]
      ,[SalesQuantity]
      ,[SalesAmount]
      ,[ReturnQuantity]
      ,[ReturnAmount]
      ,[DiscountQuantity]
      ,[DiscountAmount]
      ,[TotalCost]
      ,[UnitCost]
      ,[UnitPrice]
      ,[ETLLoadID]
      ,[LoadDate]
      ,[UpdateDate]
 FROM [SelectInto120].[dbo].[FactOnlineSales]
 GO 10

image

J’ai également passé mes 2 bases en mode Recovery Simple afin que le SELECT INTO passe en mode bulk afin de gagner un peu de temps (et d’espace dans le LDF).

Près de 19 minutes (le SSD est à bout de souffle, désolé)

image

Contre 9 minutes …

image

Encore une fois, le gain est intéressant, mais le résultat est un peu faussé pars les perfs limitées de mon SSD qui est en train de rendre l’âme. En même temps, les 139 M d’enregistrements pèsent près de 19GB non compressés (la table d’origine est compressée niveau page et l’espace disques est de plus de 21GB). Je pense qu’un bon sous-système disque améliorerait encore le gain de perf. Car la CPU est bien utilisée lors su SELECT INTO :

image

Cool. Le premier moteur de SQL Server se voit ainsi bel et bien amélioré au niveau des perfs. Qu’en est-il des 2 autres moteur ???

Ben oui, souvenez vous, la technologie xVelocity, appliquée au columnstore index et Hekaton permettent de disposer de 3 moteurs dans SQL Server, et le passage de l’un  à l’autre, voire l’utilisation simultanée des 3 se fait en toute transparence.

Sachant que le SELECT INTO crée automatiquement la table destination, il n’est pas possible des tester le scénario ColumnStore Index table en destination.

Je créé donc une nouvelle table dans chacune des bases de test pour avoir une source de données :


CREATE TABLE FactOnlineSalesColumnStore
(
	[OnlineSalesKey] [int] NOT NULL,
	[DateKey] [datetime] NOT NULL,
	[StoreKey] [int] NOT NULL,
	[ProductKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [int] NULL,
	[SalesQuantity] [int] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[ReturnQuantity] [int] NOT NULL,
	[ReturnAmount] [money] NULL,
	[DiscountQuantity] [int] NULL,
	[DiscountAmount] [money] NULL,
	[TotalCost] [money] NOT NULL,
	[UnitCost] [money] NULL,
	[UnitPrice] [money] NULL,
	[ETLLoadID] [int] NULL,
	[LoadDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL
);
GO

CREATE CLUSTERED COLUMNSTORE INDEX CCI_OnlineSalesKey 
ON FactOnlineSalesColumnStore;
GO

Une satisfaction : le poids de cette table de près de 139 M d’enregistrements est passé de 3,8GB en mode rowstore page compressées à 1,6GB avec le columnstore index.

image

Pour info, non compressée cette table pèse 21,7GB. Beau ration de compression donc, mais attention, au vu des données de test, ce n’est pas forcément très représentatif …

image

image

Le SELECT INTO fonctionne donc parfaitement en mode parallélisé avec une source de type columnstore.

Effectivement, côté CPU …

image

Que se passe t’il avec des tables Hekaton ?

Etant donné que je dispose de peu de mémoire sur ces VMs et que pour Hekaton, une table doit être contenue entièrement en mémoire, je me vois obligé de limiter le nombre d’enregistrements. Totalement arbitrairement, je choisis les premiers 2 000 000.

Tout d’abord, un peu de préparation au niveau des bases de données. Encore une fois, sachant que le SELECT INTO crée automatiquement la table destination, il n’est pas possible des tester le scénario Memory Optimized Table en destination.



ALTER DATABASE SelectInto120 
ADD FILEGROUP [SelectInto120_mod] CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE SelectInto120 
ADD FILE(NAME = 'SelectInto120_mod_dir', FILENAME='e:\data\SelectInto120_mod') 
TO FILEGROUP [SelectInto120_mod]
GO

CREATE TABLE FactOnlineSalesHekaton
(
	[OnlineSalesKey] [int] NOT NULL  PRIMARY KEY NONCLUSTERED HASH 
                WITH (BUCKET_COUNT=20000000),
	[DateKey] [datetime] NOT NULL,
	[StoreKey] [int] NOT NULL,
	[ProductKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[CustomerKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [int] NULL,
	[SalesQuantity] [int] NOT NULL,
	[SalesAmount] [money] NOT NULL,
	[ReturnQuantity] [int] NOT NULL,
	[ReturnAmount] [money] NULL,
	[DiscountQuantity] [int] NULL,
	[DiscountAmount] [money] NULL,
	[TotalCost] [money] NOT NULL,
	[UnitCost] [money] NULL,
	[UnitPrice] [money] NULL,
	[ETLLoadID] [int] NULL,
	[LoadDate] [datetime] NULL,
	[UpdateDate] [datetime] NULL
)  WITH (MEMORY_OPTIMIZED=ON);
GO

image

24 secondes pour recopier 2 000 000 d’enregistrements d’une table columnstore dans une table Hekaton (Durable de surcroit), c’est pas si mal que ça.

Et 14 secondes pour passer de la table Hekaton à la table rowstore (disk based). Assez performant, mais malheureusement pas parallélisé.

image

 

En conclusion, nous pourrons retenir que le SELECT INTO est parallélisé en mode de compatibilité 120 (SQL 2014) lorsque la table source est une table disk based ou une table columnstore. Mais ce n’est pas le cas pour une table Hekaton.

Tiens, j’imagine bien quels vont être mes prochains benchmarks … Sourire

Enjoy !

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans SQL Server, est tagué , , , . Ajoutez ce permalien à vos favoris.

3 commentaires pour SQL Server 2014 – SELECT INTO exécuté en parallèle

  1. Ping : SQL Server 2014 – Performance TempDB et écritures disque | Christophe LAPORTE – Consultant SQL Server

  2. stephane0 dit :

    Un article très clair et intéressant. Que donnent ces fameux futurs benchmarks ?

    • Christophe dit :

      Salut Stéphane

      Les journées ne font pas encore 72 heures et les semaines 200 jours. Mais je ne désespère pas d’avoir un taux de Done supérieur au taux New Items dans ma ToDo list.

      Satisfait des JSS2014 ?

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s