SQL Server provides a wealth of views and functions that allow us to gain insights into the inner workings of the database. One such view is sys.dm_exec_requests, which provides information about active requests from connected users and applications.

Let’s start by looking at some simple queries we can run using sys.dm_exec_requests. For example, we can use the following query to see all the background tasks running on our SQL Server:

SELECT session_id, start_time, command
FROM sys.dm_exec_requests
WHERE status = 'background';

This query will return a list of background tasks along with their session IDs, start times, and commands.

We can also use sys.dm_exec_requests to troubleshoot active sessions. For instance, if we want to see which sessions are experiencing waits, we can run the following query:

SELECT session_id, blocking_session_id, start_time, wait_type, wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

This query will show us any sessions that are being blocked by other sessions, along with the wait type and the type of lock being held.

If we want to find out what query is causing the blocking, we can use sys.dm_exec_sql_text along with sys.dm_exec_connections. Here’s an example query:

SELECT DISTINCT DEC.session_id, DST.text AS 'SQL'
FROM sys.dm_exec_requests AS DER
  JOIN sys.dm_exec_connections AS DEC
    ON DER.blocking_session_id = DEC.session_id
  CROSS APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DST;

This query will return the SQL statement that is causing the blocking.

In addition to troubleshooting, sys.dm_exec_requests can also help us analyze query performance. For example, if we have a long-running query and want to see its execution plan, we can use the following query:

SELECT DER.session_id, DEQP.query_plan 
FROM sys.dm_exec_requests AS DER
  CROSS APPLY sys.dm_exec_query_plan(DER.plan_handle) AS DEQP
WHERE NOT DER.status IN ('background', 'sleeping');

This query will return the execution plan for any active queries.

Another useful feature of sys.dm_exec_requests is the ability to track the progress of a query. For example, if we want to see how far along a DBCC check is, we can use the following query:

SELECT session_id, start_time, status, database_id, percent_complete 
FROM sys.dm_exec_requests
WHERE command = 'DBCC TABLE CHECK';

This query will show us the percent complete for the specified command.

Furthermore, sys.dm_exec_requests can help us identify all the active requests against a particular database. Here’s an example query:

SELECT DER.session_id, DES.login_name, DES.program_name
FROM sys.dm_exec_requests AS DER
  JOIN sys.databases AS DB
    ON DER.database_id = DB.database_id
  JOIN sys.dm_exec_sessions AS DES
    ON DER.session_id = DES.session_id
WHERE DB.name = 'Test';

This query will return all the active sessions and their associated login names and program names for a specific database.

Lastly, sys.dm_exec_requests can be used to analyze wait types and diagnose performance issues. We can use the following query to see a count of all active wait types:

SELECT COALESCE(wait_type, 'None') AS wait_type, COUNT(*) AS Total
FROM sys.dm_exec_requests
WHERE NOT status IN ('Background', 'Sleeping')
GROUP BY wait_type 
ORDER BY Total DESC;

This query will give us a breakdown of the different wait types and their respective counts.

As you can see, sys.dm_exec_requests is a powerful tool for exploring and troubleshooting SQL Server. By leveraging its capabilities, we can gain valuable insights into the performance and behavior of our database.