Shulou(Shulou.com)06/01 Report--

-1. Find out the spid with the longest execution time

Use tempdb

Go

SELECT top 20 t1.session_id,t3.total_elapsed_time

T1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count

T1.internal_objects_dealloc_page_count, t1.user_objects_dealloc_page_count

T3.login_name,t3.status

From sys.dm_db_session_space_usage t1

Inner join sys.dm_exec_sessions as T3 on t1.session_id = t3.session_id

Where (t1.internal_objects_alloc_page_count > 0)

Or t1.user_objects_alloc_page_count > 0

Or t1.internal_objects_dealloc_page_count > 0

Or t1.user_objects_dealloc_page_count > 0)

Order by t1.internal_objects_alloc_page_count desc

-- 2. Find out which sql statement caused

Select s.texttext P.P. *

From master.dbo.sysprocesses p

Cross apply sys.dm_exec_sql_text (p.sql_handle) s

Where spid = 111,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.