3

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?

6

Back in the days there was sp_who, sp_who2, but then came Adam Machanic and created sp_whoisactive. And he saw everything that he had made, and behold, it was very good. And there was documentation and downloads on the following days.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy