1

Poorly designed CRM/CMS/SCM/Call Center System, developed using C#/ASP.NET/MS SQL Server.

The problem I have with this is that the client is using this system in production and is approaching a very busy period. The business is semi-seasonal and this means that it is critical that the system is up during this time. All the above mentioned run from one core database using SQL server and because of ridiculously over complicated/large SQL statements the database sever is taking strain big time.

The server itself is pretty impressive 16GB Ram and 16 core processing power. At the moment this is barely coping. So during the busy period it will fall over. Basically there is no time to programmatically correct the problem. We need to chuck hardware at the problem.

This brings me to my question, to implement SQL load balancing do I need to get some development work done or is there a way to load balance without doing any development? The ISP that hosts the server advised that we will need to do some development.

RupDog
  • 115
  • 3
  • 16GB RAM isn't that much for a database server, depending on how large the databases are. It's entirely possible that you're swapping to disk a lot, which will kill performance. Can locate and post where the bottleneck actually is? – MDMarra Nov 17 '11 at 14:04

2 Answers2

1

The biggest bottleneck in an arbitrary database system is always disk I/O*.

16 cores ? pffft. Do you also have 16 disks running in raid-10 ?

If not, get them.

--
* Of course, you show absolutely no performance metrics whatsoever, so in case you are curious about actual advice we might give you, start collecting performance data and show them.

In fact, until you do, you cannot prove there is a problem

adaptr
  • 16,576
  • 23
  • 34
  • Great point. Coming right up! – RupDog Nov 04 '11 at 12:32
  • I am not able to upload images yet. But I can tell you the CPU usage max out when I run some functions in the application connected to SQL server. – RupDog Nov 04 '11 at 15:30
  • Its more likely disk-IO that is causing a CPU bottleneck rather than the CPU themselves. Switching to a SSD (or 15,000rpm disks) would probably relieve CPU contention a bit, if not entirely. – djangofan Nov 04 '11 at 19:59
0

There is no such thing as load balancing persisted storage. You can partition the storage (sharding) which works only for very narrow specific problem domains or for applications designed explicitly for scale-out from the get-go, you can scale out reads using readable stand-by (replication, log shipping, mirroring+snapshots, AlwaysOn) which works for reporting with acceptable back-in-time delay, and there even are from complicated master-master replication schemes, which none work.

So your only option is to fix the application or beef up the database. Application fix is always yields the best results, but requires you have access to a very rare resource (good developers) and time. The other alternative, which will never match the result of fixing the app, is to beef up the database. Which requires you to identify the bottleneck. Waits and Queues is an excellent methodology, proven to yield results when properly applied. IF you have no idea how to do it, get in contact with a reputable professional and ask for help.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23