SQL 2014 CTP1

Hekaton & CSI Version 2 unter der Lupe

Sascha Götz

Karlsruhe, 03. Dezember 2013

2

“Most of today’s database managers are built on the

assumption that data lives on a disk, with little bits of

data at a time loaded into memory for manipulation.

There’s a whole lot of infrastructure involved in paging

those subsets of data in and out[…]”- Kevin Farlee, SQL Server program manager, Microsoft -

SQL 2014 CTP1

Hekaton – Memory optimized tables

3

Hekaton

SQL 2014 CTP1

Hekaton – Memory optimized tables

4

SQL 2014 CTP1

Hekaton – Memory optimized tables

SQL Server

Memory

Disk

Query

CacheFile

group

Index

Paging

Memory-OptimizedDisk-Based

Synchronisation

Index

Locking

Disk(Memory Dump)

5

‣ Filegroup hinzufügen

ALTER DATABASE [AdventureWorksDW2012]

ADD FILEGROUP [Hekaton_FG]

CONTAINS MEMORY_OPTIMIZED_DATA

GO

‣ Datafile hinzufügen

ALTER DATABASE [AdventureWorksDW2012]

ADD FILE ( NAME = N'Hekaton', FILENAME = N'C:\Hekaton.ndf')

TO FILEGROUP [Hekaton_FG]

GO

SQL 2014 CTP1

Hekaton – Memory optimized tables

6

CREATE TABLE [dbo].[FactInternetSales_Hekaton]

(

[ProductKey] [int] NOT NULL,

[OrderDateKey] [int] NOT NULL,

[...],

)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

‣ DURABILITY:

‣ SCHEMA_AND_DATA

‣ Schema und Daten werden mit Disk synchronisiert

‣ Speicherplatz wird im RAM und auf Disk benötigt

‣ SCHEMA_ONLY

‣ Nur Schema wird mit Disk synchronisiert

SQL 2014 CTP1

Hekaton – Memory optimized tables

7

‣ Natively Compiled Stored Procedure

CREATE PROCEDURE <Schema_Name, sysname, dbo>.<Procedure_Name, sysname,

Procedure_Name>

-- Add the parameters for the stored procedure here

[…]

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS BEGIN ATOMIC WITH

--Insert statements for the stored procedure here

[…]

END

‣ Nur Abfrage auf In-Memory Tabellen

SQL 2014 CTP1

Hekaton – Memory optimized tables

8

‣ Index

‣ Memory Optimized tables müssen mindestens einen Index haben (auf PK)

‣ Keine Clustered Indizes

‣ Indexe werden nur im Speicher gehalten

‣ Kein Index Drop, Rebuild

‣ Max. 8 Index pro Tabelle

SQL 2014 CTP1

Hekaton – Memory optimized tables

CI Date

1 11.11.13

2 12.11.13

3 13.11.13

4 14.11.13

5 15.11.13

6 16.11.13

7 17.11.13

Date

13.11.13

16.11.13

14.11.13

17.11.13

15.11.13

11.11.13

12.11.13

Non-CI

1

2

3

4

5

6

7

I1 I2 … I8 Date Order# …

3 6 13.11.13 98165

6 2 16.11.13 98121

4 1 14.11.13 98474

7 3 17.11.13 98521

5 4 15.11.13 98451

1 5 11.11.13 98514

2 7 12.11.13 98563

9

‣ Limitations

‣ Keine Trigger

‣ Keine Foreign Key & Constraints

‣ Keine Identity columns

‣ Keine Schema changes (ALTER TABLE…)

SQL 2014 CTP1

Hekaton – Memory optimized tables

Demo

11

SQL 2014 CTP1

Hekaton – Memory optimized tables

40

4

20

1

0

5

10

15

20

25

30

35

40

45

Ohne Index Non-Clustered Index

Aggregierung (SUM & Group BY) über 2 Mio Datensätze

On-Disk In-Memory

Da

ue

r in

Se

ku

nd

en

12

SQL 2014 CTP1

Hekaton – Memory optimized tables

32

52

60

0

10

20

30

40

50

60

70

1

Update 2 Mio Datensätze

On-Disk In-Memory In-Mermory + Natively Compiled

Da

ue

r in

Se

ku

nd

en

13

SQL 2014 CTP1

Hekaton – Memory optimized tables

13

17

0

2

4

6

8

10

12

14

16

18

1

1 Mio. Datensätze Load

On-Disk In-Memory

Da

ue

r in

Se

ku

nd

en

14

‣ Vorteile

‣ Performance

‣ Performance

‣ Performance

‣ Kritik

‣ Stabilität

‣ Speichermanagement

‣ Auslagerungs-Falle

‣ Fehlende Features (FKs, Identity Columns, Constraints)

‣ Bestehende Tabellen können nicht in die Hekaton Filegroup verschoben

werden

SQL 2014 CTP1

Hekaton – Memory optimized tables

15

‣ Einsatzzweck

‣ DURABILITY: SCHEMA_ONLY

‣ ETL Load Tabelle

‣ Temp Tabellen

‣ DURABILITY: SCHEMA_AND_DATA

‣ Log-Tabellen

SQL 2014 CTP1

Hekaton – Memory optimized tables

16

Columnstore Index 2

SQL 2014 CTP1

Columnstore Index

17

Non-ClusteredColumnstore Index

(SQL Server 2012)

• (+) Performance

• (+) Easy-To-Use

• (-) Read-Only

ClusteredColumnstore Index

(SQL Server 2014 CTP 1)

• (+) Performance

• (+) Easy-To-Use

• (+) Updateable

• (-) Keine PK & FK

• (-) Keine Constraints

SQL 2014 CTP1

Columnstore Index

18

106

71

9

10

20

40

60

80

100

120

1

Aggregierung (SUM & Group BY) über 25 Mio Datensätze

Ohne Index Non-Clustered Index Non-Clustered Columnstore Index Clustered Columnstore Index

SQL 2014 CTP1

Columnstore Index

Da

ue

r in

Se

ku

nd

en

19

SQL 2014 CTP1

Columnstore Index

20

23

27

0

5

10

15

20

25

30

1 Mio. Datensätze Load

Ohne Index Non-Clustered Index Non-Clustered Columnstore Index Clustered Columnstore Index

Nic

ht

möglic

hDa

ue

r in

Se

ku

nd

en

Szenario: 25 Mio Datensätze, Non-Clustered CSI, 1 Mio Datensätze Load 20

‣ Daten doch Laden bei Non-Clustered Columnstore Index?

‣ Drop, Load and Rebuild

‣ Partition Swap

SQL 2014 CTP1

4

20

103

1

20

5

1

Columnstore Index

Split Load Build Swap

Drop Load Build

‣ HowTo: Partition Swapping

‣ Tabelle partitionieren

‣ Leere Partitionen

‣ Load in Temp laden

‣ Partition Split

‣ CSI Index erstellen

‣ Switch

21

SQL 2014 CTP1

Columnstore Index

<2011

2011

2012

2013

>2013

Facts

2011-2013

CSI

>2014

2014 2014

22

SQL 2014 CTP1

Columnstore Index

2023

127

27 27

0

20

40

60

80

100

120

140

1 Mio. Datensätze Load

Ohne Index Non-Clustered Index Non-Clustered CSI

Non-Clustered CSI2 Clustered Columnstore Index

Partition

SwapDro

p &

Re

build

Da

ue

r in

Se

ku

nd

en

23

‣ Fazit

‣ Query-Performance: Gigantisch

‣ Load-Performance: Mäßig bis Schlecht

‣ Nachteile: Vorhanden

‣ Einsatzzweck:

‣ Non-Clustered CSI

‣ BI Fact tables mit daily/nightly Loads

‣ Partition Swap oder Index Rebuild

‣ Clustered CSI

‣ Log Tabellen

‣ ‚Solo‘-Tabellen (ohne PK /FK)

SQL 2014 CTP1

Columnstore Index

24

SQL 2014 CTP1

Fragen?

25

‣ SQL Server Hekaton CTP1 White Paper

http://download.microsoft.com/download/F/5/0/F5096A71-3C31-4E9F-864E-

A6D097A64805/SQL_Server_Hekaton_CTP1_White_Paper.pdf

‣ http://blogs.msdn.com/b/arvindsh/archive/2013/07/03/sql-2014-in-memory-oltp-

hekaton-training-videos-and-white-papers.aspx

‣ http://www.databasejournal.com/features/mssql/new-enhanced-column-store-

index-in-sql-server-2014-part-1.html

‣ http://rusanu.com/2011/07/13/how-to-update-a-table-with-a-columnstore-index/

SQL 2014 CTP1

Ressources