November 11, 2009 at 7:13 am
I have to design new database schema for client server aplication.
We have SQL Server 2005 enterprise edition with 16GB RAM and 4 Processors
mostly application has one lot of 10 millions of records to process
we have 10 processing nodes takes 1 million each which are processing these records through dotnet application and it collects data and fires DML against Server.
my problem or confusion is that how much concurrent connections are possible with this sql server.
if we think records are ready to process one by one through these nodes can the server has any bottleneck?
can anyone share maximum concurrent user connection with the server currently they have.
November 11, 2009 at 7:37 am
By default SQL Server manages the number of concurrent connection automatically and the hard limit is 32767.
November 11, 2009 at 7:44 am
Hi
mjarsaniya (11/11/2009)
...processing nodes takes 1 million each which are processing these records through dotnet application and it collects data and fires DML against Server.
Depending on the way how you generate these DML commands, the parsing and generation of execution plans might become your real problem - not the count of connections.
How do you generate your DML operations?
* Dynamic SQL?
* Parameterized Commands?
* Stored Procedures?
* Parameterized and prepared commands?
Greets
Flo
November 11, 2009 at 8:03 am
generally i have resided to perform all dml through procedure.
please make a note something about my business process:
it has 10 millions objects and have 10 users to process it.
each users go through 1 million and go object by object and process them.(10 millions/10users)
at first phase it insert a single insert for an object and proceed with second object.
at second phase it updates record which is inserted in first phase.there 30 recursion process for a objects to complete a single process.
November 11, 2009 at 8:16 am
Hi
If your process starts with a 1:1 copy of the data into the database, you should use BCP or something else to realize a bulk operation. This works way faster.
Are those updates done as bulk operations or do you update row-by-row 30 times?
Greets
Flo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply