SQL Server DMV ve DMF Kavramları ve Kullanımları -4
Merhaba Arkadaşlar,
Bu yazımda sizlere SQL Server da DMV ve DMF kavramlarını anlatmaya devam edeceğim. Önceki yazılarımda da belirttiğim gibi SQL Server da DMV ve DMF ler kullanılarak sistemin genel durumunu ve performans problemi gibi sıkıntıların olup olmadığını sorgulayabilirsiniz.
SQL Server instancesı üzerinde çok sayıda veritabanınızın bulunduğu bir durumda her bir Veritabanının sistem CPU sunu kullanma yüzdesini aşağıdaki scriptle bulabilirsiniz.
Bu script özellikle çok sayıda veritabanının bulunduğu bir instance üzerinde performans problemi olup olmadığını sorgulamak istediğiniz durumda hangi veritabanının ne kadar CPU tükettiğini görebilirseniz eğer o veritabanının normal durumlardan fazla CPU tüketip tüketmediğini anlayabilirsiniz.
Ayrıca bu scriptle sürekli CPU yüzdelerini görmeniz ve bunları kaydetmeniz durumunda Sunucunun CPU kaynaklarının yeterli olup olmadığını belirleyebilirsiniz.
WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);
Yukardaki scriptin çıktısı aşağıdaki gibidir.
SQL Server instance sı üzerinde çalışan veritabanlarının hangileri olduğunu ve bu veritabanlarının compatibility,collation,recovery_model vb gibi bilgileri görmek istiyorsanız aşağıdaki scripti kullanabilirsiniz.
select
serverproperty('MachineName') 'Machine_name'
,d.name 'database_name'
,d.compatibility_level
,d.collation_name
,d.recovery_model_desc
,ls.cntr_value as [log size (kb)]
,lu.cntr_value as [log used (kb)]
,lp.cntr_value as [percent log used]
,ds.cntr_value as [data file(s) size (kb)]
,d.is_auto_close_on
,d.is_auto_shrink_on
,d.state_desc
,d.snapshot_isolation_state
,d.is_read_committed_snapshot_on
,d.is_auto_create_stats_on
,d.is_auto_update_stats_on
,d.is_auto_update_stats_async_on
,d.is_in_standby
,d.page_verify_option_desc
,d.log_reuse_wait_desc
from sys.databases d
inner join sys.dm_os_performance_counters as lu on lu.instance_name=d.name and lu.counter_name like N'Log File(s) Used Size (KB)%'
inner join sys.dm_os_performance_counters as ls on ls.instance_name=d.name and ls.counter_name like N'Log File(s) Size (KB)%' and ls.cntr_value > 0
inner join sys.dm_os_performance_counters as lp on lp.instance_name=d.name and lp.counter_name like N'Percent Log Used%'
inner join sys.dm_os_performance_counters as ds on ds.instance_name=d.name and ds.counter_name like N'Data File(s) Size (KB)%'
order by database_name desc;
Yukardaki scriptin çıktısı aşağıdaki gibidir.
SQL Server instance sı üzerinde çalışan Veritabanlarının Recovery_model,Son Full backup alma zamanı,toplam backup alınma sayısı gibi backup durumlarını gösteren script aşağıdaki gibidir.
SELECT DB.name AS Database_Name
,MAX(DB.recovery_model_desc) AS Recovery_Model
,MAX(BS.backup_start_date) AS Last_Backup
,MAX(CASE WHEN BS.type = 'D'
THEN BS.backup_start_date END)
AS Last_Full_backup
,SUM(CASE WHEN BS.type = 'D'
THEN 1 END)
AS Count_Full_backup
,MAX(CASE WHEN BS.type = 'L'
THEN BS.backup_start_date END)
AS Last_Log_backup
,SUM(CASE WHEN BS.type = 'L'
THEN 1 END)
AS Count_Log_backup
,MAX(CASE WHEN BS.type = 'I'
THEN BS.backup_start_date END)
AS Last_Differential_backup
,SUM(CASE WHEN BS.type = 'I'
THEN 1 END)
AS Count_Differential_backup
,MAX(CASE WHEN BS.type = 'F'
THEN BS.backup_start_date END)
AS LastFile
,SUM(CASE WHEN BS.type = 'F'
THEN 1 END)
AS CountFile
,MAX(CASE WHEN BS.type = 'G'
THEN BS.backup_start_date END)
AS LastFileDiff
,SUM(CASE WHEN BS.type = 'G'
THEN 1 END)
AS CountFileDiff
,MAX(CASE WHEN BS.type = 'P'
THEN BS.backup_start_date END)
AS LastPart
,SUM(CASE WHEN BS.type = 'P'
THEN 1 END)
AS CountPart
,MAX(CASE WHEN BS.type = 'Q'
THEN BS.backup_start_date END)
AS LastPartDiff
,SUM(CASE WHEN BS.type = 'Q'
THEN 1 END)
AS CountPartDiff
FROM sys.databases AS DB
LEFT JOIN
msdb.dbo.backupset AS BS
ON BS.database_name = DB.name
WHERE ISNULL(BS.is_damaged, 0) = 0-- exclude damaged backups
GROUP BY DB.name
ORDER BY Last_Backup desc;
Scriptin çıktısı aşağıdaki gibidir.
Böylece bu yazınında sonuna gelmiş bulunmaktayım bir sonraki yazıda görüşmek dileğiyle esen kalın..