none
同一个查询在AG主副本和辅助副本上执行时间差距非常大 RRS feed

  • 问题

  • 同一个查询在AG主副本和辅助副本上执行时间差距非常大,主副本1秒内就执行完了,辅助副本需要59秒。在检查了两者的执行计划后,发现主副本的执行计划中各个步骤实际执行的行数和评估的行数都非常接近甚至相等,但辅助副本却差距非常大,因此可以判断是辅助副本统计信息不准导致的优化器选择了错误的执行计划,于是我把查询中关键表的统计信息在主副本进行了更新,再到辅助副本执行时,执行计划显然就“准确”了很多,语句的执行时间也由原来的59秒降低到1秒。

    问题虽然解决了,但我这个处理的过程存在一些疑点,在AlwaysOn中主副本的统计信息和辅助副本的统计信息是同步的,查询既然在主副本执行很快的话,说明主副本的统计信息是准确的,所以辅助副本上的统计信息也应该是正确的,我的疑问是:

    1.既然辅助副本的统计信息也是准确的,那么相同的查询在辅助副本不应该执行这么慢;

    2.在我的解决方法中,我更新了主副本的统计信息,考虑到现有的统计信息本来就已经比较准确了,这次的更新操作应该对现有的统计信息改变不大,那同样的应该对辅助副本的统计信息改变不大,但为什么辅助副本的查询时间在更新统计信息前后有天壤之别呢?

    请各位大神帮忙解答。

    PS:对主副本更新前后的统计信息我没来得及对比,当时光想着解决问题去了。


    chenl

    2016年12月14日 2:37

全部回复

  • 辅助副本上的统计信息可能跟主副本上不同,有些临时统计信息放在tempdb上的。


    想不想时已是想,不如不想都不想。

    2016年12月14日 3:28
    版主
  • 谢谢回复,这个应该不是。

    我当时检查了,两者统计信息的数据是一样的,在辅助副本生没有看到istemparory为1的统计信息。


    chenl

    补充:我是在sys.stats中查看的。

    • 已编辑 i6first 2016年12月14日 10:57
    2016年12月14日 10:23
  • I6first 你好,

    能告诉我一下你使用的是哪个版本的SQL Server吗?根据你说的“在辅助副本生没有看到istemparory为1的统计信息“,这个说明你并没有一个临时的统计数据。

    在我看来,你这个问题应该是由只读副本(Read-only Secondary)引起的,对于高可用性组来说,统计信息(Statistics)是在主副本创建,然后通过事务日志传递到辅助副本,由于只读访问(Read-only access)的限制,辅助副本上不能产生统计数据。于是在SQL Server2012的后续版本中加入了临时的统计信息,这些信息存储在tempdb里面。并且用一个is_temporary的列来记录这个统计信息的状态,为0代表不是临时的统计数据。

    由于高可用性组的设计原因,数据需要先通过事务日志传输到辅助副本,然后固化(harden)到数据库中,这会导致事物的延时,因此,当我们在辅助副本上执行查询的时候,原先那个从主副本传过来的统计信息已经无效或者过期了。由于你的机子上并没有临时的统计数据,所以使用的还会是原来的统计数据,你所说的“两者统计信息的数据是一样的”(一般情况下,其实是不太一样的,特别是在有大量数据修改的时候)也证明了这一点,于是造成了错误。

    另外,如你所述“我更新了主副本的统计信息,考虑到现有的统计信息本来就已经比较准确了,这次的更新操作应该对现有的统计信息改变不大,那同样的应该对辅助副本的统计信息改变不大”,在这个期间,数据量改动并不大,统计数据和辅助副本上的数据相对比较吻合,所以查询计划制定的比较正确。

    你可以参考一下这个博客:https://blogs.msdn.microsoft.com/sqlserverstorageengine/2011/12/22/alwayson-challenges-with-statistics-on-readonly-database-database-snapshot-and-secondary-replica/ 

    下图解释了数据是如何从主副本传输到辅助副本的:


    如果你还有什么问题,请告诉我,我将非常乐意为您解答。

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2016年12月20日 3:25
    版主
  • 谢谢您的回复,我使用的版本是SQL Server 2014。

    看完您的内容,很抱歉我还是很困惑。

    从您链接的博客中,我看到的read only database是之所以不能使用到up to date的统计信息,是因为他的状态是只读的,如果将其设置为读写后,在更新统计信息便可以使得查询使用到up to date统计信息。

    按照这个逻辑,请来看看我的问题,当辅助副本执行异常(非常缓慢后)时,假设此时是统计信息过期导致,那么此时主副本上的统计信息应该也过期了,当我在主副本执行同样的查询时,我假设此时存储引擎自动更新了统计信息所以使得主副本的查询执行很快,那么根据AlwaysOn的特性,主副本上更新的统计信息也会同步到辅助副本,所以辅助副本再次执行查询应该会很快。

    但实际情况不是这样的,无论我在主副本上执行多少次,辅助副本依旧是很慢,直到在主副本上手动更新了统计信息后才解决。


    chenl

    2016年12月21日 8:12
  • I6first 你好,

    首先,非常抱歉,可能我的回复并没有解决您的困惑。

    第一点,我们这里讲的read only database指的是在辅助副本上的,这个其实并不能设置为读写状态(除了故障转移之后辅助副本变成主副本)。首先,根据你所说的,你在主副本和辅助副本上面都执行了查询,那么在辅助副本上执行查询,我们需要设置Readable Secondary Replicas(如果没有设置的话,是不能在辅助副本上查询),设置了这个之后呢,当你在辅助副本上执行查询的时候,就会产生read-only workload。

    然后我们执行查询需要使用统计信息进行优化,但是因为辅助副本上的数据库只有读的权限,并不能在辅助副本上产生独立的统计信息,当然后续版本中有了临时的统计信息,但是如你后文所说,并没有开启,所以只能使用主副本传过来的统计信息。

    第二点,“那么根据AlwaysOn的特性,主副本上更新的统计信息也会同步到辅助副本,所以辅助副本再次执行查询应该会很快”,这个理解并不对,造成统计信息失效的原因是主副本和辅助副本上的数据并不是真正同步的,上一个回复当中,我给了一张图,解释了这个,当辅助副本接到了主副本传过来的事务日志,固化到log里面的时候,就会返回一个信息给主副本,告诉主副本你可以开始工作了,然后辅助副本开始事务日志Redo,写到数据库里面。其实这个过程中,主副本那儿又接受了很多数据,所以造成了数据量比较大的时候主副本传过来的统计信息不能用,因为两个副本里面的数据根本不一样,这个就是原因。

    你可以参考一下官方文档上的这句话:“Statistics on columns of tables and indexed views are used to optimize query plans. For availability groups, statistics that are created and maintained on the primary databases are automatically persisted on the secondary databases as part of applying the transaction log records. However, the read-only workload on the secondary databases may need different statistics than those that are created on the primary databases. However, because secondary databases are restricted to read-only access, statistics cannot be created on the secondary databases.”


    如果有什么问题的话,我们可以继续交流,一起探讨。

    Best Regards,
    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    2016年12月21日 9:54
    版主
  • "其实这个过程中,主副本那儿又接受了很多数据,所以造成了数据量比较大的时候主副本传过来的统计信息不能用,因为两个副本里面的数据根本不一样,这个就是原因"

    个人推测哈,统计信息即使不是像数据日志一样及时传给副本,那么定时数秒、数分钟也会以日志方式传给副本,副本接收后刷新到统计数据上,那么在副本上查询应该速度就OK了,即使有个间隔期间统计不准,但这个时间间隔不会太久,按常理,几分钟足够了吧


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2016年12月22日 5:35
  • "其实这个过程中,主副本那儿又接受了很多数据,所以造成了数据量比较大的时候主副本传过来的统计信息不能用,因为两个副本里面的数据根本不一样,这个就是原因"

    个人推测哈,统计信息即使不是像数据日志一样及时传给副本,那么定时数秒、数分钟也会以日志方式传给副本,副本接收后刷新到统计数据上,那么在副本上查询应该速度就OK了,即使有个间隔期间统计不准,但这个时间间隔不会太久,按常理,几分钟足够了吧


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com


    永久统计信息的创建/更新,也是在transactional log里面推送到辅助副本的。

    想不想时已是想,不如不想都不想。

    2016年12月22日 6:40
    版主
  • "所以造成了数据量比较大的时候主副本传过来的统计信息不能用,因为两个副本里面的数据根本不一样,这个就是原因"

    有个背景我没有交待,就是我在处理这个问题的时候花了半天时间(我从上午11:00开始处理,到晚上18:30),期间我确定主副本和辅助副本的数据是同步的,所以您说的两边的数据根本不一样应该不是这个问题原因。


    chenl

    2016年12月22日 8:10
  • 确实,我当时是花了半天的时间来处理这个问题。我觉得不是数据没有同步造成的。

    chenl

    2016年12月22日 8:10
  • 我们来做一个假设,假设统计信息是准确的(因为主副本的查询很快),但辅助副本的查询优化器在解析查询时并没有用到这个统计信息,因而导致了查询很慢,当我在主副本执行了统计信息更新后,虽然更新并没有造成统计信息有什么变化,但这个更新操作触发了某种机制,但这个机制同样同步到了辅助副本后,使得辅助副本优化器重新读取新的统计信息,从而使得查询变快。

    有没有这种可能,如果这种可能存在,那么在统计信息准确的情况下,查询优化器在什么情况下还会用到“之前不准确”——历史的统计信息呢?


    chenl

    2016年12月22日 8:26
  • After updating stats, optimizer will recompile sp to get new execution plan based on current stats.
    2016年12月22日 19:33
  • 统计信息应该在更新统计信息的前后都是准确的,rmiao大侠的意思是重新生成了执行计划,不过我在处理问题的过程中尝试过清除执行计划缓存和数据缓存,按理说清除了后重新执行查询也会重新生成执行计划,但结果还是执行慢。

    chenl

    2016年12月28日 9:39