In Memory OLTP Nedir? Ve Nasıl Kullanılır?

In Memory OLTP SQL Server 2014 ile birlikte gelen bir özelliktir. Bu özellik ile verilerimizi artık memory’de tutabiliyoruz. Verileri memory’de tutabiliyoruz dediğimizde, sql server’a ilgisi olan bir çok insan buffer pool olarak algılıyor. Önemli bir nokta olarak veriler buffer pool’da değil, direk memory’de tutuluyor. “Buffer Pool Extension Nedir” isimli makalemde buffer pool’un ne olduğuna değinmiştim.

Tabi tüm verimizi memory’de tutmamız çoğunlukla olası değildir. Çok yoğun transaction alan tablolar için ya da çok hızlı cevap almak istediğimzi tablolar için bu özelliği kullanabiliriz. Tabi SQL Server 2014’te çok hoşunuza gitmeyecek bir takım kısıtlamaları var. Bu makalede SQL Server 2014 ile gelen bu özelliğin SQL Server 2016 ile nasıl geliştiğini adım adım inceleyeceğiz.

Öncelikle SQL Server 2014 ile bu özelliği size tanıtıp sonrasında SQL Server 2016 ile gelen yeniliklerine değineceğim. In Memory OLTP ile ilgili karşımıza gelen ilk özellik memory’de tutulan tablolar olarak bilinen memory optimized table’lardır.

SQL Server 2014 için Memory Optimized Table:

İlk olarak memory optimized tabloyu oluşturalım ve daha sonra açıklamalara geçelim. Tabloyu oluşturmak için öncelikle aşağıdaki script yardımıyla memory optimized filegroup’u oluşturmamız gerekiyor. Daha sonra bu filegroup içersinde bir file oluşturuyoruz.

ALTER DATABASE [Test]
ADD FILEGROUP [InMemoryFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE[Test]
ADD FILE(
NAME=N'InMemoryFile1',
FILENAME=N'C:\MSSQL\InMem'
) TO FILEGROUP [InMemoryFileGroup]

Daha sonra aşağıdaki gibi tablomuzu oluşturalım.

CREATE TABLE  dbo.MemTabloOrnek
(
ID int NOT NULL
PRIMARY KEY NONCLUSTERED HASH (ID)  WITH  (BUCKET_COUNT =1024),
AdSoyad varchar(250) NOT NULL
INDEX IDX_AdSoyad  HASH (AdSoyad) WITH  (BUCKET_COUNT =1024)
) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

Tablomuzu oluşturmak istediğimizde aşağıdaki gibi bir hata alabilirsiniz.

Msg 12329, Level 16, State 103, Line 1

The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables.

Bu hata memory optimzed tablo’ların bütün collation tiplerini desteklememesinden kaynaklanmaktadır.

Aşağıdaki script yardımıyla desteklediği collation tiplerini görebilirsiniz.

SELECT NAME ,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage] FROM sys.fn_helpcollations()
WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252

Tablomuzu oluşturabilmek için veritabanımızın collation’ını aşağıdaki script yardımıyla değiştirebilirsiniz.

USE master
GO
ALTER DATABASE Test COLLATE SQL_Latin1_General_CP1_CI_AS

Ya da veritabanı collation’ını değiştirmeden aşağıdaki script yardımıyla da tablonuzu oluşturabilirsiniz.

CREATE TABLE  dbo.MemTabloOrnek
(
ID int NOT NULL
PRIMARY KEY NONCLUSTERED HASH (ID)  WITH  (BUCKET_COUNT =1024),
AdSoyad varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
INDEX IDX_AdSoyad  HASH (AdSoyad) WITH  (BUCKET_COUNT =1024)
) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

Oops. Yine hata aldık. Bu hataları almadan direk çalışan script’i de sizinle paylaşabilirdim fakat bu hataları da görmenizi istedim. Şimdi aldığımız hata aşağıdaki gibi.

Msg 12328, Level 16, State 102, Line 1

Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint or index. See previous errors.

Varchar bir kolona index koymak için collation’ın BIN2 olması gerekiyor. Aşağıdaki sorgu yardımıyla BIN2 tipinde olan collation’ları listeleyip size uygun olanı seçebilirsiniz.

SELECT NAME,COLLATIONPROPERTY(NAME, 'codepage') AS [CodePage] FROM sys.fn_helpcollations()
WHERE COLLATIONPROPERTY(NAME, 'codepage') = 1252 and NAME like '%BIN2%'

Ben Latin1_General_BIN2 collation’ını kullanarak tabloyu aşağıdaki gibi yeniden oluşturuyorum.

CREATE TABLE  dbo.MemTabloOrnek
(
ID int NOT NULL
PRIMARY KEY NONCLUSTERED HASH (ID)  WITH  (BUCKET_COUNT =1024),
AdSoyad varchar(250) COLLATE Latin1_General_BIN2 NOT NULL
INDEX IDX_AdSoyad  HASH (AdSoyad) WITH  (BUCKET_COUNT =1024)
) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA)

Bu sefer hata almadan tablomuzu oluşturabildik.

Script’i biraz inceleyecek olursak bize yabancı gelebilecek birkaç farklılık var.

MEMORY_OPTIMIZED = ON: Tablonun memory optimized tablo olduğunu belirtiyor.

HASH INDEX: Memory Optimized tablolar normal btree index’i desteklemez. Her memory optimized tabloda en az bir tane index olmak zorundadır. Bu index bütün satırları tabloda birleştirme işlevini görür.

BUCKET_COUNT: hash index oluşturulurken, bu kolondaki unique satır sayısını belirtir. Doğru bir sayı belirlemezseniz performans azalır ve veritabanının recovery süresi uzar. Eğer bucket_count’u belirleyemiyorsanız bunun yerine range index kullanabilirsiniz. Eğer index’i hash olarak belirtmezseniz bu index range index olacaktır.

DURABILITY: SCHEMA_AND_DATA ya da SCHEMA_ONLY olabilir.

SCHEMA_AND_DATA olursa hem şema yapısı hem de veri beklenmedik bir crash durumunda ya da always on failover durumunda korunur.

SCHEMA_ONLY olursa sadece şema yapısı beklenmedik bir crash durumunda ya da always on failover durumunda korunur. Dolayısıyla verinizi kaybedersiniz.

Biraz mantıksal yapısından bahsedelim.

In Memory OLTP’nin en önemli özelli lock ve latch olmadığı için performansı çok ciddi anlamda arttırmasıdır. Microsoft 5-30 kata kadar performans artışı olduğunu belirtiyor.

Bildiğimiz normal tablolar Disk Based Table olarak geçer. Disk Based tablolar’a bir select sorgusu geldiğinde veriler diskten cache’e aktarılır. Memory Optimized Table’lar zaten memory’de tutuldukları için böyle bir işleme gerek kalmaz.

Sadece sunucuda bir crash olması durumunda memory’de tutulan verilerin kaybolmaması ve veritabanının sağlıklı bir şekilde recover olması için bazı checkpoint dosyaları(data ve delta dosyaları) yukarda bahsettiğim memory optimized filegroup üzerinde tutulur.

Memory Optimized Tablolar’da Disk Based Tablolar ile aynı transaction log dosyasını kullanır. Bildiğiniz gibi bu dosya diskt’te tutulur. “SQL Server Transaction Log Nedir” isimli makalemi okumak isteyebilirsiniz.

Index’ler her zaman memory’de tutulur. Restart sonrasında otomatik olarak rebuild edilirler. Buda recovery süresini biraz uzatır. Bu yüzden index koyarken dikkatli olmanız gerekir.

In Memory OLTP’de ki en iyi performansı natively compiled stored procedure’leri memory optimized tablolar ile birlikte kullanarak elde edebilirsiniz. “Natively Compiled Stored Procedure Nedir ve Nasıl Kullanılır?” isimli makalemi okumak isteyebilirsiniz.

Memory Optimized tablolara erişim normal tablolara erişim ile aynıdır. Aynı uygulama aynı veritabanı içersinde hem memory optimized tabloları hem de disk based tabloları kullanabilir.

Memory Optimized tablolar optimistic concurrency control kullanır.”Optimistic ve Pessimistic Concurrency Control Nedir” isimli makalemi okumak isteyebilirsiniz.

Memory Optimized Tabloların bazı kısıtlamaları vardır: Daha detaylı kısıtlamaları makalenin devamındaki tabloda bulabilirsiniz.

• DML Trigger
• XML ve CLR veri tipleri.
• Varchar(max) gibi LOB tipleri.
• FOREIGN KEY
• CHECK Constraint
• ALTER TABLE
• Sonradan Index oluşturma ve kaldırma(tabloyu yeniden oluşturmamız gerekir.)
• TRUNCATE TABLE
• 8 index’ten fazlasını desteklemez

SQL Server 2016 ile in memory OLTP’de nasıl yenilikler geldi?

SQL Server 2014 ve SQL Server 2016’da In Memory Oltp farklarını aşağıdaki tabloda görebilirsiniz.

Index rebuild ederek BUCKET_COUNT’u aşağıdaki şekilde değiştirebiliyorsunuz.

ALTER TABLE dbo.MemTabloOrnek
  ALTER INDEX IDX_ID
  REBUILD WITH (BUCKET_COUNT = 1042);

SQL Server 2017’de de In Memory OLTP’ye aşağıdaki yenilikler geldi.

• Sp_spaceused memory optimized tablolar için artık kullanılabiliyor.
• Sp_rename memory optimized tablolar ve natively compiled module’ler için artık kullanılabiliyor.
• Natively compiled module’ler için CASE desteği geldi.
• Memory optimized tablolarda 8 index limiti vardı. Bu limit kaldırıldı.
• TOP (N) WITH TIES artık Natively compiled module’lerde destekleniyor.
• Memory optimized tablolarda ALTER TABLE işlemi hızlandı.
• Transaction Log redo işlemi paralel yapılıyor. Bu da recovery süresini kısalttı.
• Memory optimized filegroup’lar Azure Blob Storage’de saklanabiliyor.
• Memory optimized tablolar için, üzerinde index oluşturulabilir computed column desteği geldi.
• Natively compiled module’lerde ve CHECK Constraint’in için JSON fonksiyonları kullanılabiliyor.
• Natively compiled module’lerde CROSS APPLY operatörü kullanılabiliyor.
• Memory Optimized Tablolardaki nonclustered index’lerin rebuild işlemi optimize edildi. Bu iyileştirme ile nonclustered index kullanan memory optimized tabloların olduğu veritabanları için recovery süresi kısaldı.