SQL Server ADO/ASP Application and Connections

[rob]

Hello,

Newbie of sorts. Been reading up on best practices though.

I'm trying to optimize performance to the SQL DB vs. the Web server and
an ASP application. I've read that you're supposed to open late/close
early which makes sense. In the past I'd created a connection and thrown
many queries against the same connection. Now, I'm working with a
recordset object which is returned and the ActiveConnection is closed
after each query. My impression was this was faster ... not sure though.
That might be more of an ASP question but let me elaborate on the SQL
server side:

I was using the SQL Profiler to try to watch connections. I wasn't even
sure what fields to show, but I was watching SPID's and Event Classes
for the user in question. I'd see Audit Login and Audit Logouts, but
wasn't sure how they matched up.

- When I had a single connection object with many queries against them,
there'd be multiple SPID's showing up as Login and Logout.

- When I had multiple connections each with a single query, there'd only
be a single SPID wich each sql query being shown.

My initial assumption was that each SPID was a connection, but I'm
thinking that's not quite right.

Is there a way to watch connections come and go? I'm just using an
ordinary ODBC connection for now.

Any help would be appreciated enormously. Many thanks,

rob

[Erland Sommarskog]

rob (ro*@pleaseremovespamcherny.com) writes:

I'm trying to optimize performance to the SQL DB vs. the Web server and
an ASP application. I've read that you're supposed to open late/close
early which makes sense. In the past I'd created a connection and thrown
many queries against the same connection. Now, I'm working with a
recordset object which is returned and the ActiveConnection is closed
after each query. My impression was this was faster ...
Faster and faster... Connecting, disconnecting and reconnecting is fairly
expensive. But so is also keeping a connection alive, on which there
is no traffic. Particularly in web applications where you can have many
users who are logged on at the same time, but who are inactive most of
the time.

To get the best of both worlds, ADO and ADO.Net uses connection pooling,
which means that when you disconnect, they keep the connection alive
for a while, and if you request an new connection, you will get back
the old one. It is tidied up, so it looks new and fresh to you.
- When I had a single connection object with many queries against them,
there'd be multiple SPID's showing up as Login and Logout.
This can happen because ADO and ADO .Net might open a second connection
behind your back, if there is data pending on the first connection.
- When I had multiple connections each with a single query, there'd only
be a single SPID wich each sql query being shown.

My initial assumption was that each SPID was a connection, but I'm
thinking that's not quite right.
Your initial assumption was correct. A spid is a connection. At least
as far as SQL Server is concerned. But the client library blurs the
issue with connection pooling and opening extra connections without
asking you.
Is there a way to watch connections come and go? I'm just using an
ordinary ODBC connection for now.

Yes, that would be the audit login and logout events. Also, if you are
tracing RPC calls, you would see calls to sp_reset_connection. This
happens when a connection is reused for connection pooling.

If you are using plain ODBC, I don't think connection pooling is
available, nor will ODBC open extra connections for you.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp