Connection Pooling, ADO.NET, SPIDs

  • [アーティクル]

If you have pooling issues with ADO.NET (leaking connections, pool size exceeded, etc...) one way to slice and dice these with SQL 2005 is to use the SQL 2005 system DMVs.

 

You can locate leaked connections like so:

 

 select 
session_id, 
datediff(s,last_read,getdate()) as SecondsSinceLastRead,
datediff(s,last_write,getdate()) as SecondsSinceLastWrite,
(select text from sys.dm_exec_sql_text(dec.most_recent_sql_handle)) as LastSqlStatment
from sys.dm_exec_connections dec

 

The idea being if the connection is properly pooled then last_read and last_write should always be active.  Leaked connections will go into garbage collector and sit there for a while still open but not reading and writing.

 

Next suppose you find leaked ones, how can you correlate these with client code?  Here is one way:

 select 
session_id as Spid, 
(select login_name from sys.dm_exec_sessions where session_id=dec.session_id) as UserName,
datediff(s,last_read,getdate()) as SecondsSinceLastRead,
datediff(s,last_write,getdate()) as SecondsSinceLastWrite,
text as LastSqlStatment
from sys.dm_exec_connections dec
cross apply sys.dm_exec_sql_text(dec.most_recent_sql_handle)

 

This will dump out each SPID and also the last TSQL statement to run on the SPID.  Then search your source code for the TSQL to locate which part of your code leaked the connection.  Not a perfect solution but should get you pretty close.

 

Suppose you are really stumped and cannot correlate the TSQL.   You can get a full memory dump of the client process (I'm assuming like an ASP.NET application here) and then dig through this using the sos debugger extension.  I'll post on this later.

Comments

  • Anonymous
    May 09, 2008
    .NETVS2008SP1Betaand.NETFramework3.5SP1BetaisnowavailableConnectionPooling,ADO.NET,...

  • Anonymous
    May 09, 2008
    .NET VS2008 SP1 Beta and .NET Framework 3.5 SP1 Beta is now available Connection Pooling, ADO.NET, SPIDs