Bu yazımızda DMV ve DMF Serimizin 6.sına devam edeceğiz. Bir önceki seride Memory’ye ilişkin DMV ve DMF’leri ele almıştık. Bu yazıda Memory konusunda devam edeceğiz. SQL server’da Memory kavramı en önemli kavramlardan biridir. Özellikle tüm transaction işlemlerinin önce Buffer sonra disk üzerinden devam ettiğini düşünürsek buffer’ın oynadığı kritik rolü daha iyi anlayabiliriz.
Bu yazıda Memory’nin durumunu ve monitör edilmesine bakacağız. Özellikle Performans sorunlarında memory konusunda sorun yaşandığı durumda nasıl okumak gerektiği önemli rol oynamaktadır.
Hangi database’de, hangi tablo’da sorun yaşandığına ilişkin bilgilere bu paylaşım sonrasında görebileceğiz.
SQL Server’ın Memory kullanım durumunu incelediğimde;
select physical_memory_in_use_kb/1048576.0 AS 'physical_memory_in_use (GB)', locked_page_allocations_kb/1048576.0 AS 'locked_page_allocations (GB)', virtual_address_space_committed_kb/1048576.0 AS 'virtual_address_space_committed (GB)', available_commit_limit_kb/1048576.0 AS 'available_commit_limit (GB)', page_fault_count as 'page_fault_count' from sys.dm_os_process_memory;
Görsel – 1
Physical_memory_in_use: Kullanımda olan Fiziksel Memory miktarını gösterir.
locked_page_allocations: Memory’de lock’lanmış olan Page’lerin miktarını belirtir.
virtual_address_space_contained: SQL Server VAS(Virtual Adress Space) için ayrılan miktarı belirtir.
available_commit_limit: SQL Server tarafından kullanılabilecek Memory Miktarını gösterir.
page_fault_count: Memory yerine Disk üzerinden getirilen Page’lerin sayısını gösterir. Bu oranın yüksek olması sistemde sorunlar sebebiyet verebilecektir.
Database Bazlı buffer pool kullanımına bakmak istersek;
DECLARE @total_buffer INT; SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Database Pages'; ;WITH DBBuffer AS ( SELECT database_id, COUNT_BIG(*) AS db_buffer_pages, SUM (CAST ([free_space_in_bytes] AS BIGINT)) / (1024 * 1024) AS [MBEmpty] FROM sys.dm_os_buffer_descriptors GROUP BY database_id ) SELECT CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS 'db_name', db_buffer_pages AS 'db_buffer_pages', db_buffer_pages / 128 AS 'db_buffer_Used_MB', [mbempty] AS 'db_buffer_Free_MB', CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer) AS 'db_buffer_percent' FROM DBBuffer ORDER BY db_buffer_Used_MB DESC;
Görsel – 2
db_name: Veritabanı adı.
db_buffer_pages: Veritabanındaki ilgili veritabanın Buffer Pool’da barındırdığı page sayısı.
db_buffer_Used_MB: MB cinsinden kullanılan veritabanı bazlı Buffer Pool boyutu
db_buffer_Free_MB: MB cinsinden veritabanı açısından Boş Buffer Pool Boyutu (tüm pagelerdeki boş alanın toplamı).
db_buffer_percent: Buffer Pool’un kullanımının veritabanı bazlı yüzdesi
Object Bazlı Buffer Pool Kullanımı;
SELECT fg.name AS [Filegroup Name], SCHEMA_NAME(o.Schema_ID) AS [Schema Name], OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id, CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)], COUNT(*) AS [BufferCount], p.[Rows] AS [Row Count], p.data_compression_desc AS [Compression Type] FROM sys.allocation_units AS a WITH (NOLOCK) INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_id INNER JOIN sys.objects AS o WITH (NOLOCK) ON p.object_id = o.object_id INNER JOIN sys.database_files AS f WITH (NOLOCK) ON b.file_id = f.file_id INNER JOIN sys.filegroups AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id WHERE b.database_id = CONVERT(int, DB_ID()) AND p.[object_id] > 100 AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%' AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%' AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%' GROUP BY fg.name, o.Schema_ID, p.[object_id], p.index_id, p.data_compression_desc, p.[Rows] ORDER BY [BufferCount] DESC OPTION (RECOMPILE);
Görsel – 3
0 Yorum