Run the query below when the performance is poor. It will show you all actively running queries. It is ordered by StartTime so the culprit will likely be the first row returned. Also, if a Stored Procedure is running, this will show you both the Stored Procedure and which statment within the Stored Procedure SQL Server is currently having trouble with.
SELECT
r.Session_ID,
DB_Name(Database_ID) DatabaseName,
r.Start_Time,
convert(decimal(10, 3), datediff(ms, r.start_time, getdate())/1000.0) Running_Time,
left(s.text, 2000) Statement_Start,
object_name(objectID, database_id) ObjectName,
SUBSTRING(text, (statement_start_offset/2) + 1,
((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END
- statement_start_offset)/2) + 1) AS Statement_Text,
Blocking_Session_ID,
*
FROM
sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
order by
R.Start_Time
how to optimized SQL Server Performance
Posted - 07/26/2012 : 00:28:19
