[newbe] SQL number of querries / connections limits ?

Hi all!
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my PERL / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by PERL script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well )
but couldn't find much - does any body knows sth about it?
pkThere is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my PERL / ASP aplications seems to behave as if
> it were some.
> PERL script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by PERL script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well ) but
> couldn't find much - does any body knows sth about it?
> pk|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so ther
e
> are definitely practical limitations. Make sure that all your code drops t
he
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk