How see what is running on SQL server


Microsoft SQL Azure


What processes are running on the SQL database server?


In SSMS enter this:

st.text, s.status 
from sys.dm_exec_requests r 
inner join sys.dm_exec_sessions s 
on r.session_id = s.session_id 
left join sys.dm_exec_connections c 
on r.session_id = c.session_id 
outer apply sys.dm_exec_sql_text(r.sql_handle) st 
where client_net_address is not null and text is not null and s.status = 'running'

Copy the content of the text column out to notepad to see the query running in detail.

To see if it is CPU or I/O that is used most the last hour, enter in SSMS this:

database_name = DB_NAME()
, UTC_time = end_time
, 'CPU Utilization In % of Limit' = rs.avg_cpu_percent
, 'Data IO In % of Limit' = rs.avg_data_io_percent
, 'Log Write Utilization In % of Limit' = rs.avg_log_write_percent
, 'Memory Usage In % of Limit' = rs.avg_memory_usage_percent 
, 'In-Memory OLTP Storage in % of Limit' = rs.xtp_storage_percent
, 'Concurrent Worker Threads in % of Limit' = rs.max_worker_percent
, 'Concurrent Sessions in % of Limit' = rs.max_session_percent
FROM sys.dm_db_resource_stats AS rs --past hour only
ORDER BY rs.end_time DESC;

Data IO show 100%, here is the bottleneck, maybe the SQL QUERY PLAN is using more TEMPDB than it should.


To see what SQL plan is in use, install, and use:

sp_blitzwho @GetLiveQueryPlan=1

Click on live-query plan, and you will get the SQL plan in used shown inside SSMS.

More Information: