Posted by Erland Sommarskog on 01/26/06 00:22

ppateel (ppateel@gmail.com) writes:
> First I would like to apologize for cross posting in three groups as
> I did not know which one would be the appropriate group. If some one
> points me to the correct one I will use that in the future.
> I am trying to findout the scalabilty of an user written extended
> store procedure. I have created a dll using a C program
> (modified/plagarized from some of the examples) . The main function of
> this extended SP is to act as a passthru to connect to an third party
> ODBC driver. All this is supposed to do is take the passthru sql
> statement, userid, passsword and connect to a remote server (IBM 3090
> Mainframe in our case) using a system ODBC connection and pass the
> returned result set back to the stored procedure calling this extended
> SP. I am trying to find out the answers for the following questions.
> 1. What are the limitations of this approach.
> 2. What would happen say if 2,000 concurrent calls per minute are made
> to the extended SP from the web app.
> 3. What would happen if this continued for say 4 hours. Will the memory
> usage increase to point that will cripple the server assuming there is
> no memory leak in the dll.
> 4. Are there any connection pooling concerns that I should pay
> attention to specifically from an Extended SP point of view.
> 5. Apart from compiling the dll using the "MultiThread" option should I
> be using other options to make sure the dll is threadsafe.

I don't have own any experiece of this situation, but my gut reaction
is that the memory pressure is very much related to much data that
the queries bring back. As long as they return on average 100 rows
with 10 columns, I would worry. But if your 2000 concurrent calls
brings back 100 MB each time, then memory may be a problem.

More of an issue may be all the context switching with that high
frequency. That is something you could try with a dummy XP, and
then just hammer the SQL machines with calls to that XP.

As for the connection pooling that is something you need to check with
the ODBC driver in question. But normally you get pooling if the
connection string is the same. So if these 2000 calls have different
username/password, then there will not be much pooling.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]