Database Engine, Storage Engine, SQLOS (SQL Server Mimarisi – I)

SQL Server bir makalede anlatılamayacak kadar detaylı bir mimariye sahiptir. Bu makalede hem yeni başlayanlar hem de deneyimli olanlar SQL Server mimarisinin genel yapısını aktarıyor olacağım. Makalede mimariyi anlatmakla birlikte birçok eğitim ve yazıda geçen kavramları da (Database Engine, Storage Engine, SQLOS) netleştirmiş olacağız. Microsoft’un kaynaklarında SQL Server mimarisi ile ilgili aşağıdaki diyagram bulunmaktadır.

Bu diyagrama baktığımızda karşımıza SQL Server veritabanı mimarisi ile ilgili 3 temel bileşen (component) çıkar.

1. SQL Server Network Interface (SNI)
2. Database Engine
  2.1. Relational Engine
  2.2. Storage Engine
3. SQL OS

1) SQL Server Network Interface: Adından anlaşılacağı üzere istemci ile sunucu arasındaki ağ yönetimini sağlar. Bunun da Shared memory, TCP/IP, Named Pipes ve Virtual Interface Adapter (VIA) olmak üzere 4 protokol ile sağlar. SQL Server Configuration Manager isimli program aracılığıyla bu protokollerin ayarları yönetilebilir.

Shared memory: En basit ve hızlı protokol olup SQL Server ve istemcinin aynı makine üzerinde olduğu durumlarda otomatik olarak kullanılır. Ayarlanabilir özellikleri bulunmamaktadır.
TCP/IP: En popüler protokol olup SQL sunucusuna IP üzerinden erişmizi sağlar. Varsayılan port 1433 olmakla birlikte bu port değiştirilebilir.
Named Pipes: SQL sunucu ile istemcinin aynı local area network üzerinden olması durumunda kullanılabilir. TCP 445 portunu kullanır. SQL Server alias tanımlanarak wide area network üzerinden de kullanılabilir ancak yavaş çalışacağı tercih edilmemesi tavsiye edilir.
Virtual Interface Adapter (VIA): Özel bir donanım gerektiren bu protok en yüksek performanslı bir protokol olarak bilinir.

İstemciden gelen istekleri bu network arayüzü tarafından Tabular Data Stream (TDS) isimli pakete dönüştürülerek network üzerinden sunucuya gönderilir. TDS, ilk olarak Sybase tarafından tasarlanmış olup veritabanı sunucusu ile istemci arasındaki veri aktarımı için kullanılan bir uygulama katmanı protokolüdür. Sunucu üzerinde bu TDS paketlerini dinleyecek endpoint’ler bulunur. Her network protokülüne özgü bir TDS endpoint mevcuttur. Bu endpoint’leri listesi sys.endpoints isimli view’den alınabilir.

Ayrıca sys.tcp_endpoints, sys.via_endpoints gibi view’ler kullanılarak o endpoint için daha fazla detay alınabilir. Bir endpoint’in hizmet verebilmesi için STARTED durumunda olması gerekir. SQL ortamında bu endpoint’leri açmak veya kapatmak için aşağıdaki gibi ALTER işlemi yapılabilir. Bir endpoint’in kapatılması yeni bağlantıları engeller mevcut bağlantıları kapatmaz.

ALTER ENDPOINT [TSQL Default TCP] state = stopped
ALTER ENDPOINT [TSQL Default TCP] state = started

Bu listede görünen Dedicated Admin Connection (DAC) isimli endpoint SQL Server’e normal şartlar altında erişlemediği durumlarda erişim sağlamak için kullanılır. Sonraki makalede bunu detaylandırmış olacağım.

Bir sorguyu çalıştırdığımızda sunucu ile istemci arasındaki TDS paketi büyüklüğünü görmek için Management Studio üzerinde sorgu ile birlikte “Client Statistics” özelliğini çalıştırmak yeterli olacaktır.

TDS paketleri default olarak 4KB olup istemci tarafından ayarlanabilir. Bunu ya doğrudan SQL Server üzerinde yaparız veya uygulamaya özel olarak connection string üzerinde “Network Packet Size” alanına değer vererek uygulamış oluruz. SQL Server üzerinde yapmak için “Management Studio » Object Explorer” içerisinde sunucuya ait Server Properties penceresindeki Advanced sekmesi kullanılabilir. Veya aşağıdaki gibi sp_configure prosedürü çalıştırılabilir.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'network packet size', 6500 ;
GO
RECONFIGURE;

Bu değişikliğin devreye girmesi için SQL servislerini yeniden başlatmak gerekiyor.

Fakat benim tavsiyem bunu sunucu düzeyinde yapmak yerine uygulamaya ait connection string tarafında yapmaktır.

OLE DB
Data Source=AKAYMAZ;Initial Catalog=CRM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;Packet Size=8192;

ADO.NET
Data Source=AKAYMAZ;Initial Catalog=CRM;Integrated Security=True;Packet Size=10000;Application Name=SSIS-package;

Eğer uygulama büyük miktarda data gönderiyorsa ağ paket boyutunun artırılması her defasında network üzerinde okuma yazma yapılmaması açısından performans sağlayacaktır.

2.1) Relational Engine: query processor olarak ta bilinen bu motor SQL sorgusunun çözümleyip keyword, parameter, operator ve identifier gibi mantıksal birimler halinde ayıklayarak yazım hatası olup olmadığı kontrolü yapar (Parsing the SQL statements). Sorgunun çalışması için gerekli ihtiyaçları belirler ve bu ihtiyaçları en kısa yoldan nasıl temin edeceğine karar verir (Optimizing the execution plans). Storage engine tarafına talebi bildirir ve o taraftan gelen sonucu proses eder, kullanıcıya uygun formatta sunar (tipik sonuç listesi, XML …). Genel olarak aşağıdaki task’lerden sorumludur.

Query Processing
Memory Management
Thread and Task Management
Buffer Management
Distributed Query Processing

2.2) Storage Engine: Veritabanına ait veritabanı ve log dosyalarını yönetiyor olup verinin depolama sistemlerine (storage system – Disk, SAN …) doğru, güvenli bir şekilde yazılmasından ve ihtiyaç durumunda bu sistemlerden okunmasından sorumludur. Yani I/O yöneticiliği burada gerçekleşiyor. Logging ve recovery işlemlerini yürütür. BACKUP, RESTORE, DBCC ifadeleri ve toplu aktarım fonksiyonları da bu motor tarafından yönetilir. Ayrıca Transaction Manager isimli task aracılığıyla concurrency olarak bilinen sorgularının eş zamanlı çalışması, kullanıcıların aynı anda aynı verilere erişmesi durumunda transaction ve lock mekanizmasını yönetir. SQL Server’in en önemli konusu olan Data File ve Log File yapısını sonraki paragraflarda aktarıyor olacağım.

SQL Server’e bir sorgu geldiği zaman Query Processor bölümündeki Buffer Pool’da bu sorgu yürütmek için bir execution plan olup olmadığına bakılır eğer yoksa Query Optimizer hemen bir execution plan oluşturur veya plan doğrultusunda Query Executor, sorgu için gerekli kaynağı (veriyi) Storage Engine’dan talep eder. Storage Engine duruma göre datayı ya buffer ya da disk ten okuyup Query Processor’a (Relational Engine) döndürür. Bu motor da istemciye datayı gönderir.

3) SQLOS API: SQL Server ile işletim sistemi arasındaki bir Application Layer olarak tanımlanabilir. Bu uygulama katmanı bellek yönetimi, thread – parallelism yönetimi, I/O yönetimi gibi düşük seviyedeki işlemlerden sorumludur. Bir makalede denk geldiğim aşağıdaki sıralama SQLOS’i anlamamıza yardımcı olur diye düşünüyorum.

1 – Bir kullanıcı SQL Server’a bağlandığı zaman, bu kullanıcıya tekil bir connection id ve session id atanır. (DMV: sys.dm_exec_connections)
2 – Bu oturumda çalıştırılan sorgu veya talepler kayıt altına alınır. (DMV: sys.dm_exec_requests ve sys.dm_exec_sql_text(plan_handle))
3 – Sorguya ait execution plan oluşturulduktan sonra bu plan için birden fazla task oluşturulabilir. Task sayısı query parallelism’e bağlıdır. (DMV: sys.dm_os_tasks)
4 – Her bir task için bir work atanır. Worker’ların sayısı makinenin mimarisine (32 bit veya 64 bit) ve CPU sayısına bağlıdır. (DMV: sys.dm_os_workers)
5 – Her worker bir iş parçacığı (thread) ile ilişkilidir. (DMV: sys.dm_os_threads) Her thread 32-bit’te 0.5MB, 64-bitte ise 2MB bellek gerektirir. Bellek yönetimi açısından SQL Server sadece ihtiyaç durumunda thread oluşturur.
6 – Bu aşamada Scheduler(işlemci zamanlayıcı) devreye girer. Scheduler, her bir task/worker’in CPU zamanını programlar. SQL servisi start olduğu zaman her CPU için bir scheduler oluşturulur. Yani Scheduler sayısı fiziksel CPU kadardır. SQL Server acil durum erişimleri yani Dedicated Administrator Connection için ek bir Scheduler kullanır.
Bu döngüde Scheduler, task’in çalışmasına bağlı olarak onu RUNNING, RUNNABLE veya SUSPENDED durumunda tutar. (DMV: sys.dm_os_schedulers)
7 – Bir task tamamlandığı zaman bu task’in kullandığı tüm kaynaklar serbest bırakılır.

Windows genel bir işletim sistemi olduğu için SQL Server bu kaynak planlaması (scheduling, I/O completion, memory management, resource management) için SQLOS isimli bu ara katman üzerinden gerçekleştirmektedir. Tıpkı .NET-CLR gibi SQL Server’in ihtiyaç duyduğu işletim sistemi uygulama arayüzlerini (OS APIs) bu ara katmanda yürütür. Bu SQLOS’in Windows’un yerine geçtiği anlamına gelmemektedir. SQLOS ile ilgili veriler için sys.dm_os_ ile başlayan DMV’ler kullanılabilir.

Bu bilgiler ışığında SQL Server’e gelen bir talebin nasıl işlendiğini dell.com sitesinden aldığım aşağıdaki diyagram çok güzel özetlemiştir.

1

SQL Statements

The client application sends “batches” of SQL statements to SQL Server for execution.

2

Syntax Check

The Parser checks the syntax of the SQL statements.

3

Cache Lookup

The Parser checks the syntax of the SQL statements, and calls the SQL Manager to see if matching SQL is already in the Procedure Cache. If a match is found, the Optimizer is bypassed.

4

Parsing

If no matching SQL is found in the Procedure Cache, the Parser translates the batch into a Query Tree and passes it to the Optimizer.

5

Optimization

The Optimizer takes the Query Tree and produces an optimal Execution Plan based on data sizes, indexes, join techniques and CPU, I/O & memory estimates.

6

Execution

The Query Executor runs the Execution Plan, often interacting heavily with the Access Methods Manager and Transaction Manager.

7

Results

The Query Executor passes results back to ODS.

8

Results

ODS buffers the results and returns them to the client only when the buffer fills or the batch of SQL statements ends.

9

Row Operations

The Access Methods Manager locates and modifies data at the row and index level. It calls the Buffer Manager to retrieve and modify individual pages.

10

Page Retrieval

When a data or index page is required, the Buffer Manager checks the data cache. If the page is already there, disk reads are avoided. If not, it is read from disk.

11

Page Modification

Pages in the cache that are modified will eventually be written back to disk (usually by the Checkpoint process). They may be modified many times in the cache before this happens.

12

Checkpoints

The Checkpoint process minimizes the amount of work SQL Server has to do on startup by periodically flushing dirty pages to disk.

13

Free Pages

The Lazy Writer process maintains the list of buffer pages that are available for immediate re-use. If necessary, it will write dirty pages to disk to make them available.

14

Log Caching

When the Buffer Manager modifies a page, it gets the Log Manager to add the log record(s) to the Log Cache. Once the user commits their changes, the Log Manager places these pages into a queue to be written to disk by the Log Writer. The user process waits for the log writer to flush the log pages to disk.

15

Log Flushes

The Log Writer process writes pages from the log flush queue out to disk. Once the writes are complete, any user processes waiting on that log cache can continue.

SQL Server mimarisi konulu bu seride aşağıdaki konuları detaylandırmaya çalışıyorum.

SQL Server’in mimarisi nasıldır?
SQL Server veri tabanı mimarisi, disk yönetim mimarisi?
SQL Server, verileri nasıl saklar, kayıt eder?
Sql Server storage architecture, storage engine, storage internals
Page, Extent, Allocation Unit, IAM Page, PFS, GAM, SGAM Pages, Row Offset Nedir?
SQL Server bellek yönetimi, memory management
SQL Server işlemci yönetimi, CPU management
SQL Server disk space, disk usage
Sql Server IO performance

Bir Cevap Yazın

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

Time limit is exhausted. Please reload CAPTCHA.