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?

Answer 1

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

See also this