Get statistics of open connection to linked servers

Question

How can I get information about connections from my MS SQL 2008 server to linked servers (actually it's an Oracle db)?

Things like number of currently open connections, and history of opened and closed connections, if these informations are stored somewhere. Who used that connection (login name, application, process id, anything)?

Background: there is a web app, and an Oracle linked server, and the Oracle dba says that our MS Sql server is spamming hundred of connections to their server. Currently our app is only accessible for a few users, and only doing simple selects from a view... So I can't really imagine that caused hundreds of connections.

Also what could cause excessive amounts of open connection form the MS Sql db to the Oracle db? Or what could keep the connection open for too long time? Can Sql Server Management Studio go wild, and open unnecessary connections?

Edit:

One thing to add: We are using OpenQuery to query the Oracle db, so the Oracle db does the heavy lifting (selecting data from a pretty big view). The documentation says nothing about having to explicitly close the connection, is there any known issue regarding this?

Answer 1

SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For whatever reason, the offending app didn't report any errors or query time outs (from it's perspective), but we did have to recycle the IIS application every night or it would eventually eat up all of the RAM on the IIS server.

I would start by running my usual "find any connection that is waiting" query:

SELECT 
@@servername 'ServerInstance',es.session_id,er.blocking_session_id
,es.status
,es.login_name,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name,er.command 
,es.reads,es.writes
,es.cpu_time
,er.wait_type,er.wait_time,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' 
    END AS transaction_isolation_level 
,last_request_start_time
,last_request_end_time
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2, 
    (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2 
        ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text   
,ph.query_plan
FROM sys.dm_exec_connections ec 
  LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
  LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
  OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
  OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID 
-- AND es.status = 'running'
AND (
es.status != 'Sleeping'
OR wait_time > 0
OR es.session_id in (select blocking_session_id from sys.dm_exec_requests where  blocking_session_id != 0)
)
ORDER BY es.session_id

I'd look for any sort of wait type like "OLEDB" (which should indicate linked server activity, and possibly other things) and try to tie those back to code that accesses the oracle server. I'd also pay attention to the last request start and end times.