Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

Monitoring SQL Server Blocked Sessions with SQLCMD


By:   |   Last Updated: 2017-12-11   |   Comments   |   Related Tips: More > 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.

SSMS Template Browser - Description: You can create your own templates.

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.

Sqlcmd Execution - Description: This is how looks that query when is executed in sqlcmd.

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.

Shorter Query - Description: Even this small query has visualization issues when executed in sqlcmd.

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

The -W switch - Description: By using the -W switch we get rid of the problem.

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.

Blocking Sessions - Description: With this little script we can get information about the blocking session.

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
   
linux

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.

Running all the scripts together - Description: All those scripts can fit in a single screen capture.
Next Steps


Last Updated: 2017-12-11


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools