How can I tell if a host is connecting via multiple spids using one application or running multiple instances of the application itself?

Question

We trace our SQL Server activity for performance issues. Some applications, written in C#, look to have up to 50 individual spids connecting to the DB from a single machine. How can I distinguish if these spids are connecting from multiple individual instances of the application running on the same host machines, or single applications connecting with lots of spids?

`sys.dm_exec_sessions` exposes a `host_name` and a `host_process_id`. This info is supplied by the client side, so not suitable for security-sensitive purposes, but assuming you can trust your clients not to deliberately spoof this info it's usable. — Jeroen Mostert, Apr 06 '22 at 14:21
Adding to @JeroenMostert's comment, each application instance on the same host will have a different process id. The client driver passes the OS PID. — Dan Guzman, Apr 06 '22 at 15:07

score 0 · Answer 1 · answered Apr 06 '22 at 14:46

0

Maybe you can adapt this script to your needs

CREATE TABLE tbl_who2 (
       SPID INT,
       Status VARCHAR(255),
       Login  VARCHAR(255),
       HostName  VARCHAR(255),
       BlkBy  VARCHAR(255),
       DBName  VARCHAR(255),
       Command VARCHAR(255),
       CPUTime INT,
       DiskIO INT,
       LastBatch VARCHAR(255),
       ProgramName VARCHAR(255),
       SPID2 INT,
       REQUESTID INT)
INSERT INTO tbl_who2 EXEC sp_who2
SELECT      *
FROM        tbl_who2
WHERE       DBName = 'your_database'
ORDER BY    DBName ASC
DROP TABLE tbl_who2

How can I tell if a host is connecting via multiple spids using one application or running multiple instances of the application itself?

1 Answers1