如何监视自适应缓存How to monitor the adaptive cache

本文介绍如何监视和排查查询性能下降的问题,只需确定工作负荷是否在充分利用专用 SQL 池的自适应缓存即可。This article describes how to monitor and troubleshoot slow query performance by determining whether your workload is optimally leveraging the adaptive cache for dedicated SQL pools.

专用 SQL 池存储体系结构自动将最常查询的列存储段分层到驻留在基于 NVMe 的 SSD 上的缓存中。The dedicated SQL pool storage architecture automatically tiers your most frequently queried columnstore segments in a cache residing on NVMe based SSDs. 如果查询检索驻留在缓存中的段,则可提高性能。You will have greater performance when your queries retrieve segments that are residing in the cache.

使用 Azure 门户进行故障排除Troubleshoot using the Azure portal

可以使用 Azure Monitor 来查看缓存指标,以便排查查询性能问题。You can use Azure Monitor to view cache metrics to troubleshoot query performance. 首先转到 Azure 门户,然后依次单击“监视” 、“指标” 和“+ 选择范围” :First go to the Azure portal and click on Monitor, Metrics and + Select a scope:

屏幕截图显示了从 Azure 门户的“指标”中选择的“选择范围”。

使用搜索栏和下拉栏找到你的专用 SQL 池。Use the search and drop down bars to locate your dedicated SQL pool. 然后选择“应用”。Then select apply.

屏幕截图显示了“选择范围”窗格,可在其中选择数据仓库。

排查缓存问题时,关键指标是“缓存命中百分比”和“缓存使用百分比”。 The key metrics for troubleshooting the cache are Cache hit percentage and Cache used percentage. 选择“缓存命中百分比” ,然后使用“添加指标” 按钮添加“缓存已用百分比” 。Select Cache hit percentage then use the add metric button to add Cache used percentage.

缓存指标

缓存命中和使用百分比Cache hit and used percentage

下表根据缓存指标的值对场景进行了说明:The matrix below describes scenarios based on the values of the cache metrics:

缓存命中百分比高High Cache hit percentage 缓存命中百分比低Low Cache hit percentage
缓存使用百分比高High Cache used percentage 方案 1Scenario 1 方案 2Scenario 2
缓存使用百分比低Low Cache used percentage 方案 3Scenario 3 方案 4Scenario 4

场景 1: 你的缓存使用已优化。Scenario 1: You are optimally using your cache. 排查可能导致查询速度变慢的其他方面的情况。Troubleshoot other areas that may be slowing down your queries.

场景 2: 当前工作数据集不适合放置在缓存中,这会因物理读取导致缓存命中百分比低。Scenario 2: Your current working data set cannot fit into the cache which causes a low cache hit percentage due to physical reads. 考虑提升性能级别并重新运行工作负荷,以便填充缓存。Consider scaling up your performance level and rerun your workload to populate the cache.

场景 3: 查询运行速度慢的原因可能与缓存无关。Scenario 3: It is likely that your query is running slow due to reasons unrelated to the cache. 排查可能导致查询速度变慢的其他方面的情况。Troubleshoot other areas that may be slowing down your queries. 也可以考虑缩减实例,通过缩减缓存大小来节省成本。You can also consider scaling down your instance to reduce your cache size to save costs.

场景 4: 你使用了冷缓存,这可能是查询速度慢的原因。Scenario 4: You had a cold cache which could be the reason why your query was slow. 考虑重新运行查询,因此工作数据集现在应该位于缓存中。Consider rerunning your query as your working dataset should now be in cached.

重要

如果缓存命中百分比或缓存使用百分比在重新运行工作负荷后未更新,则表明工作集可能已驻留在内存中。If the cache hit percentage or cache used percentage isn't updating after rerunning your workload, your working set can already be residing in memory. 仅缓存聚集列存储表。Only clustered columnstore tables are cached.

后续步骤Next steps

有关常规查询性能优化的详细信息,请参阅监视查询执行For more information on general query performance tuning, see Monitor query execution.