current running queries in sql

SELECT r.session_id, s.TEXT, r.[status], r.blocking_session_id, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s 1) When an application reports a memory pressure issue – use the DMV to identify the current load and choose a session that may be causing a problem. view source print? 1. SELECT * FROM sys.dm_exec_sessions 2) The memory_usage column returns the number of pages used by this session. ORDER BY memory_usage DESC and analyse the session. It may not be the session causing performance hit , but a methodical approach is important. view source print? 1. SELECT * FROM sys.dm_exec_sessions ORDER BY memory_usage DESC 3) Create a query to join with sys.dm_exec_requests. The sys.dm_exec_requests DMV returns detailed information about executed requests in SQL Server. This query includes a CROSS APPLY to sys.dm_exec_sql_text. view source print? 01. select es.session_id,es.memory_usage,sql_handle,(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 02. 03. ( (CASE WHEN statement_end_offset = -1 04. 05. THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 06. 07. ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement 08. FROM sys.dm_exec_sessions as es 09. INNER JOIN sys.dm_exec_requests as er ON er.session_id = es.session_id 10. CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2