This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects. You can find a list of all of the deprecated views in the introduction post.
The compatibility view sysprocesses returns information on the processes that are running on a SQL Server instance. The processes returned are both system and user processes.
When it comes to replacing sysprocesses, there are three dynamic management views (DMV) that fill that role. The first DMV is sys.dm_exec_sessions; which returns a row for every session, or process, on a SQL Server instance. The DMV returns data on both user and system processes. The next DMV is sys.dm_exec_connections. This DMV returns information about all connections that have been made to a SQL Server instance. The third DMV that is meant to replace sysprocesses is sys.dm_exec_requests; which provides information on sessions that are currently executing.
Compatibility View Column Gaps
When it comes to mapping sysprocesses to the new DMVs, there are a few gaps between the information that was previously available and the new information provided. These columns generally provide the same information but with a bit of a difference. In this section, we’ll discuss the difference between has changed for the following columns:
- WaitType
- UID
- Net Address
- SQL_Handle
WaitType
In sysprocesses, the waittype column provides information on the current wait type for the process, presented in a binary(2) format. The values, such as 0×01 and 0×208, represent wait types, such as LCK_M_SCH_S and CXPACKET, respectively. To translate the wait types from the binary value to the text description, the articles KB244455 and KB822101 are often used to determine what the wait type values mean. This same information is available through the DMV sys.dm_exec_requests, but not in the same format. In the DMV, the wait type is returned in clear text. While it is tempting to try and change the wait type in the DMV back to the binary value, it is far simpler to use the DMV version as is and modify applications that use sysprocesses by removing the logic to translate the binary value.
UID
The definition for UID in sysprocesses states that it is the user that executed the command. While correct, that’s not the full story. The UID value maps into the user security role that executed the command. For instance, if a user logs into a database under the sa login, then the UID will map to the dbo role, which is the security context for the user. If the login maps in using a certificate, then it maps the to certificate. This information is not maintained with the DMVs. Instead, though, there is a security id in sys.dm_exec_sessions that can be used to join to sys.server_principals to get the principal_id.
Net Address
There is a stark difference between the data provided in sysprocesses for net_address and the column in the DMVs that is the closest approximation for that column. With net_address, the column returns the network adapter address for the connection that is made to the SQL Server instance. This isn’t the connections IP address, but that value can be translated into an IP address. From the DMVs, the net_address value isn’t available. That gap isn’t necessarily an issues since the column client_net_address is available, which contains the IP address, through sys.dm_exec_connections.
SQL_Handle
The column SQL_handle in sysprocesses does not return the same information that a SQL_handle in DMVs will provide. While the information is the same from a definition level, a hash value that uniquely identifies a set of SQL text, the values cannot be used interchangeably. The sql_handle in sysprocesses requires the use of the sys.fn_get_sql function and is only valid while the SQL statement is executing. On the other hand, the sql_handle in sys.dm_exec_sessions and sys.dm_exec_requests can be used with sys.dm_exec_query_stats and the other DMVs that use sql_handle.
Query Via sysprocesses
After considering the information on the columns in the previous section, querying sysprocesses is fairly simple. The typical query will just query out the information included as it is without any transformation. This results in a query, such as the one in Listing 1.
--Listing 1 – Query for sys.sysprocesses SELECT spid ,kpid ,blocked ,waittype ,waittime ,lastwaittype ,waitresource ,dbid ,uid ,cpu ,physical_io ,memusage ,login_time ,last_batch ,ecid ,open_tran ,status ,sid ,hostname ,program_name ,hostprocess ,cmd ,nt_domain ,nt_username ,net_address ,net_library ,loginame ,context_info ,sql_handle ,stmt_start ,stmt_end ,request_id FROM sysprocesses
Query via the DMVs
Even with the gaps between sysprocesses and the DMVs, noted earlier, getting the results from the DMVs to map completely to sysprocesses takes a bit of work. For starters, the columns from sysprocesses have been split between multiple DMVs and most often renamed. Some of the values appear in multiple DMVs and need to all be combined to get the sysprocesses value, such as read and writes in sys.dm_exec_sessions and sys.dm_exec_requests. A few values, such as ecid and kpid need to be retrieved from views outside of the three core DMVs, these two require the DMVs sys.dm_os_tasks and sys.dm_os_threads. In the end, pulling all of this together results in the query in Listing 2, which provides nearly all of the same output as sysprocesses, with only some slight variations.
--Listing 2 – Query using sys.dm_exec_sessions, sys.dm_exec_connections, and sys.dm_exec_requests
SELECT es.session_id AS spid
,COALESCE(oth.os_thread_id,0) AS kpid
,COALESCE(er.blocking_session_id,0) AS blocked
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,es.database_id AS dbid
,sp.principal_id AS uid
,COALESCE(es.cpu_time,0)
+ COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
+ COALESCE(es.writes,0)
+ COALESCE(er.reads,0)
+ COALESCE(er.writes,0) AS physical_io
,es.memory_usage AS memusage
,es.login_time
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,COALESCE(ota.exec_context_id,0) AS ecid
,es.open_transaction_count AS open_tran
,es.status
,es.security_id AS sid
,COALESCE(es.host_name,'') AS hostname
,COALESCE(es.program_name,'') AS program_name
,COALESCE(es.host_process_id,'') AS hostprocess
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,COALESCE(es.nt_domain,'') AS nt_domain
,COALESCE(es.nt_user_name, '') AS nt_username
,client_net_address AS net_address
,net_transport AS net_library
,es.login_name AS loginame
,es.context_info
,ec.most_recent_sql_handle AS sql_handle
,COALESCE(er.statement_start_offset,0) AS stmt_start
,COALESCE(er.statement_end_offset,0) AS stmt_end
,COALESCE(er.request_id,0) AS request_id
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
ORDER BY es.session_id
More From the DMVs
With the three core DMVs that replace sysprocesses, there are a number of additional columns that are not included in the results with the old compatibility view. These columns provide a wealth of additional information that can be extremely useful in looking at sessions, requests, and connections that exist on a SQL Server instance.
More From sys.dm_exec_sessions
The first DMV, sys.dm_exec_sessions, contains a number of additional columns that are useful. Some of the columns refer to ANSI settings made when the session was created, such as ANSI_PADDING and ANSI_NULLS. Others include information on the previous error, the success of the last logon and original login information. Some of the more interesting and useful of the additional columns in sys.dm_exec_sessions are:
- client_interface_name:Protocol used by the client to connect to the instance
- client_version:TDS protocol version used by the client
- total_scheduled_time:Total time in which the session was scheduled to execute
- total_elapsed_time:Time since the session has been created in milliseconds
- endpoint_id:End point in use by the session
- logical_reads:Number of logical reads for the session
- is_user_process:Identifies if the session is a user process. More precise, and accurate, than using session_id greater than 50
- transaction_isolation_level:Isolation level the session is utilizing
- lock_timeout:LOCK_TIMEOUT setting
- deadlock_priority:DEADLOCK_PRIORITY setting
- row_count:Number of rows returned by the session
- group_id:ID of the workload group for resource governor
- authenticating_database_id: Database ID that the principal logged in through
A query with all of the columns from sys.dm_exec_sessions that aren’t used in the sysprocesses query is provided in Listing 3.
--Listing 3 – Additional columns for sys.dm_exec_sessions SELECT session_id ,client_version ,client_interface_name ,total_scheduled_time ,total_elapsed_time ,endpoint_id ,logical_reads ,is_user_process ,text_size ,language ,date_format ,date_first ,quoted_identifier ,arithabort ,ansi_null_dflt_on ,ansi_defaults ,ansi_warnings ,ansi_padding ,ansi_nulls ,concat_null_yields_null ,transaction_isolation_level ,lock_timeout ,deadlock_priority ,row_count ,prev_error ,original_security_id ,original_login_name ,last_successful_logon ,last_unsuccessful_logon ,unsuccessful_logons ,group_id ,authenticating_database_id FROM sys.dm_exec_sessions
More From sys.dm_exec_connections
When it comes to sys.dm_exec_connections, there are a number of additional columns that are available but not included in the query for sysprocesses. These columns all focus on the connection made to the SQL Server and include information about when the connection was established, the unique value identifying the connection, and the end point associated with the connection. There are others though, such as the following; which provide very useful information for troubleshooting connection issues:
- net_transport: The physical transport protocol in use
- protocol_type: The protocol type for the connection
- protocol_version: The version of the protocol in use
- encrypt_option: Indicates whether encryption is enabled on the connection
- node_affinity: The memory node which the connection has affinity.
- net_packet_size: Network packet size
- client_tcp_port: Port number on the client computer
A query with all of the columns from sys.dm_exec_connections that aren’t used in the sysprocesses query is provided in Listing 4.
--Listing 4 – Additional columns for sys.dm_exec_connections SELECT session_id ,most_recent_session_id ,connect_time ,protocol_type ,protocol_version ,endpoint_id ,encrypt_option ,auth_scheme ,node_affinity ,last_read ,last_write ,net_packet_size ,client_net_address ,client_tcp_port ,local_net_address ,local_tcp_port ,connection_id ,parent_connection_id FROM sys.dm_exec_connections
More From sys.dm_exec_requests
Of the three DMVs, sys.dm_exec_sessions has some of the most useful information that is available but not included with sysprocesses. Like sys.dm_exec_connections, there are columns for ANSI settings, such as ANSI_PADDING and ANSI_NULLS. There is also, deadlock priority, timeout, and a connection id, to make back to sys.dm_exec_connections. But beyond those are the following columns, which provide significant insight into the request and provide ways to analyze the request inflight with statistis from other DMVs:
- start_time: Timestamp when the request started
- plan_handle:Hash value for execution plan in the plan cache
- transaction_id:ID of the transaction in which this request executes. “
- percent_complete:Percentage of work completed for a limited number of operations, including ALTER INDEX REORGANIZE, BACKUP DATABASE, DBCC CHECKDB, and RESTORE DATABASE.
- scheduler_id:ID of the scheduler that is scheduling this request.
- task_address:Memory address allocated to the task that is associated with this request.
- transaction_isolation_level: Isolation level the session is utilizing
- granted_query_memory:Number of pages allocated to the execution of a query on the request.
- group_id:ID of the workload group for resource governor .
- query_hash:Binary hash value calculated on the query and used to identify queries with similar logic.
- query_plan_hash: Binary hash value calculated on the query execution plan and used to identify similar query execution plans.
A query with all of the columns from sys.dm_exec_requests that aren’t used in the sysprocesses query is provided in Listing 5.
--Listing 5 – Additional columns for sys.dm_exec_requests SELECT session_id ,start_time ,plan_handle ,user_id ,connection_id ,open_resultset_count ,transaction_id ,percent_complete ,estimated_completion_time ,total_elapsed_time ,scheduler_id ,task_address ,text_size ,language ,date_format ,date_first ,quoted_identifier ,arithabort ,ansi_null_dflt_on ,ansi_defaults ,ansi_warnings ,ansi_padding ,ansi_nulls ,concat_null_yields_null ,transaction_isolation_level ,lock_timeout ,deadlock_priority ,row_count ,prev_error ,nest_level ,granted_query_memory ,executing_managed_code ,group_id ,query_hash ,query_plan_hash FROM sys.dm_exec_requests
Summary
In this post, we looked at the compatibility view sysprocesses with how the dynamic management view sys.dm_exec_sessions, sys.dm_exec_connections, and sys.dm_exec_requests. These DMVs provide a pretty good match to the information provided from sysprocesses, but not exact. In some cases, the changes make the information easier to digest and in others, the information really had limited use (such as uid). After reading all of this, do you see any reason to continue using sysprocesses? Is there anything missing from this post that people continuing to use the compatibility view should know?
