Ever feel like you’re trying to tune a guitar while it’s still being played in a rock concert? Well, querying what’s currently running on your SQL Server can give you that insider’s look without the earplugs. Here’s how you can become the backstage manager of your database:

Step 1: Understanding What You’re Looking For

Before we dive into the SQL, let’s understand what we want:

  • Queries currently executing on the SQL Server.
  • Information like who’s running the query, what resources are they eating up, and how long they’ve been running.

Step 2: The Magic SQL Query

Here’s a SQL query using Dynamic Management Views (DMVs) to fetch current running queries:

SELECT 
    r.session_id,
    r.status,
    r.start_time,
    r.command,
    s.login_name,
    s.host_name,
    t.text AS [Query Text],
    r.cpu_time,
    r.logical_reads,
    r.wait_type,
    r.wait_duration_ms,
    r.blocking_session_id
FROM 
    sys.dm_exec_requests r
INNER JOIN 
    sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE 
    r.status NOT IN ('background', 'sleeping')  -- Filter out background tasks and sleeping sessions
    AND r.session_id != @@SPID  -- Exclude this current session
ORDER BY 
    r.cpu_time DESC;  -- Order by CPU time to see the most resource-intensive first

Step-by-Step Breakdown:

  1. sys.dm_exec_requests: This DMV gives you details about requests being executed.
  2. sys.dm_exec_sessions: Joined with requests to get user information.
  3. CROSS APPLY with sys.dm_exec_sql_text: This fetches the actual SQL text of the query using the sql_handle from the request.
  4. WHERE Clause: We exclude background tasks, sleeping sessions, and the session running this query itself for clarity.
  5. ORDER BY: We order by CPU time to see which queries are the heavy lifters.

Step 3: Running the Query

  • Open SQL Server Management Studio (SSMS) or any SQL client of your choice.
  • Connect to your SQL Server instance.
  • Copy and paste the above query into a new query window and execute it.

Interpreting Results:

  • session_id: Each query’s session identifier.
  • status: Tells you if the query is running, suspended, etc.
  • start_time: When the query started.
  • login_name & host_name: Who’s running this query and from where.
  • Query Text: The actual SQL being run. It might be truncated; if you need more, you might have to handle sql_handle differently.
  • cpu_time, logical_reads: Resource usage metrics.
  • wait_type & wait_duration_ms: If the query is waiting, what’s it waiting for and how long has it waited.
  • blocking_session_id: If this query is blocked, this shows who’s doing the blocking.

Note:

  • Running this query requires certain permissions. If you get permission errors, you might need VIEW SERVER STATE or similar permissions.
  • Remember, this is like taking a peek backstage. Don’t be surprised if what you find looks chaotic; databases are busy places!

There you go! With this query, you’re now equipped to spy on what’s happening in real-time on your SQL Server, with all the wit and wisdom of a database detective. Enjoy your sleuthing!

Categorized in:

Databases, SQL Server,