Support Basics: How to track a SQL Session ID back to a Windows Process ID? (Snow Products)

• The purpose of this article is to show the process of identifying a SQL Session ID in Activity Monitor and then mapping this back to the corresponding Windows process that is running in a customer environment.
• This may be required for Support to be able to better understand how Snow Windows services are interacting with a given SQL instance and aid in troubleshooting. 
• For example, this process could be used to locate a service that was causing a deadlock on the Snow License Manager database.

Steps to Follow

• Access SQL Activity Monitor by right-clicking on the SQL instance in Object Explorer: 

• You will be presented with a page similar to the below: 

• Select Processes to expand the view: 

• If we take the first example from above, which is session ID 51, we can see this is running via the Snow License Manager database.
• The connection is coming from the Snow License Manager application server (SRV-SLM-001), but we are not shown further information such as what has opened this connection. 
• We can identify this by querying existing system tables in SQL, via the following script:
  NOTE: This script has Aliases outlined for the column names for ease of use

SELECT 
session_id AS 'SQL Session ID',
host_name 'Host Name (Originator)',
host_process_id AS 'Windows Process ID',
program_name AS 'Application',
status AS 'Status',
login_name AS 'Login' 
FROM sys.dm_exec_sessions
WHERE host_name IS NOT NULL
ORDER BY session_id ASC

• Below shows an example output: 

• If we then relate this output to the SQL Activity monitor, we can see Session ID 51 as mentioned earlier and that this is running on the Snow License Manager Application Server.
• If we then take the 'Windows Process ID' (2444), we can then use this on the application server through task manager to validate what service this is.
• As you can see from the below, this connection is coming from the Snow Update Service:

 NOTE: The PID is not shown as default in Task Manager and may need to be added through the column selector.