Monday, April 6, 2020

How to find the currently running queries in SQL Server

 

How to Identify Currently Running Queries in SQL Server

When troubleshooting SQL Server performance issues, one of the key steps is to determine which queries are actively running on the server. Monitoring running queries helps you identify long-running or resource-intensive processes that might be impacting overall performance.

The following T-SQL script is essential for checking the active queries on your SQL Server instance. It provides detailed information about each running query, including the session ID, status, login name, host, blocking session, database, CPU time, start time, elapsed time, and the SQL statement itself.

Script to View Current Running Queries

-- Get information about currently executing queries
SELECT 
    SPID = er.session_id,
    STATUS = ses.status,
    [Login] = ses.login_name,
    Host = ses.host_name,
    BlkBy = er.blocking_session_id,
    DBName = DB_Name(er.database_id),
    CommandType = er.command,
    ObjectName = OBJECT_NAME(st.objectid),
    CPUTime = er.cpu_time,
    StartTime = er.start_time,
    TimeElapsed = CAST(GETDATE() - er.start_time AS TIME),
    SQLStatement = st.text
FROM 
    sys.dm_exec_requests er
OUTER APPLY 
    sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN 
    sys.dm_exec_sessions ses ON ses.session_id = er.session_id
LEFT JOIN 
    sys.dm_exec_connections con ON con.session_id = ses.session_id
WHERE 
    st.text IS NOT NULL;

Explanation of Key Columns:

  • SPID: The session ID of the running query.
  • STATUS: Current status of the session.
  • Login: The login name of the user executing the query.
  • Host: The client host name from where the query originated.
  • BlkBy: Session ID of any blocking session.
  • DBName: The database context where the query is running.
  • CommandType: The type of command being executed (e.g., SELECT, INSERT).
  • ObjectName: The object (like a table or procedure) involved in the query.
  • CPUTime: The amount of CPU time used by the query.
  • StartTime: When the query execution started.
  • TimeElapsed: Duration since the query started running.
  • SQLStatement: The actual SQL query text.

Why Use This Script?

This script gives you real-time insight into the workload on your SQL Server instance. By identifying which queries are running and how long they have been executing, you can:

  • Detect blocking and deadlocks caused by long-running queries.
  • Analyze queries consuming excessive CPU.
  • Gain visibility into user activities affecting performance.
  • Assist in tuning and troubleshooting bottlenecks.

Feel free to use and customize this script to suit your monitoring needs. It’s a handy tool for DBAs and developers alike to maintain optimal database performance.

at

Popular Posts