My evaluation configuration is as follows:- 1 windows 7 PC SAP 16 HA Server and 1 Cloud based VPS running 2 SAP 16 HA Servers arbiter and mirror. The Client application running on windows 7 (non-mirrored configuration) has a respectable performance (no real noticeable delays). [I am only talking about form opening when several queries are run to populate form fields.] However when I switch to a mirrored configuration, form opening is noticeably (and unacceptably) slower on the local primary server. Below is an excerpt from the SAP SQL 16 help system:
The first paragraph led me to believe I would not encounter performance degradations the second may explain why I do have them I have 2 questions:- 1) Can someone please clarify why the slow network would slow down queries against the local primary database (which does not need the remote at all to complete the query? 2) Would read-only scale-out solve this performance issue? TIA Ephraim asked 29 Jun '15, 15:47 effytuby Volker Barth |
From your other question I assume that you do use HA with synchronous mode. In that mode, during each commit the primary has to wait on the partner's acknowledgement that the sent transaction log pages have been received successfully, so a data modification will usually be somewhat slower in that mode than without HA. Apparently, the latency will depend on the network connection between both partners. I guess "queries" is meant as SELECT statements (i.e. without data modifications) in the first cited paragraph. They do not get recorded in the log (unless auditing is enabled), so they should perform as good as without HA unless they are affected by locks (e.g. write locks) from other transactions and those are delayed because of HA. answered 29 Jun '15, 17:01 Volker Barth |
What we have once had, was that the connection to the HA system was configured to use DoBroadcast=no and the IP and Port of both servers, interestingly searching for the primary server during opening the connection was in this configuration very slow. answered 30 Jun '15, 02:51 Martin |
Does your application do any updates to the database when it opens a form? E.g. perhaps it is logging activity into the database? If you are unsure if any updates occur then you could dbtran your transaction log to see what operations (if any) were written to the log during the time of the form opening operation.
If any updates occur and your HA system is configured as synchronous (which is recommended if you really care about not losing any transactions), then the update needs to be recorded by the secondary/mirror before the commit succeeds and hence network latency can be the major factor in determining performance.
Indeed synchronous mode as it is recomended. But I beleive Selects are not written to the transaction log. Will the logfile show me inter-partner comunications?
The logfile specified in the create mirror server statement will tell you whether any log is being shipped to the mirror, and if so, you can compare the send time on the primary with the receive time on the mirror (and vice versa for the reply) to identify how long the network communication is taking. The transaction log will not contain this information.