With the @@ SPID Function, we can find the session id of the current session. Usually we use when we want to exclude the current session in our queries.
Sample Uses are as follows:
|
1 |
SELECT @@SPID |
Current Queries Running On SQL Server Without This Session:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
select r.total_elapsed_time / 1000.0 as total_elapsed_s,percent_complete, r.blocking_session_id,r.last_wait_type, s.login_name,'MySessionID= ' + cast(r.session_id as varchar) as MySessionID, DB_NAME(r.database_id) as DatabaseName,command ,SUBSTRING(t.text, (r.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1) AS statement_text ,r.status,wait_time ,wait_type,wait_resource,text,start_time,s.program_name ,r.last_wait_type,s.host_name,r.granted_query_memory * 8 / 1024 as memory_mb from sys.dm_exec_requests r inner join sys.dm_exec_sessions s on r.session_id = s.session_id cross apply sys.dm_exec_sql_text(r.sql_handle) t where r.session_id <> @@SPID --and r.database_id =DB_ID('Your_DB_Name') --and t.text like '%Part_Of_Your_Query_That_You_Want_To_Filter%' --and r.session_id = sessionid --and s.Login_Name like '%Your_Login_name%' --and s.program_name LIKE '%The Program_Name_You_Want_To_Filter%' --and r.wait_type !='The_Wait_Type_You_Want_To_Filter' --and r.blocking_session_id<>0" order by start_time asc |
