Definitions More Info.
Definition ID	902
Title	SQL
Category	NOTES
Definition	Uzun suren sorgu 2 - long duration query 2
Definition Description	INSERT INTO [dbr].[DBA_LongRuningQueries] ([DB_NAME], [COMMAND], [CPU_TIME], [TOTAL_ELAPSED_TIME], [PERC_CMPLT], [ISOLATION_LEVEL], [LOGICAL_READS], [ect], [PID], [SSID], [BLOCK_SSID], [WAIT_TYPE], [WAIT_TIME], [ENCRYPTED], [STATEMENT_TEXT], [BATCH_TEXT], [STATUS], [LOGIN_NAME], [login_time], [HOST_NAME], [PROGRAM_NAME], [client_interface_name], [IS_USER_PROCESS], [QUERY_PLAN], [session_id], [request_id], [start_time], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_handle], [database_id], [user_id], [connection_id], [blocking_session_id], [wait_type], [wait_time], [last_wait_type], [wait_resource], [open_transaction_count], [open_resultset_count], [transaction_id], [context_info], [percent_complete], [estimated_completion_time], [cpu_time], [total_elapsed_time], [scheduler_id], [task_address], [reads], [writes], [logical_reads], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [nest_level], [granted_query_memory], [executing_managed_code], [group_id], [query_hash], [query_plan_hash], [statement_sql_handle], [statement_context_id]) SELECT DB_NAME(r.database_id) AS [DB_NAME] ,r.command AS [COMMAND] ,CONVERT( VARCHAR ,DATEADD( ms ,r.cpu_time ,0 ) ,114 ) AS [CPU_TIME] ,CONVERT( VARCHAR ,DATEADD( ms , r.total_elapsed_time ,0 ) ,114 ) AS [TOTAL_ELAPSED_TIME] ,r.percent_complete AS [PERC_CMPLT] ,CASE r.transaction_isolation_level WHEN 0 THEN N'Unspecified' WHEN 1 THEN N'ReadUncomitted' WHEN 2 THEN N'ReadCommitted' WHEN 3 THEN N'Repeatable' WHEN 4 THEN N'Serializable' WHEN 5 THEN N'Snapshot' ELSE N'' END AS [ISOLATION_LEVEL] ,r.logical_reads AS [LOGICAL_READS] ,r.estimated_completion_time as ect ,ss.host_process_id AS [PID] ,r.session_id AS [SSID] ,r.blocking_session_id AS [BLOCK_SSID] ,ISNULL( r.wait_type,N'None' ) AS [WAIT_TYPE] ,r.wait_time AS [WAIT_TIME] ,CASE st.encrypted WHEN 0 THEN N'NO' ELSE N'YES' END AS [ENCRYPTED] ,SUBSTRING( st.text , r.statement_start_offset / 2 + 1 , ( ( CASE WHEN r.statement_end_offset = -1 THEN ( LEN( CONVERT( NVARCHAR(MAX) , st.text )) * 2 ) ELSE r.statement_end_offset END ) - r.statement_start_offset ) / 2 + 1 ) AS [STATEMENT_TEXT] ,st.text AS [BATCH_TEXT] ,r.status AS [STATUS] ,ss.login_name AS [LOGIN_NAME] ,ss.login_time ,ss.host_name AS [HOST_NAME] ,ss.program_name AS [PROGRAM_NAME] ,ss.client_interface_name , [ss].[is_user_process] AS [IS_USER_PROCESS] , [qp].[query_plan] AS [QUERY_PLAN] ,r.session_id ,r.request_id ,r.start_time ,r.sql_handle ,r.statement_start_offset ,r.statement_end_offset ,r.plan_handle ,r.database_id ,r.user_id ,r.connection_id ,r.blocking_session_id ,r.wait_type ,r.wait_time ,r.last_wait_type ,r.wait_resource ,r.open_transaction_count ,r.open_resultset_count ,r.transaction_id ,r.context_info ,r.percent_complete ,r.estimated_completion_time ,r.cpu_time ,r.total_elapsed_time ,r.scheduler_id ,r.task_address ,r.reads ,r.writes ,r.logical_reads ,r.text_size ,r.language ,r.date_format ,r.date_first ,r.quoted_identifier ,r.arithabort ,r.ansi_null_dflt_on ,r.ansi_defaults ,r.ansi_warnings ,r.ansi_padding ,r.ansi_nulls ,r.concat_null_yields_null ,r.transaction_isolation_level ,r.lock_timeout ,r.deadlock_priority ,r.row_count ,r.prev_error ,r.nest_level ,r.granted_query_memory ,r.executing_managed_code ,r.group_id ,r.query_hash ,r.query_plan_hash ,r.statement_sql_handle ,r.statement_context_id FROM sys.dm_exec_requests [r] LEFT JOIN sys.dm_exec_sessions AS [ss] ON ss.session_id = r.session_id CROSS APPLY sys.dm_exec_sql_text( r.sql_handle ) AS [st] OUTER APPLY sys.dm_exec_query_plan( r.plan_handle ) AS [qp] WHERE r.session_id <> @@SPID AND ss.host_process_id<>9940 AND r.session_id <>56
RecordBy	cunay
Record Date	16-11-2018 15:15:33
Düzenle
Kopyala
Sil