Package element: SqlCommand
Name | Value |
ContainsID | False |
Description | The SQL command to be executed. |
EncryptionRequired | False |
ExpressionType | CPET_NONE |
ID | 14 |
IdentificationString | ODS - Get snapshot of dm_exec_requests.Properties[SqlCommand] |
Name | SqlCommand |
ObjectType | OT_PROPERTY |
State | PS_DEFAULT |
TypeConverter | |
UITypeEditor | Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 |
Value | SET NOCOUNT ON{LF}{LF}-- Get the collection time as UTC time{LF}DECLARE @collection_time datetime{LF}SET @collection_time = GETDATE(){LF}{LF}SELECT{LF}CONVERT(int, ROW_NUMBER() OVER (ORDER BY sess.session_id, ISNULL (req.request_id, -1), ISNULL (tasks.exec_context_id, -1)) ) AS row_id,{LF}-- IDs and Blocking IDs{LF}sess.session_id, {LF}ISNULL (req.request_id, -1) AS request_id, {LF}ISNULL (tasks.exec_context_id, -1) AS exec_context_id, {LF}ISNULL (req.blocking_session_id, 0) AS blocking_session_id,{LF}CONVERT (bit, CASE {LF} WHEN EXISTS (SELECT TOP 1 session_id FROM sys.dm_exec_requests bl WHERE bl.blocking_session_id = req.session_id) THEN 1{LF} ELSE 0{LF} END) AS is_blocking,{LF}ISNULL (waits.blocking_exec_context_id, 0) AS blocking_exec_context_id, {LF}tasks.scheduler_id, {LF}DB_NAME(req.database_id) as database_name, {LF}req.[user_id], {LF}{LF}-- State information{LF}LEFT (tasks.task_state, 10) AS task_state, {LF}LEFT (req.status, 15) AS request_status, {LF}LEFT (sess.status, 15) AS session_status,{LF}req.executing_managed_code, {LF}{LF}-- Session information{LF}sess.login_time, {LF}sess.is_user_process, {LF}LEFT (ISNULL (sess.[host_name], ''), 20) AS [host_name], {LF}LEFT (ISNULL (sess.[program_name], ''), 50) AS [program_name], {LF}LEFT (ISNULL (sess.login_name, ''), 30) AS login_name, {LF}{LF}-- Waits information{LF}LEFT (ISNULL (req.wait_type, ''), 45) AS wait_type, {LF}LEFT (ISNULL (req.last_wait_type, ''), 45) AS last_wait_type, {LF}ISNULL (waits.wait_duration_ms, 0) AS wait_duration_ms, {LF}LEFT (ISNULL (req.wait_resource, ''), 50) AS wait_resource, {LF}LEFT (ISNULL (waits.resource_description, ''), 140) AS resource_description,{LF}{LF}-- Transaction information{LF}req.transaction_id, {LF}ISNULL(req.open_transaction_count, 0) AS open_transaction_count,{LF}COALESCE(req.transaction_isolation_level, sess.transaction_isolation_level) AS transaction_isolation_level,{LF}{LF}-- Request stats{LF}req.cpu_time AS request_cpu_time, {LF}req.logical_reads AS request_logical_reads, {LF}req.reads AS request_reads, {LF}req.writes AS request_writes, {LF}req.total_elapsed_time AS request_total_elapsed_time, {LF}req.start_time AS request_start_time, {LF}{LF}-- Session stats{LF}sess.memory_usage, {LF}sess.cpu_time AS session_cpu_time, {LF}sess.reads AS session_reads, {LF}sess.writes AS session_writes, {LF}sess.logical_reads AS session_logical_reads, {LF}sess.total_scheduled_time AS session_total_scheduled_time, {LF}sess.total_elapsed_time AS session_total_elapsed_time, {LF}sess.last_request_start_time, {LF}sess.last_request_end_time, {LF}req.open_resultset_count AS open_resultsets, {LF}sess.row_count AS session_row_count, {LF}sess.prev_error, {LF}tasks.pending_io_count, {LF}{LF}-- Text/Plan handles{LF}ISNULL (req.command, 'AWAITING COMMAND') AS command, {LF}req.plan_handle, {LF}req.sql_handle, {LF}req.statement_start_offset, {LF}req.statement_end_offset,{LF}@collection_time AS collection_time{LF}FROM sys.dm_exec_sessions sess {LF}LEFT OUTER MERGE JOIN sys.dm_exec_requests req ON sess.session_id = req.session_id{LF}LEFT OUTER MERGE JOIN sys.dm_os_tasks tasks ON tasks.session_id = sess.session_id AND tasks.request_id = req.request_id AND tasks.task_address = req.task_address{LF}LEFT OUTER MERGE JOIN sys.dm_os_waiting_tasks waits ON waits.session_id = sess.session_id AND waits.waiting_task_address = req.task_address{LF}WHERE {LF} sess.session_id <> @@SPID{LF} AND{LF} ({LF} (req.session_id IS NOT NULL AND (sess.is_user_process = 1 OR req.status COLLATE Latin1_General_BIN NOT IN ('background', 'sleeping'))){TAB}-- active request{LF} OR {LF} (sess.session_id IN (SELECT DISTINCT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0)){TAB}{TAB} -- not active, but head blocker{LF} ){LF}OPTION (FORCE ORDER) |