[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

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

0
MrVault
Asked:
MrVault
  • 5
  • 3
1 Solution
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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

0
 
MrVaultAuthor Commented:
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?
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Hmm.  Inner join is the default when Join is specified.  (If no join type is specified, Inner is used.)

For the second question, I don't see any reason query would be filtering on host_process_id.  I'll look a little further.

Modified then, to add host_process_id, adding the optional 'inner' to the new joins, (and moving the ','s to make commenting out sections a little easier, I have:
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.host_process_id
    ,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
  inner join sys.dm_exec_sessions AS s on r.session_id=s.session_id
  inner 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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MrVaultAuthor Commented:
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.
0
 
MrVaultAuthor Commented:
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?
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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.
0
 
MrVaultAuthor Commented:
oh well. thanks anyway. at least you got the join working :)
0
 
MrVaultAuthor Commented:
got the join working. unfortunately it doesn't show the path or tcp port coming into the server, just to SQL .
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now