SQL Server performans çalışması yaparken kullanabilecek DMV’ler

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 ; http://www.kazimcesur.com/sql-server-dmv/ Dynamic Management Views and Functions (Transact-SQL) sys.dm_exec_requests