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

theres a lot of talk on the net, do you have to dispose a sqlconnection in each procedure? this is not that debate.
my question is: what effect does closing/disposing a sql connection have on the amount of sleeping SPIDs when
you execute sp_who2 on sqlserver?

e.g. if i have a sqlconnection.close  command execute, will that SPID with status sleeping, remove itself from sp_who2 list?
or will it only happen when sqlconnection.dispose is called?

is there a relationship between the items listed in sp_who2 with status = sleeping, and the sqlconnection.close() / sqlconnection.dispose() being called in code?
jxhardingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Asim NazirCommented:
jxhardingAuthor Commented:
great link thanks,  sp_who3 looks great

doesn't seem to show the relationship unfortunately :(

Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
These two have different effects in different environment, If you have connection pool then on close call connection is not closed in actual and sent back to pool and becomes sleep depending on setings. same happens when some client creat a connection and do not use it for long time or do not close connection properly.
On the other hand Dispose cause the resources to be freed immediately and it is also removed from list

When you close the connection it may be marked as
following artical may help you
http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/dda47834-b1b9-44f4-a470-b343d8b5338b
http://dotnetguts.blogspot.com/2007/06/difference-between-close-and-dispose.html

Thanks

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Asim NazirCommented:
Yep. sp_who3 is a wraper on 2 and only shows sessions that have a current executing request.
khan_webguruCommented:
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();
}
}
}

Open 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
deightonprogCommented:
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!


jxhardingAuthor Commented:
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?


deightonprogCommented:
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.
jxhardingAuthor Commented:
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.

deightonprogCommented:
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.

khan_webguruCommented:
@deighton you are absolutely right I also verified that :)
deightonprogCommented:
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

jxhardingAuthor Commented:
thanks a million, it is now understood much better!!!
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
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
deightonprogCommented:
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.
jxhardingAuthor Commented:
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?
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
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
deightonprogCommented:
IJZ - you are correct
jxhardingAuthor Commented:
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?
deightonprogCommented:
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)
deightonprogCommented:
So in fact I think it is .net that decides to close the pooled connections if they are not being used.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.