Natively Compiled Stored Procedures

Merhaba,
Bugünkü yazımda Memory Optimized Tablo'lardan ve Natively Compiled Stored Procedure'lerin kullanımından bahsedeceğim.

Konuyu;
1. In-Memory OLTP Teknolojisi
2. Memory Optimized Tablolara Erişim Yolları
3. DEMO

başlıkları altında anlatacağım.


1. In-Memory OLTP Teknolojisi
Natively Compiled Stored Procedure'lerin kullanımına geçmeden önce In-Memory OLTP teknolojisinden bahsedelim. Bildiğiniz gibi SQL Server'da verilerimiz diskte veya buffer pool'da tutulur. SQL Server 2014 ile birlikte gelen In-Memory OLTP teknolojisi sayesinde artık verilerimiz memory'de tutulabiliyor. Dolayısıyla artık sorguyu çalıştırmak için SQL Server'ın I/O yapmasına gerek kalmıyor; tablolarımız direkt olarak memory'den okunuyor. Bu teknoloji sayesinde yüksek oranda performans artışı ve sorgu hızı sağlıyoruz.

2. Memory Optimized Tablolara Erişim Yolları
Bellek içinde tuttuğumuz bu In-Memory Optimized Tablolara ise iki şekilde erişebiliyoruz:
1. T-SQL kullanarak,
2. Natively Compiled Stored Procedure kullanarak.

T-SQL kullanarak disk-based tablolara göre performans artışı sağlıyoruz; fakat Natively Compiled Stored Procedure kullanarak sağladığımız performans artışı çok daha yüksek oranda oluyor. Bunun nedeni ise; çalışma mantığının ilkinde interpretation (yorumlama), ikincisinde ise compilation (derleme) şeklinde olması.
Compiler (derleyici)'ler; bir kaynak kodu hedef koda çevirdikten sonra çalıştıran dolayısıyla koddaki hataları yakalama işlemini ve kodun iyileştirilmesini daha kod çalıştırmadan yapan çeviricilerdir.

Nativey Compiled Stored Procedure'ler T-SQL ile yazılır fakat C native kodu ile derlenirler.

Interpreter (yorumlayıcı)'lar; kodu satır satır veya bloklar halinde çalıştırıp sırası gelmeyen satırları hiç çalıştırmayan, bu satırlardaki hataları hiçbir zaman göremeyen ve kodun bütününe ait iyileştirmeleri yapamayan çeviricilerdir.

Bizim disk-based tablalarımızda kullandığımız Stored Procedure'ler de bu mantıkla çalışırlar. Memory Optimized Table ve disk-based table'ların ikisinin birden kullanıldığı sorgulara T-SQL koduyla erişebiliriz. Fakat; Natively Compiled Stored Procedure'ler ile disk-based table'lara erişim sağlayamayız.

3. DEMO
Natively Compiled Stored Procedure'lerin performans artışında nasıl bir etkisi olduğunu anlamak için bir DEMO yapalım. Yapacağımız işlemler sırasıyla şu şekilde olacak:

1. Memory Optimized Data Filegroup içeren bir veri tabanı oluşturacağız.
2. Oluşturduğumuz bu veri tabanında Memory Optimized File Group içerisine dosya ekleyeceğiz.
3. Veri tabanımızda Disk-Based tablo oluşturacağız.
4. Memory-Optimized tablo oluşturacağız.
5. Disk-Based tablomuza 500000 kayıt ekleyeceğiz.
6. Natively Compiled Stored Procedure oluşturup Memory-Optimized tablomuza 500000 kayıt ekleyeceğiz.

ve son iki maddede yaptığımız işlerde harcanan performansları karşılaştıracağız.

1. Memory Optimized Data Filegroup içeren bir veri tabanı oluşturuyoruz.
Öncelikle veri tabanı oluştururken kullandığımız T-SQL kodlarını ve veritabanı file ve filegroup yapılarını bir hatırlayalım:

    1.1. T-SQL İle Veri Tabanı Oluşturma
    1.2. Veri Tabanı Dosyaları (Database Files)
    1.3. Veri Tabanı Dosya Grupları (Database Filegroups)

1.1. T-SQL İle Veri Tabanı Oluşturma
CREATE DATABASE [veri tabanı ismi]
ON PRIMARY
(
NAME = [dosya takma ismi] ,
FILENAME = [fiziki dosya ismi] ,
SIZE = [dosya boyutu] ,
MAXSIZE = [maksimum dosya boyutu] ,
FILEGROWTH = [dosya artım miktarı]
)
LOG ON 
(
NAME = [dosya takma ismi] ,
FILENAME = [fiziki dosya ismi],
SIZE = [dosya boyutu] ,
MAXSIZE = [maksimum dosya boyutu] ,
FILEGROWTH = [dosya artım miktarı]
)

Veri tabanı ismi: Veri tabanına verilecek isimdir.
Dosya takma ismi: Veri tabanındaki dosyalar için belirlenen takma isimdir. İlgili dosyaya erişimde pratiklik sağlar. İşletim sistemince bilinen isimdir.
Fiziki dosya ismi: İşletim sisteminde saklanacak dosyanın adını ve yolunu belirtir. Bu isim de işletim sistemince bilinen bir isimdir.
Dosya boyutu: Veri tabanı tanımlandığındaki boyutunu belirtir. Başlangıçta 1MB’tır. Management Studio’da bu 3MB olarak tanımlanır. Oluşturulacak log dosyası da bunun %10’u kadardır.
Maksimum dosya boyutu: Dosyanın maksimum boyutunu belirtir. Belirtilmezse disk dolana kadar dosya artım miktarı kadar artmaya devam eder.
Dosya artım miktarı: Dosyanın belirtilen boyutu dolduğunda dosya boyutu otomatik olarak artar. Artım miktarı bu parametreyle belirtilir.

1.2. Veri Tabanı Dosyaları (Database Files)
Veri tabanlarında veri dosyaları (data files) ve log dosyaları (log files) olmak üzere iki tür dosya bulunur:

Data files (veri dosyaları); veri tabanındaki verilerin fiziksel olarak saklandıkları dosyalardır. Tablolar, indeksler, viewler, stored procedure'ler bu dosyaların içinde bulunur.

Veri dosyaları iki tiptir: 

• Primary Veri Dosyaları
  Primary veri dosyası; veri tabanının başlangıç noktasıdır ve veri tabanındaki diğer dosyaları işaret eder. Her veri tabanında ilk oluşacak dosya primary data file'dır. Bir veri tabanı içinde sadece bir tane primary data file olabilir. Primary data file'lar için tavsiye edilen dosya adı uzantısı .mdf'dir.
• Secondary Veri Dosyaları
  Secondary veri dosyaları; opsiyoneldir, kullanıcı tanımlıdır ve kullanıcı verilerini saklarlar. Veri dosyalarını farklı disklere yerleştirmek istediğimizde secondary data file kullanılabilir ya da tek bir windows dosyası için maksimum boyutu aşarsa secondary data file kullanılıp veri tabanın büyümesi devam ettirilebilir. Bazı veri tabanları hiç secondary data file içermezken, bazısı birden fazla secondary data file içerebilir. Secondary data file'lar için tavsiye edilen dosya adı uzantısı .ndf'dir.

Log files (log dosyaları); veri tabanı içerisindeki tüm transactionları kurtarmak için gerekli bilgiyi içerirler.  Bir veri tabanında yapılacak olan her işlem, ilk önce istemci bilgisayardan yola çıkar, daha sonra SQL Server'a ulaşır, burada doğruluğu denetlenir, daha sonra Transaction Log'a işlenir ve en son olarak da veri tabanındaki ulaşılmak istenen nesneleri etkiler. Yani her komut ilk önce Transaction Log'lara işlenir. Bu da yapılan her işlemin geri alınabileceği anlamına gelir. Her veri tabanında en az bir log dosyası bulunur. Transaction log file'lar için tavsiye edilen dosya adı uzantısı .ldf'dir.

1.3. Veri Tabanı Dosya Grupları (Database Filegroups)
Filegroup'lar veri tabanı nesnelerinin mantıksal gruplanmasını gösterirler. (Data file'lar verilerin fiziksel olarak saklandıkları dosyalardı.)

Filegrouplar iki çeşittir:

Primary Filegroup: Her veri tabanı bir primary filegroup'a sahiptir. Bu filegroup primary veri dosyasını ve (eğer başka bir filegroup'a dahil edilmemişlerse) secondary veri dosyalarını içerirler.

User-Defined Filegroup: Kullanıcı tanımlı filegroup'lar CREATE DATABASE VE ALTER DATABASE ifadeleri içinde özel olarak FILEGROUP anahtar sözcüğü kullanılarak oluşturulan filegroup'lardır.

Veri tabanı oluştururken kullandığımız T-SQL kodlarını ve veritabanı file ve filegroup yapılarını hatırladığımıza göre artık Memory Optimized Data Filegroup içeren bir database oluşturabiliriz.

------------------------------------------------------------------------------------------------------------------------------
CREATE DATABASE [OrnekDB]
ON  PRIMARY
( NAME = N'Primary', FILENAME = N'C:\OrnekDB\Primary.mdf' , SIZE = 5 MB , FILEGROWTH = 10% )
 LOG ON
( NAME = N'Transaction_Log', FILENAME = N'C:\OrnekDB\Transaction_Log.ldf' , SIZE = 5 MB , FILEGROWTH = 10%)
GO
ALTER DATABASE [OrnekDB] ADD FILEGROUP [OrnekDB_FileGroup] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [OrnekDB] SET COMPATIBILITY_LEVEL = 120
GO
------------------------------------------------------------------------------------------------------------------------------

Ne Yaptık?

1. Bilgisayarımızda Yerel Disk (C:)'te OrnekDB adında bir klasör oluşturduk.
2. Primary File için Name olarak 'Primary', Filename olarak OrnekDB klasörünü açtığımızda görmüş olduğumuz url'yi yazdıktan sonra yanına 'Primary.mdf' ekledik. Log File için Name olarak 'Transaction_Log', Filename olarak OrnekDB klasörünü açtığımızda görmüş olduğumuz yine aynı url'yi yazdıktan sonra yanına 'Transaction_Log.ldf' ekledik. Her iki dosyanın maksimum boyutlarını ve otomatik artma oranlarını belirledik.
3. In-Memory Optimized tablolarını tutmak için CONTAINS MEMORY_OPTIMIZED_DATA komutuyla 'OrnekDB_FileGroup' adında Memory Optimized Data Filegroup oluşturduk.
4. SQL Server 2014'e uyumlu olması için COMPATIBILITY_LEVEL = 120 yazdık.

2. Oluşturduğumuz bu veri tabanında Memory Optimized File Group içerisine File ekliyoruz.

USE [master]
GO
ALTER DATABASE [OrnekDB]
ADD FILE (NAME = N'OrnekDB_File', FILENAME = N'C:\OrnekDB\File.ndf')
TO FILEGROUP [OrnekDB_FileGroup]
GO
------------------------------------------------------------------------------------------------------------------------------

Ne Yaptık?

1. Kendi tanımlamış olduğumuz (User-Defined FileGroup) 'OrnekDB_FileGroup' Filegroup'una File ekledik. Kendimiz tanımladığımız için bu Filegroup'u içerisine ekleyeceğimiz File da Secondary data file olacak. Dolayısıyla 'OrnekDB_File' olarak adlandırdığımız File'ımıza Filename olarak .ndf uzantılı bir ad verdik.
2. Şimdiye kadar yazdığımız tüm kodları çalıştırdığımızda bilgisayarımızdaki OrnekDB klasörüne 'File.ndf', 'Primary', 'Transaction_Log' dosyaları eklenmiş oldu.
























3. Veri tabanımızda Disk-Based tablo oluşturuyoruz.

USE [OrnekDB]
GO

CREATE TABLE dbo.diskbasedtable
(
c1 int NOT NULL PRIMARY KEY,
c2 int NOT NULL INDEX idx_c2 NONCLUSTERED,
c3 DATETIME2 NOT NULL,
c4 NCHAR(400)
)
GO
------------------------------------------------------------------------------------------------------------------------------

Ne Yaptık?

OrnekDB veri tabanında daha sonra içerisine veri eklemek için kullanacağımız 4 sütundan oluşan bir tablo oluşturduk ve bu tablomuza "diskbasedtable" adını verdik. Birinci sütunumuzu primary key olarak belirledik, ikinci sütunumuza nonclustered index atadık.

4. Veri tabanımızda In-Memory Optimized tablo oluşturuyoruz.

USE OrnekDB
GO

CREATE TABLE dbo.inmemorytable
(
c1 int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 20000000),
c2 int NOT NULL,
c3 DATETIME2 NOT NULL,
c4 NCHAR(400)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA);
GO
------------------------------------------------------------------------------------------------------------------------------

Ne Yaptık?

1. OrnekDB veri tabanında daha sonra içerisine veri eklemek için kullanacağımız 4 sütundan oluşan bir tablo oluşturduk ve bu tablomuza "inmemorytable" adını verdik.
2. Disk-based tablo oluşturmaya göre burada yaptığımız farklı işlemlerden bahsedelim:
- Tablomuzu "In-Memory Optimized Table" hale getiren en sondaki MEMORY_OPTIMIZED = ON ifadesidir. 
-  Hemen yanında belirtilen DURABILITY ifadesi ile verinin kalıcılığını belirtebiliyoruz. Yani server yeniden başlatılsa bile verinin memory'ye tekrar yüklenip yüklenmeyeceğini bu ifadeyle belirtebiliyoruz. Buraya yazdığımız "SCHEMA_AND_DATA" ifadesi ile hem şema hem de veri korunacaktır. Eğer bunun yerine "SCHEMA_ONLY" ifadesini kullanırsak şemanın kalıcılığı garanti altına alınır fakat veri kalıcı olmaz.
- In-Memory Optimized Table'larda clustered indeks yoktur. In-Memory Optimized Table'lar en az bir indeks içermelidir. In-Memory Optimized Table'lar Hash Indeks ve Range Indeks olmak üzere iki tür indeksi destekler. İçerisinde HASH ifadesi geçen Hash Index, geçmeyen Range Indeks olarak isimlendirilir.

• Hash Indeks türünde; indeks tanımlanırken BUCKET_COUNT ile belirttiğimiz hücrelere veriler hash algoritmasından geçerek yerleşir. Bucket'lar satırlardaki verilerin bellek adreslerinin bulunduğu hücrelerdir. Hash indeksler <,> ve BETWEEN gibi aralık aramalarını desteklemezler. Aralık sorgulamalarının sıkça yapıldığı durumlarda Range Indeks tercih edilir.
• Range Index türünde; In-Memory mantığına uygun olarak eklenen satırların mantıksal adresleri "Page Mapping Table" isimli alanda sırayla tutulur. Diğer pageler de pointerlar yardımıyla bir sonraki pageleri işaret ederler. En son leaf seviyede satırların fiziksel adresleri tutulur. Index pageleri update edilmez; bunun yerine Page Mappping Table update edilir.

Biz burada Primary Key olarak tanımladığımız birinci sütunumuza nonclustered indeks olarak Hash Indeks atadık. Primary Key olarak belirlediğimiz sütuna herhangi bir indeks atamazsak bunu default olarak clustered indeks atanmış kabul edeceği için hata verecektir. Çünkü daha önce de belirttiğimiz gibi In-Memory Optimized Table'lar clustered indeksleri desteklemezler.

5. Diskte Tutulan Tablomuza 500000 kayıt ekliyoruz

USE [OrnekDB]

GO

DECLARE @i INT = 0

WHILE @i < 500000

BEGIN

INSERT INTO dbo.diskbasedtable VALUES (@i, @i/2, GETDATE(), N'my string')

SET @i += 1

END

------------------------------------------------------------------------------------------------------------------------------

Ne Yaptık?

Sırasıyla int, int, datetime, nchar tipinde olan sütunlarımıza WHILE döngüsü içerisinde veri ekledik. 

6. Natively Compiled Stored Procedure kullanarak In-Memory Optimized olarak oluşturduğumuz tablomuza 500000 kayıt ekliyoruz

USE [OrnekDB]
GO
CREATE PROCEDURE Insert500000Rows
WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
DECLARE @i INT = 0
WHILE @i < 500000
BEGIN
INSERT INTO dbo.inmemorytable VALUES (@i, @i/2, GETDATE(), N'my string')
SET @i += 1
END
END
GO
------------------------------------------------------------------------------------------------------------------------------

Ne Yaptık?

Diskbased tablolarda stored procedure yazarken kullandığımız şema aşağıdaki gibiydi:

CREATE PROCEDURE [Procedure ismi]
(
@Parametre1 VeriTipi
@Parametre2 VeriTipi...)
AS
BEGIN
[Yazılacak sorgu komutu]
END

Natively Compiled Stored Procedure oluşturmak için kullandığımız koda baktığımızda diskbased stored procedure'lerden farklı olarak bazı ifadeler görüyoruz.

Sırayla inceleyelim:

1. NATIVE_COMPILATION ifadesi procedure'ün In-Memory kapsamında olduğunu gösteriyor.
2. Native Compiled Stored Procedure'lerin SCHEMABINDING ile işaretlenmesi zorunludur; bu şekilde bağlı olduğu nesnelerin şemalarının değiştirilmesi engellenmiş olur.
3. EXECUTE AS OWNER ifadesi ile procedure'ün kimin yetkileri ile çalıştırılacağı belirtilir. Bunun dışında Natively Compiled Stored Procedure EXECUTE AS USER ve EXECUTE AS SELF seçeneklerini destekler. EXECUTE AS CALLER'ı desteklemez.

4. BEGIN ATOMIC WITH ifadesinin içerisinde dilin, transaction'ın izolasyon seviyesinin ne olacağı gibi session ayarları belirtilir.

Transaction izolasyon seviyesi dediğimiz şey ne peki?
Aslında açıklaması isminde gizli. Yapılan bir transaction'ın diğer transactionlara karşı ne kadar izole edilmesi istendiğini belirtir. Mesela; aynı server'a bağlı iki kullanıcının aynı anda aynı tabloda işlem yaptığını düşünelim. İlk kullanıcımız SELECT sorgusuyla WHERE koşulunu da kullanarak tablodan bazı bilgileri çekti diyelim. Henüz transaction'ı kapatmadan (COMMIT ya da ROLLBACK yapmadan) diğer kullanıcı aynı tabloda ve aynı where koşulunda UPDATE işlemi yaptı diyelim. Bu durumda birinci kullanıcı tekrar SELECT sorgusuyla tabloyu görmek istediğinde, diğer kullanıcının güncellemiş olduğu bilgileri mi görecek yoksa eski bilgileri mi? Transaction izolasyon seviyelerini belirleyerek buna biz karar verebiliyoruz.

In-Memory Optimized Table'lar sadece SNAPSHOT, REPEATABLE READ, SERIALIZABLE olmak üzere 3 izolasyon seviyesini desteklerler.

Aynı anda iki transaction'ın açık olduğunu düşünelim. Birinde tabloyu okumak istiyoruz, diğerinde bu tablo üzerinde güncelleme yapmak istiyoruz:

• Transaction izolasyon seviyesini SNAPSHOT belirlediğimizde; SELECT ile tabloyu okumak istediğimizde, UPDATE işlemi yaptığımız transactionda transaction'ın COMMIT ya da ROLLBACK yapılıp yapılmamasını beklemeden ilk transaction'ımızda sorgu bize eski veriyi gösterir.
• Transaction izolasyon seviyesini SERIALİZABLE belirlediğimizde; SELECT ile tabloyu okumak istediğimizde, sorgu çalışmaz; diğer transactionın kapanmasını bekler. Eğer diğer transaction ROLLBACK yapılırsa bize eski veriyi gösterir, eğer COMMIT edilirse yeni veriyi gösterir.
• Transaction izolasyon seviyesini REPEATABLE belirlediğimizde; SELECT ile tabloyu okumak istediğimizde, UPDATE işlemi yaptığımız transaction'ın COMMIT ya da ROLLBACK edilmesine bakmadan direkt olarak UPDATE edilmiş halini gönderir.

Biz bu kodumuzda transaction izolasyon seviyesini SNAPSHOT olarak belirledik.

Diskbased tablomuza 500000 veri ekleme ve Natively Compiled Stored Procedure oluşturarak In-Memory Optimized Table'ımıza 500000 veri ekleme performanslarını karşılaştırdığımızda; diskbased tablomuza yaklaşık 3 dakikada veri eklenirken, In-Memory Optimized Table'ımıza milisaniyeler içinde veri eklendiğini görüyoruz.

Kaynaklar

• 20464C Querying Microsoft® SQL Server®
• https://msdn.microsoft.com/en-us/library/dn452286.aspx
• https://msdn.microsoft.com/en-us/library/dn249342(v=sql.120).aspx
• http://abdullahkise.blogspot.com.tr/2014/01/microsoft-sql-server-2014-ctp2.html
• http://sqlservernedir.blogspot.com.tr/2015/08/sql-server-2014-yenilikleri-1-in-memory.html
• http://yunuskaradag.blogspot.com.tr/2014/10/sql-server-2014-in-memory-oltp-kullanm.html
• http://www.misjournal.com/?p=8923
• http://www.ismailgursoy.com.tr/transaction-nedir-nasil-kullanilir/
• http://www.buraksenyurt.com/post/Transaction-larda-Izolasyon-Seviyeleri-(Isolation-Level)-1-bsenyurt-com-dan.aspx
• http://bilgisayarkavramlari.sadievrenseker.com/2008/01/03/derleyici-compiler/
• http://hbogm.meb.gov.tr/modulerprogramlar/kursprogramlari/bilisim/moduller/agveritabaniplanlama.pdf