Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Unanswered: What is the workload limit in MS SQL Server?

    Hi, I need some help about a project I'm working on.

    I'm working on a project (based on MS SQL Server) that will involve a lot of clients, each one constantly querying the server.
    Just to have an idea of the project, I'm talking of about 2000 clients, each one queries the database 3-4 times per second: a total of 6-8000 queries per second.
    The database is reached through Internet and every client uses a dedicated DSL connection.

    My questions are:
    - What is the maximum workload of a Microsoft SQL Server?
    - What happens when the server workload reaches 100%? is there a queue? and what happens when the queue is full? is it possible that the server goes out of service due the excess of work?
    - What are (more or less) the hardware requirements for such a server?

    can anyone help me, at least telling me how to find these answers (web sites, books, ...)

    thank you for you support

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The workload for the server is practically unlimited, given appropriate hardware. Queuing is not default, but you can code it.

    What kind of infrastructure are you imagining? I'm guessing that you'll overwhelm it LONG before you tax the SQL Engine.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    .....but the deadline for the paper is probably due much before then...do they even do paper anymore?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2006
    Posts
    33
    It also depends on the type of queries - Are you updating or just reading from the database? What types of queries will be run - Will the queries be simple one row queries or a complex analytical queries? The database design, hardware, infrastructure and the size of the database also need to be considered.

    As Pat said - the worklod isn't limited as such. You might find that clients have a query time out setting and their queries time out if the load on the server is high. Connections may be refused if you have defined a maximum number of concurrent connections setting on the server.

    If you do reach the "max", you could always scale out...
    SQL Server is used by the smallest of companies to the largest of organisations - you can be confident that it will scale to your needs.

    PS

    This is about the best website around for SQL Server performance related issues:
    http://www.sql-server-performance.com/
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  5. #5
    Join Date
    Dec 2004
    Posts
    46
    For your situation, for intel platform, for SQL server, you don't have much choice except to scale out your application. Those TPC-C benchmark stuff are all talking about scale out clusters. Or you may consider IA64 platform so that the no of CPU in a server can exceed > 8.

  6. #6
    Join Date
    Jun 2006
    Posts
    3
    thank you for your answers.

    So it's not possible on Intel platforms to scale out clusters, right?

    In the next days I'll write a little software to emulate the traffic load on the server and see what is the limit of my current system (a single 2.4Ghz Xeon machine), just to have an idea of the system I'll need.

  7. #7
    Join Date
    Dec 2004
    Posts
    46
    Intel can scale out but not scaling up by adding CPUs. Unix have machines like 32, 96 CPU. But for Windows, they us a number of servers with 4-8 CPUs.

    Then, your application cannot be a single SQL Server in one machine. It has to be many instance on many machines. Usually, you have to partition your application so that different groups of users connect to different server in order to share the load. Unless your application is READ ONLY, in that case, all you need is to use load balance.

  8. #8
    Join Date
    Dec 2004
    Posts
    46
    I have experience on 4 3.0GHz CPU Intel machine, I can achieve 1000 transaction per second (very simply txn). May be you need a few of those.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe someone ought to break this news to Unisys, IBM, and NEC. I'm sure that they'll be shocked to find out that 32 way Windows boxes are considered impossible.

    Come to think of it, since the highest TPC-C certified benchmark is held by one of those machines, I suspect that other folks will be caught off guard too!

    I'll repeat my question from above: What are you planning for infrastructure? I'm sure that you'll overwhelm that infrastructure long before you can tax the SQL Server engine.

    -PatP

  10. #10
    Join Date
    Dec 2004
    Posts
    46
    http://www.tpc.org/tpcc/results/tpcc...n_results=none

    What's required, can only be achieve by IA64 architecture, right now.

  11. #11
    Join Date
    Jun 2006
    Posts
    3
    Quote Originally Posted by Pat Phelan
    I'll repeat my question from above: What are you planning for infrastructure? I'm sure that you'll overwhelm that infrastructure long before you can tax the SQL Server engine.
    I was thinking about what to do:
    i can upgrade my existing server with a second cpu (now is a 2.4Ghz single Xeon).
    otherwise i can plan to buy a new server, maybe with 2 x dual-core xeon and double the processing power.

    But my primary question is:
    if my 2000 clients start querying the database continuously, will the server keep work or will it block?
    and the block is only a hardware performance problem or is a SQL software limit?
    Because if there are only hardware limitations, it's not a big problem, I can plan to buy a more powerful server.
    If the problem is about MS SQL it's a bigger problem because I have to move my existing database to another one.

    But thanks to your answers, I understand that the problem is about hardware, and now I'm a little more serene.

  12. #12
    Join Date
    Mar 2005
    Location
    Kiev, Ukraine
    Posts
    5
    By "infrastructure", one could mean "network" at the first place. Are you sure that the network connection of your server can handle the load of 6-8k queries/sec?

    ...and for those talking about Windows only suitable for 4-8 CPU, here's a little picture:

    http://static.flickr.com/49/163570062_ef745d547c_b.jpg

    Like it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •