Tüm veritabanı sistemlerinde olduğu gibi SQL Server’da da memory 2 farklı amaç için kullanılır:
- Kendi iç sistemleri (plan cache, bazı denetim DLL dosyaları ve çalışan kodlar, procedure cache vs) için ayrılan diğer kısma göre küçük kalacak bir alan
- Diskten okuma yazma işlemlerini azaltmak amaçlı kullanılan ve SQL için ayrılan memory’nin büyük kısmını oluşturan “Buffer Cache”
Teknolojiye paralel olarak disk erişim hızları ne kadar artmış olursa olsun disk erişimleri memory erişimlerine kıyasla her zaman çok ama çok yavaş kalacaktır. Bunun nedeni de tabiki disk erişimlerinin mekanik aksamlardan sağlanıyor olmasıdır. Yani bir disk kafası fiziksel olarak ilgili alana yönelmeli ve buradan okumayı gerçekleştirmesi gerekir. Aksine memory okumaları ise tamamen elektronik içeriklidir. Kısaca bu pragrafın özeti: diskler yavaş memory hızlıdır.
Bu nedenle de veritabanı sistemleri okuyabildikleri kadar veriyi önceden Cache üzerine yüklerler. Böylece SQL Server’ın ilk yapacağı işlem talep edilen verinin Cache’de olup olmadığını kontrol edilmesidir. Eğer var ise (cache Hit) disk erişimine gerek kalmaz.
Büyük veriye sahip olan sistemlerde Cache, veritabanına kıyasla ufak kalacağı için cache üzerindeki veri yaşlandırılır ve belirli bir süre burada kalıp zamanını dolduran kısımlar yeni okumalara yer açmak amaçlı olarak diske yazılır.
Şimdi biraz scriptler ile SQL Server’ın kullandığı memory konusunda bilgi alalım :
SELECT
physical_memory_kb,
virtual_memory_kb,
committed_kb,
committed_target_kb
FROM sys.dm_os_sys_info;
Buradaki sonuçlarn manasını kısaca açıklamak gerekir ise:
- Physical_memory_kb: sunucu üzerindeki RAM miktarı
- Virtual_memory_kb: sunucu üzerindeki tanımlı sanal memory miktarı
- Committed_kb: veritabanının Buffer Cache için kullandığı miktar
- Committed_target_memory_kb: Buffer Cache’in kullanmak istediği alanı gösterir. Bu değer eğer committed kısmından ufak ise verinin diske yazılmasının zamanı gelmiş demektir. Eğer büyük ise daha fazla memory kullanılmak üzere talep edilecek manasına gelir.
Burada veritabanınızı performans olarak en iyi etkiyi sağlayacak olan parameter, Buffer cache için ayrılan alanın yeterli olup olmadığıdır. Ek olarak aşağıdaki sorgu ile de buffer cache için ne kadar alan ayrıldığını tam olarak tespit edebilirsiniz :
SELECT
COUNT(*) AS buffer_cache_pages,
COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors;
Peki bu alanı en çok hangi tablo kullanıyor :
SELECT
indexes.name AS index_name,
objects.name AS object_name,
objects.type_desc AS object_type_description,
COUNT(*) AS buffer_cache_pages,
COUNT(*) * 8 / 1024 AS buffer_cache_used_MB
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units
ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions
ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))
OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))
INNER JOIN sys.objects
ON partitions.object_id = objects.object_id
INNER JOIN sys.indexes
ON objects.object_id = indexes.object_id
AND partitions.index_id = indexes.index_id
WHERE allocation_units.type IN (1,2,3)
AND objects.is_ms_shipped = 0
AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY indexes.name,
objects.name,
objects.type_desc
ORDER BY COUNT(*) DESC;
Peki, veri disk üzerinden cache’e okunuyor ve burada saklanıyor. Belki daha önce yazmayı unuttuk ama malum 8 KB olacak şekilde formatlanmış bir disk alanımız var. aynı nedenle cache üzerindeki minimum blok büyüklüğü de 8KB. Tabii bunun ne kadarının gerçekten dolu olduğu data büyüklüğünüze bağlı. Yani bir satırı 5KB büyüklüğünde bir tablo tanımlamış iseniz çok kaba bir tarif ile her blokta 3KB boş alanınız var demektir.
Peki cache üzerindeki kayıtların ne kadar gerçekten dolu ve ne kadarı boş:
SELECT
objects.name AS object_name,
objects.type_desc AS object_type_description,
COUNT(*) AS buffer_cache_pages,
CAST(COUNT(*) * 8 AS DECIMAL) / 1024 AS buffer_cache_total_MB,
CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / 1024 / 1024 AS buffer_cache_free_space_in_MB,
CAST((CAST(SUM(CAST(dm_os_buffer_descriptors.free_space_in_bytes AS BIGINT)) AS DECIMAL) / 1024 / 1024) / (CAST(COUNT(*) * 8 AS DECIMAL) / 1024) * 100 AS DECIMAL(5,2)) AS buffer_cache_percent_free_space
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.allocation_units
ON allocation_units.allocation_unit_id = dm_os_buffer_descriptors.allocation_unit_id
INNER JOIN sys.partitions
ON ((allocation_units.container_id = partitions.hobt_id AND type IN (1,3))
OR (allocation_units.container_id = partitions.partition_id AND type IN (2)))
INNER JOIN sys.objects
ON partitions.object_id = objects.object_id
WHERE allocation_units.type IN (1,2,3)
AND objects.is_ms_shipped = 0
AND dm_os_buffer_descriptors.database_id = DB_ID()
GROUP BY objects.name,
objects.type_desc,
objects.object_id
HAVING COUNT(*) > 0
ORDER BY COUNT(*) DESC;
Bu ne işimize yarar derseniz, aslında çok bir işinize yaramaz ama en azından tablolarınızdaki boş alanları, blob alanları ve fragmantasyonu düşünmeye başlamanıza yardımcı olur.
DBCC DROPCLEANBUFFERS
Cache üzerinde bir rahatsızlık hissettiniz bu komutu çalıştırabilirsiniz. SQL tarafından bakarsanız çok istenen bir durum değil zira cache üzerindeki tüm clean ya da dirty alanları diske yazarak cache’I boşaltacaktır. Dolayısı ile de ilk zamanlarda tüm okumalar disk üzerinden yapılacağı için bir süre yavaşlama olabilir.
No responses yet