SLEEPING/AWAITING COMMAND question

[Chad]

I am using SQL Server 2000. In Enterprise Mgr, under Management/Current
Activity, I see a list SPIDs.

I have a multi-tiered .NET web app that uses ADO.NET to connect to the
database. Each time a Stored Proc is called, a new connection is opened, the
data retreieved, and the connection closed and set to Nothing (in VB.NET).

Yet, I see several (15 or so) SPIDs for the user ID that the web app uses to
connect to the database.

The database is local and private, so I know all connections are from me.

What does Status=SLEEPING Command=Waiting Command mean?

What will happen when we get 1000's users on the web app?

Is SQL doing some kind of connection pooling?

[Erland Sommarskog]

Chad (ch**************@unisys.com) writes:

I am using SQL Server 2000. In Enterprise Mgr, under Management/Current
Activity, I see a list SPIDs.

I have a multi-tiered .NET web app that uses ADO.NET to connect to the
database. Each time a Stored Proc is called, a new connection is opened,
the data retreieved, and the connection closed and set to Nothing (in
VB.NET).

Yet, I see several (15 or so) SPIDs for the user ID that the web app
uses to connect to the database.

The database is local and private, so I know all connections are from me.

What does Status=SLEEPING Command=Waiting Command mean?

What will happen when we get 1000's users on the web app?

Is SQL doing some kind of connection pooling?

No, SQL Server is not doing any connection pooling. ADO .Net is. And it
appears that you are not handling it well.

What should happen is that you open a connection in the VB .Net code, and
the first time SqlClient opens a connection to SQL Server. When you close
that connection from the program, SqlClient does not actually close the
connection to the database, but lingers to it for some time, default is
60 seconds. If you don't reconnect within that time, SqlClient will
close the connection for real. But if you reconnect, SqlClient will
reuse that connection. The condition then is that you use the same
connection properties.

But there are ways to make this break down. For instance if you fail to
retrieve all rows and result sets when you use ExecuteReader, and on
top of that do not close the reader, may cause this problem. (I'm a
little foggy here, as ADO .Net is something I only do left-handedly.)

One way to investigate, is to run the SQL Server Profiler with the default
template. If you see an Audit Login event, then there is a new connection.
If you see the even RPC:Completed with the text "sp_reset_connection",
this means that a connection was reused from the pool. If you never see
any sp_reset_connection, then you know you have a problem.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx