Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from serverfault.com Feb 22 '12 at 14:52

This question came from our site for system and network administrators.

    
How does the Oracle DBA know that he's being spammed by a linked server on a SQL machine and not directly the web app? I've seen plenty of IIS apps that do not clean up their connections very well. –  darin strait Feb 22 '12 at 17:15
    
The web app interacts only with the ms sql db, doesn't even have the connection string to the oracle db. –  Akos Lukacs Feb 22 '12 at 17:20

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
Thanks, i will try it when i get back to my work pc. –  Akos Lukacs Feb 22 '12 at 18:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.