Labels

How do I see the full SQL / Query text of a spid?


This is usual problem when sql query is very large, So I shared a scrip found online on TechNet by one of MVP’s


SELECT   s.session_id,
            r.status,
            r.blocking_session_id                                 'Blk by',
            r.wait_type,
            wait_resource,
            r.wait_time / (1000.0)                             'Wait Sec',
            r.cpu_time,
            r.logical_reads,
            r.reads,
            r.writes,
            r.total_elapsed_time / (1000.0)                    'Elaps Sec',
            Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
                    ((CASE r.statement_end_offset
                        WHEN -1
                        THEN Datalength(st.TEXT)
                        ELSE r.statement_end_offset
                        END - r.statement_start_offset) / 2) + 1) AS statement_text,
            Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)),
                    '') AS command_text,
            r.command,
            s.login_name,
            s.host_name,
            s.program_name,
            s.last_request_end_time,
            s.login_time,
            r.open_transaction_count
FROM     sys.dm_exec_sessions AS s
            JOIN sys.dm_exec_requests AS r
            ON r.session_id = s.session_id
            CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE    r.session_id != @@SPID
ORDER BY r.cpu_time desc, r.status,
            r.blocking_session_id,
            s.session_id




No comments:
Write comments

Please do not enter spam links

Services

More Services