DMV (Dynamic management views) ve DMF (Dynamic management functions), sql server ve instance’larını gözlemlemek (monitor etmek), herhangi bir problemi teşhis etmek yada sql sunucunun daha performanslı çalışması için index, key vb. eksik tanımları bulmak için kullanılan, sunucu durumu hakkında bilgi dönen sql server ile hazır gelen sistem view ve function’larıdır.
DMV ve DMF’lere örnek bir kaç kullanım aşağıdaki gibidir.
Bir sorgunun ne kadar sürede çalıştığı bunun için ne kadar CPU tükettiği bu zamana kadar ne kadar çalıştırıldığı hangi SP,Function vb. sorgu içerisinde yer aldığını öğrenmek için kullanılabilecek DMV
SELECT
CAST(qs.Total_Elapsed_Time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)] ,
CAST (qs.Total_Worker_Time * 100.0 / (
CASE WHEN ISNULL(qs.Total_Elapsed_Time, 0.0) = 0.0 THEN 1.0
ELSE qs.Total_Elapsed_Time
END ) AS DECIMAL(28, 2)) AS [% CPU] ,
CAST(( qs.Total_Elapsed_Time - qs.Total_Worker_Time ) * 100.0 / (
CASE WHEN ISNULL(qs.Total_Elapsed_Time, 0.0) = 0.0 THEN 1.0
ELSE qs.Total_Elapsed_Time
END ) AS DECIMAL(28, 2)) AS [% Waiting] ,
qs.Execution_Count ,
CAST(qs.Total_Elapsed_Time / 1000000.0 / qs.Execution_Count AS DECIMAL(28,2))
AS [Average Duration (s)] ,
SUBSTRING(qt.Text, ( qs.Statement_Start_Offset / 2 ) + 1,
( ( CASE WHEN qs.Statement_End_Offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.Text)) * 2
ELSE qs.Statement_End_Offset
END - qs.Statement_Start_Offset ) / 2 ) + 1) AS [Individual Query] ,
SUBSTRING(qt.Text, 1, 100) AS [Parent Query] ,
DB_NAME(qt.DBid) AS DatabaseName ,
qs.Creation_Time ,
qs.Last_Execution_Time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qs.Total_Elapsed_Time > 0
AND ( qs.Creation_Time >= DATEADD(mm, -10, CURRENT_TIMESTAMP)
OR qs.Last_Execution_Time >= DATEADD(mm, -10, CURRENT_TIMESTAMP))
ORDER BY qs.Total_Elapsed_Time DESC
Eksik indeks’leri sorgulamak için kullanılabileceğiniz DMV
SELECT DB_NAME(id.database_id) AS DatabaseName ,
id.statement AS TableName ,
id.equality_columns ,
id.inequality_columns ,
id.included_columns ,
ROUND(gs.avg_total_user_cost * gs.avg_user_impact * ( gs.user_seeks + gs.user_scans ),0) AS [Total Cost],
gs.last_user_seek ,
gs.user_seeks ,
gs.last_user_scan ,
gs.user_scans,
'Create NonClustered Index IX_' + t.name + '_missing_'
+ CAST(id.index_handle AS VARCHAR(10))
+ ' On ' + id.STATEMENT
+ ' (' + IsNull(id.equality_columns,'')
+ CASE WHEN id.equality_columns IS Not Null
And id.inequality_columns IS Not Null THEN ','
ELSE '' END
+ IsNull(id.inequality_columns, '')
+ ')'
+ IsNull(' Include (' + id.included_columns + ');', ';'
) AS sql_statement
FROM sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig ON gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details id ON id.index_handle = ig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats ddmigs ON ddmigs.group_handle = ig.index_group_handle
INNER JOIN sys.tables AS t ON id.object_id = t.object_id
ORDER BY gs.avg_total_user_cost * gs.avg_user_impact * ( gs.user_seeks + gs.user_scans ) DESC
En pahalı 10 sorguyu bulmak için kullanabileceğiniz DMV
SELECT TOP 10
SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
( ( CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset ) / 2 ) + 1) ,
qs.execution_count ,
qs.total_logical_reads ,
qs.last_logical_reads ,
qs.total_logical_writes ,
qs.last_logical_writes ,
qs.total_worker_time ,
qs.last_worker_time ,
qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
qs.last_execution_time ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time
Veri ve log dosyaları performasını incelemek için kullanabileceğiniz DMV
SELECT DB_NAME(database_id) AS 'Database_Name'
, CASE WHEN file_id = 2 THEN 'Log' ELSE 'Data' END AS 'File_Type'
, ((size_on_disk_bytes/1024)/1024.0) AS 'Size_On_Disk_in_MB'
, io_stall_read_ms / num_of_reads AS 'Avg_Read_Transfer_in_Ms'
, CASE WHEN file_id = 2 THEN
CASE
WHEN io_stall_read_ms / num_of_reads < 5 THEN
'Good'
WHEN io_stall_read_ms / num_of_reads < 15 THEN
'Acceptable'
ELSE
'Unacceptable'
END
ELSE
CASE
WHEN io_stall_read_ms / num_of_reads < 10 THEN
'Good'
WHEN io_stall_read_ms / num_of_reads < 20 THEN
'Acceptable'
ELSE
'Unacceptable'
END
END AS 'Average_Read_Performance'
, io_stall_write_ms / num_of_writes AS 'Avg_Write_Transfer_in_Ms'
, CASE WHEN file_id = 2 THEN
CASE
WHEN io_stall_write_ms / num_of_writes < 5 THEN
'Good'
WHEN io_stall_write_ms / num_of_writes < 15 THEN
'Acceptable'
ELSE
'Unacceptable'
END
ELSE
CASE
WHEN io_stall_write_ms / num_of_writes < 10 THEN
'Good'
WHEN io_stall_write_ms / num_of_writes < 20 THEN
'Acceptable'
ELSE
'Unacceptable'
END
END AS 'Average_Write_Performance'
FROM sys.dm_io_virtual_file_stats(null,null)
WHERE num_of_reads > 0 AND num_of_writes > 0
Bloklanmış tüm sessionları bulmak için kullanabileceğiniz DMV
WITH blocking_info AS
(
SELECT
[blocker] = wait.blocking_session_id,
[waiter] = lock.request_session_id,
b_handle = br.[sql_handle],
w_handle = wr.[sql_handle],
[dbid] = lock.resource_database_id,
duration = wait.wait_duration_ms / 1000,
lock_type = lock.resource_type,
lock_mode = block.request_mode
FROM
sys.dm_tran_locks AS lock
INNER JOIN
sys.dm_os_waiting_tasks AS wait
ON lock.lock_owner_address = wait.resource_address
INNER JOIN
sys.dm_exec_requests AS br
ON wait.blocking_session_id = br.session_id
INNER JOIN
sys.dm_exec_requests AS wr
ON lock.request_session_id = wr.session_id
INNER JOIN
sys.dm_tran_locks AS block
ON block.request_session_id = br.session_id
WHERE
block.request_owner_type = 'TRANSACTION'
)
SELECT
[database] = DB_NAME(bi.[dbid]),
bi.blocker,
blocker_command = bt.,
bi.waiter,
waiter_command = wt.,
[duration MM:SS] = RTRIM(bi.duration / 60) + ':'
+ RIGHT('0' + RTRIM(bi.duration % 60), 2),
bi.lock_type,
bi.lock_mode
FROM
blocking_info AS bi
CROSS APPLY
sys.dm_exec_sql_text(bi.b_handle) AS bt
CROSS APPLY
sys.dm_exec_sql_text(bi.w_handle) AS wt;
Kaynak ;
Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_requests
Bir cevap yazın
Yorum yapabilmek için giriş yapmalısınız.