{"id":380,"date":"2024-07-26T10:11:41","date_gmt":"2024-07-26T10:11:41","guid":{"rendered":"https:\/\/humtechno.com\/intro\/?p=380"},"modified":"2024-07-26T10:12:35","modified_gmt":"2024-07-26T10:12:35","slug":"check-active-sessions-detail-in-sql-server","status":"publish","type":"post","link":"https:\/\/humtechno.com\/intro\/2024\/07\/26\/check-active-sessions-detail-in-sql-server\/","title":{"rendered":"Check Active Sessions Detail in SQL Server"},"content":{"rendered":"\n\n\n\n<p>select<br>r.session_id<br>, r.status<br>, r.command<br>, r.database_id<br>, r.blocking_session_id<br>, r.open_transaction_count<br>, r.cpu_time \/ 1000 as cpu_time_sec<br>, r.total_elapsed_time \/ 1000 as duration_sec<br>, r.logical_reads \/ 128 as logical_reads_mb<br>, r.reads \/ 128 as physical_reads_mb<br>, r.writes \/ 128 as logical_writes_mb<br>, wt.*<br>, t.text as batch<br>, case when r.statement_end_offset = -1 then<br>substring(<br>t.text<br>, r.statement_Start_offset \/ 2<br>, 8000<br>)<br>else<br>substring(<br>t.text<br>, r.statement_Start_offset \/ 2<br>, (r.statement_end_offset &#8211; r.statement_start_offset) \/ 2<br>)<br>end as statement<br>, qp1.query_plan as query_plan_batch<br>, try_cast(qp2.query_plan as xml) as query_plan_statement<br>from sys.dm_exec_requests r<br>join sys.dm_exec_sessions s on s.session_id = r.session_id<br>left join sys.dm_os_waiting_tasks wt on wt.session_id = r.session_id<br>cross apply sys.dm_exec_sql_text(r.sql_handle) t<br>outer apply sys.dm_exec_query_plan(r.plan_handle) qp1<br>outer apply sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) qp2<br>where s.is_user_process = 1<br>order by r.total_elapsed_time desc;<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":2,"featured_media":372,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[5,4],"tags":[42,41],"class_list":["post-380","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ms-sql","category-sql-server","tag-active-session-in-sql-server","tag-session-detail-sql-server"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/380","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/comments?post=380"}],"version-history":[{"count":2,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/380\/revisions"}],"predecessor-version":[{"id":382,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/380\/revisions\/382"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media\/372"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media?parent=380"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/categories?post=380"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/tags?post=380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}