SQL Server – Monitoring Chek Listesi

Veritabanı sistemimizin an ve an monitoringi çok önemlidir. Hatta bu monitoring süreçlerini otomatik yapıya getirmemiz gerekmektedir.

Bu makalemizde en basit şekliyle ama en önemli ve atlanan ve önemsenmeyen fakat bizim için hayati önem taşıyan noktaları monitör etmenizi ve nasıl monitör edeceğinize değineceğim. ,

   1. SQL Server Servislerini Aktif Durumda mı ? Yani Çalışıyor mu ?

Sunucular üzerinde koşan SQL Server Servislerimiz aktif durumda mı bunuan ve an monitör etmemiz gerekmekte. Hatta ve hatta bi sıkıntı olduğunda bizi anında haber verecek sistemler kurmamız gerekmektedir. (Kritik ve durmaması gereken sistemlerimiz için)

exec master.dbo.xp_servicecontrol sistem sp_ si ile servis durumlarını kontrol edebiliriz. (MSSQLServer,SQLServerAgent,SQLBrowser servisleri gibi…)

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'

 2. JOb Monitoring

SQL Server Agent ı üzerinde yer alan Job ların durumlarını gözlemlememiz gerekmektedir. Önemli Job larımıza Alert eklememiz gerekmektedir. Örneğin aşağıda ki script imiz ile periyodik çıkan sonucu mail attırarak Fail olan Job larımızı monitör edebiliriz.

USE msdb
GO
SELECT
	'FAILED' AS Status
   ,CAST(sj.name AS VARCHAR(100)) AS "Job Name"
   ,CAST(sjs.step_id AS VARCHAR(5)) AS "Step ID"
   ,CAST(sjs.step_name AS VARCHAR(30)) AS "Step Name"
   ,CAST(REPLACE(CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date)), 102), '.', '-') + ' ' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 5, 2) AS VARCHAR(30)) 'Start Date Time'
   ,sjh.message AS "Message"
FROM sysjobs sj
JOIN sysjobsteps sjs
	ON sj.job_id = sjs.job_id
JOIN sysjobhistory sjh
	ON sj.job_id = sjh.job_id
		AND sjs.step_id = sjh.step_id
WHERE sjh.run_status <> 1
AND CAST(sjh.run_date AS FLOAT) * 1000000 + sjh.run_time >
CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS FLOAT) * 1000000 + 70000 --yesterday at 7am
UNION
SELECT
	'FAILED'
   ,CAST(sj.name AS VARCHAR(100)) AS "Job Name"
   ,'MAIN' AS "Step ID"
   ,'MAIN' AS "Step Name"
   ,CAST(REPLACE(CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date)), 102), '.', '-') + ' ' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 5, 2) AS VARCHAR(30)) 'Start Date Time'
   ,sjh.message AS "Message"
FROM sysjobs sj
JOIN sysjobhistory sjh
	ON sj.job_id = sjh.job_id
WHERE sjh.run_status <> 1
AND sjh.step_id = 0
AND CAST(sjh.run_date AS FLOAT) * 1000000 + sjh.run_time >
CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS FLOAT) * 1000000 + 70000 --yesterday at 7am

3. Backup Monitoring

Veritabanlarımızın Backup kontrollerini yapmamız gerekmektedir. Backup veritabanları sistemlerimiz için Can Simididir DBA lerin. Bu yüzden mutlak ve mutlak her firmanın yapısına özel bir Backup lama planıolmalı ve aynı zamanda belirli aralıklarda Backup Sağlık Süreç Politikası hazırlanıp işletilmelidir. Yani alınan Backup ların Restore edilip DB bütünlük kontorlü yapılması gibi.

Aşağıda ki script ile günlük Backup kontrollerinizi sağlayabilirsiniz.

SELECT
	d.name AS "Database"
   ,ISNULL(CONVERT(VARCHAR, b.backupdate, 120), 'NEVER') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT
		database_name
	   ,type
	   ,MAX(backup_finish_date) backupdate
	FROM backupset
	WHERE type LIKE 'D'
	GROUP BY database_name
			,type) b
	ON d.name = b.database_name
WHERE (backupdate IS NULL
OR backupdate < GETDATE() - 1)
SELECT
	d.name AS "Database"
   ,ISNULL(CONVERT(VARCHAR, b.backupdate, 120), 'NEVER') AS "Last Log Backup"
FROM sys.databases d
LEFT JOIN (SELECT
		database_name
	   ,type
	   ,MAX(backup_finish_date) backupdate
	FROM backupset
	WHERE type LIKE 'L'
	GROUP BY database_name
			,type) b
	ON d.name = b.database_name
WHERE recovery_model = 1
AND (backupdate IS NULL
OR backupdate < GETDATE() - 1)

   4.Error Log Monitoring

En sık kontrol etmeniz gereken noktalardan biri de SQL Server Error Log kontrolleri dir. Error loglar kontrol edilerek alınan hatalar monitör edilip kritiklik seviyelerine göre önceliklendirilerek çözümlenmeleri sağlanmalıdır. Hatta ErrorLogları periyodik olarak tabloya arşivleyip recycle edin ki hem Loglarınız şişmesin hemde problem anında monitoring ederken kalabalık bir text bloğunun içinde boğulmayın. Problemin detayına hızlı ulaşın …

DECLARE @Time_Start DATETIME;
DECLARE @Time_End DATETIME;
SET @Time_Start = GETDATE() - 2;
SET @Time_End = GETDATE();
-- Create the temporary table
CREATE TABLE #sqlturkiyeErrorLog (
	logdate DATETIME
   ,processinfo VARCHAR(255)
   ,Message VARCHAR(500)
)
-- Populate the temporary table
INSERT #sqlturkiyeErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0
							   ,1
							   ,NULL
							   ,NULL
							   ,@Time_Start
							   ,@Time_End
							   ,N'desc';
-- Filter the temporary table
SELECT
	LogDate
   ,Message
FROM #sqlturkiyeErrorLog
WHERE (Message LIKE '{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}error{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}'
OR Message LIKE '{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}failed{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}')
AND processinfo NOT LIKE 'logon'
ORDER BY logdate DESC
-- Drop the temporary table 
DROP TABLE #sqlturkiyeErrorLog

 

 5.Disk Monitoring

Veritabanlarının koştuğu Disklerin performansı ve doluluk oranlarıda çok önemli. Sağlam bir monitoring sürecinde tutulup Capacity Planning yapılmalı. Bu yüzden disk Free Size değerlerini master.dbo.xp_fixeddrives sistem sp_ si ile öğrenebiliriz.

exec master.dbo.xp_fixeddrives

   6. Memory Control ve PLE

Veritabanlarının Performans açısından en önemli soruce larından biri olan Memory de an ve an monitör edip hatta Page Life değerlerini de anlık kontrol etmeli ve bu değerlere göre Performance tuning süreçlerimizi belirlemeliyiz.

NOT : Page Life Nedir ve Nasıl Monitör ederiz sorularınızı PLE (Page Life Expectancy) Nedir ve Ne İşe Yarar ?  isimli makalemde detaylı öğrenebilirsiniz.

Aşağıda ki scriptimiz ile Total Memorymizi görüp bu Memory nin {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9} kaçı Free yani kullanılmıyor sonucuna erişebiliyoruz.

SELECT available_physical_memory_kb/1024 as "Total Memory MB",
       available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9} Memory Free"
FROM sys.dm_os_sys_memory

   7. High Query List

Cache de yer alan uzun süren sorguları an ve an loglayıp Tuning işlemleri yaparsanız problemleriniz büyümeden önüne geçiyor olursunuz.

SELECT TOP 10
	text AS "SQL Statement"
   ,last_execution_time AS "Last Execution Time"
   ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
   ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
   ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
   ,execution_count AS "Execution Count"
   ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

   8.Batch Request/sec

SQL Server Instance ımız üzerine gelen istek/işlem sayısını da bilmelisiniz ki belirli limitlere dayandığında response time sürelerinizin durumu ne I/O değerleriniz, CPU kullanımı, Memory durumu vb. süreçleri o ana göre yorumlayıp doğru karar vermenizi sağlayacaktır.

DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value 
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}'
WAITFOR DELAY '000:00:10'
SELECT ([email protected])/10.0 AS "Batch Requests/sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec{6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9}'

 

Kolay Gelsin…

Leave a Reply

Your email address will not be published. Required fields are marked *