what is relation between closing/disposing a sqlconnection, and the SPIDs in SQL with status = sleeping when executing sp_who2

Nice article here: http://sqlserverplanet.com/dba/using-sp_who2/

great link thanks,  sp_who3 looks great

doesn't seem to show the relationship unfortunately :(

Yep. sp_who3 is a wraper on 2 and only shows sessions that have a current executing request.

Difference between Close() and Dispose() Method

SpreadsheetGear for ASP.NET and WinForms
Excel Reporting, dashboards from Excel charts and ranges, Windows Forms spreadsheet controls, Excel compatible charting, the fastest and most complete Excel compatible calculations and more.
Difference between Close() and Dispose() Method

Close() Vs Dispose Method

The basic difference between Close() and Dispose() is, when a Close() method is called, any managed resource can be temporarily closed and can be opened once again. It means that, with the same object the resource can be reopened or used. Where as Dispose() method permanently removes any resource ((un)managed) from memory for cleanup and the resource no longer exists for any further processing.

Example showing difference between Close() and Dispose() Method:

 

using System;
using System.Data;
using System.Data.SqlClient;
public class Test
{
private string connString = "Data Source=COMP3;Initial Catalog=Northwind;User Id=sa;Password=pass";
private SqlConnection connection;
public Test()
{
connection = new SqlConnection(connString);
}
private static void Main()
{
Test t = new Test();
t.ConnectionStatus();
Console.ReadLine();
}
public void ConnectionStatus()
{
try
{
if(connection.State == ConnectionState.Closed)
{
connection.Open();
Console.WriteLine("Connection opened..");
}

if(connection.State == ConnectionState.Open)
{
connection.Close();
Console.WriteLine("Connection closed..");
}
// connection.Dispose();

if(connection.State == ConnectionState.Closed)
{
connection.Open();
Console.WriteLine("Connection again opened..");
}
}
catch(SqlException ex)
{
Console.WriteLine(ex.Message+"\n"+ex.StackTrace);
}
catch(Exception ey)
{
Console.WriteLine(ey.Message+"\n"+ey.StackTrace);
}
finally
{
Console.WriteLine("Connection closed and disposed..");
connection.Dispose();
}
}
}

Select allOpen in new window

In the above example if you uncomment the "connection.Dispose()" method and execute, you will get an exception as, "The ConnectionString property has not been initialized.".This is the difference between Close() and Dispose().

Hope this will help you.

Regards,

Asif Ahmed Khan

Are you accessing SQL server via sites/applications written in .net?

.net utilises connection pooling by default.  Let's say you open a connection to your DB for the first time, a connection is opened, then you close or dispose of the connection, that is fine, but the system hangs on to the connection for reuse in a pool.  
With connection pooling, an open connection is retained for when you next need it.  The system will keep a defined number of pooled connections.  This behaviour gives advantages, but you can turn it off with pooling=false in the connection string.  Don't turn off connection pooling without assessing what you are doing first, it might well add an overhead of reopening closed connections.

As far as the SQL server is concerned, a connection closed and disposed of in .net can still be open.

You should dispose of your connection once it is not needed.  Dispose will also .close the connection, so .close followed by .dispose is superfluous to requirements (but not harmful is my understanding).

Never use a connection then allow it to drop out of scope without disposal.  You risk leaving dangling connections that will not close until the garbage collector kicks in, or longer!

thanks everyone

because pooling is enabled by default, and we dont set it to false anywhere, we are using connection pooling.
it is a web environment

does this make sense:
people log onto website, different SPIDs are created, each SPID is a pool(?)
the webpages check if there are any sleeping SPIDs, and uses that as a pool
if one page does have sqlconnection.dispose, and it is called, then one SPID is removed
the other webpages then dont see that connection because it is killed, but what if they were currently using that SPID at the same time that it was disposed by a person on another webpage?

are you sure the SPID goes after dispose?  I thought that dispose returned the connection to the pool, for reuse by another process.  You'd really have to check that.

Usage of pooled connections is consecutive rather than concurrent, if 4 pages are using connections simultaneously, then there has to be 4 connections.  If one of these finishes and disposes the connection, then the next page to come along picks up that closed connection.  If a fifth page comes along, 5 connections.  If a connection is closed, goes to the pool, then leaves the pool, a new one is opened next time.

pooling manages the connections, closing unused connections eventually and balancing the number in the pool.  Pooling looks after itself, and sounds right for what you are doing.  Are you being quizzed on this?  As I was recently!  I was asked why I wasn't closing connections, lol.

definitely not sure about anthing now :)


i think you summed it up here, if this is correct
"if 4 pages are using connections simultaneously, then there has to be 4 connections.  If one of these finishes and disposes the connection, then the next page to come along picks up that closed connection.  If a fifth page comes along, 5 connections"

we're trying to find out where all the sleeping SPIDs are coming from
went through every single page and there is no sqlconnection.open which does not get closed.

ok did a test, each connection is a SPID - I opened 20 connections and I got 20 SPIDS, then I disposed of the 20 connections and I still had 20 SPIDs

If I re-run that process, I get no new SPIDS, I pick up the old ones from the pool.  That is what is expected to happen.

If I leave the connections, they will time out and the SPIDS will go.

@deighton you are absolutely right I also verified that :)

so you have excess connections?  How many are you ending up with?  
Could a routine be exited before .close or .dispose gets hit?
If you are on version 2005 or higher, consider using the USING keyword so that when the variable goes out of scope, it will be disposed.

Errors with Try Catch are a way that a routine can be exited without closing a connection, you can put in Finally that disposes the connection (or utilise USING) to get round the risk.

USING has c# equivalent I believe

thanks a million, it is now understood much better!!!

It is handled automatically in application pool for ASP.NET so you can check following link for more details:

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.80).aspx

Thanks

set max pool size to 5 as an experiment, if sleeping SPIDS is way over that, then it is almost certain you are failing to close/dispose of a connection.  I have seen thousands of connections resulting from an unclosed connection error.

we have about 40 sleeping SPIDs on top of the 50 essential SQL SPIDs

will recheck for anything out of try catch thanks, but we did put at least 99% of everything in "finally" section.
didnt dispose of everything though, just closed everything

looks like its just a matter of waiting for sql server to close the sleeping connections after a while?

If you set your pool size too small say 5 then 6th concurrent  call will wait for any other request to be finished and return to pool before be available to new request

IJZ - you are correct

this is related, but perhaps I must ask in a new thread?
apologies if i should i ask in a  new thread, please indicate and i will do so

when does SQL server dispose of the SPID's who have a state of sleeping?
it is obviously not .net which manages this, so it has to be SQL server?

I think .net holds client side connections on the application server.  The connections are pooled by default on the application server (client side connections).  Eventually pooled connections that are not used time-out.

You can test this for yourself, write an application that opens and closes a connection when you say hit a button, you will see the connection if you run sp_who2 as before, if you re-run no new connection will appear - after a time the connection will close itself (if connection pooling is not switched off)

So in fact I think it is .net that decides to close the pooled connections if they are not being used.