CODEWITHSUNDEEP
sql-serverperformanceload-balancing
Eqx
Eqx

Reputation: 41

Improving the application performance by load balancing SQL queries on Always on availability group nodes?

We have an intranet browser-based application written in ASP.NET with MS SQL Server as the database backend. One of our clients has always availability groups setup with two nodes. Our application requests are routed (via an availability group listener) to the primary R/W node and our client uses the R/O node for their custom reporting (crystal reports).

As the number of users is growing, we’re getting into performance problems - mostly CPU related.

We would like the customer to add more CPU’s, while they want us to start routing read-only queries to the R/O node.

We are really hesitant because these would be application changes and really non-trivial ones:

  1. We understand that reporting is an ideal case to be sent to the R/O node (reduces load, blocking, …). Is it a recommended practice to load balance by sending the read-only queries to the read-only node(s)?

  2. It seems to me we would need to be very careful in terms of what we can afford. It takes some time before the R/O node is synchronized, so we would need to always understand that we could be reading old data. For example, the user clicks the “Save” button and after the record is saved, we re-read the list of records to be displayed. I assume we would have to go to the R/W node to guarantee that the new records will be there. Is that correct?

  3. If we send queries to the R/O node, don’t we degrade the robustness on the system? If one node crashes, the other node needs to be able to sustain the load on itself. Are there recommended scenarios when it makes sense to send requests to the R/O node and when it does not?

Upvotes: 4

Views: 426

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30665

It is preferable to send queries which related to reporting (Only) to secondary nodes so that CPU intensive reports does not degrade the performance of your online database. Your transactions does not get affected from non-transactional usage.

However this does not mean that you need to make all R/O queries on secondary node. Lets say that you have a transactional operation which first needs select operation with row lock, you shouldn't be doing read operation from passive node and DML operation on active node.

We can say that all operational queries can be queried from Active node whereas Passive node(s) are more appropriate to be used for just long running reports.

For your second question If the second node is configured as Async, then yes there might be some delay and also on the case of log shipping failure It is possible to see old data.

For the third question, it really depends on current/future/peak-hours system load. It is hard to tell this or that. It also depends on the budget, IF you can afford it you can have 1 more node. It all depends. But keep in mind that RDBMS systems are not very feasible for horizontal scaling.

Upvotes: 4

Related Questions