With SQL Server 2005+, it is very easy to view the specifics of connection information. This is very useful because when troubleshooting slowdowns. Luckily there are a few dynamic management views that provide insight into connection and session information.

The following query groups the connections according the program that is connected to SQL Server. This information can be spoofed however using a connection string. When running this query, you will find how important it is to add the application name to the query string. The query also shows the number of connections opened by each application.

-- By Application
SELECT
	 CPU			= SUM(cpu_time)
	,WaitTime		= SUM(total_scheduled_time)
	,ElapsedTime	= SUM(total_elapsed_time)
	,Reads			= SUM(num_reads)
	,Writes			= SUM(num_writes)
	,Connections	= COUNT(1)
	,Program		= program_name
FROM sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
	ON ses.session_id = con.session_id
GROUP BY program_name
ORDER BY cpu DESC

This next query groups the same information by user:

-- Group By User
SELECT
	 CPU			= SUM(cpu_time)
	,WaitTime		= SUM(total_scheduled_time)
	,ElapsedTime	= SUM(total_elapsed_time)
	,Reads			= SUM(num_reads)
	,Writes			= SUM(num_writes)
	,Connections	= COUNT(1)
	,[login]		= original_login_name
from sys.dm_exec_connections con
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = con.session_id
GROUP BY original_login_name

By using sp_who3, you can also find the exact statements running, along with more detailed information.

• sql server active connections
• sql server view active connections
• sql server view connections
• sql server 2008 active connections
• sql server see active connections