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, ...)
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.
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.
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.
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.
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.