stored procedure - Parallel Operation.

[selvam]

Hi,

I've a doubt.

I've a stored procedure in SQL server2000. Suppose multiple users are accessing
that stored procedure concurrently through ADO, how does SQL server process
those multiple requests? Does SQL serialize the requests or all the requests
are processed simultaneously?

Also, plz do give any help links for this ....

Thanx in adavance.
Selvam.

[Doug]

selvam,

We just went through a bunch of this stuff. SQL Server will process concurrently.
If you have temp tables, it will create tables with unique identifiers in
tempdb.sysobjects. A nice way to test if you are getting concurrency is to
put a WAITFOR DELAY '00:00:10' in your sproc and see if you see multiple
instances of any temp tables in the sysobjects table. (Let me know if you
want the test script)

If you are accessing the db from MTS or COM+ components, make sure that they
are not main apartment threaded. This will cause the objects to serialize
and will effectively serialize your calls to SQL Server!

THere is also a knowledge base article that details the Transaction Isolation
Level for COM+ and MTS components is SERIALIABLE by default.
(http://support.microsoft.com/support.../Q125/5/20.asp)

Hope this helps.

"selvam" <selvam_anna@yahoo.com> wrote:
>
>Hi,
>
>I've a doubt.
>
>I've a stored procedure in SQL server2000. Suppose multiple users are accessing
>that stored procedure concurrently through ADO, how does SQL server process
>those multiple requests? Does SQL serialize the requests or all the requests
>are processed simultaneously?
>
>Also, plz do give any help links for this ....
>
>Thanx in adavance.
>Selvam.

[Daniel Laurent Lemire]

I did something in real life to with a web intereface, ado and sql.

how can you do parallel processing without multiple processor.

If I read the sql books online it says parallel processing requires more
then 1 processor.

It is a hardware issue. In our case we were trying to send 3600 transactions
in 1 sec and
get the fastest throughput.

we had a 1 processor machine.

Processor usage was showing as too high in thread handling. (thread looping
scenario)
Maximum number of threads were available.

Solution...to really to parallel processing you need more then 1 processor
working in parallel.




"Doug" <dhunter@transcentive.com> wrote:
>
>selvam,
>
>We just went through a bunch of this stuff. SQL Server will process concurrently.
>If you have temp tables, it will create tables with unique identifiers in
>tempdb.sysobjects. A nice way to test if you are getting concurrency is
to
>put a WAITFOR DELAY '00:00:10' in your sproc and see if you see multiple
>instances of any temp tables in the sysobjects table. (Let me know if you
>want the test script)
>
>If you are accessing the db from MTS or COM+ components, make sure that
they
>are not main apartment threaded. This will cause the objects to serialize
>and will effectively serialize your calls to SQL Server!
>
>THere is also a knowledge base article that details the Transaction Isolation
>Level for COM+ and MTS components is SERIALIABLE by default.
>(http://support.microsoft.com/support.../Q125/5/20.asp)
>
>Hope this helps.
>
>"selvam" <selvam_anna@yahoo.com> wrote:
>>
>>Hi,
>>
>>I've a doubt.
>>
>>I've a stored procedure in SQL server2000. Suppose multiple users are accessing
>>that stored procedure concurrently through ADO, how does SQL server process
>>those multiple requests? Does SQL serialize the requests or all the requests
>>are processed simultaneously?
>>
>>Also, plz do give any help links for this ....
>>
>>Thanx in adavance.
>>Selvam.
>