优化Power BI中的Power Query合并查询效率：每个Query最高256MB内存限制的说明

关于优化Power BI中的Power Query合并查询效率，之前写过三篇文章：

优化Power BI中的Power Query合并查询效率，Part 1：通过删除列来实现

优化Power BI中的Power Query合并查询效率，Part 2：合并查询前or后删除多余的列有区别吗？

优化Power BI中的Power Query合并查询效率，Part 3：Table.Join和SortMerge

针对文中说到的256MB内存限制的问题，有朋友提出了质疑：

为此，我也专门去研究了一番，找到了一些解答。

Chris Webb的原文中是这么说的：

其实作者只是根据以往的经验和博客内容进行的合理猜测。作者给出了原文链接，所以先找到原文看看是怎么说的：

https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-in-power-bi/

在这篇文章中，作者提到2020年4月PowerBI的更新中Query Diagnostics功能被加强：

https://docs.microsoft.com/en-us/power-query/querydiagnostics

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2020-feature-summary/#_Enhancements_to_Query

Power Query每半秒种会记录一次资源利用情况，如果是时间很短的查询，没啥用处，但是如果查询时间很长，那么就可以用它来进行一些分析了。

下图就是一个查询的过程被Query Diagnostics记录了一下，横轴是时间，纵轴是消耗的内存：

上图没什么异常，因为使用内存比较小。

不过，当运行一个需要内存较大的查询时，就会出现下面的图形：

黄色的线代表提交的数据量大小，显示的是真实的查询占用的内存；而蓝色的线代表的是查询使用的物理内存的大小。我们可以看到，单一查询的物理内存使用永远不会超过256MB，那么查询一旦大于256MB怎么办呢？只能调用虚拟内存分页机制了，这个过程造成了运行效率降低。

为什么限制了物理内存的使用未256MB呢？

微软开发团队Curt Hagenlocher大神在解答中是这么说的：

原文链接：https://social.technet.microsoft.com/Forums/en-US/471b18e6-9086-489d-9445-12b3a6205120/pq-query-optimization?forum=powerquery)

将表存储在内存中，这样后续再调用这些表的时候可以直接在内存中调用，而不是再从硬盘中获取，提高了效率。但是这样会造成一个问题，如果表比较多的情况下，那么会发生内存占用非常大从而导致卡死的现象，为了避免这个情况发生，对每个查询限制了256MB的内存最高占用，一旦超过256MB的查询，会强行使用页面文件。

以下这篇文章也给出了256MB限制的说明：

https://social.technet.microsoft.com/Forums/en-US/d7a858db-9905-4f97-8a77-bed761a92423/questions-about-the-technical-limits-of-power-query?forum=powerquery

参考文档：

https://blog.crossjoin.co.uk/2020/05/21/monitoring-power-query-memory-usage-with-query-diagnostics-in-power-bi/

https://docs.microsoft.com/en-us/power-query/querydiagnostics

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2020-feature-summary/#_Enhancements_to_Query

https://social.technet.microsoft.com/Forums/en-US/471b18e6-9086-489d-9445-12b3a6205120/pq-query-optimization?forum=powerquery)

https://blog.crossjoin.co.uk/2019/04/21/power-bi-dataflow-container-size/

https://social.technet.microsoft.com/Forums/en-US/d7a858db-9905-4f97-8a77-bed761a92423/questions-about-the-technical-limits-of-power-query?forum=powerquery