Performance and Scalability

[sguslan]

Hi SQL Gurus >>>

I am planning the size of server hardware to support a 500 simultaneous users running several applications with 25000 transactions per hour. I am wondering how big the server should : how many CPUs should there be in the server.
We are contemplating using the newer IBM x360 servers with 8 CPUs initially but can raise the number to 16 CPUs, but may be expensive for us!
Will a Compaq 8 CPU server do the trick?

Has any one had similar experience ?

I appreciate your comments and thankx in advance ..

Salim

 

[MikePP]

Hi

Some of what I say below may seem obvious to you. Apologies.

The Server Load depends on the nature of the queries and how they are written, and the volumes of data involved.

If you do suffer performance problems it can be as much down to poor indexing or badly written stored procedures, as the level of database activity.

Having said that the more users and transactions you have, the more resources you will need.

If you have the time, I suggest testing the application on a lower spec server, and monitoring its performance. Use this data to determine what you actually need.

Consider the following for improving your performance though:

1 - Performance can be limited by the network, hard-drives and memory as well as the processors.

2 - Moving from a single processor to dual-processor gives big benefits. Moving from dual processors to quad gives smaller benefits. I have no experience of using 16 processors, but it seems over the top.

3 - Ensure your processors each have a large memory cache. This is more important than the number of processors. Eight Processors with a 2MB cache each is far better than 16 with none.

4 - Ensure that the database's transaction log is stored on a different disk (array) than the database itself. If using RAID consider RAID 1 for the transaction log.

5 - Do not use Software RAID

6 - Ensure your Server has lots of RAM. I don't know what you are doing, but I would consider 1GB or more.

7 - Ensure that your disks and disk controllers are very fast and efficient. If the disk controllers have in-built write caching, ensure that this is battery backed.

8 - Ensure you know how to use the performance monitoring tools that SQL Server provides.

9 - Increase the thread priority of SQL Server over other applications. (This is safe with multi-processor machines)

10 - Make sure your developers know how to write efficient SQL queries.

The last point is the most important. It doesn't matter how powerful your hardware is. If your database code isn't running efficiently, the server will grind to a halt.

Consider starting with 2 or 4 processors, and expanding up to 8 or 16 if necessary.

Regards

sadcow

 

[sguslan]

Thanx for the tips. I will be using the Compaq "Server sizing for MS SQL" tools hoping to find help ....