Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-22 : 10:07:36
|
| Tai writes "Hi, I will have to implement a vendor based solution soon. I haveno control over the SQL server components, other than makingsure I buy a big enough server. It has to be a standalone, noclustering or any other such. Each of the 100,000 clients will call home to the middle tierapplication, and that middle tier will then talk to the SQLserver and make 138 updates and 138 deletes (276 transactionstotal). Each client will call home one time a day. This givesus 1.15 million transactions/hour. Sometimes, a client may makemore than 276 transactions, if there are special circumstances,but we do not have to take that into consideration at this point. Vendor notes say anything from 2 Gigs to 10 Gigs of data willbe gathered in a year. The data that is stored will only beavailable to a few people, and then, it's only on an adhocbasis, running canned reports (in other words, the data querieswill only be used infrequently if compared with the gatheringprocess). Do you guys have any advice on sizing such a server? I haveused the PowerMatch tool from Dell, but was wondering if there'sanything else out there? As it is a 3rd party vendor app, thereis no way for me to do anything/optimize the app/SQL involved.All I can do is make sure I spec out a big enough server. Vendordocs say that a 4xPentium III Xeon + 4 Gigs Ram + lotsa fast HDwill server 60k users. Then they tell us it'll be fine for 100kusers. I want a somewhat independent verification. Thanx for any help or pointers you can provide. Thanx." |
|
|
chadmat
The Chadinator
1974 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-22 : 14:11:03
|
quote:
Each of the 100,000 clients will call home to the middle tier application, and that middle tier will then talk to the SQL server and make 138 updates and 138 deletes (276 transactions total). Each client will call home one time a day. This gives us 1.15 million transactions/hour. Sometimes, a client may make more than 276 transactions, if there are special circumstances, but we do not have to take that into consideration at this point.
I can't say whether the hardware can handle this (not my strong point), but you should definitely reconsider the 138 updates and 138 deletes being done via the middle tier. If all of these transaction MUST occur, and have to occur in a certain order, I don't see why they can't be packaged up into a SQL stored procedure (or several) and performed as one call by the middle tier. Unless there needs to be client interaction between steps, this is much better for performance reasons.If all of the transactions are done as separate calls from the middle layer, it will create a lot of unnecessary network traffic and possible resource contention/exhaustion. Can you provide more info on what these transactions do, and whether they truly need to managed this way? There's no point in getting monster hardware just to handle excessive CPU cycles if those cycles can be scaled back. |
 |
|
|
|
|
|
|
|
SQL server sizing: looking for pointers and advice