#0174 – SQL Server – sys.dm_exec_sessions – Identify a session based on the Windows Process Id

Recently at the office an automated performance testing team came up to me and asked me a very interesting question – Based on the Windows Process Id, how can one determine the number sessions that have been initiated by a process? The question is interesting because their application is a client-server based application, capable of running on a terminal server. Multiple instances of the same application may be running from the same machine, and hence, simply tracing based on the host name would not be sufficient.

Depending upon the requirement, there are two methods to answer this question:

If a T-SQL based method is required for logging and other purposes, the DMV: sys.dm_exec_sessions can be used
If the intention is to troubleshoot or monitor a process (i.e. a one-time activity), the SQL Server Profiler can be used

Using the DMV: sys.dm_exec_sessions

The sys.dm_exec_sessions DMV returns one row per authenticated session on SQL Server. Therefore, the view will return us one record for each session that an application establishes with the SQL Server.

Because the sys.dm_exec_sessions DMV maps to the sys.processes system table (it’s a best practice not to query the system tables directly), the columns returned contain information about the client/host process including (but not limited to):

Host Name
User context used to establish the session
Program name, and
Host Process Id

The column of interest for us is the “host_process_id”. This columns holds the (Windows) process ID of the client program that initiated the session. Please note that this column will show a value of NULL for internal sessions (established by the SQL Server itself).

Using the SQL Server Profiler

The SQL Server profiler, being a dedicated debugging and performance analysis tool, has to have a mechanism to track the processes based on the windows process Id.

Editing a profiler trace allows us to select data columns of our choice (If you are new to Profiler, or need instructions on how to customize a Profiler/SQL Trace, please follow my series of tutorials on the subject at: http://beyondrelational.com/modules/12/tutorials/631/getting-started-with-sql-server-profiler.aspx?tab=tutorials&ts=46&bs=57). The column – “ClientProcessId” captures the (windows) process ID of the application calling SQL Server.

Filtering on the required ClientProcessId column will give us a way to monitor the required session.

Example

Let’s take an example – assume we want to monitor the sessions initiated by the SQL Server Management Studio on my machine.

To begin, get the Process ID (PID) of the required process.

An application might use system functions to obtain this information
If you wish to manually get this information, you can use the Task Manager (Press: Ctrl + Alt + Del, launch the Task Manager)

Based on the screenshot below, you can see that the SSMS process on my test workstation is running under a PID = 2152

Armed with this information, let us go to SQL Server Management Studio, and use the following query to know the open sessions initiated by SQL Server:

SELECT *
FROM sys.dm_exec_sessions
WHERE host_process_id = 2152

Running this query yields 2 records as shown below:

(Remember that the SSMS can open multiple connections to the SQL Server: http://beyondrelational.com/modules/2/blogs/77/posts/11275/the-multiple-connections-of-ssms.aspx)

Now, let us launch the SQL Server profiler. Choose a template of your choice to create the SQL trace.

(If you are new to Profiler, or need instructions on how to customize a Profiler/SQL Trace, please follow my series of tutorials on the subject at: http://beyondrelational.com/modules/12/tutorials/631/getting-started-with-sql-server-profiler.aspx?tab=tutorials&ts=46&bs=57)

As part of the customization, choose to display all columns for editing and choose to display the “ClientProcessID” in the trace output.

Click on “Column Filters” and filter on the required host process Id (i.e. PID = 2152).

Once done, click “OK” to start the trace.

Navigate to the SSMS, create a new window and run any test query. For example,

USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Employee
GO

Look at the output of the sys.dm_exec_sessions (query above) and the Profiler. Note that both show the presence of a new user session. The Profiler also shows the associated activity.