Bu makalede sql server system veritabanlarının ne olduğuna değinmiş olacağız. System veritabanları Master, Model, Msdb ve Tempdb olarak 4 ana başlıktan oluşmaktadır. Şimdi bu yapıların ne olduğuna değinelim.
System database’leri Databases>System Databases sekmesi altında bulunmaktadır.
Master Db: System databases’lerin içerisinde en önemli database diyebiliriz. Master veritabanımız olmadan kurulu olan sql server instance çalışmaz. İnstance bazında tüm kullanıcı bilgileri, veritabanı bilgileri(collation,recovery model vs..), veritabanına kullanıcı erişim bilgisi kısacası system’le alakalı temel konfigürasyon bilgileri tutulur. Dikkat edelim bilgileri tutulur veriler tutulmaz. Eğer herhangi bir veritabanı taşıma adımlarında master bilgilerinde veritabanı mdf ve ldf file bilgileriyle eşleşmiyorsa veya veritabanı path’leri farklı ise veritabanı recovery pending modunda kalmaktadır.
Not: Master veritabanımızı başka bir ortama restore edeceğimiz zaman master veritabanının önceki ortamda disk ve klasör uzantısının olması gerekiyor. Yoksa restore işleminde başarısız oluruz. Aşağıdaki ekran görüntüsünde fiziksel uzantısının olması gerekmektedir. Master veritabanı backup dosyasını restore edildikten sonra üzerinde bulunan veritabanlarına ait verileri bulundurmadığını belirtmiştik. Sadece master metadata bilgileri içerisinde database bilgileri ve fiziksel uzantıları olduğu yeni bir ortama master backup dosyasını yüklediğimizde meta data bilgisine bakar veritabanlarımız ayağa kalkmış olur içerisindeki veri ile birlikte.
Not: Sql server versiyonları aynı olmak koşuluyla master veritabanı bir ortamdan başka bir ortama taşınabilir.
Not: Master veritabanını başka bir ortama taşıdığımızda master veritabanının meta data içindeki bilgilerle aynı bir veritabanı oluştursak hata mesajı alırız.
Aşağıdaki komut ile hangi veritabanının hangi kullanıcılarla ilişkili olduğu ve veritabanı hakkında bilgileri görebiliriz.
select*from sys.master_files
Not: master veritabanı için log_reuse_desc = CHECKPOINT Bu normaldir. master sistem veritabanı her zaman SIMPLE recovery modelindedir ve CHECKPOINT sonrası log tekrar kullanılabilir hâle gelir. Yapılacak bir şey yok — bu SQL Server’ın beklenen davranışıdır.
Not: Master veritabanına ikinci bir log dosyası eklenmez.
Model Db: Şablon veritabanıdır. İnstance üzerinde oluşturduğumuz her bir veritabanı model db’yi referans alarak oluşur. Model db properties ekranında files sekmesinde data dosyalarımızın autogrowth değerini ayarlarsak oluşturduğumuz her database için bu yapı aynen oluşacaktır.
Model db altında autogrowth değerimi 1024 yapıyorum.
Daha sonra bir yeni bir veritabanı oluşturduğumda Autogrowth değeri 1024 olarak karşımıza çıkacaktır.
Kısacası modeldb her veritabanında olmasını istediğimiz yapıların belirlendiği bir yapı olarak karşımıza çıkmaktadır. Başka bir örnekte vermek gerekirse model db altında bir tablo veya tablolar oluşturursak oluşturduğunuz her veritabanı altında bu tablolar olmuş olacaktır. Model db altında collation recovery model,compatibility level vb.. tüm ayarlamaların diğer dbler tarafından referans alınır. Model db olmazsa sql server system’i çalışmaz. Genelde webhosting firmaları kullanmaktadır.
MSDB: Sql Server agent servisinin kullandığı bir yapıdır. Periyodik olarak çalıştırılan her türlü işlem(job,alert,Schedule vs..) burada tutulur. Msdb datasının bozulması sql server’ın çalışmasına bir engel değildir. Sadace sql server agent yapısının çalışmasına bir engeldir.
Not: Backup işlemleri msdb veritabanı altında olmaktadır.
Temp Db: Geçici tabloların oluşturduğu işlemler burada gerçekleşir. Veritabanlarında çekilen order by,group by sum,count,max,min.. gibi komutlarla özetlediğimiz aggregation işlemleri için temp db veritabanımız kullanılmaktadır. Ayrıca trigerlar,update istatistik,online indexs işlemleri,cursor,join işlemleri,dbcc checkdb operasyonlarıda bünyesinde barındırmaktadır. Bu sebeple işlem yoğunluğu fazla olan bir instance üzerinde tempdb file sayımızı arttırmak gerekebilir. Belirli script’lerle tempdb file’ın ne kadar read-write yaptığını görebiliriz. Read-write değerimiz 20 ms üzerindeyse tempdb file sayısının artırılması gerekmektedir. Temp db yapısı ile veritabanımızı çöplüğe dönüştürmeyiz. Geçici kullanıcı istekleri ile şekillenen yapıların memory üzerinde çalıştırılıp kullanıcıya sunulması gerekmektedir. Aşağıdaki komut ile bunu görebiliriz.
USE tempdb;
GO
SELECT
DB_NAME(vfs.database_id) AS [Database Name],
vfs.file_id,
mf.name AS [Logical File Name],
mf.physical_name AS [Physical File Path],
vfs.num_of_reads AS [Reads],
vfs.num_of_writes AS [Writes],
vfs.io_stall_read_ms AS [Read Stall (ms)],
vfs.io_stall_write_ms AS [Write Stall (ms)],
vfs.size_on_disk_bytes / 1024 / 1024 AS [Size on Disk (MB)]
FROM
sys.dm_io_virtual_file_stats(DB_ID('tempdb'), NULL) AS vfs
JOIN
sys.master_files AS mf
ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
WHERE
vfs.database_id = DB_ID('tempdb');
Not: Birden fazla session geçici tablo oluşturduğu sırada TempDB’nin sistem tablolarına aynı anda erişmek ister bunun sonucunda MetaData contention oluşur. Bu iş yükü sistem tablolarında gecikmeye sebep olur ve sorgu performansları düşmeye başlar. TempDB üzerindeki darboğazı gidermek ve iş yükünü bölmek için TempDB içerisine yeni file eklenmelidir.
Not: SQL Server’da çalıştırmış olduğumuz sp komutu (sp_whoIsactive @show_sleeping_Spids = 0) ile anlık login olan sorguları kontrol ederken wait_info column içeriğinde görmüş olduğumuz PAGELATCH_EX, PAGELATCH_UP, PAGELATCH_SH ve CXPACKET türleri TempDB ile doğrudan ilgilidir.
Not: tempdb veritabanı için log_reuse_desc = LOG_BACKUP Bu biraz daha ilginç çünkü tempdb de SIMPLE recovery modelindedir ve normalde transaction log backup gerekmez. Ancak log_reuse_desc = LOG_BACKUP görüyorsan: Bu büyük ihtimalle bir “görüntüleme hatası” ya da geçici bir durumdur.
Yani SQL Server burada yanlış veya geçici bir durum bildiriyor olabilir. Çünkü: Bu alanın LOG_BACKUP göstermesi genellikle bir sorun değildir. tempdb için log backup yapılamaz. tempdb her sunucu yeniden başlatıldığında yeniden oluşturulur, bu yüzden temiz başlar.
Tempdb properties ekranında birden fazla file oluşturulabilir.
Yukarıdaki resimdeki yapımız sql server kurulum ekranında yapılmıştır. Kurulum yaparken veya system kurulduktan sonra yukarıdaki bölümden yeni file’lar eklenebilir.
Not: Daha önceden Trace Flag 1117,1118 ile aktif ettiğimiz TempDB data dosyalarının eşit biçimde büyümesi işlemi artık default olarak aktif geliyor. Sql Server Trace Flag makelesini okuyabilirsiniz.
Not: Tempdb autogrowth ve initial size değerlerinin iyi konfigüre edilmesi gerekmektdir.
Not: Tempdb üzerinde session bazlı tablo oluşturmak istersek # işareti oluşturulan tablonun önüne koyulmalıdır.
Create table #table23Tempdb üzerinde instance bazlı tablo oluşturmak istersek ## işareti oluşturulan tablonun önüne koyulmalıdır.
Create table ##table23Tempdb read-write değerini nasıl öğrenildiğini Task Manager ekranında bulunan Resource Monitor kısmından bulabiliriz. Yukarıdaki paylaşmış olduğumuz sorgudanda tespit edilebilir.
Aşağıdaki yazı ve tempdb de belirtilen bazı bölümler ilgili linkten alınmıştır. Mutlaka okumalısınız.
SQL Server servisini yeniden baslattıgımızda TempDB tekrar drop edilip olusturulur. Diger veritabanlari gibi sürekli değildir. Servis her başladığında Model veritabanindan kopyalanarak oluşturulur. TempDB’nin yedegi alinamaz, yedekten geri dönülemez ve recovery modeli değiştirilemez. Simple modeldedir. Drop edilemez. Filegroup sayısı artırılamaz. Collation bilgisi değiştirilemez. Offline ya da read only mode a alınamaz. Yukarıda sıraladiğımız içeriğinden dolayı, sorgu performansını direkt etkileyen bir sistem veritabanıdır. TempDb’de bir geçici tablo oluşturduğumuzda, normal veritabanında tablo oluşturduğumuzda nasıl yer ayrılıyorsa (allocation), tempdbde de öyle bir allocation süreci isler. Bu süreçte üç page söz konusudur. PFS(Page Free Space), GAM(Global Allocation Map), SGAM(Shared Global Allocation Map). Bir sql server objesini oluşturmak ya da silmek için Sql server PFS ve SGAM page lerine yazar. Latch dediğimiz, aşağıda açıkladığımız yapılar bu pageleri hafızada korur. Her bir tempdb data file için birer adet PFS ve SGAM page mevcuttur.
PFS(Page Free Space): Her page için bir byte lık bir bilgi tutar. Bu bilgi, ilgili page de ne kadar boş alan olduğunu, ne için kullanıldığını tutar. Bir PFS page yaklaşık 64MB boyutunda page’e dair bilgileri tutar. Bir data file içerisinde her 64 MB için bir PFS page den söz edebiliriz. Herhangi bir veritabanı data file için ilk page PFS dir. Page ile ilgili bir veritabanı hatasında bunu görebiliriz. Örneğin 2:1:1 ifadesi, database id:2 ve 1 numaralı page demektir. Keza tempdin içinde şu ifade 5:3:1 database id: 5, file id:3 ve ilk PFS page i göstermektedir.
GAM (Global Allocation Map) : GAM Page her extent’te (8 page), hangisinin dolu hangisinin boş olduğunu gösteren bir bitlik bir bilgi tutar. SQL Server boş extent’leri bu page lere bakarak tespit eder. Her extent için bir bit kullanılmış bir GAM page in 4 GB bir alanı öğretmesine olanak tanır. Bir data file’daki ilk GAM page’in page numarası 2’dir. 2:1:2 TempDB deki ilk GAM page’i gösterir.
SGAM (Shared Global Allocation Map) : Her extent için yine bir bit olarak mixed extent mi uniform extent mi bilgisini tutar. SQL server küçük verileri yerleştirmek için boş alan içeren mixed extentleri bulmak için bu page’i kullanır. GAM gibi 4GB lik bir alanı yönetir. Bir data file daki ilk SGAM page in numarası 3 tür. Yani 2:1:3 tempb’nin SGAM page’idir.
Latch Kavramı
Buffer pool, işletim sisteminin SQL server için hafızada(memory) rezerve ettiği bir alandır. SQL Server gelen taleplere göre diskten ilgili page’leri memory de buffer pool’a atar. Bu sistem içerisinde, verinin buffer pool içerisinde tutarlılığını korumak için bir sisteme ihtiyaç vardır. Latch, SQL Server’in memory içindeki bu verinin tutarlılığını sağladığı bir lock mekanizmasıdır. Paylaşılan hafıza kaynaklarını korumaya yarar. Bir thread bir page üzerinde çalışmayı talep ettiğinde, aynı anda bir page’i sadece bir thread değiştirebileceğinden bu latch üzerinde “latch queue” denilen kuyruk oluşur. Bu page taleplerini dagıtmak amacıyla tempdb data file sayısının, işlemci çekirdek sayısıyla orantılı olarak artırılması önerilir. Çünkü her bir data file için ayrı bir “latch queue” vardır. Bu data file sayısını artırma işlemi thread’in page’i bekleme süresini azaltır. Bu da sorgu performansının artması demektir.
Mssql serverda 5. olan ve görünmeyen system veritabanı mevcuttur. SQL Server Management Studio görünmez. Server üzerinde hangi sql server sürümü yüklüyse aşağıdaki dizin altında bulunmaktadır.
SQL Server sürümüne göre değişmektedir.
• SQL Server 2017 → MSSQL14.MSSQLSERVER
• SQL Server 2019 → MSSQL15.MSSQLSERVER
• SQL Server 2022 → MSSQL16.MSSQLSERVER
Bu sistem veritabanının görevi tüm sistem nesnelerini (örneğin sys.objects, sistem procedure’ler vb.) içerir. Yani master veritabanında gördüğün sistem tablolarının gerçek kaynağı aslında Resource Database’dir. SQL Server güncellendiğinde (örneğin servis paketi veya CU kurulunca), sistem nesneleri buradan güncellenir. Böylece kullanıcı veritabanları etkilenmez. Resource Database salt okunurdur (read-only). SSMS üzerinden bağlantı gerçekleştirilmez.
Aşağıdaki klasörde gördüğünüz master, model, msdb, tempdb benzeri dosyalar gerçek çalışan sistem veritabanları değildir. Bunlar SQL Server’ın kurulum sırasında ve yeni instance oluştururken kullandığı şablon (template) dosyalarıdır. Data klasöründeki sistem veritabanlarından farklıdır.
C:\Program Files\Microsoft SQL Server\<Instance>\MSSQL\Binn\Templates\ dizininde gördükleriniz: Yeni bir SQL Server instance’ı oluştururken, Setup sırasında sistem veritabanları bu template dosyalarından kopyalanarak oluşturulur. master veya model bozulursa “rebuild” işleminde, setup.exe /ACTION=REBUILDDATABASE çalıştırıldığında SQL Server, bu template dosyalarını kullanarak master, model ve msdb’yi yeniden oluşturur. Template dosyaları tamamen boştur.
SQL servisini kapatıp Templates klasöründeki dosyaları gerçek system database dosyalarıyla (master, model, msdb) değiştirirsen SQL Server büyük ihtimalle AÇILMAZ.
Bu template dosyaları sadece:
setup.exe /ACTION=REBUILDDATABASE
SQL kurulumu sırasında ilk oluşturma için kullanılır. Başka bir kullanım amacı yoktur.
Template dosyaları tamamen BOŞTUR Yani master.mdf içindeki: loginler, instance bilgileri,server configuration,collation, path bilgileri, endpointler hepsi yoktur.
SQL Server bu dosyalarla açılmaya çalıştığında: instance bilgilerini bulamaz, logins/metadata bulunamaz bağlı MSDB bilgileri tutmaz. collation uyumsuz olabilir SQL Server service start edemez.
Template dosyaları, senin gerçek instance’ın collation’ı veya file pointer bilgileriyle uyumlu değildir
Örneğin:
Senin instance SQL_Latin1_General_CP1_CI_AS kullanıyor olabilir.
Template dosyası ise Latin1_General_CI_AI olabilir.
Bu durumda SQL Server “Cannot recover master database” hatası verir. SQL dosya yapı versiyonları da farklı olabilir Template dosyaları SQL Server’ın kurulum anındaki versiyonuna aittir. Senin instance SP kurulmuş, CU güncellemesi almış olabilir. Template dosyaları eski versiyonsa SQL Server açılmaz.
setup.exe /ACTION=REBUILDDATABASE
Bu komut:
1. SQL servisini durdurur
2. Template dosyalarını alır
3. master, model, msdb’yi tekrar oluşturur
4. Doğru collation, doğru path, doğru metadata ile yeniden inşa eder
5. SQL Server’ı çalıştırır
Yani template dosyalarını ham olarak kopyalamaz, onları kullanarak doğru şekilde yeni sistem DB’leri yaratır.
Bu makalede System database’lerini görmüş olduk. Başka bir makalede görüşmek dileğiyle..
Onlar – ” Söz Verdiklerinde Sözünde Dururlar.” Bakara / 177
