Listing running active SQL queries

If your SQL server suddenly begin running slowly and you need to find out the cause of the problem. You can list actively running queries.

To view active executing request. You can use this query.

SELECT 
  s.text,
  r.session_id,
  r.status, 
  r.command,
  r.cpu_time,
  r.total_elapsed_time,
  DB_NAME(r.database_id) database_name,
  USER_NAME(r.user_id) [user_name],
  OBJECT_NAME(s.objectid,r.database_id) [object_name]
FROM
  sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s
WHERE
  r.session_id <> @@SPID
ORDER BY
  r.total_elapsed_time DESC

Column descriptions briefly;

Text: Executing query string.

Session_id: The session id of the request. If necessary, you can kill the process by using this id (Ex: kill 123 )

Status: Status of the request. Possible values;

Background

Running

Runnable

Sleeping

Suspended

Command: Type of command. Possible values;

SELECT

INSERT

UPDATE

DELETE

BACKUP LOG

BACKUP DATABASE

DBCC

FOR

Cpu_time: CPU time in milliseconds that is used by the request.

Total_elepsed_time: Total elapsed time in milliseconds.

Database_name: Database name.

User_name: User name of request owner.

Object_name: If the running query is stored procedure, it’s name of the procedure.

For detailed information::

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql