Fun things I discovered in just my last performance tuning engagement: * Database with homegrown software load-balancer in front of it, and the database replic

Fun things I discovered in just my last performance tuning engagement: * Database with homegrown software load-balancer in front of it, and the database replicated to a half-dozen subscribers. Load balancing required database calls, and the subscribers had their LUNs on the same array as the main database. So 7 different servers, but all beating the same storage. In the end, we ended up with no load balancing (just the single instance). * Application server with lots of agents (one on every workstation). Agents were checking in every 200ms, even if they had nothing to report. Result was lots of tiny database transactions (for logging no activity). Throttled that down to 10 seconds (product managers agreeing that was the more realistic threshold) and batching the updates, made the logging asynchronous, and saw the database transaction log no longer waiting on log writes constantly. * Bad clustering keys for tables. Some tables that were being searched constantly as heaps. Sharded tables with divergent schemas. This was fun, as had to conform everything while it was live (got agreement on what was reasonable delay for synch jobs). Chose correct clustering keys, compressed the tables, and then got rid of other indexes that were no longer necessary. Cut IO by 99%, and writes were now just appends. * Badly written queries using inadequate indexes on badly structured tables. TL;DR: An analysis process took 24 hours, and was tuned to take 4.5 hours. I spent a day on it to cut it down to an hour while I worked on other things. We then spent 2 weeks refactoring it to cut it down to about 8 minutes, despite the underlying data model being bleh. We also added ad-hoc functionality to do stuff out-of-band of the regular scheduled job, with an average response time of 6ms. * ORM lazy loading by default, including for things that constantly retrieved collections. Rewrote them to be programmatically eager loaded and reduced database calls from millions a day to thousands a day. * And a little bit of hardware. SAN HBA links were moved form dual 1Gbps to dual 10Gbps. Some SSDs added. Ended up reducing IO latency by 96-98%, depending on the server. Needed to happen, as IO latency was in hundreds of ms, and we were able to get it down to where it should be (< 10ms for reads, 1-3ms for writes). Non-scaling SAAS stack went from falling on its face with an entire rack of hardware, to being cut down to 1/3rd of a rack of hardware and the hardware basically twiddling its thumbs. The organization was in the process of building of entirely new SAAS platform in a completely different tech stack -- they immediately started questioning that decision when the old stack handled everything trivially, and they knew there was a lot more tuning that still could be done.