January 25, 2006 at 3:00 pm
Hi,
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.
SQL server Environment :
OS - Windows 2000 Advanced Server SP4
SQL - SQLServer 2000 Enterprise edition SP3
Hardware - 8 way 2 node cluster with 6Gb RAM
Any help regarding this is greately appreciated.
Prahalad
January 26, 2006 at 5:48 am
Can you not simply use OPENDATASOURCE or OPENROWSET instead? I am just guessing here but you should be able to create an OLE DB provider for ODBC using your DSN and connect with that.
Anyway, regarding xprocs and scalability. Extended stored procedures are always bad for scalability. The main reason is that they cannot be executed on a thread that is in cooperative scheduling mode, as SQL Server normally uses. Instead the UMS must take the thread and switch it to preemptive mode, which has a lot of costs involved. Then that thread is sent off to do it's thing, being scheduled by Windows in a normal way. But at the same time a new thread is created for handling the work that should have been done by this 'runaway' thread. These two threads will now compete for CPU time, of course meaning a lot more overhead. And if the xproc then sends any results back to the client the thread needs to be switched back to cooperative mode, send it's results and then back again for further processing.
In summary, xprocs are inherently bad for scalability.
Note that xprocs get most of their memory from the multi-page allocator manager, so there is not a whole lot of memory available. And if it does not do a good job of managing that memory it can have serious results, such as connections not being able to connect to SQL Server since there is not enough memory available to create it.
January 26, 2006 at 10:23 am
I tried going the Linked server route as the Books online suggested that that was the preferred way as opposed to OPENDATASOURCE and OPENROWSET route "for any data sources accessed more than a few times". One of the problems with the Linked server option was double execution. If I called a stored procedure it was being executed once to get the resultset column information and once again to get the data. This is unnecessary load on the server but would be still OK if all the stored procedure did was a "SELECT". If the remote stored proc did an "INSERT or UPDATE" then I am in big trouble as it would result in two insert/update statements being executed. When I contacted Microsoft and many others in the newsgroups I was told that is the way a linked server was designed to work and there was not anything anybody else could do to not make it execute the remote SP twice. So I had to choose the XP route. But if my understanding is wrong I would love to go back to the linked server route provided I am able to eliminate the double execution problem.
Prahalad.
January 27, 2006 at 1:50 am
Hm, never heard about that. I have not used linked servers a lot so I could be wrong, but it does sound very strange. But how about OPENDATASOURCE or OPENROWSET then? Even though linked servers might be better anything that lets you avoid xprocs should be considered.
January 27, 2006 at 6:01 am
If my understanding is correct OPENDATASOURCE or OPENROWSET both use the same concepts of linked server the only advantage is that you dont have to create an explicit Linked server because they are created on the fly. If this is incorrect please let me know.
Prahalad
January 27, 2006 at 8:16 am
No I think that is correct. But like I said it does sound strange in general that these (and linked servers) should execute a proc twice. But unfortunately I am not an expert in that area.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply