SQL Sleeping Connections

[trent]

Hi All,

Our Nettalk Web Server seems to create a lot of SQL 'Sleeping Connections' which then causes our desktop and web apps to not load the tables tables that have these open connections. Is there any way to clear our or stop these sleeping connections so that the SQL Server Instance does not need to be restarted every few days?

Regards,
Trent

[kevin plummer]

Hi Trent,

the sleeping connections are ok and actually make SQL work faster re-using a connection rather than closing them and creating new connections (they do close after a period of inactivity). It's sort of like a Thread Pool.

The problem you are *probably* having is dead-locked connections. You can see this in the view process's screen columns blocking and blocked.

Bruce added a method in NT7 called SetSQLTimeout(filename,net:on/net:off)

You may need to call this method if you are doing a lot of file access in handcode.

HTH's

Kevin

[trent]

Hi Kevin,

Thanks for the reply. I don't have NT7 but I see that it is in NT6 as 'experimental'. Will give it a go.

Regards,
Trent

[trent]

Hi Kevin,

I've just gotten around to testing the SetSQLTimeout method. Would you be able to guide in the right direction of implementing this? Do I have to call this method for every table every time I am accessing the tables in hand code? Can I set this once on each table being used at WebServer init?

Regards,
Trent

[Bruce]

you use it to "wrap" your actual file access. for example (in NT7);

    self.SetSqlTimeout(File,net:On)
    Add(file)
    self.SetSqlTimeout(File,net:Off)

or

    self.SetSqlTimeout(File,net:On)
    put(file)
    self.SetSqlTimeout(File,net:Off)

you can control the actual timeout period in the WebHandler, ProcessLink method, before parent call.

self.Site.SqlTimeout = 10000

10000 is the default value (ie if the line above is omitted then 10000 is used.)

The template already generates all the above around generated file access code.

Cheers
Bruce

[trent]

Awesome thank you Bruce! Does this work with NT6?

[kevin plummer]

try it in NT6 - I think it was added just before NT7.

If not here is some prop:sql I use.

      TAGGED{Prop:SQL} = 'Set Lock_Timeout 10000'      !wait 10 seconds
      TAGGED{Prop:SQL} = 'Select * from TAGGED Where TAG_SessionId = <39>' & clip(p_web.SessionId) & '<39> and TAG_Tagged = 1 and TAG_Module = <39>POT<39>'
      TAGGED{Prop:SQL} = 'Set Lock_Timeout -1'      !

[Bruce]

Hi Trent,

yes, I think it's in very late builds of NT6.

cheers
Bruce

[trent]

Do I need to use this on:

- access:TABLE.Fetch
- relate:TABLE.Delete
- and any {prop:sql} statements I'm running as well?

Regards,
Trent

[kevin plummer]

That is the safest option to avoid the dreaded lock. The other option is to just track down the code that is locking but that can be really hard and it may take a few goes to plug all the holes.

[trent]

Ok cool, thanks Kevin and Bruce.

[Poul]

Since Set Lock_Timeout is connection based, if you set it once early it should persist for the life of the connection.

For discussion:  Does it not make sense to set it once as you make the initial connection and never set it back to -1. 
When would you Not want  it to timeout. (By default), You want it to timeout before the web request times out.

Performance issues due to needless blocking are the worst,  And dangling Netttalk threads that have lost their way to
the original client request (because the user gave up or timed out), leave  the system in an unhealthy state.
So an abandoned thread that is still blocked, could in-turn block others...  (in my experience, the same user who
is simply trying again, and again and again...<sigh>)

thoughts?

[Bruce]

>> And dangling Netttalk threads that have lost their way to the original client request (because the user gave up or timed out),

I'm not sure this statement is accurate. The thread creates the response, and passes that back to the Server thread for passing to the client. So the thread will complete even if the client has disconnected.
If this wasn't so, then the ram consumed by a server would quickly leak away until the process crashed.

(It certainly is possible for a thread to never end, but the above would not be the cause of it.)

cheers
Bruce

[kevin plummer]

> Since Set Lock_Timeout is connection based, if you set it once early it should persist for the life of the connection.

I originally tried to set it in the process link of the webhandler but it did not "seem" to work. Maybe there is a better way to implement with some experimentation but finding the steps to replicate is part of the problem.

[Poul]

>>The thread creates the response, and passes that back to the Server thread for passing to the client. So the thread will complete even if the client has disconnected.

My dangling reference is the case where the timeout is -1,  the thread/connection to SQL would persist until the blocking is resolved, or the connection is killed. The SQL connection that is BLOCKED - remains, um,  dangling!?  "Sleeping Connections" are fine, But ones that are BLOCKED and persist for any length of time can create a scenario for continued degradation.  In these scenarios the users (or the Browser) will give up, or try again...

MS says "At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection." My experience is mainly with NT4 and in practise I set the timeout in two places either when i make my initial connection or sometimes within a stored procedure.

IN NT7 IO statements are preceeded explicitly with a LOCK_timeout, So you address the main issue, but you ALSO EXPLICITLY Set it after your IO to the default(-1) not The Setting that existed before you changed it. Nothing wrong with that so long as one understands the impact. To me this overrides the implied behaviour from MS as now "ALL IO statements must EXPLICITLY set the TIMEOUT, because NETTALK is always resetting the behaviour for the connection", but not for a good reason (to me) because all Nettalk generated IO EXPLICITLY SET a good timeout anyway. (not necessarily true for any other code) 

I guess a CAVEAT in the DOCS that all hand code and non-nettalk MSSQL I-O should explicitly set the lock_timeout, would suffice.

In my mind, By default, threads that quietly die are better than threads that quietly live a long life, contributing to the additional Blocking. Hence i believe -1 is the least desireable default behaviour, it should be left at what was last intended. (in nettalks defence for some reason it intends -1 <g>)
 
KEVIN, the basic BLOCK behavior can be tested using Query analzyer, BEGIN a TRANSACTION, select/update a resources with LOCK, test your app accessing that resource. (ROLLBACK when done).  SQL Profiler is handy, it will clearly show each time the SET LOCk_TIMEOUT setting.