Recently, for the first time in my life I had a customer from India. A far away, beautiful country I had never opportunity to visit, with 1.3 billion of people. I thought it would be interesting experience, and it was indeed.
They have a 1.5TB SQL Server Enterpise Edition database used by Microsoft Dynamics AX. Server with 16 CPU cores, 256GB RAM and workload of 1500 reqests per second, reaching 4000 in peak minutes. Users could feel the system is way slower than they would like, and server was struggling with the ever increasing workload. CPU stayed at 95% for the good part of the day, as you see in this picture, averaging 72%:
After thorough investigation and carefully crafted optimisation changes in several level, from the OS, SQL settings and indexes, this is the “after” CPU graph. It dropped to less than third of before, averaging at 28%:
More important is, how fast requests have been served, are users really feel the difference? There is a counter that shows how many requests piled up in execution. When requests are slower, more of them pile up. For the graph below you can imagine it shows how many cars are waiting in the line to pass through a bottleneck, like paying the highway. The slower is paying, the lines of cars are longer, more of them pile-up. The faster is response (processing of highway payment), less requests will be piled there. Before:
Similar result would be produced by tripling the CPU power. That is, buying additional 32 CPU cores. Since it is SQL Enterprise Edition, additional cores would cost 8000 EUR per core licence*32 cores = 256 000 EUR. We wont even include rest of the hardware price (different motherboard etc), and install+data migration cost to move to a new machine. This optimisation kept the same hardware, took around 2 days, and was so much cheaper than buying more CPU power, that it is almost insignificant cost, saving the customer a nice pile of money. Also it was much quicker improvement of troubled situation than waiting for the hardware replacement. They could stop being worried and relax, thinking of other business decisions.
This is my first customer from India, and I must say a really pleasant experience in every way!
A big thanks to them!