How see what is running on SQL server

Product:

Microsoft SQL Azure

Issue:

What processes are running on the SQL database server?

Solution:

In SSMS enter this:

select 
r.session_id, 
s.login_name, 
c.client_net_address, 
s.host_name, 
s.program_name, 
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:

SELECT
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;