Azure SQL GeoReplication - Queries on secondary DB are slower

Question

I've setup two SQL DBs on Azure with geo-replication. The primary is in Brazil and a secondary in West Europe. Similarly I have two web apps running the same web api. A Brazilian web app that reads and writes on the Brazilian DB and a European web app that reads on the European DB and writes in the Brazilian DB.

When I test response times on read-only queries with Postman from Europe, I first notice that on a first "cold" call the European Web app is twice as fast as the Brazilian one. However, immediate next calls response times on the Bazilian web app are 10% of the initial "cold" call whereas response times on the European web app remain the same. I also notice that after a few minutes of inactivity, results are back to the "cold" case.

So:

1. why do query response times drop in Brazil?
2. whatever the answer is to 1, why doesn't it happen in Europe?
3. why does the response times optimization occurring in 1 doesn't last after a few minutes of inactivity?

Note that both web apps and DB are created as copy/paste (except geo-replication) from each other in an Azure ARM json file. Both web apps are alwaysOn.

Thank you.

UPDATE

Actually there are several parts in action in what I see as a end user. The webapps and the dbs. I wrote this question thinking the issue was around the dbs and geo-replication however, after trying @Alberto's script (see below) I couldn,' see any differences in wait_times when querying Brazil or Europe so the problem may be on the webapps. I don't know how to further analyse/test that.

UPDATE 2

This may be (or not) related to query store. I asked on a new more specific question on that subject.

UPDATE 3

Queries on secondary database are not slower. My question was raised on false conclusions. I won't delete it as others took time to answer it and I thank them.

I was comparing query response times through rest calls to a web api running EF queries on a SQL Server DB. As rest calls to the web api located in the region querying the db replica are slower than rest calls to the same web api deployed in another region targeting the primary db, I concluded the problem was on the db side. However, when I run the queries in SSMS directly, bypassing the web api, I observe almost no differences in response times between primary and replica db.

I still have a problem but it's not the one raised in that question.

Answer 1

On Azure SQL Database your database' memory utilization may be dynamically reduced after some minutes of inactivity, and on this behavior Azure SQL differs from SQL Server on-premises. If you run a query two or three times it then start to execute faster again.

If you examine the query execution plan and its wait stats, you may find a wait named MEMORY_ALLOCATION_EXT for those queries executing after the memory allocation has been shrinked by Azure SQL Database service. Databases with a lot activity and query execution may not see its memory allocation reduced. For a detailed information of my part please read this StackOverflow thread.

Take in consideration also both databases should have the same service tier assigned.

Use below script to determine query waits and see what is the difference in terms of waits between both regions.

DROP TABLE IF EXISTS #before;

 SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],
        [signal_wait_time_ms]
 INTO #before
 FROM sys.[dm_db_wait_stats];

 -- Execute test query here

 SELECT *
 FROM [dbo].[YourTestQuery]

  -- Finish test query

DROP TABLE IF EXISTS #after;

 SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms],
        [signal_wait_time_ms]
 INTO #after
 FROM sys.[dm_db_wait_stats];

 -- Show accumulated wait time

 SELECT [a].[wait_type], ([a].[wait_time_ms] - [b].[wait_time_ms]) AS [wait_time]
 FROM [#after] AS [a]
 INNER JOIN [#before] AS [b] ON
  [a].[wait_type] = [b].[wait_type]
 ORDER BY ([a].[wait_time_ms] - [b].[wait_time_ms]) DESC;