Prev: Microsoft SQL Server 2005 Express Edition Service Pack 3 (KB955706
Next: Figuring sql server version via code
From: tiberiox on 20 Mar 2010 08:39
I have to maintain a rather large vb6 app using ado and the standard sql
client to connect to 2005/2008 database. The application opens an ADO
connection from a 'menu' .exe, then the connection is passed to a number
of activex dlls making up the bulk of the application. When the menu
closes, the connection is closed.

I've noticed that each time the app is run, there are a number of sql
server processes for each instance, say 10-20 processes.. mostly in the
sleeping/AWAITIN COMMAND state. Is this normal?
From: Andrew J. Kelly on 20 Mar 2010 10:26
That depends totally on your app. SQL Server does not make new connections
on its own so the app must be doing it. Probably just poor design on the app
part. But another thing to look into is connection pooling. If each app is
run from a separate machine and those machines are setup for connection
pooling to SQL Server you will get a pool of connections for each machine.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

<tiberiox(a)hotmail.com> wrote in message
news:ho2fm3$j79$1(a)news.eternal-september.org...
> I have to maintain a rather large vb6 app using ado and the standard sql
> client to connect to 2005/2008 database. The application opens an ADO
> connection from a 'menu' .exe, then the connection is passed to a number
> of activex dlls making up the bulk of the application. When the menu
> closes, the connection is closed.
>
> I've noticed that each time the app is run, there are a number of sql
> server processes for each instance, say 10-20 processes.. mostly in the
> sleeping/AWAITIN COMMAND state. Is this normal?

From: Dan Guzman on 20 Mar 2010 11:04
> I've noticed that each time the app is run, there are a number of sql
> server processes for each instance, say 10-20 processes.. mostly in the
> sleeping/AWAITIN COMMAND state. Is this normal?

It seems that the intended behavior is that each ActiveX will use the same
connection since you are passing the connection to each ActiveX object. But
if you see many connections from the same client process, then the app may
be misbehaving.

ADO is a strange beast in that it will open additional SQL connections
behind the scenes when needed. For example, if you issue a query and don't
consume the results, ADO will open an additional SQL connection when you
execute another query using the same ADO connection object. Furthermore,
ADO connections are pooled so the number of SQL connections from a single
client app instance reflects the high-water mark of connections used.

Rather than passing the ADO connection object, you might try just passing
the connection string and let each ActiveX object open/close it's own
connection. Connection pooling will make the subsequent opens fairly
lightweight. Also, use SET NOCOUNT ON unless you need that functionality.
This will suppress DONE_IN_PROC messages (rowcounts) that can cause issues
with classic ADO applications.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<tiberiox(a)hotmail.com> wrote in message
news:ho2fm3$j79$1(a)news.eternal-september.org...
> I have to maintain a rather large vb6 app using ado and the standard sql
> client to connect to 2005/2008 database. The application opens an ADO
> connection from a 'menu' .exe, then the connection is passed to a number
> of activex dlls making up the bulk of the application. When the menu
> closes, the connection is closed.
>
> I've noticed that each time the app is run, there are a number of sql
> server processes for each instance, say 10-20 processes.. mostly in the
> sleeping/AWAITIN COMMAND state. Is this normal?

From: ralph on 20 Mar 2010 13:09
On Sat, 20 Mar 2010 13:39:34 +0100, "tiberiox(a)hotmail.com"
<tiberiox(a)hotmail.com> wrote:

>I have to maintain a rather large vb6 app using ado and the standard sql
>client to connect to 2005/2008 database. The application opens an ADO
>connection from a 'menu' .exe, then the connection is passed to a number
>of activex dlls making up the bulk of the application. When the menu
>closes, the connection is closed.
>
>I've noticed that each time the app is run, there are a number of sql
>server processes for each instance, say 10-20 processes.. mostly in the
>sleeping/AWAITIN COMMAND state. Is this normal?

It can happen, so yes this is kind of normal, though perhaps something
that can be avoided or at least mitigated. Often times it is merely an
Apps architecture, however, for some queries SQL Server will create
additional 'connections' for its own use.

"Connection Mangement" or the whole series of events and processes
that occur within the joint psychosis of an OLE DB Provider and an OLE
DB Service, while not particularly complex, is too lengthy to be
detailed here. There are a number of good ADO books, and lots of
articles on the web. I suggest you brush off your browser skills, get
your reading cap on, and get busy. <g>

In the meantime to help you get started.

What you're doing is correct. Your application should create one
ADO.Connection object. But what you should also be doing is helping
out the connection pool by opening a connection only when you need
one, and closing the connection when you don't need it any more.

That is all your requests should look something like this ...
' some procedure
oADOConnection.Open
..
oADOConnection.Execute("<some query>")
' or
Dim rs As ADODB.Recordset
Set rs = oADOConnection.Execute("<query>")
' do stuff
rs.Close
...
oADOConnection.Close
What you do inbetween isn't important, just that you bracket their use
with .Open/.Close.

If you are creating recordsets that are only being used for storage,
or are being used for lengthy massage consider disconnecting them.

Also as noted above, SQL Server will often create additional 'internal
connections' for its own use for some queries. So when using a SQL
Server management/analysis tool the 'connections' you see may not be a
reflection of the 'ado connection' pool. You seldom have much control
over that, but if you can at least insure your application is using
the connection pool as proficiently as possible.

-ralph
From: ralph on 20 Mar 2010 13:24
On Sat, 20 Mar 2010 10:04:28 -0500, "Dan Guzman"
<guzmanda(a)nospam-online.sbcglobal.net> wrote:


>
>Rather than passing the ADO connection object, you might try just passing
>the connection string and let each ActiveX object open/close it's own
>connection.

This will have no effect except to add additional over-head.

The key is help ADO with its connection management by signaling when
connections are needed and when they are not.

>Connection pooling will make the subsequent opens fairly
>lightweight.

Yes, if the OP uses one ADO.Connection object and thus is certain of
chewing on the same pool. (Actually ADO/OLE DB runs out-of-proc so it
is likely it will recognize the same connection string and present the
same pool anyway, but again no reason to ask ADO to go check out the
plumbing all over again, or worse confuse it into running additional
pools.)

> Also, use SET NOCOUNT ON unless you need that functionality.
>This will suppress DONE_IN_PROC messages (rowcounts) that can cause issues
>with classic ADO applications.

Good advice.

-ralph
 |  Next  |  Last
Pages: 1 2 3
Prev: Microsoft SQL Server 2005 Express Edition Service Pack 3 (KB955706
Next: Figuring sql server version via code