A Performance Issue: Why DBTwin Can while Always On Can Not

A Performance Issue: Why DBTwin Can while Always On Can Not

The other day, one of our ERP clients gave us a ring and told us that the stored procedure p_GetSpKcFx_DRP_E3_FR is heavily resource-consuming. Whenever it is run, the CPU spikes(above 80) and many blockings pop up, so the whole database seems like frozen.

After investigating the procedure with our client's IT engineer, we find that p_GetSpKcFx_DRP_E3_FR is actually a reporting sp. It is just easy peasy to tackle this kind of problem: read-write separation and load balance without source modification. After being informed by our special proprietary DBTwin_cmd command:

dbtwin_cmd sproc ltread "E3FR" "dbo" "p_GetSpKcFx_DRP_E3_FR"

the gateway of our DBTwin AA database cluster will forward the sp to execute on the secondary node, thus greatly offloading the heavy burden on the primary node.

What is worth of mentioning is: No source code of the application needs to be modified at all.

Now the primary returns to normal(CPU below 30 and blockings disappeared) and our client is happy with the amazing result.

Q: Can the sp be forwarded to the secondary replica of Always On in order to offload the heavy burden on the primary node?

A: No! The client is neither willing nor capable of modifying the source code, because:

 1) The client needs to create a read-only connectionstring and modify the source to redirect the sp to execute on the secondary replica.

 2) There is a create table statement in the sp which can not be executed on the read-only replica, so the client needs to rewrite the sp.