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?
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?
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.
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:
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.
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:
select OBJECT_DEFINITION(OBJECT_ID('sp_who2'))
This presents you with the code behind the procedure.
Enjoy your journey.