Our SaaS app is hitting a wall with database latency during peak hours. We are currently on a General Purpose tier, but queries are slowing down significantly when we hit 500+ concurrent users. Is moving to Hyperscale the only option, or are there specific indexing and caching strategies I should try first?
3 answers
Before jumping to Hyperscale, check your "Resource Governance" metrics in the Azure Portal. You might simply be hitting DTU or vCore limits. I highly recommend enabling "Intelligent Insights" and "Query Store" to identify top-waits. For SaaS, "Elastic Pools" are often better than standalone databases as they allow multiple databases to share a resource bucket. Also, look into Azure Cache for Redis to offload frequent read queries. If your data size is over 4TB or you need near-instant scaling, then Hyperscale becomes a necessity due to its decoupled architecture.
Have you considered using "Read Scale-Out" to offload your read-only workloads to a secondary replica, or is the bottleneck primarily on your write operations?
Check your indexes first. 90% of the time, a missing index or a poorly written JOIN is the real culprit, not the hardware tier.
Martha is right. Use the "Index Advisor" in the Azure portal; it literally tells you which indexes to add to fix the slow queries.
The bottleneck is definitely the writes during our morning sync period. We have a lot of bulk inserts happening at once. I've heard that the Business Critical tier offers lower latency because of the local SSD storage, but the price jump is pretty steep. Would using a memory-optimized table for those specific high-concurrency staging tables help alleviate the I/O pressure without a full tier upgrade?