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 performance of queries against the primary server is usually not affected by mirroring. If your application does more reads than updates and auditing is not enabled, you are unlikely to experience a change in the performance of the primary server. But, if database auditing is enabled, you may experience a change in performance because auditing writes additional information to the transaction log. “
“The performance of your client applications can be affected by the network connection between the servers in the mirroring system, especially when the servers are located in different geographic locations. A slow network connection between the servers can degrade the performance of your client applications.”

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's gravatar image

effytuby
46334
accept rate: 0%

edited 29 Jun '15, 17:02

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819

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.

(29 Jun '15, 17:03) Mark Culp

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?

(29 Jun '15, 17:05) effytuby
Replies hidden

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.

(29 Jun '15, 21:42) MarySteele

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.

permanent link

answered 29 Jun '15, 17:01

Volker%20Barth's gravatar image

Volker Barth
40.0k361549819
accept rate: 34%

edited 29 Jun '15, 17:14

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.
Changing it to DoBroadcast=Direct helped here and the connections can now be opened without any delay. So maybe for you it is also not the select itself which runs slower, but the open connection command which needs now to determine the primary server.

permanent link

answered 30 Jun '15, 02:51

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275
×61

question asked: 29 Jun '15, 15:47

question was seen: 2,332 times

last updated: 30 Jun '15, 02:51