#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:

  1. If a T-SQL based method is required for logging and other purposes, the DMV: sys.dm_exec_sessions can be used
  2. 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):

  1. Host Name
  2. User context used to establish the session
  3. Program name, and
  4. 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.

  1. An application might use system functions to obtain this information
  2. 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

image
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:

image
(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.
image
Click on “Column Filters” and filter on the required host process Id (i.e. PID = 2152).

Once done, click “OK” to start the trace.
image
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.

image
image
image

References:

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s