asked on

How to get port number from sys.dm_exec_sessions

We use the query below to show us all the statements currently running longer than a second, the "long running queries". I would like to join this info with the sys.dm_exec_sessions dmv so that I can show what application is running these queries. Usually it's an executable that's running them. They have one of a handful of names and in task manager I can see their ports, so I was hoping I could put that info into this table. For examples we have 3 instances of the same application all running on different ports. SQL however returns the same name over and over just with different host_session_id values. If I could show the port number in a column (such as port 3353) then I could easily see what app is responsible for which query without having to cross reference task manager back and forth.

SELECT r.session_id, 
    SUBSTRING(qt.[text],r.statement_start_offset/2, 
	(CASE WHEN r.statement_end_offset = -1 
		THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
	ELSE r.statement_end_offset 
	END - r.statement_start_offset)/2) AS [statement_executing],
	r.[status], 
	r.wait_type, 
	r.scheduler_id, 
    r.cpu_time as [CPU (ms)], r.total_elapsed_time/1000.0 as [Elapsed Time (sec)], r.start_time, GETDATE() as [NOW], 
    r.reads, r.writes, r.logical_reads,
    DB_NAME(qt.[dbid]) AS [DatabaseName],
    OBJECT_NAME(qt.objectid) AS [ObjectName],
    r.plan_handle,
    qp.query_plan
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE r.session_id > 50 AND r.total_elapsed_time > 1000 -- queries running longer than a second
ORDER BY r.total_elapsed_time desc,r.scheduler_id, r.[status], r.session_id;

Open in new window

Rich Weissler

Not sure if I missed something... but I assume you just want to join dm_exec_sessions and probably dm_exec_connections ?

SELECT r.session_id, 
    SUBSTRING(qt.[text],r.statement_start_offset/2, 
	(CASE WHEN r.statement_end_offset = -1 
		THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 
	ELSE r.statement_end_offset 
	END - r.statement_start_offset)/2) AS [statement_executing],
	r.[status], 
	r.wait_type, 
	r.scheduler_id, 
    r.cpu_time as [CPU (ms)], r.total_elapsed_time/1000.0 as [Elapsed Time (sec)], r.start_time, GETDATE() as [NOW], 
    r.reads, r.writes, r.logical_reads,
    DB_NAME(qt.[dbid]) AS [DatabaseName],
    OBJECT_NAME(qt.objectid) AS [ObjectName],
    r.plan_handle,
    qp.query_plan,
    s.program_name,
    c.client_tcp_port
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
join sys.dm_exec_sessions AS s on r.session_id=s.session_id
join sys.dm_exec_connections AS c on r.session_id=c.session_id
WHERE r.session_id > 50 AND r.total_elapsed_time > 1000 -- queries running longer than a second
ORDER BY r.total_elapsed_time desc,r.scheduler_id, r.[status], r.session_id;

Open in new window

MrVault

ASKER

forgive me but what is a join vs inner join? I tried putting inner join after the plan_handle cross apply and it wigged out (red squigley). If I comment out that line I can get it to work, but I'd prefer to have that plan_handle column.

last question hopefully:

This seems to show the results that have an executable attached to them, ie a host_process_id. It thus filters out the long running queries that don't have one of those. Is there a way to join the results so that it shows all top 10 long running queries and if there is a proc associated it shows the PID and name and if there is not it shows NULL?

ASKER CERTIFIED SOLUTION

Rich Weissler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MrVault

ASKER

I checked and nothing in sys.dm_exec_connections shows the port that it is connecting over. we're using 3353 for our app as this is the only one through the firewall and it's showing some other 4 or 5 digit port. this is for. not sure what that is. I confirmed with wireshark what port we're using too.

MrVault

ASKER

it might be a randomly assigned port that windows uses to communicate between our app and SQL whereas our customers are communicating over the port I was looking for. In task manager if you add the command path column I can see the port listed. Is there a way to show the command path in the results?

Rich Weissler

Oh, I see. The client_tcp_port is the what the application is using to talk to SQL. (You can look at them with 'netstat' in the command prompt. The host_process_id does look like it's coming from the remote system.

Doesn't look like it's 'filtering' on that, as much as, it's defined for each request. If I was trying to pull information on the busiest queries, regardless of whether there was currently an existing connection/session still connected for it, I'd probably pull that from sys.dm_exec_query_stats. Unfortunately, I don't see a way to then tie that back to any connection/process information.

MrVault

ASKER

oh well. thanks anyway. at least you got the join working :)

MrVault

ASKER

got the join working. unfortunately it doesn't show the path or tcp port coming into the server, just to SQL .