Post reply

sys.dm_exec_requests does not have login_time column, any alternative?

  • April 13, 2010 at 7:07 pm

    #233876

    Hi,

    I've got a job which logs active processes from sys.dm_exec_requests DMV into a history table every minute. In order to make sure I am looking at the same process in the history table I need the session_id, which I have and the login_time, which is not present in the DMV.

    Is there any other way to tell if the information in the history table (which has all the columns from the DMV) relates to the same process or a different one?

    E.g. using columns like group_id or something. "start_time" does not look like the equivalent of "login_time"

    I know that I could just join the query which logs the historical information to sys.sysprocesses table, but I want to avoid an extra join if possible.

    Below is the query I use to log the historical data.

    Thanks.

    select GetDate(), session_id, ec.status, command, database_id,

    blocking_session_id,wait_type,wait_time,last_wait_type,wait_resource,

    open_transaction_count,open_resultset_count,

    percent_complete,estimated_completion_time,cpu_time,total_elapsed_time,scheduler_id,

    reads,writes,logical_reads, transaction_isolation_level,lock_timeout,deadlock_priority,

    row_count,prev_error,nest_level,granted_query_memory,executing_managed_code,

    SUBSTRING(text, (statement_start_offset + 2)/2,

    CASE statement_end_offset

    WHEN -1 THEN (datalength(text))

    WHEN 0 THEN (datalength(text))

    ELSE (statement_end_offset - statement_start_offset +2)/2

    END) query_text

    from sys.dm_exec_requests ec (nolock)

    cross apply sys.dm_exec_sql_text(ec.sql_handle) est

  • April 13, 2010 at 7:42 pm

    #1151098

    You can try connection_id which is a uniqueidentifier. The login_time is not technically the same as start_time since a request can contain spawned processes that start at different times.

    Caution about connection_id is that it is nullable.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply