MSSQL Server'da En çok Memory Tüketen Sorgular

SQL Server’da performans sorunlarının en büyük iki kaynağından biri CPU, diğeri ise Bellek (Memory) kullanımıdır. Bir sorgu çalıştırıldığında, SQL Server bu sorgunun düzgün işlemesi (sıralama – sorting, birleştirme – joining gibi işlemler) için belirli bir miktar bellek rezervasyonu yapar. Buna “Memory Grant” denir.

Sql server üzerinde en çok memory kullanan sorguları göstermektedir.

SELECT
    mg.session_id
    ,t.text AS [sql]
    ,qp.query_plan AS [plan]
    ,mg.is_small  /* Resource Semaphore Queue information */
    ,mg.dop
    ,mg.query_cost
    ,mg.request_time
    ,mg.grant_time
    ,mg.wait_time_ms
    ,mg.required_memory_kb
    ,mg.requested_memory_kb
    ,mg.granted_memory_kb
    ,mg.used_memory_kb
    ,mg.max_used_memory_kb
    ,mg.ideal_memory_kb
FROM
    sys.dm_exec_query_memory_grants mg WITH (NOLOCK)
        CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
        CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
--WHERE -- Uncomment to see only pending memory grants
--    mg.grant_time IS NULL
ORDER BY
    mg.granted_memory_kb DESC
OPTION (RECOMPILE, MAXDOP 1);

Aşağıdaki resimde görüldüğü gibi sorgumuz ve aynı zamanda execution plan yapısı görülmektedir.

granted_memory_kb SQL Server’ın bu sorguya fiilen ayırdığı RAM miktarı.
requested_memory_kb Sorgunun çalışmak için talep ettiği ideal miktar.
used_memory_kb Tahsis edilen belleğin ne kadarının o an aktif kullanıldığı. (Fark büyükse sorgu verimsizdir).
query_cost Sorgunun karmaşıklık puanı. Genelde maliyet arttıkça bellek ihtiyacı artır.
wait_time_ms Sorgu, bellek alabilmek için ne kadar süredir sırada bekliyor?
is_small Sorgunun küçük bir bellek havuzunda çalışıp çalışmadığını belirtir.
dop: Sorgu için maxdop parametresini göstermektedir.

Yukarıda herhangi bir kolonun

required_memory_kb (66.624 KB): Sorgunun çalışmaya başlayabilmesi için ihtiyaç duyduğu minimum bellek miktarıdır. Bu miktar yaklaşık 65 MB’dır. Bu miktar olmadan sorgu asla çalışmaya başlamaz.
requested_memory_kb (18.480.064 KB): SQL Server’ın istatistiklere ve sorgu planına bakarak “bu sorgu en performanslı şekilde yaklaşık 17.6 GB RAM ile çalışır” dediği talep edilen miktardır.
granted_memory_kb (18.480.064 KB): İşte krizin çıktığı nokta burası. SQL Server, talep edilen 17.6 GB RAM’i bu sorguya fiilen tahsis etmiş (grant). Şu an bu bellek bu sorgu için rezerve edildi.
used_memory_kb (664.664 KB): Sorgunun o anki gerçek bellek kullanımı. Sadece 649 MB kullanıyor.
max_used_memory_kb (664.664 KB): Sorgunun ömrü boyunca şu ana kadar ulaştığı en yüksek bellek noktası. Yani hala 1 GB bile değil.
ideal_memory_kb (18.480.064 KB): Tüm verileri belleğe sığdırmak için gereken (hashing/sorting için) en ideal miktar tahmini.

Burada bir İstatistik (Statistics) Yanılgısı veya Kötü Sorgu Planı söz konusu.

Tahmin Hatası: SQL Server, tablodaki verilere (istatistiklere) bakarak milyarlarca satırı sıralayacağını veya birleştireceğini sanmış. Bu yüzden sorguya “Sana 17.6 GB yer ayırdım, rahat rahat çalış” demiş.
Büyük İsraf: Sorgu gerçekte sadece ~650 MB veri işliyor. Yani sistemden 17.6 GB RAM çalmış ama bunun sadece %4’ünü kullanıyor. Geri kalan yaklaşık 17 GB RAM tamamen boşta yatıyor ancak başka hiçbir sorgu bu boş alanı kullanamıyor.
Zincirleme Etki: Eğer sunucunda 32 GB RAM varsa, bu sorgudan sadece 2 tane aynı anda çalışsa, sunucudaki tüm bellek tükenir. 3. bir sorgu gelirse, o sorgu küçücük bir işlem yapacak olsa bile RAM bulamadığı için RESOURCE_SEMAPHORE beklemesine girer ve sistem kilitlenmiş gibi görünür.

Çözüm Önerileri

İstatistik Güncelleme: UPDATE STATISTICS TabloAdi komutuyla ilgili tabloların verilerini güncelleyin. Tahminler düzelirse requested_memory_kb dramatik şekilde düşecektir.
MAX_GRANT_PERCENT: Sorguya özel bir HINT vererek bellek tahsisini %10 gibi bir oranla sınırlayabilirsiniz.
Eksik İndeksler: Genellikle bu kadar yüksek bellek talebi, büyük tabloların RAM üzerinde “Sort” (Sıralama) veya “Hash Match” (Eşleştirme) yapmasından kaynaklanır. Uygun bir indeks bu işlemin RAM ihtiyacını sıfıra indirebilir.

Eğer sunucuda sorgular çalışmaya başlamıyor ve bekleme tipi (wait type) olarak RESOURCE_SEMAPHORE görünüyorsa, bu sorgu hayat kurtarır. Bu durum, bir veya birkaç sorgunun RAM’in büyük kısmını rezerve ettiğini ve diğerlerine yer kalmadığını gösterir.

Bir sorguda ORDER BY veya DISTINCT kullanıldığında, SQL Server verileri bellekte sıralar. Eğer indeksleme eksikse, SQL Server devasa miktarda bellek talep eder. Bu sorgu ile bu verimsiz planları yakalayabilirsiniz.

Bazen SQL Server, bir sorgunun 10 satır döndüreceğini tahmin ederken aslında 1 milyon satır döner. Ya da tam tersi olur ve gereksiz yere GB’larca RAM talep eder. ideal_memory_kb ile granted_memory_kb arasındaki uçurumlar, istatistiklerin güncellenmesi gerektiğine işarettir.

Yukarıdaki kodun içindeki şu satırın yorumunu kaldırırsan:

WHERE mg.grant_time IS NULL

Henüz bellek alamadığı için park edilmiş (suspended) olan sorguları görürsün. Eğer burada çok fazla satır varsa, sunucunuzda ciddi bir bellek yetersizliği veya bir “sorgu darboğazı” var demektir.

Bu sorgu, anlık bellek tüketimini izlemek ve sistemi yavaşlatan “obur” sorguları teşhis etmek için mükemmeldir. Bellek tahsislerini düzenli izlemek, sunucunun disk (paging) yerine RAM üzerinde yüksek performansla çalışmasını sağlar.

Yukarıda bulunan sorgunun benzeri:

SELECT s.session_id, s.login_name, mg.requested_memory_kb, (mg.requested_memory_kb/1024) MB_Request,
mg.granted_memory_kb,(mg.granted_memory_kb/1024) MB_Granted,
* 
FROM sys.dm_exec_query_memory_grants mg, 
	 sys.dm_exec_sessions s
where s.session_id = mg.session_id
order by s.login_name

Yukarıdaki sorgunun database name ve database id değeri öğrenilmek istenirse aşağıdaki kod çalıştırılabilir.

SELECT
    DB_NAME(r.database_id) AS [database_name]
    ,r.database_id AS [database_id]
    ,mg.session_id
    ,t.text AS [sql]
    ,qp.query_plan AS [plan]
    ,mg.is_small  /* Resource Semaphore Queue information */
    ,mg.dop
    ,mg.query_cost
    ,mg.request_time
    ,mg.grant_time
    ,mg.wait_time_ms
    ,mg.required_memory_kb
    ,mg.requested_memory_kb
    ,mg.granted_memory_kb
    ,mg.used_memory_kb
    ,mg.max_used_memory_kb
    ,mg.ideal_memory_kb
FROM
    sys.dm_exec_query_memory_grants mg WITH (NOLOCK)
        LEFT JOIN sys.dm_exec_requests r WITH (NOLOCK) 
            ON mg.session_id = r.session_id
        CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
        CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
--WHERE -- Uncomment to see only pending memory grants
--    mg.grant_time IS NULL
ORDER BY
    mg.granted_memory_kb DESC
OPTION (RECOMPILE, MAXDOP 1);

Aşağıdaki komut yardımıyla memory boyutu 250 gb olan sorguları kill edilmektedir.

USE DBAMON
GO

CREATE OR ALTER PROCEDURE dbo.sp_KillAndLogHighMemoryQueries
AS
BEGIN
    SET NOCOUNT ON;

    -- 250 GB'ın KB cinsinden karşılığı: 250 * 1024 * 1024 = 262,144,000 KB
    DECLARE @MemoryThresholdKB BIGINT = 262144000; 

    -- Geçici tablo
    IF OBJECT_ID('tempdb..#QueriesToKill') IS NOT NULL 
        DROP TABLE #QueriesToKill;

    CREATE TABLE #QueriesToKill
    (
        SessionID INT,
        RequestedMemoryKB BIGINT,
        GrantedMemoryKB BIGINT,
        LoginName NVARCHAR(256),
        HostName NVARCHAR(256),
        ProgramName NVARCHAR(256),
        DatabaseName NVARCHAR(128),
        QueryText NVARCHAR(MAX)
    );

    -- 1. Yalnızca temel verileri hızlıca çek (Plan okuma yok, kilitlenme riski yok)
    INSERT INTO #QueriesToKill
    (
        SessionID,
        RequestedMemoryKB,
        GrantedMemoryKB,
        LoginName,
        HostName,
        ProgramName,
        DatabaseName,
        QueryText
    )
    SELECT 
        mg.session_id,
        CAST(mg.requested_memory_kb AS BIGINT),
        CAST(ISNULL(mg.granted_memory_kb, 0) AS BIGINT),
        s.login_name,
        s.host_name,
        s.program_name,
        DB_NAME(s.database_id) AS DatabaseName,
        ISNULL(st.text, 'Query Text Alinamadi') AS QueryText
    FROM sys.dm_exec_query_memory_grants mg WITH (NOLOCK)
    INNER JOIN sys.dm_exec_sessions s WITH (NOLOCK) ON mg.session_id = s.session_id
    OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) st
    WHERE (mg.requested_memory_kb >= @MemoryThresholdKB OR mg.granted_memory_kb >= @MemoryThresholdKB)
      AND mg.session_id <> @@SPID;

    -- 2. Eğer yakalanan session varsa tabloya kaydet ve KILL et
    IF EXISTS (SELECT 1 FROM #QueriesToKill)
    BEGIN
        -- Tabloya Ekleme İşlemi
        INSERT INTO DBAMON.dbo.KilledMemoryQueriesLog
        (
            KilledDate,
            SessionID,
            LoginName,
            HostName,
            ProgramName,
            DatabaseName,
            RequestedMemoryMB,
            GrantedMemoryMB,
            QueryText
        )
        SELECT 
            SYSDATETIME(),
            SessionID,
            LoginName,
            HostName,
            ProgramName,
            DatabaseName,
            RequestedMemoryKB / 1024, -- KB -> MB
            GrantedMemoryKB / 1024,   -- KB -> MB
            QueryText
        FROM #QueriesToKill;

        -- Döngü ile Hızlıca KILL Et
        DECLARE @CurrSessionID INT;
        DECLARE @SqlKill NVARCHAR(100);

        DECLARE kill_cursor CURSOR LOCAL FAST_FORWARD FOR 
        SELECT SessionID FROM #QueriesToKill;

        OPEN kill_cursor;
        FETCH NEXT FROM kill_cursor INTO @CurrSessionID;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                SET @SqlKill = CONCAT('KILL ', @CurrSessionID);
                EXEC sp_executesql @SqlKill;
            END TRY
            BEGIN CATCH
                -- Session o esnada kapandıysa hata vermesini engelle
            END CATCH;

            FETCH NEXT FROM kill_cursor INTO @CurrSessionID;
        END;

        CLOSE kill_cursor;
        DEALLOCATE kill_cursor;
    END

    DROP TABLE #QueriesToKill;
END;
GO

Kaydedilen tablo yapımız:

CREATE TABLE [dbo].[KilledMemoryQueriesLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[KilledDate] [datetime2](3) NOT NULL,
	[SessionID] [int] NOT NULL,
	[LoginName] [nvarchar](256) NULL,
	[HostName] [nvarchar](256) NULL,
	[ProgramName] [nvarchar](256) NULL,
	[DatabaseName] [nvarchar](128) NULL,
	[RequestedMemoryMB] [bigint] NOT NULL,
	[GrantedMemoryMB] [bigint] NOT NULL,
	[QueryText] [nvarchar](max) NULL,
	[QueryPlanXML] [xml] NULL,
 CONSTRAINT [PK_KilledMemoryQueriesLog] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[KilledMemoryQueriesLog] ADD  CONSTRAINT [DF_KilledMemoryQueriesLog_KilledDate]  DEFAULT (sysdatetime()) FOR [KilledDate]
GO

Aşağıdaki yapı ile sunucu üzerine gelen memory değerlerini bir tabloya kaydede bilirsiniz.

CREATE TABLE [dbo].[QueryMemoryGrantsLog](
	[CreatedDate] [datetime] NOT NULL,
	[database_name] [nvarchar](128) NULL,
	[database_id] [smallint] NULL,
	[session_id] [int] NULL,
	[sql] [nvarchar](max) NULL,
	[plan] [xml] NULL,
	[is_small] [bit] NULL,
	[dop] [smallint] NULL,
	[query_cost] [float] NULL,
	[request_time] [datetime] NULL,
	[grant_time] [datetime] NULL,
	[wait_time_ms] [bigint] NULL,
	[required_memory_kb] [bigint] NULL,
	[requested_memory_kb] [bigint] NULL,
	[granted_memory_kb] [bigint] NULL,
	[used_memory_kb] [bigint] NULL,
	[max_used_memory_kb] [bigint] NULL,
	[ideal_memory_kb] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[QueryMemoryGrantsLog] ADD  DEFAULT (getdate()) FOR [CreatedDate]
GO

-----------------------------------
INSERT INTO QueryMemoryGrantsLog (
    CreatedDate,
    database_name,
    database_id,
    session_id,
    [sql],
    [plan],
    is_small,
    dop,
    query_cost,
    request_time,
    grant_time,
    wait_time_ms,
    required_memory_kb,
    requested_memory_kb,
    granted_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    ideal_memory_kb
)
SELECT
    GETDATE() AS CreatedDate,
    DB_NAME(r.database_id) AS [database_name],
    r.database_id AS [database_id],
    mg.session_id,
    t.text AS [sql],
    qp.query_plan AS [plan],
    mg.is_small,
    mg.dop,
    mg.query_cost,
    mg.request_time,
    mg.grant_time,
    mg.wait_time_ms,
    mg.required_memory_kb,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.ideal_memory_kb
FROM
    sys.dm_exec_query_memory_grants mg WITH (NOLOCK)
        LEFT JOIN sys.dm_exec_requests r WITH (NOLOCK) 
            ON mg.session_id = r.session_id
        CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
        CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
WHERE mg.dop = 2
ORDER BY mg.granted_memory_kb DESC
OPTION (RECOMPILE, MAXDOP 1);
---------------------
delete from  QueryMemoryGrantsLog where 
CreatedDate< DATEDIFF(DAY,7,GETDATE())

Başka makalede görüşmek dileğiyle..

Kötülüğe iyilikle karşılık verin.Fussilet-34

MSSQL Server’da En çok Memory Tüketen Sorgular

Bir yanıt yazın