Azure SQL GeoReplication - 辅助DB上的查询速度较慢

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.

我在Azure上使用地理复制设置了两个SQL DB。主要在巴西,在西欧是次要。同样,我有两个运行相同web api的Web应用程序。一个巴西网络应用程序,可读取和写入巴西数据库和欧洲网络应用程序,该应用程序读取欧洲数据库并在巴西数据库中写入。

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.

当我使用来自欧洲的Postman测试只读查询的响应时间时,我首先注意到,在第一次“冷”调用时,欧洲Web应用程序的速度是巴西的两倍。但是,巴西网络应用程序的下一次呼叫响应时间是最初“冷”呼叫的10%,而欧洲网络应用的响应时间保持不变。我还注意到,在几分钟不活动后,结果又回到了“冷”的情况。

So:

why do query response times drop in Brazil?

为什么巴西的查询响应时间会下降?

whatever the answer is to 1, why doesn't it happen in Europe?

无论答案是什么,为什么不在欧洲发生?

why does the response times optimization occurring in 1 doesn't last after a few minutes of inactivity?

为什么1中发生的响应时间优化不会在几分钟不活动后持续?

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.

请注意,Web应用程序和数据库都是在Azure ARM json文件中相互创建复制/粘贴(地理复制除外)。这两个网络应用程序总是在。

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.

实际上,我认为最终用户有几个部分在起作用。 webapps和dbs。我写了这个问题,认为这个问题围绕着dbs和geo-replication,然而,在尝试@Alberto的脚本(见下文)之后,我无法',在查询巴西或欧洲时看到wait_times的任何差异所以问题可能出在webapps上。我不知道如何进一步分析/测试。

~~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.

我通过在SQL Server数据库上运行EF查询的web api的休息调用来比较查询响应时间。由于对位于查询db副本的区域中的web api的休息调用比在针对主db的另一个区域中部署的相同web api的rest调用慢,我得出结论问题是在db端。但是,当我直接在SSMS中运行查询时,绕过web api,我发现主数据库和副本数据库之间的响应时间几乎没有差异。

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

我还有一个问题,但不是那个提出的问题。

1 个解决方案

#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.

在Azure SQL数据库上,数据库的内存利用率可能会在几分钟不活动后动态降低,并且在此行为上,Azure SQL与本地SQL Server不同。如果您运行查询两到三次,那么再次开始执行得更快。

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 * thread.

如果检查查询执行计划及其等待统计信息,则可能会发现在Azure SQL数据库服务缩小内存分配后执行的那些查询的名为MEMORY_ALLOCATION_EXT的等待。具有大量活动和查询执行的数据库可能看不到其内存分配减少。有关我的详细信息,请阅读此*线程。

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;

#1