SQL Server Performance

Scaling Up My SQL Server Application

Discussion in 'Performance Tuning for Hardware Configurations' started by tnichols, Nov 23, 2004.

  1. tnichols New Member

    Hi All

    I have recently had a request to work out a Technical Architecture for my software app which will support between 2000 and 3000 users.

    The most we've ever done before is 150!!!

    What do I need to start thinking about. I've read the article posted here

    http://www.sql-server-performance.com/dk_massive_scalability.asp

    This seems to suggest a number of options:

    1. Federation - which I'm not too keen on.
    2. Multiple DBs with Replication - this would be good as we have a proprietary replication system in built. I just wouldn't want to recommend purchasing of many servers when one big one would do the job and be easier to manage.
    3. Multiple DBs with a Connection Pooling method - again, not keen as it would involve alot of recoding.


    What are the thoughts on 64 bit technology to scale up for users? From the literature I can find it would seem that 64 bit helps performance but it isn't clear whether it helps concurrency??

    Thanks.

    Tim
  2. mmarovic Active Member

  3. tnichols New Member

    I'm told that making changes like that to our app are out of scope :-(

    I guess it's now a choice between

    Multiple Servers and Replicated Databases
    or
    One big powerful server (potentially 64 bit)

    I would prefer the last approach due to its ease of management.....do you think that would solve my problem?

  4. Twan New Member


    are we really talking 2-3000 concurrent users? Are they all in the same building (LAN) or are they coming in over WAN?

    How big are the databases? I presume we're talking OLTP?

    Cheers
    Twan
  5. tnichols New Member

    Twan

    2000 is what we're aiming for.

    Users will be connecting via Terminal Services over a WAN.
    Database is 10GB - so not too big
    OLTP

    Thanks
  6. mmarovic Active Member

    I believe one big can handle 2000 users without problems if application is well written. Since this is the simplest solution I would recommend that one.

    But keep in mind the main drowback of SQL Server architecture: locking. For dbs I support nolock hint on almost any query is acceptable. That can be the problem even with the most powerfull hardware.
  7. Twan New Member

    that's a lot of terminal servers, and potentially quite a fat WAN pipe! <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> <br /><br />agree with mmarovic, if the app is tuned and properly written, a single server should be fine, keep the log on its own raid set since you're gonna be hitting it hard.<br /><br />You may want to work out how many ts and how big your WAN connection(s) ought to be... 2000 users if each transmits at say average of 1KB/s then that is still 2,000KB/s = 16Mbps and 1KB/s isn't that much, it could easily be 10 times that...<br /><br />Cheers<br />Twan
  8. tnichols New Member

    by my reckoning the terminal server count will need to be about 50 based on 4-way 4Gb boxes!!!!

    your point with regards to bandwidth is very useful. the client was planning on using a 5 Mbps connection to host this - we'd already told them this probably wouldn't be enough but your calculations help.

    when you talk about 'properly written', are there any specifics we should be aware of over and above making sure that the app is as tuned as possible. recently we have modified the application to use uncommitted reads where possible (mostly on the ad-hoc querying) which I believe will help our concurrency limitations alot.

    log on its own raid is standard setup for us

    thanks for all your suggestions
  9. mmarovic Active Member

    Take a look here:http://www.sql-server-performance.com/application_design.asp
    Also there are programming language/technology used specific tips on this site.

    Satya posted recently links to troubleshooting presentations I found very usefull, I have pdfs downloaded but I can't remember now where it is posted.
  10. Twan New Member

    hi ya,

    'basic Twan rules' for a properly written app...

    - 3NF design or better (unless specific performance reasons to move away from this)
    - be consistent about datatype usage e.g. is a field is an int in the parent table then make sure it is also int in any child table, etc.
    - no dynamic sql and no ad hoc access
    - very efficient triggers (or better yet no triggers)
    - very efficient stored procedures
    - appropriate indexes
    - regularly maintained stats and indexes


    to work out the number of TS and bandwidth, you may want to give a very rough estimate now based on 'finger in the air' but with the prerequisite that a roll out to 50 clients is done first off so you can monitor load on TS and the WAN.

    Like I said depending on how chattery the app is you could find that 100Mbps WAN is not fast enough for 2000 clients...

    Cheers
    Twan
  11. tnichols New Member

    hi twan

    thanks for that. i think we hit most of those, and where we don't there's a good reason.

    the ts side of things should be rolled out fairly slowly anyway so we'll have plenty of oppurtunity to monitor bandwidth requirements.

    cheers
    tim

  12. Argyle New Member

    I read some MS documents about Terminal Services on Windows 2003. Each user used around 1100-1400 bytes/second depending on user type in the system. So that's about 10kbits/s per user.

    But you could tune this a bit. For example switching from 16-bit color depth to 8-bit color depth would decrease the average bandwidth usage from 1450 bytes/user to 1150 bytes/user. RAM was also a big factor.

Share This Page