Server resource and open database connections

[dave]

If a host suggests poor performance is possibly due to 'open database'
connections and Im fairly sure that Ive closed everything - what is an easy
method to test for connections that havent been cleaned up? (assuming 1000's
of pages)
Or, is it just a blow off by the host for their server problem?

Is it easier to investigate at the server end? if so what can I suggest?

Cheers.

[Bob Barrows]

dave wrote:

> If a host suggests poor performance is possibly due to 'open database'
> connections and Im fairly sure that Ive closed everything - what is
> an easy method to test for connections that havent been cleaned up?
> (assuming 1000's of pages)
> Or, is it just a blow off by the host for their server problem?

Probably not. There's been lots of documented experiences where the failure
to close AND DESTROY all ADO objects has caused intermittent server hangs.

>
> Is it easier to investigate at the server end? if so what can I
> suggest?
>
> Cheers.

Depends on your backend database.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[dave]

msSQL Server


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eb2TUWF$DHA.3232@TK2MSFTNGP10.phx.gbl...

> dave wrote:

> > If a host suggests poor performance is possibly due to 'open database'
> > connections and Im fairly sure that Ive closed everything - what is
> > an easy method to test for connections that havent been cleaned up?
> > (assuming 1000's of pages)
> > Or, is it just a blow off by the host for their server problem?

>
> Probably not. There's been lots of documented experiences where the

failure

> to close AND DESTROY all ADO objects has caused intermittent server hangs.
>

> >
> > Is it easier to investigate at the server end? if so what can I
> > suggest?
> >
> > Cheers.

>
> Depends on your backend database.
>
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows [MVP]]

Well, you can look for open connections in Enterprise Manager, or by using
the sp_who system stored procedure, but this may not be foolproof. There may
be connections that are legitimately left open due to connection pooling
rather than the failure to close and destroy them. One clue is if there are
connections that cannot be killed without restarting the SQL Server.

I just noticed: you said the " ... host suggests poor performance is
possibly due to 'open database'
connections ... ". This is different from what I was talking about, where
the failure to destroy connections caused server hangups. Is the host
instead talking about your use of Connection object in Application or
Session? If so, he is right on the mark. Best practices for IIS include the
maxim "open ADO connections late and close them early". Using a connection
or recordset object in Application or Session forces all use of that object
to be serialized - one at a time. This can kill performance. Connections
should be opened on each page just before they are needed, and closed and
destroyed immediately after their task is done. It's OK to store a
connection string in Application. But ADO objects should not be stored in
Application or Session without using the makfre.bat batch file to change a
registry setting to make the ADO objects free-threaded. This should NOT be
done if Jet databases are being used on the server.

So, is this what your host is talking about?

Bob Barrows

dave wrote:

> msSQL Server
>
>
> "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eb2TUWF$DHA.3232@TK2MSFTNGP10.phx.gbl...

>> dave wrote:

>>> If a host suggests poor performance is possibly due to 'open
>>> database' connections and Im fairly sure that Ive closed everything
>>> - what is
>>> an easy method to test for connections that havent been cleaned up?
>>> (assuming 1000's of pages)

>>
>> Depends on your backend database.
>>
>> Bob Barrows
>>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[dave]

No, all database connections occur when needed - not as application objects
(yuk :)
Its just a general perception of slowness by users and the host suggested
checking all connections were destroyed.
Just going through the motions to satisfy them and me.
Ill check out EM thanks Bob




"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:e7ckFdH$DHA.624@TK2MSFTNGP11.phx.gbl...

> Well, you can look for open connections in Enterprise Manager, or by using
> the sp_who system stored procedure, but this may not be foolproof. There

may

> be connections that are legitimately left open due to connection pooling
> rather than the failure to close and destroy them. One clue is if there

are

> connections that cannot be killed without restarting the SQL Server.
>
> I just noticed: you said the " ... host suggests poor performance is
> possibly due to 'open database'
> connections ... ". This is different from what I was talking about, where
> the failure to destroy connections caused server hangups. Is the host
> instead talking about your use of Connection object in Application or
> Session? If so, he is right on the mark. Best practices for IIS include

the

> maxim "open ADO connections late and close them early". Using a connection
> or recordset object in Application or Session forces all use of that

object

> to be serialized - one at a time. This can kill performance. Connections
> should be opened on each page just before they are needed, and closed and
> destroyed immediately after their task is done. It's OK to store a
> connection string in Application. But ADO objects should not be stored in
> Application or Session without using the makfre.bat batch file to change a
> registry setting to make the ADO objects free-threaded. This should NOT be
> done if Jet databases are being used on the server.
>
> So, is this what your host is talking about?
>
> Bob Barrows
>
> dave wrote:

> > msSQL Server
> >
> >
> > "Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
> > news:eb2TUWF$DHA.3232@TK2MSFTNGP10.phx.gbl...

> >> dave wrote:
> >>> If a host suggests poor performance is possibly due to 'open
> >>> database' connections and Im fairly sure that Ive closed everything
> >>> - what is
> >>> an easy method to test for connections that havent been cleaned up?
> >>> (assuming 1000's of pages)
> >>
> >> Depends on your backend database.
> >>
> >> Bob Barrows
> >>

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bob Barrows [MVP]]

dave wrote:

> No, all database connections occur when needed - not as application
> objects (yuk :)
> Its just a general perception of slowness by users and the host
> suggested checking all connections were destroyed.
> Just going through the motions to satisfy them and me.
> Ill check out EM thanks Bob
>

I doubt that would affect performance, unless the orphaned connections were
leaking memory - however I would think that would cause a server crash, not
slow performance.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.