Intelligent Insights 使用 AI 來針對資料庫的效能進行監視和疑難排解Intelligent Insights using AI to monitor and troubleshoot database performance

Azure SQL Database Intelligent Insights 可讓您知道 SQL Database 和受控執行個體資料庫效能發生了什麼。Azure SQL Database Intelligent Insights lets you know what is happening with your SQL Database and Managed Instance database performance.

Intelligent Insights 使用內建的智慧功能透過人工智慧持續監視資料庫使用情況,並偵測導致效能不佳的干擾性事件。Intelligent Insights uses built-in intelligence to continuously monitor database usage through artificial intelligence and detect disruptive events that cause poor performance. 一旦偵測到,它就會執行詳細的分析來產生含有該問題智慧型評估的診斷記錄。Once detected, a detailed analysis is performed that generates a diagnostics log with an intelligent assessment of the issue. 此評估包含資料庫效能問題的根本原因分析,並在可能的情況下提供效能改進建議。This assessment consists of a root cause analysis of the database performance issue and, where possible, recommendations for performance improvements.

Intelligent Insights 可以為您做什麼What can Intelligent Insights do for you

Intelligent Insights 是 Azure 內建智慧功能的一項獨特功能,可提供下列價值:Intelligent Insights is a unique capability of Azure built-in intelligence that provides the following value:

  • 主動監視Proactive monitoring
  • 量身打造的效能深入解析Tailored performance insights
  • 提早偵測到資料庫效能降低Early detection of database performance degradation
  • 針對所偵測到問題的根本原因分析Root cause analysis of issues detected
  • 效能改進建議Performance improvement recommendations
  • 以數十萬個資料庫向外延展的功能Scale out capability on hundreds of thousands of databases
  • 為 DevOps 資源及擁有權總成本帶來正面影響Positive impact to DevOps resources and the total cost of ownership

Intelligent Insights 如何運作How does Intelligent Insights work

Intelligent Insights 會藉由將最後一小時的資料庫工作負載與過去七天的基準工作負載做比較,來分析資料庫效能。Intelligent Insights analyzes database performance by comparing the database workload from the last hour with the past seven-day baseline workload. 資料庫工作負載是由被判斷為對資料庫效能最具影響的查詢 (例如重複次數最多和最大型的查詢) 所組成。Database workload is composed of queries determined to be the most significant to the database performance, such as the most repeated and largest queries. 由於每個資料庫皆根據其結構、資料、使用方式及應用程式而有所不同,因此所產生的每個工作負載基準對個別執行個體來說都是特定且唯一的。Because each database is unique based on its structure, data, usage, and application, each workload baseline that is generated is specific and unique to an individual instance. 獨立於工作負載基準之外,Intelligent Insights 也會監視絕對作業臨界值,並偵測有關等候時間過長的問題、重大例外狀況,以及有關可能影響效能之查詢參數化的問題。Intelligent Insights, independent of the workload baseline, also monitors absolute operational thresholds and detects issues with excessive wait times, critical exceptions, and issues with query parameterizations that might affect performance.

在使用人工智慧透過多個受觀察計量偵測到效能降低問題之後,系統便會執行分析。After a performance degradation issue is detected from multiple observed metrics by using artificial intelligence, analysis is performed. 系統會產生診斷記錄,以及針對您資料庫所發生之情況的智慧型深入解析。A diagnostics log is generated with an intelligent insight on what is happening with your database. Intelligent Insights 可讓您從資料庫效能問題一開始出現到解決為止,都能輕鬆追蹤情況。Intelligent Insights makes it easy to track the database performance issue from its first appearance until resolution. 系統會針對每個偵測到的問題追蹤其整個生命週期 (從初始問題偵測、驗證效能改進,到其完成為止)。Each detected issue is tracked through its lifecycle from initial issue detection and verification of performance improvement to its completion.

資料庫效能分析工作流程

用來測量和偵測資料庫效能問題的計量,是以查詢持續時間、逾時要求、過長的等候時間及發生錯誤的要求作為基礎。The metrics used to measure and detect database performance issues are based on query duration, timeout requests, excessive wait times, and errored requests. 如需計量的詳細資訊,請參閱本文件的偵測計量一節。For more information on metrics, see the Detection metrics section of this document.

已識別的 SQL Database 效能降低會記錄在診斷記錄中,其中會包含由下列屬性組成的智慧項目:Identified SQL Database performance degradations are recorded in the diagnostics log with intelligent entries that consist of the following properties:

屬性Property 詳細資料Details
資料庫資訊Database information 偵測到有深入解析之資料庫的相關中繼資料,例如資源 URI。Metadata about a database on which an insight was detected, such as a resource URI.
觀察的時間範圍Observed time range 偵測到之深入解析的期間開始和結束時間。Start and end time for the period of the detected insight.
受影響的計量Impacted metrics 導致產生深入解析的計量:Metrics that caused an insight to be generated:
  • 查詢持續時間增加 [秒]。Query duration increase [seconds].
  • 過長的等候時間 [秒]。Excessive waiting [seconds].
  • 逾時的要求 [百分比]。Timed-out requests [percentage].
  • 發生錯誤的要求 [百分比]。Errored-out requests [percentage].
影響值Impact value 所測量計量的值。Value of a metric measured.
受影響的查詢和錯誤碼Impacted queries and error codes 查詢雜湊或錯誤碼。Query hash or error code. 這些可用來與受影響的查詢輕鬆相互關聯。These can be used to easily correlate to affected queries. 系統會提供由查詢持續時間增加、等候時間、逾時計數或錯誤碼所組成的計量。Metrics that consist of either query duration increase, waiting time, timeout counts, or error codes are provided.
偵測Detections 發生事件時在資料庫識別出的偵測。Detection identified at the database during the time of an event. 一共有 15 個偵測模式。There are 15 detection patterns. 如需詳細資訊,請參閱使用 Intelligent Insights 針對資料庫效能問題進行疑難排解For more information, see Troubleshoot database performance issues with Intelligent Insights.
根本原因分析Root cause analysis 所識別之問題的根原因分析,此分析會以人類看得懂的格式顯示。Root cause analysis of the issue identified in a human-readable format. 有些深入解析可能會在可能的情況下包含效能改進建議。Some insights might contain a performance improvement recommendation where possible.

如需搭配使用 Intelligent Insights 與 Azure SQL 分析以及一般使用方式情節的實際操作概觀,請觀看內嵌影片:For a hands-on overview on using Intelligent Insights with Azure SQL Analytics and for typical usage scenarios, see the embedded video:

Intelligent Insights 在發現和針對 SQL Database 效能問題進行疑難排解方面表現出色。Intelligent Insights shines in discovering and troubleshooting SQL Database performance issues. 若要使用 Intelligent Insights 來針對 SQL Database 和受控執行個體資料庫效能問題進行疑難排解,請參閱使用 Intelligent Insights 針對 Azure SQL Database 效能問題進行疑難排解In order to use Intelligent Insights to troubleshoot SQL Database and Managed Instance database performance issues, see Troubleshoot Azure SQL Database performance issues with Intelligent Insights.

設定 Intelligent InsightsConfigure Intelligent Insights

Intelligent Insights 的輸出是一個智慧效能診斷記錄。Output of the Intelligent Insights is a smart performance diagnostics log. 此記錄的使用方式有數種:透過將它串流處理到 Azure SQL 分析、Azure 事件中樞和 Azure 儲存體,或協力廠商產品。This log can be consumed in several ways - through streaming it to Azure SQL Analytics, Azure Event Hubs and Azure storage, or a third party product.

  • 搭配 Azure SQL 分析使用產品,以透過 Azure 入口網站使用者介面檢視見解。Use the product with Azure SQL Analytics to view insights through the user interface of the Azure portal. 這是整合式 Azure 解決方案,而且是檢視深入解析的最常見方式。This is the integrated Azure solution, and the most typical way to view insights.
  • 搭配使用產品與 Azure 事件中樞,以開發自訂監視和警示情節Use the product with Azure Event Hubs for development of custom monitoring and alerting scenarios
  • 搭配使用產品與 Azure 儲存體來開發自訂應用程式,例如自訂報告、長期資料封存等等。Use the product with Azure storage for custom application development, such are for example custom reporting, long-term data archival and so forth.

Intelligent Insights 與其他產品 (Azure SQL 分析、Azure 事件中樞、Azure 儲存體或供取用的協力廠商產品) 的整合方式,為先在資料庫的 [診斷設定] 刀鋒視窗中啟用 Intelligent Insights 記錄 ("SQLInsights" 記錄),然後將 Intelligent Insights 記錄資料設定為串流處理到其中一個產品。Integration of Intelligent Insights with other products Azure SQL Analytics, Azure Event Hub, Azure storage, or third party products for consumption is performed through first enabling Intelligent Insights logging (the "SQLInsights" log) in the Diagnostic settings blade of a database, and then configuring Intelligent Insights log data to be streamed into one of these products.

如需如何啟用 Intelligent Insights 記錄,以及將記錄資料設定為串流處理到取用產品的相關詳細資訊,請參閱 Azure SQL Database 計量和診斷記錄For more information on how to enable Intelligent Insights logging and to configure log data to be streamed to a consuming product, see Azure SQL Database metrics and diagnostics logging.

使用 Azure SQL 分析設定Set up with Azure SQL Analytics

Azure SQL 分析解決方案提供圖形化使用者介面、資料庫效能的報告和警示功能,以及 Intelligent Insights 診斷記錄資料。Azure SQL Analytics solution provides graphical user interface, reporting and alerting capabilities on database performance, along with the Intelligent Insights diagnostics log data.

提示

快速入門:開始使用 Intelligent Insights 的最簡單方式是將它與 Azure SQL 分析搭配使用,以提供資料庫效能問題的圖形化使用者介面。Quick getting started: The easiest way to get off the ground with using Intelligent Insights is to use it along with Azure SQL Analytics which will provide a graphical user interface to database performance issues. 從市集新增 Azure SQL 分析解決方案,並在此解決方案內建立工作區,然後針對您想要在其上啟用 Intelligent Insights 的每個資料庫,在資料庫的 [診斷設定] 刀鋒視窗中設定將 "SQLInsights" 記錄串流處理到 Azure SQL 分析的工作區。Add Azure SQL Analytics solution from the marketplace, create a workspace inside this solution, and then for each database you wish to enable Intelligent Insights on, configure streaming of "SQLInsights" log in the Diagnostics settings blade of a database to the workspace of Azure SQL Analytics.

預先需求是將 Azure SQL 分析從市集新增至 Azure 入口網站儀表板,以及建立工作區,請參閱設定 Azure SQL 分析Pre-requirement is to have Azure SQL Analytics added to your Azure portal dashboard from the marketplace and to create a workspace, see configure Azure SQL Analytics

若要搭配使用 Intelligent Insights 與 Azure SQL 分析,請將 Intelligent Insights 記錄資料設定為串流處理到您在上個步驟中建立的 Azure SQL 分析工作區,請參閱 Azure SQL Database 計量和診斷記錄To use Intelligent Insights with Azure SQL Analytics, configure Intelligent Insights log data to be streamed to Azure SQL Analytics workspace you've created in the previous step, see Azure SQL Database metrics and diagnostics logging.

下列範例顯示透過 Azure SQL 分析所檢視的 Intelligent Insights:The following example shows an Intelligent Insights viewed through Azure SQL Analytics:

Intelligent Insights 報表

使用事件中樞設定Set up with Event Hubs

若要使用 Intelligent Insights 搭配事件中樞,請將 Intelligent Insights 記錄資料設定為串流處理到事件中樞,請參閱將 Azure 診斷記錄串流處理至事件中樞To use Intelligent Insights with Event Hubs, configure Intelligent Insights log data to be streamed to Event Hubs, see Stream Azure diagnostics logs to Event Hubs.

若要使用事件中樞設定自訂監視和警示,請參閱如何在事件中樞處理計量和診斷記錄To use Event Hubs to setup custom monitoring and alerting, see What to do with metrics and diagnostics logs in Event Hubs.

使用 Azure 儲存體設定Set up with Azure Storage

若要使用 Intelligent Insights 搭配儲存體,請將 Intelligent Insights 記錄資料設定為串流處理到儲存體,請參閱串流處理到 Azure 儲存體To use Intelligent Insights with Storage, configure Intelligent Insights log data to be streamed to Storage, see Stream into Azure Storage.

自訂的 Intelligent Insights 記錄整合Custom integrations of Intelligent Insights log

若要使用 Intelligent Insights 搭配協力廠商工具,或自訂警示與監視開發,請參閱使用 Intelligent Insights 資料庫效能診斷記錄To use Intelligent Insights with third party tools, or for custom alerting and monitoring development, see Use the Intelligent Insights database performance diagnostics log.

偵測計量Detection metrics

用於產生 Intelligent Insights 之偵測模型的計量是以監視下列各項為基礎:Metrics used for detection models that generate Intelligent Insights are based on monitoring:

  • 查詢持續時間Query duration
  • 逾時要求Timeout requests
  • 過長的等候時間Excessive wait time
  • 發生錯誤的要求Errored out requests

查詢持續時間和逾時要求會作為偵測資料庫工作負載效能相關問題的主要模型。Query duration and timeout requests are used as primary models in detecting issues with database workload performance. 之所以會使用它們,是因為它們會直接測量工作負載發生什麼情況。They're used because they directly measure what is happening with the workload. 為了偵測所有可能的工作負載效能降低情況,會使用過長的等候時間和發生錯誤的要求作為額外的模型,以指出影響工作負載效能的問題。To detect all possible cases of workload performance degradation, excessive wait time and errored-out requests are used as additional models to indicate issues that affect the workload performance.

系統會自動將對工作負載所做的變更及對資料庫發出的查詢要求數所做的變更納入考量,以動態判斷正常和異常的資料庫效能臨界值。The system automatically considers changes to the workload and changes in the number of query requests made to the database to dynamically determine normal and out-of-the-ordinary database performance thresholds.

將會透過以科學方式衍生並將所偵測到之每個問題分類的資料模型,將所有計量以各種關聯性一起納入考量。All of the metrics are considered together in various relationships through a scientifically derived data model that categorizes each performance issue detected. 透過智慧型深入解析所提供的資訊包括:Information provided through an intelligent insight includes:

  • 所偵測到問題的詳細資料。Details of the performance issue detected.
  • 針對所偵測到問題的根本原因分析。A root cause analysis of the issue detected.
  • 在可能的情況下改善被監視 SQL 資料庫效能之方法的建議。Recommendations on how to improve the performance of the monitored SQL database, where possible.

查詢持續時間Query duration

查詢持續時間效能降低模型會分析個別的查詢,並偵測與效能基準相比,花費在編譯和執行查詢上的時間是否增加。The query duration degradation model analyzes individual queries and detects the increase in the time it takes to compile and execute a query compared to the performance baseline.

當 SQL Database 內建的智慧功能偵測到查詢編譯或查詢執行時間大幅增加而影響到工作負載效能時,就會將這些查詢標示為查詢持續時間效能降低問題。If SQL Database built-in intelligence detects a significant increase in query compile or query execution time that affects workload performance, these queries are flagged as query duration performance degradation issues.

Intelligent Insights 診斷記錄會輸出效能降低之查詢的查詢雜湊。The Intelligent Insights diagnostics log outputs the query hash of the query degraded in performance. 查詢雜湊會表示效能降低是否與會增加查詢持續時間的查詢編譯或執行時間增加有關。The query hash indicates whether the performance degradation was related to query compile or execution time increase, which increased query duration time.

逾時要求Timeout requests

逾時要求效能降低模型會分析個別的查詢,並偵測與效能基準期間相比,查詢執行層級的逾時及資料庫層級的整體要求逾時是否有任何增加。The timeout requests degradation model analyzes individual queries and detects any increase in timeouts at the query execution level and the overall request timeouts at the database level compared to the performance baseline period.

某些查詢甚至可能會在抵達執行階段之前便逾時。Some of the queries might time out even before they reach the execution stage. 透過比較已中止背景工作角色和做出的要求,SQL Database 的內建智慧功能會測量並分析所有抵達資料庫的查詢,無論它們是否抵達執行階段。Through the means of aborted workers vs. requests made, SQL Database built-in intelligence measures and analyzes all queries that reached the database whether they got to the execution stage or not.

在所執行之查詢的逾時數目或已中止之要求背景工作角色的數目增加到超出系統所管理的臨界值之後,診斷記錄中就會填入智慧型深入解析。After the number of timeouts for executed queries or the number of aborted request workers crosses the system-managed threshold, a diagnostics log is populated with intelligent insights.

產生的深入解析包括逾時要求的數目,以及逾時查詢的數目。The insights generated contain the number of timed-out requests and the number of timed-out queries. 效能降低的跡象會與執行階段的逾時提升相關聯,否則便會提供整體的資料庫層級。Indication of the performance degradation is related to timeout increase at the execution stage, or the overall database level is provided. 當系統認為逾時增加對資料庫效能有重大影響時,就會將這些查詢標示為逾時效能降低問題。When the increase in timeouts is deemed significant to database performance, these queries are flagged as timeout performance degradation issues.

過長的等候時間Excessive wait times

過長的等候時間模型會監視個別的資料庫查詢。The excessive wait time model monitors individual database queries. 它會偵測超出系統所管理之絕對臨界值的特高查詢等候統計資料。It detects unusually high query wait stats that crossed the system-managed absolute thresholds. 您可以使用新的 SQL Server 功能「查詢存放區等候統計資料」(sys.query_store_wait_stats),來觀察下列查詢過長等候時間計量:The following query excessive wait-time metrics are observed by using the new SQL Server feature, Query Store Wait Stats (sys.query_store_wait_stats):

  • 達到資源限制Reaching resource limits
  • 達到彈性集區資源限制Reaching elastic pool resource limits
  • 過多的背景工作或工作階段執行緒數目Excessive number of worker or session threads
  • 過多的資料庫鎖定Excessive database locking
  • 記憶體壓力Memory pressure
  • 其他等候統計資料Other wait stats

當達到資源限制或彈性集區資源限制時,即表示訂用帳戶上或彈性集區中的可用資源耗用量已超出絕對臨界值。Reaching resource limits or elastic pool resource limits denote that consumption of available resources on a subscription or in the elastic pool crossed absolute thresholds. 這些統計資料表示工作負載效能降低。These stats indicate workload performance degradation. 當背景工作或工作階段執行緒數目過多時,即表示所起始的背景工作執行緒或工作階段數目已超出絕對臨界值。An excessive number of worker or session threads denotes a condition in which the number of worker threads or sessions initiated crossed absolute thresholds. 這些統計資料表示工作負載效能降低。These stats indicate workload performance degradation.

當資料庫鎖定過多時,即表示資料庫上的鎖定計數已超出絕對臨界值。Excessive database locking denotes a condition in which the count of locks on a database has crossed absolute thresholds. 此統計資料表示工作負載效能降低。This stat indicates a workload performance degradation. 當發生記憶體壓力時,即表示要求記憶體授與的執行緒數目已超出絕對臨界值。Memory pressure is a condition in which the number of threads requesting memory grants crossed an absolute threshold. 此統計資料表示工作負載效能降低。This stat indicates a workload performance degradation.

其他等候統計資料偵測所表示的情況則是,透過「查詢存放區等候統計資料」測量的其他計量已超出絕對臨界值。Other wait stats detection indicates a condition in which miscellaneous metrics measured through the Query Store Wait Stats crossed an absolute threshold. 這些統計資料表示工作負載效能降低。These stats indicate workload performance degradation.

偵測到過長的等候時間之後,根據可用的資料,Intelligent Insights 診斷記錄會輸出效能降低之影響和受影響查詢的雜湊、導致查詢在執行中等候的計量詳細資料,以及測量到的等候時間。After excessive wait times are detected, depending on the data available, the Intelligent Insights diagnostics log outputs hashes of the affecting and affected queries degraded in performance, details of the metrics that cause queries to wait in execution, and measured wait time.

發生錯誤的要求Errored requests

發生錯誤的要求效能降低模型會監視個別的查詢,並偵測與基準期間相比,發生錯誤的查詢數目是否增加。The errored requests degradation model monitors individual queries and detects an increase in the number of queries that errored out compared to the baseline period. 此模型也會監視已超出 SQL Database 內建智慧功能所管理之絕對臨界值的重大例外狀況。This model also monitors critical exceptions that crossed absolute thresholds managed by SQL Database built-in intelligence. 系統會自動將在受監視期間內,對資料庫和帳戶發出以進行任何工作負載變更的查詢要求數目納入考量。The system automatically considers the number of query requests made to the database and accounts for any workload changes in the monitored period.

當系統認為發生錯誤之要求的提升,相對於所發出的整體要求數目來說,對工作負載效能有重大影響時,就會將受影響的查詢標示為發生錯誤的要求效能降低問題。When the measured increase in errored requests relative to the overall number of requests made is deemed significant to workload performance, affected queries are flagged as errored requests performance degradation issues.

Intelligent Insights 記錄會輸出發生錯誤之要求的計數。The Intelligent Insights log outputs the count of errored requests. 它會指出效能降低是否和發生錯誤之要求的提升,或是和超出被監視的重大例外狀況臨界值相關聯,以及測量到的效能降低時間。It indicates whether the performance degradation was related to an increase in errored requests or to crossing a monitored critical exception threshold and measured time of the performance degradation.

如果任何一個受監視的重大例外狀況超出系統所管理的絕對臨界值,就會產生含有重大例外狀況詳細資料的智慧型深入解析。If any of the monitored critical exceptions cross the absolute thresholds managed by the system, an intelligent insight is generated with critical exception details.

後續步驟Next steps