Re: SLEEPING/AWAITING COMMAND question

Chad (chad.dokmanovich@xxxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... Create a Connection object and pass in a ConnectionString to ... manage the database table you reference. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: (Newbie)Application Roles
    ... level: the database itself. ... SQL Server accommodates these needs through the use of application ... the user's connection through a specific application. ... the connection permanently loses all permissions applied to ...
    (microsoft.public.sqlserver.security)
  • Re: Help! Set up Windows Group to access application
    ... The connection string for a trusted connection is: ... --add login as database user ... SQL Server MVP ... If you have troubles with finding your ways in Crystal Reports, ...
    (microsoft.public.sqlserver.security)
  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Visual Studio but not to VS 2003. ... Create a Connection object and pass in a ConnectionString to address ... manage the database table you reference. ... Yep, my latest book can help too, but mostly if you're targeting SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)