I am working on a system, there is a cisco load balancer that can handle 40K requests.
There are two app servers. There are 12 or so web services.
and there is a SQL server with 2TB of data.
Bottle neck here is SQL server, it is on a single machine with Quad Core CPU, and 80 GB ram.
There are about 2.5 million items stored in database.
The kind of queries are , select product with some properties which are not indexed.
There is a huge index and we don't want to index everything, because index fragmentation is really hurting performance.
I thought about caching, distributed caching, but some of the queries are not very convenient for caching, such as give me companies that starts with foo. There is a huge combination there.
How would you go working on this issues?
There is more select than insert. mostly select. There are several small select. and there are some joins with 3 tables.