适用于 Azure SQL 数据库的 Query Performance InsightQuery Performance Insight for Azure SQL Database

适用于: Azure SQL 数据库

Query Performance Insight 为单一数据库和共用数据库提供了智能查询分析。Query Performance Insight provides intelligent query analysis for single and pooled databases. 它有助于确定工作负荷中资源消耗最多且运行时间最长的查询。It helps identify the top resource consuming and long-running queries in your workload. 这可帮助你找到要优化的查询,以提高整体工作负荷性能并有效使用要支付的资源。This helps you find the queries to optimize to improve overall workload performance and efficiently use the resource that you are paying for. Query Performance Insight 通过提供以下功能,帮助减少排查数据库性能问题所花费的时间:Query Performance Insight helps you spend less time troubleshooting database performance by providing:

  • 深入了解数据库资源 (DTU) 消耗Deeper insight into your databases resource (DTU) consumption
  • 有关按 CPU、持续时间和执行计数列出的热门数据库查询的详细信息(用于性能改进的潜在优化候选项)Details on top database queries by CPU, duration, and execution count (potential tuning candidates for performance improvements)
  • 可以深入到查询详细信息,以查看查询文本和资源利用率历史记录The ability to drill down into details of a query, to view the query text and history of resource utilization
  • 用于显示数据库顾问提供的性能建议的注释Annotations that show performance recommendations from database advisors

Query Performance Insight

先决条件Prerequisites

查询性能见解要求已在数据库上启用 Query StoreQuery Performance Insight requires that Query Store is active on your database. 默认情况下,会自动为 Azure SQL 数据库中的所有数据库启用查询存储。It's automatically enabled for all databases in Azure SQL Database by default. 如果查询存储未运行,Azure 门户将提示你启用它。If Query Store is not running, the Azure portal will prompt you to enable it.

备注

如果门户中显示“未在此数据库中正确配置查询存储”消息,请参阅优化查询存储的配置If the "Query Store is not properly configured on this database" message appears in the portal, see Optimizing the Query Store configuration.

权限Permissions

需要拥有以下 Azure 基于角色的访问控制 (Azure RBAC) 权限才能使用 Query Performance Insight:You need the following Azure role-based access control (Azure RBAC) permissions to use Query Performance Insight:

  • 需要拥有“读取者”、“所有者”、“参与者”、“SQL DB 参与者”或“SQL Server 参与者”权限才能查看资源消耗量靠前的查询和图表。 Reader, Owner, Contributor, SQL DB Contributor, or SQL Server Contributor permissions are required to view the top resource-consuming queries and charts.
  • 需要具备 所有者参与者SQL DB 参与者SQL Server 参与者 权限才能查看查询文本。Owner, Contributor, SQL DB Contributor, or SQL Server Contributor permissions are required to view query text.

使用 Query Performance InsightUse Query Performance Insight

Query Performance Insight 很容易使用:Query Performance Insight is easy to use:

  1. 打开 Azure 门户并找到要检查的数据库。Open the Azure portal and find a database that you want to examine.

  2. 在左侧菜单中,打开“智能性能” > “Query Performance Insight”。 From the left-side menu, open Intelligent Performance > Query Performance Insight.

    菜单中的“Query Performance Insight”

  3. 在第一个选项卡上,查看资源占用排名靠前的查询列表。On the first tab, review the list of top resource-consuming queries.

  4. 选择单个查询以查看其详细信息。Select an individual query to view its details.

  5. 打开“智能性能” > “性能建议”,检查是否提供了任何性能建议。 Open Intelligent Performance > Performance recommendations and check if any performance recommendations are available. 有关内置性能建议的详细信息,请参阅 Azure SQL 数据库顾问For more information on built-in performance recommendations, see Azure SQL Database Advisor.

  6. 使用滑块或缩放图标更改检测间隔。Use sliders or zoom icons to change the observed interval.

    性能仪表板

备注

要使 Azure SQL 数据库在 Query Performance Insight 中呈现信息,查询存储需要捕获几个小时的数据。For Azure SQL Database to render the information in Query Performance Insight, Query Store needs to capture a couple hours of data. 如果在某段时间内数据库不活动或查询存储不活动,则 Query Performance Insight 在显示该时间范围时,图表将是空的。If the database has no activity or if Query Store was not active during a certain period, the charts will be empty when Query Performance Insight displays that time range. 如果查询存储未运行,随时可以启用它。You can enable Query Store at any time if it's not running. 有关详细信息,请参阅有关查询存储的最佳做法For more information, see Best practices with Query Store.

若要查看数据库性能建议,请在 Query Performance Insight 导航边栏选项卡上选择“建议”。For database performance recommendations, select Recommendations on the Query Performance Insight navigation blade.

“建议”选项卡

查看 CPU 消耗量靠前的查询Review top CPU-consuming queries

默认情况下,首次打开 Query Performance Insight 时,它会显示 CPU 消耗量靠前的五个查询。By default, Query Performance Insight shows the top five CPU-consuming queries when you first open it.

  1. 使用复选框选择或清除要在图表中包含或排除的各个查询。Select or clear individual queries to include or exclude them from the chart by using check boxes.

    最上面一行显示数据库的总体 DTU 百分比。The top line shows overall DTU percentage for the database. 条形显示所选查询在所选间隔内消耗的 CPU 百分比。The bars show CPU percentage that the selected queries consumed during the selected interval. 例如,如果选择了“过去一周”,则每个条形代表一天。For example, if Past week is selected, each bar represents a single day.

    消耗量靠前的查询

    重要

    显示的 DTU 线条将与一小时期限内的最大消耗值相聚合。The DTU line shown is aggregated to a maximum consumption value in one-hour periods. 此信息仅供与查询执行统计信息进行高级比较。It's meant for a high-level comparison only with query execution statistics. 在某些情况下,与执行的查询相比,DTU 利用率看似过高,但事实可能并非如此。In some cases, DTU utilization might seem too high compared to executed queries, but this might not be the case.

    例如,如果某个查询只是在几分钟时间内利用了 100% 的 DTU,Query Performance Insight 中的 DTU 线条会将整个小时的消耗量显示为 100%(最大聚合值的结果)。For example, if a query maxed out DTU to 100% for a few minutes only, the DTU line in Query Performance Insight will show the entire hour of consumption as 100% (the consequence of the maximum aggregated value).

    若要进行更精细的比较(一分钟精度),请考虑创建自定义的 DTU 利用率图表:For a finer comparison (up to one minute), consider creating a custom DTU utilization chart:

    1. 在 Azure 门户中,选择“Azure SQL 数据库” > “监视”。 In the Azure portal, select Azure SQL Database > Monitoring.
    2. 选择“指标”。Select Metrics.
    3. 选择“+添加图表”。Select +Add chart.
    4. 在图表上选择 DTU 百分比。Select the DTU percentage on the chart.
    5. 另外,请左上方的菜单中选择“过去 24 小时”,并将其更改为 1 分钟。In addition, select Last 24 hours on the upper-left menu and change it to one minute.

    使用详细程度更高的自定义 DTU 图表来与查询执行图表进行比较。Use the custom DTU chart with a finer level of details to compare with the query execution chart.

    底部网格显示可见查询的聚合信息:The bottom grid shows aggregated information for the visible queries:

    • 查询 ID(数据库中的查询的唯一标识符)。Query ID, which is a unique identifier for the query in the database.
    • 每个查询在可观测的间隔内消耗的 CPU(取决于聚合函数)。CPU per query during an observable interval, which depends on the aggregation function.
    • 每个查询的持续时间(也取决于聚合函数)。Duration per query, which also depends on the aggregation function.
    • 特定查询的执行总次数。Total number of executions for a specific query.
  2. 如果数据已过时,请选择“刷新”按钮。If your data becomes stale, select the Refresh button.

  3. 使用滑块和缩放按钮来更改观测间隔和调查消耗峰值:Use sliders and zoom buttons to change the observation interval and investigate consumption spikes:

    用于更改间隔的滑块和缩放按钮

  4. 或者,可以选择“自定义”选项卡来自定义以下属性的视图:Optionally, you can select the Custom tab to customize the view for:

    • 指标(CPU、持续时间、执行计数)。Metric (CPU, duration, execution count).
    • 时间间隔(过去 24 小时、过去一周或过去一个月)。Time interval (last 24 hours, past week, or past month).
    • 查询数。Number of queries.
    • 聚合函数。Aggregation function.

    “自定义”选项卡

  5. 选择“转到 >”按钮查看自定义的视图。Select the Go > button to see the customized view.

    重要

    Query Performance Insight 只能显示消耗量靠前的 5 到 20 个查询,具体取决于所做的选择。Query Performance Insight is limited to displaying the top 5-20 consuming queries, depending on your selection. 数据库运行的查询数可能远远超过显示的查询数,但图表中不包括这些多出的查询。Your database can run many more queries beyond the top ones shown, and these queries will not be included on the chart.

    某种数据库工作负荷类型中可能存在大量的小型查询,其数目超过了显示的数目,并且它们频繁运行,占用了大部分 DTU。There might exist a database workload type in which lots of smaller queries, beyond the top ones shown, run frequently and use the majority of DTU. 这些查询也不会显示在性能图表上。These queries don't appear on the performance chart.

    例如,某个查询可能在一段时间内消耗了大量的 DTU,不过,在观测期内,其总消耗量小于其他消耗量靠前的查询。For example, a query might have consumed a substantial amount of DTU for a while, although its total consumption in the observed period is less than the other top-consuming queries. 在这种情况下,此查询的资源利用率不会显示在图表上。In such a case, resource utilization of this query would not appear on the chart.

查看单个查询的详细信息View individual query details

若要查看查询详细信息,请执行以下操作:To view query details:

  1. 在消耗量靠前的查询列表中选择任一查询。Select any query in the list of top queries.

    消耗量靠前的查询列表

    此时会打开详细视图。A detailed view opens. 其中显示了不同时间的 CPU 消耗量、持续时间和执行计数。It shows the CPU consumption, duration, and execution count over time.

  2. 选择用于查看详细信息的图表功能。Select the chart features for details.

    • 顶部图表显示一条反映数据库 DTU 总百分比的线条。The top chart shows a line with the overall database DTU percentage. 条形代表所选查询消耗的 CPU 百分比。The bars are the CPU percentage that the selected query consumed.
    • 第二个图表显示所选查询的总持续时间。The second chart shows the total duration of the selected query.
    • 底部图表显示所选查询的执行总数。The bottom chart shows the total number of executions by the selected query.

    查询详细信息

  3. 或者,可以使用滑块、缩放按钮或选择“设置”来自定义查询数据显示方式或选择不同的时间范围。Optionally, use sliders, use zoom buttons, or select Settings to customize how query data is displayed, or to pick a different time range.

    重要

    Query Performance Insight 不捕获任何 DDL 查询。Query Performance Insight does not capture any DDL queries. 在某些情况下,它可能不会捕获所有即席查询。In some cases, it might not capture all ad hoc queries.

根据持续时间查看热门查询Review top queries per duration

Query Performance Insight 中的两个指标可帮助你查找潜在的瓶颈:持续时间和执行计数。Two metrics in Query Performance Insight can help you find potential bottlenecks: duration and execution count.

长时间运行的查询长时间锁定资源、阻止其他用户和限制可伸缩性的可能性最大。Long-running queries have the greatest potential for locking resources longer, blocking other users, and limiting scalability. 它们也非常适合进行优化。They're also the best candidates for optimization.

识别长时间运行的查询:To identify long-running queries:

  1. 在 Query Performance Insight 中打开所选数据库对应的“自定义”选项卡。Open the Custom tab in Query Performance Insight for the selected database.

  2. 将指标更改为“持续时间”。Change the metrics to duration.

  3. 选择查询数和观测间隔。Select the number of queries and the observation interval.

  4. 选择聚合函数:Select the aggregation function:

    • Sum 对整个观测间隔内的所有查询执行时间求和。Sum adds up all query execution time for the whole observation interval.
    • Max 查找整个观测间隔内执行时间最长的查询。Max finds queries in which execution time was maximum for the whole observation interval.
    • Avg 查找所有查询执行的平均执行时间,并显示计算平均值时执行时间靠前的查询。Avg finds the average execution time of all query executions and shows you the top ones for these averages.

    查询持续时间

  5. 选择“转到 >”按钮查看自定义的视图。Select the Go > button to see the customized view.

    重要

    调整查询视图不会更新 DTU 线条。Adjusting the query view does not update the DTU line. DTU 线条始终显示间隔的最大消耗值。The DTU line always shows the maximum consumption value for the interval.

    若要更详细了解数据库 DTU 消耗(一分钟精度),请考虑在 Azure 门户中创建自定义图表:To understand database DTU consumption with more detail (up to one minute), consider creating a custom chart in the Azure portal:

    1. 选择“Azure SQL 数据库” > “监视”。 Select Azure SQL Database > Monitoring.
    2. 选择“指标”。Select Metrics.
    3. 选择“+添加图表”。Select +Add chart.
    4. 在图表上选择 DTU 百分比。Select the DTU percentage on the chart.
    5. 另外,请左上方的菜单中选择“过去 24 小时”,并将其更改为 1 分钟。In addition, select Last 24 hours on the upper-left menu and change it to one minute.

    我们建议使用自定义 DTU 图表来与查询性能图表进行比较。We recommend that you use the custom DTU chart to compare with the query performance chart.

根据执行计数查看热门查询Review top queries per execution count

即使大量的执行不会影响数据库本身,并且资源使用率较低,使用该数据库的用户应用程序也可能会变慢。A user application that uses the database might get slow, even though a high number of executions might not be affecting the database itself and resources usage is low.

在某些情况下,较高的执行计数可能会导致网络往返增加。In some cases, a high execution count can lead to more network round trips. 往返会影响性能。Round trips affect performance. 它们会造成网络延迟和下游服务器的延迟。They're subject to network latency and to downstream server latency.

例如,许多数据驱动的网站会频繁访问数据库来完成每个用户请求。For example, many data-driven websites heavily access the database for every user request. 尽管连接池会有所帮助,但增加的网络流量和处理服务器上的负载可能会降低性能。Although connection pooling helps, the increased network traffic and processing load on the server can slow performance. 一般情况下,应将往返保持在最低限度。In general, keep round trips to a minimum.

识别频繁执行的(“聊天式”)查询:To identify frequently executed ("chatty") queries:

  1. 在 Query Performance Insight 中打开所选数据库对应的“自定义”选项卡。Open the Custom tab in Query Performance Insight for the selected database.

  2. 将指标更改为“执行计数”。Change the metrics to execution count.

  3. 选择查询数和观测间隔。Select the number of queries and the observation interval.

  4. 选择“转到 >”按钮查看自定义的视图。Select the Go > button to see the customized view.

    查询执行计数

了解性能优化注释Understand performance tuning annotations

在 Query Performance Insight 中浏览工作负荷时,可能会注意到图表顶部上带有竖线的图标。While exploring your workload in Query Performance Insight, you might notice icons with a vertical line on top of the chart.

这些图标是注释。These icons are annotations. 注释显示 Azure SQL 数据库顾问提供的性能建议。They show performance recommendations from Azure SQL Database Advisor. 将鼠标悬停在某条注释上可以获取有关性能建议的摘要信息。By hovering over an annotation, you can get summarized information on performance recommendations.

查询注释

若要了解更多信息或应用顾问的建议,请选择该图标以打开建议操作的详细信息。If you want to understand more or apply the advisor's recommendation, select the icon to open details of the recommended action. 如果这是一条可行的建议,可在门户中直接应用它。If this is an active recommendation, you can apply it right away from the portal.

查询注释详细信息

在某些情况下,由于缩放级别的设置,相互靠近的注释可能会折叠成单个注释。In some cases, due to the zoom level, it's possible that annotations close to each other are collapsed into a single annotation. Query Performance Insight 以组注释图标的形式显示此信息。Query Performance Insight represents this as a group annotation icon. 选择组注释图标会打开一个新的边栏选项卡,其中列出了每条注释。Selecting the group annotation icon opens a new blade that lists the annotations.

关联查询和性能优化操作有助于更好地了解工作负荷。Correlating queries and performance-tuning actions might help you to better understand your workload.

优化查询存储配置Optimize the Query Store configuration

使用 Query Performance Insight 时,可能会看到以下查询存储错误消息:While using Query Performance Insight, you might see the following Query Store error messages:

  • “此数据库上的 Query Store 未正确配置。"Query Store is not properly configured on this database. 请单击此处了解详细信息。”Click here to learn more."
  • “此数据库上的 Query Store 未正确配置。"Query Store is not properly configured on this database. 单击此处更改设置。”Click here to change settings."

当查询存储无法收集新数据时,通常会显示这些消息。These messages usually appear when Query Store can't collect new data.

查询存储处于只读状态且以最佳方式设置参数时,会发生第一种情况。The first case happens when Query Store is in the read-only state and parameters are set optimally. 可以通过增加数据存储的大小或清除查询存储来解决此问题。You can fix this by increasing the size of the data store, or by clearing Query Store. (如果清除查询存储,以前收集的所有遥测数据将会丢失。)(If you clear Query Store, all previously collected telemetry will be lost.)

查询存储详细信息

查询存储未启用,或者未以最佳方式设置参数时,会发生第二种情况。The second case happens when Query Store is not enabled, or parameters are not set optimally. 可以通过运行 SQL Server Management Studio (SSMS) 或 Azure 门户中提供的以下命令,来更改保留和捕获策略,同时启用查询存储。You can change the retention and capture policy, and also enable Query Store, by running the following commands provided from SQL Server Management Studio (SSMS) or the Azure portal.

有两种类型的保留策略:There are two types of retention policies:

  • 基于大小:如果将此策略设置为“自动”,则会在快要达到最大大小时自动清除数据。Size based: If this policy is set to AUTO, it will clean data automatically when near maximum size is reached.
  • 基于时间:此策略默认设置为 30 天。Time based: By default, this policy is set to 30 days. 如果查询存储的空间不足,它会删除 30 天以前的查询信息。If Query Store runs out of space, it will delete query information older than 30 days.

可将捕获策略设置为:You can set the capture policy to:

  • 全部:查询存储将捕获所有查询。All: Query Store captures all queries.
  • 自动:查询存储将忽略不频繁的查询以及编译和执行持续时间很短的查询。Auto: Query Store ignores infrequent queries and queries with insignificant compile and execution duration. 执行计数、编译持续时间和运行时持续时间的阈值在内部确定。Thresholds for execution count, compile duration, and runtime duration are internally determined. 这是默认选项。This is the default option.
  • :查询存储将停止捕获新查询,但仍会收集已捕获的查询的运行时统计信息。None: Query Store stops capturing new queries, but runtime statistics for already captured queries are still collected.

我们建议通过执行 SSMS 或 Azure 门户中的以下命令,将所有策略设置为“自动”,将清理策略设置为 30 天。We recommend setting all policies to AUTO and the cleaning policy to 30 days by executing the following commands from SSMS or the Azure portal. (请将 YourDB 替换为数据库名称。)(Replace YourDB with the database name.)

    ALTER DATABASE [YourDB]
    SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

    ALTER DATABASE [YourDB]
    SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

    ALTER DATABASE [YourDB]
    SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

通过 SSMS 或 Azure 门户连接到数据库并运行以下查询,以增加查询存储的大小。Increase the size of Query Store by connecting to a database through SSMS or the Azure portal and running the following query. (请将 YourDB 替换为数据库名称。)(Replace YourDB with the database name.)

    ALTER DATABASE [YourDB]
    SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

应用这些设置最终会使查询存储收集新查询的遥测数据。Applying these settings will eventually make Query Store collect telemetry for new queries. 如果需要查询存储立即正常运行,可以选择性地通过 SSMS 或 Azure 门户运行以下查询,以清除查询存储。If you need Query Store to be operational right away, you can optionally choose to clear Query Store by running the following query through SSMS or the Azure portal. (请将 YourDB 替换为数据库名称。)(Replace YourDB with the database name.)

备注

运行以下查询会删除查询存储中以前收集到的所有受监视遥测数据。Running the following query will delete all previously collected monitored telemetry in Query Store.

    ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;