Imagine this: you have a database, and it’s on a SQL Server instance, which is on a server. That server may be physical or it may be virtualized. The database, or the instance, or the server, has a high availability solution implemented. It might be failover clustering; it might be synchronous mirroring; it might be VMware HA.
One day, your queries start to run a little slower. But the performance degradation is slow, and gradual. Weeks or months slip by, and bit by bit, millisecond by millisecond, the queries are taking longer. Before you know it, queries are taking out locks, blocking each other, and leading to timeouts.
What You Want To Happen
Your server is overloaded. CPU usage is high. Available memory is low. I/O times are dragging on. You know there is another server sitting in the data center that is waiting to take on this load. It’s just sitting there, idle!
You want your HA solution to kick in and take over. Why doesn’t it realize that performance has tanked and it needs to come to the rescue?
Your SQL Server Isn’t Down, It’s Just Thinking
Chances are that if there was a disaster and your HA solution kicked in right now, you’d experience the same terrible performance on the other server, too – with the added pain of having downtime to failover.
High availability solutions are implemented to be there in case of failure. High CPU usage, low memory warnings, or excessive I/O times don’t constitute a failure. As much as you wish the workload could be transferred to the server with lower utilization, it won’t be – you have to tune your workload.
Yes, I’m telling you to roll up your sleeves and start performance tuning.
Do you have monitoring in place so you can check your baselines to see what resources are being used more than normal?
Have you checked the plan cache for the most resource-intensive queries in your server?
Are you performing regular index tuning (using a tool like sp_BlitzIndex®, so you have the right indexes for your workload?
Remember, your HA solution is there to pick up the pieces when something fails – not to be a safety net for poor performance.