Is SQL Server sp_who2 deprecated?

I keep hearing sp_who2 is deprecated soon, unsupported. We are on SQL Server 2016 Enterprise.

What is a good official Microsoft replacement item or DMV which team should utilize instead of sp_who2?

I heard one is sys.dm_exec_sessions?

If you want to use the standard features provided by Microsoft then you might want to consider using the available DMVs (Dynamic Management Views) which are an integral part of Microsoft SQL Server.

Best starting point

Try out the starting page for System Dynamic Management Views (Microsoft Docs). This page provides an overview of the DMV grouped into categories.

The benefit of using these DMVs is that you get to know SQL Server better, just by using them on a daily basis. You don't have to rely on updated third-party tools, because you always have the up-to-date information already at hand.

That said, even the DMVs will change over time which is noted in the following statement found on the DMV overview:


Important

Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.

Replacing sp_who2

As a replacement for sp_who2 you could just query various DMVs using the following example:

-- replacement for sp_who2
SELECT sdes.session_id
      ,sdes.[status]
      ,sdes.login_name
      ,sdes.[host_name]
      ,sder.blocking_session_id
      ,sdb.name
      ,sdes.cpu_time
      ,sdes.logical_reads --optionally: + sdes.reads + sdes.writes
      ,sdes.last_request_start_time
      ,sdes.program_name
      ,sdes.session_id
      ,sder.request_id
      ,dest.[text]
FROM   sys.dm_exec_sessions             AS sdes
       LEFT JOIN sys.dm_exec_connections     AS sdec
            ON  sdes.session_id = sdec.session_id
       JOIN sys.databases               AS sdb
            ON  sdes.database_id = sdb.database_id
       LEFT JOIN sys.dm_exec_requests  AS sder
            ON  sdes.session_id = sder.session_id
       CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest

The big advantage of using DMVs is that you learn a lot about the internal workings for SQL Server.

What is sp_who2?

You could for example find out how sp_who2 works by running the following command:

select  OBJECT_DEFINITION(OBJECT_ID('sp_who2'))

This presents you with the code behind the procedure.

Enjoy your journey.

StackExchange Database Administrators Q#194069, answer score: 7