Free SQL Help for Devs, DBAs, and the Curious

SQL Server provides a wealth of dynamic management views (DMVs) that can be leveraged to troubleshoot a variety of issues. One of the most useful for investigating blocking issues is sys.dm_exec_requests. This DMV returns information about all requests that are currently executing in SQL Server. In this post, we'll delve into how to use sys.dm_exec_requests to identify and resolve blocking issues in SQL Server 2016 and 2017, MySQL 5.7, DB2 11.1, and Azure SQL.

The sys.dm_exec_requests DMV includes a column called blocking_session_id. This column will display the session ID of the blocking transaction if a request is being blocked, or 0 if it is not. To get a quick view of any current blocking situations, you can run the following T-SQL query in SQL Server Management Studio:

SELECT session_id, blocking_session_id, wait_time, wait_type, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id != 0;

Unfortunately, MySQL doesn't have an equivalent DMV to sys.dm_exec_requests. However, you can get similar information by querying the INFORMATION_SCHEMA.PROCESSLIST table. Here's an equivalent MySQL query to find blocking issues:

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 200;

For DB2 11.1, you can gather similar information from the MONITOR functions. Here's a sample DB2 query to find blocking issues:

SELECT APPLICATION_HANDLE, LOCK_WAIT_TIME, LOCK_WAIT_STATUS
FROM TABLE(MON_GET_CONNECTION(NULL, -2)) AS t
WHERE LOCK_WAIT_TIME > 0;

Azure SQL Database, being a fully managed version of SQL Server, also supports sys.dm_exec_requests. You can use the same T-SQL query as for SQL Server 2016 and 2017 to find blocking issues in Azure SQL Database.

It's worth mentioning that these simple queries only provide an initial indication of a possible problem. They don't show the complete picture and won't always accurately identify the root cause of a blocking issue. However, they're a good first step in diagnosing blocking issues in your database.

In conclusion, sys.dm_exec_requests and its equivalents in other databases are powerful tools for identifying blocking issues. With a bit of understanding and some simple queries, you can use them to quickly identify and resolve blocking issues in SQL Server 2016 and 2017, MySQL 5.7, DB2 11.1, and Azure SQL.