Tuesday, December 16, 2014

What is running on the box

So you have an SQL server and you need to find out what the little box is up to. You could open up the activity monitor but i find i faster to just run this query:

SELECT 
command, DB_NAME(database_id) as DbName, 
percent_complete as PctComp, 
reads,
writes, 
logical_reads,
Cpu_time/1000 as CpuSec,
total_elapsed_Time/1000 as ElapsSec,
SUBSTRING ( s.text, statement_start_offset/2+1, 4096 ) as StatementTxt,
start_time, session_id, 
blocking_session_id, 
open_transaction_count 
FROM sys.dm_exec_requests r 

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s 
order by start_time 



This query will give you an instant view on queries running on right now, how long time they have been running (ElapsSec) and how much CPU seconds they have consumed. You can also see blocked queries and what query that are head of blocking.

No comments: