In the large SQL server database maximum time, we are stuck in the situation when database responding very slow when we check in the server end it shows the utilization of CPU is 95-98%, and when these things happen all processes running in the database starts taking so much time to execute.
To overcome
this situation or to identify which query is taking time to execute the below
query will help you to get SPID, when batch execution started and which query
is taking time etc. so that you can go through that particular query to resolve
the issue.
SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN
statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID -- don't show this SPID query
AND session_id > 50 -- don't
show system queries
ORDER BY r.start_time;
What other ideas can you add to this post that I may have not mentioned?
