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

17 May by NURULLAH ÇAKIR

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.

 

Özellik ve Limitler

SQL Server 2014

SQL Server 2016

Maksimum Tablo Büyüklüğü

256 GB

Limit yok

Transparent Data Encryption (TDE)

Desteklemiyor

Destekliyor

İç içe(Nested) Natively Compiled Procedure

Desteklemiyor

Destekliyor

Natively-compiled scalar UDF(user defined function)

Desteklemiyor

Destekliyor

ALTER TABLE,PROCEDURE,INDEX

Desteklemiyor

Destekliyor(OFFLINE)

AFTER TRIGGER

Desteklemiyor

Destekliyor(WITH NATIVE_COMPILATION ile)

Nullable kolonlar’da index

Desteklemiyor

Destekliyor

BIN2 olmayan kolonlarda index

Desteklemiyor

Destekliyor

Foreign Keys

Desteklemiyor

Destekliyor

Check/Unique Constraints

Desteklemiyor

Destekliyor

Parallelism

Desteklemiyor

Destekliyor

OUTER JOIN, OR, NOT, UNION [ALL], DISTINCT, EXISTS, IN

Desteklemiyor

Destekliyor

Multiple Active Result Sets (MARS)

Desteklemiyor

Destekliyor

SSMS Üzerinde Tablo Design İşlemleri

Desteklemiyor

Destekliyor

Memory Optimized Tablolarda ColumnStore Index

Desteklemiyor

Destekliyor

Varbinary(max),varchar(max),nvarchar(max)

Desteklemiyor

Destekliyor

Natively Compiled SP’de EXECUTE AS OWNER ifadesi

Gerek var

Gerek Yok

 

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ı.

2 Comments

  1. Merhaba Öncelikle emeğinize teşekkür ederim . İçerik mantıklı senaryo dahilinde yazılmış çok güzel bir çalışma.Size sorum şu olacaktı Bucket Size olayını anlatabilirmisiniz örnek olarak 1 milyarın üzerinde bir kaydın olduğunu düşünerekiyi çalışmalar.

    1. Merhaba,

      Rica ederim.

      Sorunuza gelecek olursak, anladığım kadarıyla Bucket Count’u nasıl belirleyeceğinizi merak ediyorsunuz.

      Hash index’te veriler btree yapısına göre index’lenmez.

      Bucket adı verilen alanlara hash’lenerek atılırlar ve daha sonra hash değerlerinden tekrar veriye erişim sağlanır.

      Bucket Count’u belirlerken hash index koyacağınız kolondaki unique satır sayısı önemlidir. Çünkü duplike kayıtlar aynı bucket’lara yerleşir.

      Örneğin 1 milyar kaydı olan bir tablodaki bir kolona hash index koymanız gerekti.

      Öncelikle tablodaki unique satır sayısına bakmanız gerekiyor.

      diyelim ki 999 milyon unique kaydınız var.

      bucket count’u 999 milyon ya da bu değerin 2 katı olan 1 milyar 998 milyon olarak belirleyebilirsiniz.

      Fakat bucket count’un da bir sınırı vardır. Maksimum sınırı: 1073741824

      Bu sınırı aşarsanız aşağıdaki gibi hata dönecektir.

      The bucket count for a hash index must be a positive integer not exceeding 1073741824.

      Yani bucket count hash index’teki uniqe kayıt sayısı ya da 2 katı kadar olmalıdır. Genellikle unique satır sayısı kadar set edilir.

      Fakat siz ne set ederseniz edin sql server gerçek bucket count değerini set ederken sizin set ettiğiniz değerden sonraki 2^n koşuluna uyan en yakın değer olarak set edecektir.

      Örneğin 1000 olarak set ettiyseniz 1000’den sonraki 2^n koşuluna uyan en yakın değer olan 1024(2^10)’e set edecektir.

      Fakat tablodaki duplike kayıt sayıları fazla ise(ortalama her kayıt için en az 10 duplike kayıt ve bundan fazlası) hash index oluşturmak yerine nonclustered index oluşturmalısınız.

      örneğin 1 milyarlarlık tabloda 100 milyon unique kaydınız var ve ortalama her kayıttan 10 adet duplike var. Böyle bir durumda hash index yerine nonclustered index oluşturmalısınız.

      Özetle hash index oluşturacaksanız hash index oluşturacağınız kolon üzerindeki unique satır sayısı önem taşımaktadır.

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir

Lütfen captcha kodunu giriniz *

Lütfen Resimdeki Kodu Boşluğa Giriniz.