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
I heard one is sys.dm_exec_sessions?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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:
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.
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.
You could for example find out how
sp_who2 works by running the following command:
This presents you with the code behind the procedure.
Enjoy your journey.