Categories:

Tüm veritabanı sistemlerinde olduğu gibi SQL Server’da da memory 2 farklı amaç  için kullanılır:

  1. 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
  2. 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.

Tags:

No responses yet

Bir cevap yazın

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