Buffer Pool Extension Columnstore Indexes InMemory OLTP Memory
ම ඩයල වශල ෂණය • Buffer Pool Extension භ ව තය. • Columnstore Indexes ක රය තමක • In-Memory OLTP කරම . – Memory Optimized Tables – Memory Optimized Stored Procedures කර ම.
Buffer Pool Extension භ වතය Buffer cache (RAM) Buffer cache extension (SSD) Clean pages Pages Data files (Disk) .
Microsoft Virtual Academy DEMO Enabling Buffer Pool Extension
columnstore indexes හඳනව Row Store ද ම. Column Store Product. ID Order. Date Cost 310 20010701 2171. 29 311 20010701 1912. 15 311 … 1912. 15 312 20010702 2171. 29 313 20010702 413. 14 313 … 413. 14 314 … 333. 42 315 20010703 1295. 00 316 … 4233. 14 317 … 641. 22 Data page 1000 Product. ID Order. Date Cost 314 20010701 333. 42 318 … 24. 95 315 20010701 1295. 00 319 … 64. 32 316 20010702 4233. 14 320 20010704 1111. 25 317 20010702 641. 22 321 … Data page 1001 Data page 2000 Data page 2001 Data page 2002
Columnstore Index වර ග. • Clustered columnstore indexes • Nonclustered columnstore indexes
Microsoft Virtual Academy DEMO Columnstore Indexes
In-Memory OLTP…
Memory-Optimized Tables • memory-optimized tables යන ම නව ද ?
Creating Memory-Optimized Tables (1) • memory-optimized data සඳහ filegroup එකක එකත කරම ALTER DATABASE My. DB ADD FILEGROUP mem_data CONTAINS MEMORY_OPTIMIZED_DATA; GO ALTER DATABASE My. DB ADD FILE (NAME = 'Mem. Data' FILENAME = 'D: DataMy. DB_Mem. Data. ndf') TO FILEGROUP mem_data;
Creating Memory-Optimized Tables (2) • memory-optimized table එකක සකස කරම CREATE TABLE dbo. Memory. Table (Order. Id INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), Order. Date DATETIME NOT NULL, Product. Code INTEGER NULL, Quantity INTEGER NULL) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Memory-Optimized Tables Query කර ම. • Query Interop • Native Compilation Transact-SQL CREATE PROCEDURE… SELECT t 1. col 1, t 3. col 2 FROM Tab 1 t 1 JOIN Tab 2 t 2 ON t 1. Col 1 = t 2. col 1; Translate to C #define __in HRESULT hkp_(… Compile to DLL Query Interop 01101 Tab 2 Memory-Optimized Tables Tab 3 Tab 4 Disk-Based Tables
Microsoft Virtual Academy DEMO Memory-Optimized Tables
Natively Compiled Stored Procedures CREATE PROCEDURE dbo. Delete. Customer @Customer. ID INT WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT; LANGUAGE = 'us_English') DELETE dbo. Open. Orders WHERE Customer. ID = @Customer. ID DELETE dbo. Customer WHERE Customer. ID = @Customer. ID END;
Microsoft Virtual Academy DEMO Natively Compiled Stored Procedure
In-Memory Objects සමපණඩනය • Buffer Pool Extension භ වතය . • Columnstore Indexes ක රය තමක • In-Memory OLTP කරම . – Memory Optimized Tables – Memory Optimized Stored Procedures කරම .
- Slides: 15