MSSQL Server Query Store Alert Mekanizması

Bu makalede Query Store üzerinde Operation Mode Read only yapısına geçen veritabanlarını mail olarak bizlere iletmektedir. Öncelikle bu yapıya geçince ne olur ona değinelim.

Aşağıdaki resimde Current Disk Usage kısmında sol kısımda bulunan Mavi daire bizim database’in boyutunu sağ taraftaki daire ise Query Store boyutunu göstermektedir. Sağ alt tarafta bulunan Purge Query Data kısmında ise Query Store yapısında  bulunan tüm verileri silebiliriz.

1. Salt Okunur (Read-Only) Moda Geçiş

Query Store için ayrılan alan (MAX_STORAGE_SIZE_MB) dolduğunda, Query Store otomatik olarak READ_ONLY moduna geçer. Bu durumda:

•  Yeni sorgular yakalanmaz.
•  Mevcut sorgular için yeni çalışma istatistikleri (runtime stats) kaydedilmez.
•  Ancak mevcut veriler üzerinden rapor alabilir ve daha önce atanmış “Force Plan” (Plan Sabitleme) özelliklerini kullanmaya devam edebilirsiniz.

2. Veri Temizleme (Cleanup Policy)

Eğer CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS) aktifse, SQL Server arka planda eski veya düşük öncelikli verileri temizlemeye çalışır.

•  Temizleme işlemi başarılı olur ve alan açılırsa, Query Store tekrar READ_WRITE moduna döner.
•  Eğer temizleme yeterli alan açamazsa, manuel müdahale gerekene kadar salt okunur kalmaya devam eder.

3. “Üzerine Yazma” Durumu

SQL Server verilerin üzerine dairesel bir şekilde (circular) yazmaz. Ancak SIZE_BASED_CLEANUP_MODE ayarı AUTO ise, doluluk oranına yaklaşıldığında sistem otomatik olarak eski verileri silerek yer açmaya çalışır. Bu ayar kapalıysa (OFF), alan dolduğunda doğrudan salt okunur moda geçer.

Read Only moda düşen bir query store yapısın Max Size değerini artırdığımızda Query store read only moddan Read-Write moduna düşmektedir.

Veritabanı altında aşağıdaki store procedure yapısı oluşmaktadır.

USE DBAMON
GO
CREATE PROCEDURE QueryStoreKontrol
AS
DECLARE @DBName NVARCHAR(256);
DECLARE @ReadOnlyDBs NVARCHAR(MAX) = '';
BEGIN

DECLARE db_cursor CURSOR FOR 
SELECT d.name 
FROM sys.databases d
-- Availability Group rolünü kontrol etmek için katılım yapıyoruz
LEFT JOIN sys.dm_hadr_availability_replica_states rs ON EXISTS (
    SELECT 1 FROM sys.dm_hadr_database_replica_states drs 
    WHERE drs.database_id = d.database_id 
    AND drs.replica_id = rs.replica_id
) AND rs.is_local = 1
WHERE d.state_desc = 'ONLINE' 
  AND d.database_id > 4
  -- Sadece PRIMARY roldeki veritabanlarını VEYA AG üyesi olmayan (Stand-alone) veritabanlarını seç
  AND (rs.role_desc = 'PRIMARY' OR rs.role_desc IS NULL);

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @Status INT = 0;

    -- Query Store durumunu kontrol et
    SET @SQL = N'SELECT @StatusOut = COUNT(*) 
                 FROM [' + @DBName + '].sys.database_query_store_options 
                 WHERE actual_state_desc = ''READ_ONLY'' 
                 AND readonly_reason <> 0';

    EXEC sp_executesql @SQL, N'@StatusOut INT OUTPUT', @StatusOut = @Status OUTPUT;

    IF @Status > 0
    BEGIN
        SET @ReadOnlyDBs = @ReadOnlyDBs + @DBName + ', ';
    END

    FETCH NEXT FROM db_cursor INTO @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- Eğer sorunlu veritabanı bulunduysa mail gönder
IF LEN(@ReadOnlyDBs) > 0
BEGIN
    SET @ReadOnlyDBs = LEFT(@ReadOnlyDBs, LEN(@ReadOnlyDBs) - 1);
    DECLARE @ErrorMessage NVARCHAR(MAX) = '<b>UYARI:</b> Aşağıdaki veritabanlarında Query Store doldu ve READ_ONLY moda geçti: <br><br><b>' + @ReadOnlyDBs + '</b>';
    DECLARE @Server varchar(100)='Query Store Limit Aşımı - '+' ' +@@SERVERNAME

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SqlMailProfile', 
        @recipients = 'veritabaniyonetimi@jandarma.gov.tr', 
        @subject = @Server ,
        @body = @ErrorMessage,
        @body_format = 'HTML';
 END
END

İlgili procedure yapısı oluşturulduktan sonra aşağıdaki job yardımıyla belirli aralıklarla çalıştırılır.

Always on yapılarında secondary sunucuları üzerinde bulunan veritabanlarında okuma modunda olduğu için otomatik olarak query store özelliği açık olan tüm veritabanları dönmektedir. Bu sebepten yukarıdaki sorgumuzda AG kontrol şartı vardır.

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

“Kim zerre kadar iyilik yapmışsa onu görür.”Zilzal Suresi; 7. Ayet