Monitoring SQL Server Blocked Sessions with SQLCMD

By: Daniel Farina   |   Updated: 2017-12-11   |   Comments (1)   |   Related: > Locking and Blocking

Problem

The release of SQL Server 2017 introduced support for Linux and one of the big differences in this release is that you need to administer almost everything from the console. This is a habit changer for most of us that have spent most of our careers using the Windows platforms with the graphical user interfaces.

One of the most basic tasks for a SQL Server Database Administrator is to check for blocked sessions and we must be prepared to deal with this task without using a graphical interface. In this tip I will show to you how you can use sqlcmd to achieve this task regardless of what platform or version of SQL Server you are running.

Solution

Working as a SQL Server Database Administrator sometimes resembles a rollercoaster. Everything is going fine and suddenly a problem emerges out of nowhere; that’s what makes our work so challenging and fun. When there is a blocked session on SQL Server your phone starts to ring and on the other side of the phone there is a user freaking out. The only information you can get from the user is “it’s not working”, or “the application is slow”. Of course you won’t be able to solve the problem with only that information; you have to do your own research to find out what’s happening.

When the problem is on a server on which you can use SQL Server Management Studio, your problem is almost (and I emphasize almost) solved. That’s because you can browse the MSSQLTips.com SQL Server Locking and Blocking Tips Category and you will have the information as well as lots of sample code to deal with blocked sessions. With foresight and being prepared for these situations, you may already have a folder in the Template Browser holding all the scripts needed for troubleshooting blocking issues.

On the other hand, if you are unable to use SQL Server Management Studio, you will realize that you are in a little trouble. Let’s take for instance the script from the tip Identify the cause of SQL Server blocking:

WITH [Blocking]
          AS ( SELECT   w.[session_id] ,
                         s.[original_login_name] ,
                        s.[login_name] ,
                        w.[wait_duration_ms] ,
                        w.[wait_type] ,
                        r.[status] ,
                        r.[wait_resource] ,
                        w.[resource_description] ,
                        s.[program_name] ,
                        w.[blocking_session_id] ,
                        s.[host_name] ,
                        r.[command] ,
                        r.[percent_complete] ,
                        r.[cpu_time] ,
                        r.[total_elapsed_time] ,
                        r.[reads] ,
                        r.[writes] ,
                        r.[logical_reads] ,
                        r.[row_count] ,
                        q.[text] ,
                        q.[dbid] ,
                        p.[query_plan] ,
                        r.[plan_handle]
               FROM     [sys].[dm_os_waiting_tasks] w
                        INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
                        INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
                        CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
                        CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
               WHERE    w.[session_id] > 50
                        AND w.[wait_type] NOT IN ( 'DBMIRROR_DBM_EVENT',
                                                   'ASYNC_NETWORK_IO' )
             )
    SELECT  b.[session_id] AS [WaitingSessionID] ,
            b.[blocking_session_id] AS [BlockingSessionID] ,
            b.[login_name] AS [WaitingUserSessionLogin] ,
            b.[original_login_name] AS [WaitingUserConnectionLogin] ,
            b.[wait_duration_ms] AS [WaitDuration] ,
            b.[wait_type] AS [WaitType] ,
            b.[host_name] AS [WaitingHost],
            UPPER(b.[status]) AS [WaitingProcessStatus] ,
            b.[wait_resource] AS [WaitResource] ,
            b.[resource_description] AS [WaitResourceDescription] ,
            b.[program_name] AS [WaitingSessionProgramName] ,
            s1.[login_name] AS [BlockingUserSessionLogin] ,
            s1.[original_login_name] AS [BlockingSessionConnectionLogin] ,
            s1.[program_name] AS [BlockingSessionProgramName] ,
            s1.[host_name] AS [BlockingHost],
            UPPER(s1.[status]) AS [BlockingSessionStatus] ,
            t.[request_mode] AS [WaitRequestMode] ,
            t.[resource_type] AS [WaitResourceType] ,
            t.[resource_database_id] AS [WaitResourceDatabaseID] ,
            DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName] 
    FROM    [Blocking] b
            INNER JOIN [sys].[dm_exec_sessions] s1 ON b.[blocking_session_id] = s1.[session_id]
            INNER JOIN [sys].[dm_tran_locks] t ON t.[request_session_id] = b.[session_id]
    WHERE   t.[request_status] = 'WAIT'
GO   

This is the kind of query we must keep close, so we have it when there are performance issues. This query returns a lot of information when you are troubleshooting a blocking issue. Even though this query is useful and very complete, there is an issue if we can only connect to the problematic SQL Server instance by using sqlcmd. Just take a look at the screen captures on the tip Identify the cause of SQL Server blocking. In order to take screen captures, the author had to split the result set into seven images just to make it fit the screen.

For the purposes of illustration the issue of running that script in sqlcmd I took the following screen capture.

As you can see on the previous image, what was a perfect query to troubleshoot blocking has become useless when we execute it inside sqlcmd. The fact is that when we are using the console we can’t display too much information, we need to use queries that return as few columns as possible in order to aid with visualization.

Using sqlcmd to Find Blocked SQL Server Sessions

Now that we are aware of the problem let’s try to find a solution.

First we must create a short query that returns the basic information, like the following:

SELECT session_id,        
   status,
        DB_NAME(database_id) [Database],
        blocking_session_id,
        wait_type,
        wait_time,
        wait_resource 
FROM    sys.dm_exec_requests
WHERE   blocking_session_id <> 0
GO

This query returns far less columns than the previous query, but as you can see on the next image we still have the same problem with reading the results and understanding the query output.

In order to solve it we can use the –W switch when invoking sqlcmd. This option removes trailing spaces from columns.

As you can see on the previous image, we now have legible query output. But if you look closely you can see that instead of writing the query in the sqlcmd console I just loaded the query from a text file with the :r sqlcmd command. Having the query in a file saves time. Furthermore, you can have more queries like this in script files and copy them to each server where you can’t connect by using SQL Server Management Studio.

Now that we know we have a blocked session, we need to gather more information about both the blocked session and the blocking session, but we must do it one step at a time.

First let’s check out what the blocking session is executing with the following query.

SELECT  A.Session_id,
        A.blocking_session_id,
        DB_NAME(B.database_id) [Database],
        C.text
FROM    sys.dm_exec_requests A
        LEFT JOIN sys.dm_exec_requests B ON A.blocking_session_id = B.session_id
        OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) C
WHERE   A.blocking_session_id <> 0
GO

The query above returns the T-SQL code that is running the blocking session, its wait type, the database on which its running and the id of the session that is being blocked.

On the following image you can see the output of this query.

Also, we may like to see the query that is being blocked and we can do so with the following script.

SELECT  A.Session_id,
        A.blocking_session_id,
        DB_NAME(A.database_id) [Database],
        B.text
FROM    sys.dm_exec_requests A        
        CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
WHERE   A.blocking_session_id <> 0
GO
   

As you can see on the image above, with the previous query we have a result set that fits the screen and gives us valuable information.

In other words, we have found a way to troubleshoot blocking with sqlcmd by using script files that return just the data we need. This method of having script files adapted to run on sqlcmd in a folder can be used for other types of issues like checking free database space or log file fragmentation just to enumerate a few. In fact you can emulate the functionality of SQL Server Management Studio’s Template Browser with the difference that if you want to see the script names you need to execute the !!dir sqlcmd command to list the contents of a folder.

On the following screen capture you will see how it looks when all three scripts are run together.

Next Steps

• If you don’t know much about blocking and locking in SQL Server, take a look at the following two tips:
  • Understanding SQL Server Blocking
  • Understanding SQL Server Locking.
• You will find more information on how to identify blocking in SQL Server in the following tip: How to identify blocking in SQL Server.
• Also, this tip will help: Different techniques to identify blocking in SQL Server.
• In case you need to refresh your knowledge on the sqlcmd utility you can read my previous tip: Introduction to SQL Server's sqlcmd utility.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips