Azure SQL Database 計量和診斷記錄Azure SQL Database metrics and diagnostics logging

在本主題中,您將瞭解如何透過 Azure 入口網站、PowerShell、Azure CLI、Azure 監視器 REST API 和 Azure Resource Manager 範本來設定 Azure SQL Database 的診斷遙測記錄。In this topic, you will learn how to configure logging of diagnostics telemetry for Azure SQL Database through the Azure portal, PowerShell, Azure CLI, Azure Monitor REST API, and Azure Resource Manager template. 這些診斷可用於測量資源使用率和查詢執行統計資料。These diagnostics can be used to gauge resource utilization and query execution statistics.

單一資料庫、彈性集區中的集區資料庫,以及受控執行個體中的執行個體資料庫可以傳輸計量和診斷記錄,讓您以較輕鬆的方式監視效能。Single databases, pooled databases in elastic pools, and instance databases in a managed instance can stream metrics and diagnostics logs for easier performance monitoring. 您可以將資料庫設定為將資源使用量、背景工作角色與工作階段及連線傳輸下列其中一項 Azure 資源:You can configure a database to transmit resource usage, workers and sessions, and connectivity to one of the following Azure resources:

  • Azure SQL 分析:發揮 Azure SQL 資料庫的智慧型監視功能,包含效能報告、警示和降低風險的建議。Azure SQL Analytics: to get intelligent monitoring of your Azure SQL databases that includes performance reports, alerts, and mitigation recommendations.

  • Azure 事件中樞:整合 SQL Database 遙測與自訂監視解決方案或管線。Azure Event Hubs: to integrate SQL Database telemetry with your custom monitoring solutions or hot pipelines.

  • Azure 儲存體:用以封存大量遙測資料,價格實惠。Azure Storage: to archive vast amounts of telemetry for a fraction of the price.

    架構

如需進一步了解不同 Azure 服務所支援的計量和記錄類別,請參閱:For more information about the metrics and log categories supported by the various Azure services, see:

本文會指引您啟用 Azure SQL 資料庫、彈性集區和受控執行個體的診斷遙測。This article provides guidance to help you enable diagnostics telemetry for Azure SQL databases, elastic pools, and managed instances. 也有助於您了解如何將 Azure SQL 分析設為檢視資料庫診斷遙測的監視工具。It also can help you understand how to configure Azure SQL Analytics as a monitoring tool for viewing database diagnostics telemetry.

啟用診斷遙測的記錄功能Enable logging of diagnostics telemetry

您可以使用下列其中一種方法來啟用及管理計量和診斷遙測記錄功能︰You can enable and manage metrics and diagnostics telemetry logging by using one of the following methods:

  • Azure PortalAzure portal
  • PowerShellPowerShell
  • Azure CLIAzure CLI
  • Azure 監視器 REST APIAzure Monitor REST API
  • Azure Resource Manager 範本Azure Resource Manager template

當您啟用計量和診斷記錄時,您需要指定用來收集診斷遙測資料的 Azure 資源目的地。When you enable metrics and diagnostics logging, you need to specify the Azure resource destination for collecting the diagnostics telemetry. 可用的選項包括:Available options include:

  • Azure SQL 分析Azure SQL Analytics
  • Azure 事件中樞Azure Event Hubs
  • Azure 儲存體Azure Storage

您可以佈建新的 Azure 資源,或選取現有的資源。You can provision a new Azure resource or select an existing resource. 使用 [診斷設定]選項選擇資源之後,指定要收集的資料。After you choose a resource by using the Diagnostic settings option, specify which data to collect.

支援 Azure SQL 資料庫和實例資料庫的診斷記錄Supported diagnostic logging for Azure SQL databases, and instance databases

在 SQL Database 啟用預設未啟用的計量和診斷記錄功能。Enable the metrics and diagnostics logging on SQL databases - they're not enabled by default.

您可以設定 Azure SQL 資料庫和實例資料庫,以收集下列診斷遙測:You can set up Azure SQL databases, and instance databases to collect the following diagnostics telemetry:

監視資料庫的遙測Monitoring telemetry for databases 單一資料庫和集區資料庫支援Single database and pooled database support 實例資料庫支援Instance database support
基本計量:包含 DTU/cpu 百分比、DTU/cpu 限制、實體資料讀取百分比、記錄寫入百分比、成功/失敗/防火牆連線、會話百分比、背景工作百分比、儲存體、儲存體百分比和 XTP 儲存體百分比。Basic metrics: Contains DTU/CPU percentage, DTU/CPU limit, physical data read percentage, log write percentage, Successful/Failed/Blocked by firewall connections, sessions percentage, workers percentage, storage, storage percentage, and XTP storage percentage. Yes No
實例和應用程式 Advanced:包含 tempdb 系統資料庫資料和記錄檔大小,以及所使用的 tempdb 百分比記錄檔。Instance and App Advanced: Contains tempdb system database data and log file size and tempdb percent log file used. Yes No
QueryStoreRuntimeStatistics:包含查詢執行時間統計資料的相關資訊,例如 CPU 使用率和查詢持續時間統計資料。QueryStoreRuntimeStatistics: Contains information about the query runtime statistics such as CPU usage and query duration statistics. Yes Yes
QueryStoreWaitStatistics:包含查詢等候統計資料的相關資訊(您的查詢等候的時間),例如 CPU、記錄和鎖定。QueryStoreWaitStatistics: Contains information about the query wait statistics (what your queries waited on) such are CPU, LOG, and LOCKING. Yes Yes
錯誤:包含有關資料庫上 SQL 錯誤的資訊。Errors: Contains information about SQL errors on a database. Yes Yes
DatabaseWaitStatistics:包含和資料庫花費在不同等候類型的等候時間長度有關的資訊。DatabaseWaitStatistics: Contains information about how much time a database spent waiting on different wait types. Yes No
超時:包含資料庫上之超時的相關資訊。Timeouts: Contains information about timeouts on a database. Yes No
封鎖:包含有關資料庫上封鎖事件的資訊。Blocks: Contains information about blocking events on a database. Yes No
鎖死:包含有關資料庫上之鎖死事件的資訊。Deadlocks: Contains information about deadlock events on a database. Yes No
AutomaticTuning:包含資料庫之自動調整建議的相關資訊。AutomaticTuning: Contains information about automatic tuning recommendations for a database. Yes No
SQLInsights:包含資料庫的效能 Intelligent Insights。SQLInsights: Contains Intelligent Insights into performance for a database. 若要深入了解,請參閱 Intelligent InsightsTo learn more, see Intelligent Insights. Yes Yes

重要

彈性集區和受控實例有自己的個別診斷遙測,其來自其所包含的資料庫。Elastic pools and managed instances have their own separate diagnostics telemetry from databases they contain. 這一點很重要,因為診斷遙測會針對每個資源分別設定,如下所述。This is important to note as diagnostics telemetry is configured separately for each of these resources, as documented below.

注意

Azure PortalAzure portal

您可以在 Azure 入口網站中,針對每個單一、集區或實例資料庫使用 [診斷設定] 功能表,以設定診斷遙測的串流處理。You can use the Diagnostics settings menu for each single, pooled, or instance database in Azure portal to configure streaming of diagnostics telemetry. 此外,也可以為資料庫容器分別設定診斷遙測:彈性集區和受控實例。In addition, diagnostic telemetry can also be configured separately for database containers: elastic pools and managed instances. 您可以設定下列目的地以串流診斷遙測: Azure 儲存體、Azure 事件中樞和 Azure 監視器記錄檔。You can set the following destinations to stream the diagnostics telemetry: Azure Storage, Azure Event Hubs, and Azure Monitor logs.

設定彈性集區診斷遙測的串流處理Configure streaming of diagnostics telemetry for elastic pools

彈性集區圖示

您可以設定彈性集區資源,以收集下列診斷遙測:You can set up an elastic pool resource to collect the following diagnostics telemetry:

資源Resource 監視遙測Monitoring telemetry
彈性集區Elastic pool 基本計量包含 EDTU/cpu 百分比、EDTU/cpu 限制、實體資料讀取百分比、記錄寫入百分比、會話百分比、背景工作百分比、儲存體、儲存體百分比、儲存體限制,以及 XTP 儲存體百分比。Basic metrics contains eDTU/CPU percentage, eDTU/CPU limit, physical data read percentage, log write percentage, sessions percentage, workers percentage, storage, storage percentage, storage limit, and XTP storage percentage.

若要針對彈性集區中的彈性集區和資料庫設定診斷遙測的串流處理,您必須分別設定下列兩者To configure streaming of diagnostics telemetry for elastic pools and databases in elastic pools, you will need to separately configure both of the following:

  • 啟用彈性集區診斷遙測的串流處理,以及Enable streaming of diagnostics telemetry for an elastic pool, and
  • 針對彈性集區中的每個資料庫啟用診斷遙測的串流處理Enable streaming of diagnostics telemetry for each database in elastic pool

這是因為彈性集區是一個資料庫容器,其本身的遙測與個別的資料庫遙測分開。This is because elastic pool is a database container with its own telemetry being separate from an individual database telemetry.

若要啟用彈性集區資源診斷遙測的串流處理,請遵循下列步驟:To enable streaming of diagnostics telemetry for an elastic pool resource, follow these steps:

  1. 移至 Azure 入口網站中的彈性集區資源。Go to the elastic pool resource in Azure portal.

  2. 選取 [診斷設定]。Select Diagnostics settings.

  3. 如果沒有先前的設定存在,請選取 [開啟診斷],或者選取 [編輯設定] 來編輯先前的設定。Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting.

    啟用彈性集區的診斷功能

  4. 輸入供您自己參考的設定名稱。Enter a setting name for your own reference.

  5. 選取串流診斷資料的目的地資源: [封存至儲存體帳戶]、[串流至事件中樞] 或 [傳送至 Log Analytics]。Select a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.

  6. 針對 log analytics,選取 [ + 建立新的工作區],或選取現有的工作區來設定並建立新的工作區。For log analytics, select Configure and create a new workspace by selecting +Create New Workspace, or select an existing workspace.

  7. 選取彈性集區診斷遙測的核取方塊: [基本計量]。Select the check box for elastic pool diagnostics telemetry: Basic metrics. 設定彈性集區的診斷Configure diagnostics for elastic pools

  8. 選取 [儲存]。Select Save.

  9. 此外,請遵循下一節所述的步驟,為您想要監視的彈性集區中的每個資料庫設定診斷遙測的串流。In addition, configure streaming of diagnostics telemetry for each database within the elastic pool you want to monitor by following steps described in the next section.

重要

除了設定彈性集區的診斷遙測以外,您還需要為彈性集區中的每個資料庫設定診斷遙測,如下所述。In addition to configuring diagnostics telemetry for an elastic pool, you also need to configure diagnostics telemetry for each database in elastic pool, as documented below.

針對單一資料庫或彈性集區中的資料庫設定診斷遙測的串流處理Configure streaming of diagnostics telemetry for single database, or database in elastic pool

SQL Database 圖示

若要啟用單一或集區資料庫之診斷遙測的串流處理,請遵循下列步驟:To enable streaming of diagnostics telemetry for single or pooled databases, follow these steps:

  1. 移至 Azure SQL database資源。Go to Azure SQL database resource.

  2. 選取 [診斷設定]。Select Diagnostics settings.

  3. 如果沒有先前的設定存在,請選取 [開啟診斷],或者選取 [編輯設定] 來編輯先前的設定。Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting.

    • 您最多可建立三個平行連線來串流處理診斷遙測。You can create up to three parallel connections to stream diagnostics telemetry.
    • 選取 [新增診斷設定],以設定將診斷資料平行串流處理至多個資源。Select Add diagnostic setting to configure parallel streaming of diagnostics data to multiple resources.

    啟用單一、集區式或執行個體資料庫的診斷

  4. 輸入供您自己參考的設定名稱。Enter a setting name for your own reference.

  5. 選取串流診斷資料的目的地資源: [封存至儲存體帳戶]、[串流至事件中樞] 或 [傳送至 Log Analytics]。Select a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.

  6. 針對標準的事件監視體驗,請選取下列資料庫診斷記錄遙測的核取方塊: SQLInsightsAutomaticTuningQueryStoreRuntimeStatisticsQueryStoreWaitStatistics錯誤DatabaseWaitStatistics超時區塊鎖死For the standard, event-based monitoring experience, select the following check boxes for database diagnostics log telemetry: SQLInsights, AutomaticTuning, QueryStoreRuntimeStatistics, QueryStoreWaitStatistics, Errors, DatabaseWaitStatistics, Timeouts, Blocks, and Deadlocks.

  7. 如需以一分鐘為基礎的先進監視體驗,請選取 [基本計量] 的核取方塊。For an advanced, one-minute-based monitoring experience, select the check box for Basic metrics. 設定單一、集區式或實例資料庫的診斷Configure diagnostics for single, pooled, or instance databases

  8. 選取 [儲存]。Select Save.

  9. 針對您想要監視的每個資料庫重複這些步驟。Repeat these steps for each database you want to monitor.

提示

針對您想要監視的每個 Azure SQL Database 重複執行這些步驟。Repeat these steps for each Azure SQL Database you want to monitor.

設定受控執行個體診斷遙測的串流Configure streaming of diagnostics telemetry for managed instances

受控執行個體圖示

您可以設定受控執行個體,以收集下列診斷遙測:You can set up a managed instance resource to collect the following diagnostics telemetry:

資源Resource 監視遙測Monitoring telemetry
受控執行個體Managed instance ResourceUsageStats 包含 V 核心計數、平均 CPU 百分比、IO 要求、讀取/寫入的位元組、保留的儲存空間,以及使用的儲存空間。ResourceUsageStats contains vCores count, average CPU percentage, IO requests, bytes read/written, reserved storage space, and used storage space.

若要設定受控實例和實例資料庫診斷遙測的串流處理,您必須分別設定下列兩者To configure streaming of diagnostics telemetry for managed instance and instance databases, you will need to separately configure both of the following:

  • 啟用受控實例診斷遙測的串流處理,以及Enable streaming of diagnostics telemetry for managed instance, and
  • 針對每個實例資料庫啟用診斷遙測的串流處理Enable streaming of diagnostics telemetry for each instance database

這是因為受控實例是具有自己的遙測的資料庫容器,與個別實例資料庫遙測分開。This is because managed instance is a database container with its own telemetry, separate from an individual instance database telemetry.

若要啟用受控執行個體資源診斷遙測的串流,請遵循下列步驟:To enable streaming of diagnostics telemetry for a managed instance resource, follow these steps:

  1. 移至 Azure 入口網站中的受控實例資源。Go to the managed instance resource in Azure portal.

  2. 選取 [診斷設定]。Select Diagnostics settings.

  3. 如果沒有先前的設定存在,請選取 [開啟診斷],或者選取 [編輯設定] 來編輯先前的設定。Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting.

    啟用受控執行個體的診斷

  4. 輸入供您自己參考的設定名稱。Enter a setting name for your own reference.

  5. 選取串流診斷資料的目的地資源: [封存至儲存體帳戶]、[串流至事件中樞] 或 [傳送至 Log Analytics]。Select a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.

  6. 針對 log analytics,選取 [ + 建立新的工作區],或使用現有的工作區,以選取 [設定並建立新的工作區]。For log analytics, select Configure and create a new workspace by selecting +Create New Workspace, or use an existing workspace.

  7. 選取 [實例診斷遙測: ResourceUsageStats] 的核取方塊。Select the check box for instance diagnostics telemetry: ResourceUsageStats.

    設定受控執行個體的診斷

  8. 選取 [儲存]。Select Save.

  9. 此外,請遵循下一節所述的步驟,為您要監視的受控實例中的每個實例資料庫設定診斷遙測的串流。In addition, configure streaming of diagnostics telemetry for each instance database within the managed instance you want to monitor by following the steps described in the next section.

重要

除了設定受控實例的診斷遙測以外,您還需要為每個實例資料庫設定診斷遙測,如下所述。In addition to configuring diagnostics telemetry for a managed instance, you also need to configure diagnostics telemetry for each instance database, as documented below.

設定實例資料庫診斷遙測的串流處理Configure streaming of diagnostics telemetry for instance databases

受控執行個體中的執行個體資料庫圖示

若要啟用實例資料庫診斷遙測的串流處理,請遵循下列步驟:To enable streaming of diagnostics telemetry for instance databases, follow these steps:

  1. 移至受控實例中的實例資料庫資源。Go to instance database resource within managed instance.

  2. 選取 [診斷設定]。Select Diagnostics settings.

  3. 如果沒有先前的設定存在,請選取 [開啟診斷],或者選取 [編輯設定] 來編輯先前的設定。Select Turn on diagnostics if no previous settings exist, or select Edit setting to edit a previous setting.

    • 您最多可建立三 (3) 個平行連線來串流處理診斷遙測。You can create up to three (3) parallel connections to stream diagnostics telemetry.
    • 選取 [+新增診斷設定] 建立診斷資料到多個資源的多個平行串流處理。Select +Add diagnostic setting to configure parallel streaming of diagnostics data to multiple resources.

    啟用執行個體資料庫的診斷

  4. 輸入供您自己參考的設定名稱。Enter a setting name for your own reference.

  5. 選取串流診斷資料的目的地資源: [封存至儲存體帳戶]、[串流至事件中樞] 或 [傳送至 Log Analytics]。Select a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.

  6. 選取資料庫診斷遙測的核取方塊: [ SQLInsights]、[ QueryStoreRuntimeStatistics]、[ QueryStoreWaitStatistics ] 和 [錯誤]。Select the check boxes for database diagnostics telemetry: SQLInsights, QueryStoreRuntimeStatistics, QueryStoreWaitStatistics and Errors. 設定實例資料庫的診斷Configure diagnostics for instance databases

  7. 選取 [儲存]。Select Save.

  8. 針對您想要監視的每個實例資料庫重複這些步驟。Repeat these steps for each instance database you want to monitor.

提示

針對您想要監視的每個實例資料庫重複這些步驟。Repeat these steps for each instance database you want to monitor.

PowerShellPowerShell

注意

本文已更新為使用新的 Azure PowerShell Az 模組。This article has been updated to use the new Azure PowerShell Az module. AzureRM 模組在至少 2020 年 12 月之前都還會持續收到錯誤 (Bug) 修正,因此您仍然可以持續使用。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要深入了解新的 Az 模組和 AzureRM 的相容性,請參閱新的 Azure PowerShell Az 模組簡介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 如需 Az 模組安裝指示,請參閱安裝 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

重要

Azure SQL Database 仍然支援 PowerShell Azure Resource Manager 模組,但所有未來的開發都是針對 Az .Sql 模組。The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. 如需這些 Cmdlet,請參閱AzureRMFor these cmdlets, see AzureRM.Sql. Az 模組和 AzureRm 模組中命令的引數本質上完全相同。The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

您可以使用 PowerShell 啟用計量和診斷記錄功能。You can enable metrics and diagnostics logging by using PowerShell.

  • 若要啟用儲存體帳戶中診斷記錄的儲存體,請使用下列命令:To enable storage of diagnostics logs in a storage account, use this command:

    Set-AzDiagnosticSetting -ResourceId [your resource id] -StorageAccountId [your storage account id] -Enabled $true
    

    儲存體帳戶識別碼是目的地儲存體帳戶的資源識別碼。The storage account ID is the resource ID for the destination storage account.

  • 若要將診斷記錄串流至事件中樞,請使用下列命令:To enable streaming of diagnostics logs to an event hub, use this command:

    Set-AzDiagnosticSetting -ResourceId [your resource id] -ServiceBusRuleId [your service bus rule id] -Enabled $true
    

    Azure 服務匯流排規則識別碼是此格式的字串︰The Azure Service Bus rule ID is a string with this format:

    {service bus resource ID}/authorizationrules/{key name}
    
  • 若要將診斷記錄傳送到 Log Analytics 工作區,請使用下列命令:To enable sending diagnostics logs to a Log Analytics workspace, use this command:

    Set-AzDiagnosticSetting -ResourceId [your resource id] -WorkspaceId [resource id of the log analytics workspace] -Enabled $true
    
  • 您可以使用下列命令取得 Log Analytics 工作區的資源識別碼:You can obtain the resource ID of your Log Analytics workspace by using the following command:

    (Get-AzOperationalInsightsWorkspace).ResourceId
    

您可以結合這些參數讓多個輸出選項。You can combine these parameters to enable multiple output options.

若要設定多個 Azure 資源To configure multiple Azure resources

若要支援多個訂用帳戶,從使用 PowerShell 啟用 Azure 資源計量記錄使用 PowerShell 指令碼。To support multiple subscriptions, use the PowerShell script from Enable Azure resource metrics logging using PowerShell.

在執行指令碼 Enable-AzureRMDiagnostics.ps1 將診斷資料從多個資源傳送至工作區時,提供工作區資源識別碼 <$WSID> 做為參數。Provide the workspace resource ID <$WSID> as a parameter when executing the script Enable-AzureRMDiagnostics.ps1 to send diagnostic data from multiple resources to the workspace.

  • 若要取得您診斷資料目的地的工作區識別碼 <$WSID>,請使用下列指令碼:To get the workspace ID <$WSID> of the destination for your diagnostic data, use the following script:

    $WSID = "/subscriptions/<subID>/resourcegroups/<RG_NAME>/providers/microsoft.operationalinsights/workspaces/<WS_NAME>"
    .\Enable-AzureRMDiagnostics.ps1 -WSID $WSID
    

    以訂用帳戶識別碼取代 <subID>,以資源群組的名稱取代 <RG_NAME>,並且以工作區名稱取代 <WS_NAME>。Replace <subID> with the subscription ID, <RG_NAME> with the resource group name, and <WS_NAME> with the workspace name.

Azure CLIAzure CLI

您可以使用 Azure CLI 啟用計量和診斷記錄功能。You can enable metrics and diagnostics logging by using the Azure CLI.

注意

Azure CLI v1.0 支援啟用診斷記錄的腳本。Scripts to enable diagnostics logging are supported for Azure CLI v1.0. 請注意,目前不支援 CLI 2.0 版。Please note that CLI v2.0 is unsupported at this time.

  • 若要啟用儲存體帳戶中診斷記錄的儲存體,請使用下列命令:To enable the storage of diagnostics logs in a storage account, use this command:

    azure insights diagnostic set --resourceId <resourceId> --storageId <storageAccountId> --enabled true
    

    儲存體帳戶識別碼是目的地儲存體帳戶的資源識別碼。The storage account ID is the resource ID for the destination storage account.

  • 若要將診斷記錄串流至事件中樞,請使用下列命令:To enable the streaming of diagnostics logs to an event hub, use this command:

    azure insights diagnostic set --resourceId <resourceId> --serviceBusRuleId <serviceBusRuleId> --enabled true
    

    服務匯流排規則識別碼是此格式的字串︰The Service Bus rule ID is a string with this format:

    {service bus resource ID}/authorizationrules/{key name}
    
  • 若要將診斷記錄傳送到 Log Analytics 工作區,請使用下列命令:To enable the sending of diagnostics logs to a Log Analytics workspace, use this command:

    azure insights diagnostic set --resourceId <resourceId> --workspaceId <resource id of the log analytics workspace> --enabled true
    

您可以結合這些參數讓多個輸出選項。You can combine these parameters to enable multiple output options.

REST APIREST API

了解如何使用 Azure 監視器 REST API 變更診斷設定Read about how to change diagnostics settings by using the Azure Monitor REST API.

Resource Manager 範本Resource Manager template

了解如何使用 Resource Manager 範本在建立資源時啟用診斷設定Read about how to enable diagnostics settings at resource creation by using a Resource Manager template.

Azure SQL 分析中的資料流Stream into Azure SQL Analytics

Azure SQL 分析是雲端解決方案,可以跨多個訂用帳戶大規模監視 Azure SQL 資料庫、彈性集區和受控執行個體的效能。Azure SQL Analytics is a cloud solution that monitors the performance of Azure SQL databases, elastic pools, and managed instances at scale and across multiple subscriptions. 可協助您收集 Azure SQL Database 效能計量,並以視覺效果方式呈現,而且有內建智慧可以執行效能疑難排解。It can help you collect and visualize Azure SQL Database performance metrics, and it has built-in intelligence for performance troubleshooting.

Azure SQL 分析概觀

使用入口網站的 [診斷設定] 索引標籤中內建的 [傳送至 Log Analytics] 選項,即可將 SQL Database 計量和診斷記錄串流到 Azure SQL 分析中。SQL Database metrics and diagnostics logs can be streamed into Azure SQL Analytics by using the built-in Send to Log Analytics option in the diagnostics settings tab in the portal. 您也可以透過 PowerShell Cmdlet、Azure CLI 或 Azure 監視器 REST API,使用診斷設定來啟用 log analytics。You can also enable log analytics by using a diagnostics setting via PowerShell cmdlets, the Azure CLI, or the Azure Monitor REST API.

安裝概觀Installation overview

您可以藉由 Azure SQL 分析監視 Azure SQL Database Fleet。You can monitor a SQL Database fleet with Azure SQL Analytics. 請執行下列步驟:Perform the following steps:

  1. 從 Azure Marketplace 建立 Azure SQL 分析解決方案。Create an Azure SQL Analytics solution from the Azure Marketplace.
  2. 在解決方案中建立監視工作區。Create a monitoring workspace in the solution.
  3. 將資料庫設定為將診斷遙測串流到工作區中。Configure databases to stream diagnostics telemetry into the workspace.

如果您使用的是彈性集區或受控執行個體,則也需要設定來自這些資源的診斷遙測串流。If you're using elastic pools or managed instances, you also need to configure diagnostics telemetry streaming from these resources.

建立 Azure SQL 分析資源Create Azure SQL Analytics resource

  1. 在 Azure Marketplace 中搜尋 Azure SQL 分析並加以選取。Search for Azure SQL Analytics in Azure Marketplace and select it.

    在入口網站中搜尋 Azure SQL 分析

  2. 在解決方案的 [概觀] 畫面上選取 [建立]。Select Create on the solution's overview screen.

  3. 在 Azure SQL 分析表單中填入所需的其他資訊:工作區名稱、訂用帳戶、資源群組、位置及定價層。Fill in the Azure SQL Analytics form with the additional information that is required: workspace name, subscription, resource group, location, and pricing tier.

    在入口網站中設定 Azure SQL 分析

  4. 選取 [確定] 確認,然後選取 [建立]。Select OK to confirm, and then select Create.

將資料庫設定為記錄計量和診斷記錄Configure databases to record metrics and diagnostics logs

若要設定資料庫記錄計量的位置,最簡單的方式是使用 Azure 入口網站。The easiest way to configure where databases record metrics is by using the Azure portal. 如上所述,在 Azure 入口網站中,移至您的 SQL Database 資源,並選取 [診斷設定]。As previously described, go to your SQL Database resource in the Azure portal and select Diagnostics settings.

如果您使用的是彈性集區或受控執行個體,則也需要設定這些資源的診斷設定,以便將診斷遙測串流到工作區中。If you're using elastic pools or managed instances, you also need to configure diagnostics settings in these resources to enable the diagnostics telemetry to stream into the workspace.

使用 SQL 分析解決方案進行監視和警示Use the SQL Analytics solution for monitoring and alerting

您可以使用 SQL 分析做為階層式儀表板,檢視您的 SQL Database 資源。You can use SQL Analytics as a hierarchical dashboard to view your SQL Database resources.

串流至事件中樞Stream into Event Hubs

您可以使用 Azure 入口網站中內建的 [串流至事件中樞] 選項,將 SQL Database 計量和診斷記錄串流到事件中樞。You can stream SQL Database metrics and diagnostics logs into Event Hubs by using the built-in Stream to an event hub option in the Azure portal. 您也可以透過 PowerShell Cmdlet、Azure CLI 或 Azure 監視器 REST API 使用診斷設定來啟用服務匯流排規則識別碼。You also can enable the Service Bus rule ID by using a diagnostics setting via PowerShell cmdlets, the Azure CLI, or the Azure Monitor REST API.

如何在事件中樞處理計量和診斷記錄What to do with metrics and diagnostics logs in Event Hubs

所選的資料串流到事件中樞之後,您很快就能啟用進階監視案例。After the selected data is streamed into Event Hubs, you're one step closer to enabling advanced monitoring scenarios. 事件中樞是作為事件管線的大門。Event Hubs acts as the front door for an event pipeline. 資料收集到事件中樞之後,這些資料可以透過即時分析提供者或儲存體配接器來轉換和儲存。After data is collected into an event hub, it can be transformed and stored by using a real-time analytics provider or a storage adapter. 事件中樞會讓事件串流的產生從這些事件的取用分離。Event Hubs decouples the production of a stream of events from the consumption of those events. 如此一來,事件消費者可以在自己的排程存取事件。In this way, event consumers can access the events on their own schedule. 如需事件中樞的詳細資訊,請參閱:For more information on Event Hubs, see:

您可以在事件中樞使用串流的計量:You can use streamed metrics in Event Hubs to:

  • 藉由將熱路徑資料串流至 Power BI 來查看服務健全狀況View service health by streaming hot-path data to Power BI

    您可以使用事件中樞、串流分析和 PowerBI,輕鬆快速地將計量和診斷資料轉換為 Azure 服務上的深入解析。By using Event Hubs, Stream Analytics, and Power BI, you can easily transform your metrics and diagnostics data into near real-time insights on your Azure services. 如需如何設定事件中樞、使用串流分析處理資料,以及使用 PowerBI 作為輸出的概觀,請參閱串流分析和 Power BIFor an overview of how to set up an event hub, process data with Stream Analytics, and use Power BI as an output, see Stream Analytics and Power BI.

  • 將記錄串流至協力廠商記錄和遙測串流Stream logs to third-party logging and telemetry streams

    使用事件中樞串流,您可以將計量和診斷記錄放入各種的第三方監視和記錄分析解決方案中。By using Event Hubs streaming, you can get your metrics and diagnostics logs into various third-party monitoring and log analytics solutions.

  • 建立自訂遙測和記錄平臺Build a custom telemetry and logging platform

    您是否已建立自訂的遙測平台,或正考慮建置一個?Do you already have a custom-built telemetry platform or are considering building one? 事件中樞的高度可調整的發佈訂閱特性,可讓您靈活地內嵌診斷記錄。The highly scalable publish-subscribe nature of Event Hubs allows you to flexibly ingest diagnostics logs. 請參閱 Dan Rosanova 指南,以在全球級別的遙測平台中使用事件中樞See Dan Rosanova's guide to using Event Hubs in a global-scale telemetry platform.

串流到儲存體Stream into Storage

使用 Azure 入口網站中內建的 [封存至儲存體帳戶] 選項,就可以將 SQL Database 計量和診斷記錄儲存在 Azure 儲存體。You can store SQL Database metrics and diagnostics logs in Azure Storage by using the built-in Archive to a storage account option in the Azure portal. 您也可以透過 PowerShell Cmdlet、Azure CLI 或 Azure 監視器 REST API 使用診斷設定來啟用儲存體。You can also enable Storage by using a diagnostics setting via PowerShell cmdlets, the Azure CLI, or the Azure Monitor REST API.

儲存體帳戶中的計量和診斷記錄結構描述Schema of metrics and diagnostics logs in the storage account

設定計量和診斷記錄集合之後,當第一批資料列可用時,系統會在您選取的儲存體帳戶中建立儲存體容器。After you set up metrics and diagnostics logs collection, a storage container is created in the storage account you selected when the first rows of data are available. Blob 的結構為:The structure of the blobs is:

insights-{metrics|logs}-{category name}/resourceId=/SUBSCRIPTIONS/{subscription ID}/ RESOURCEGROUPS/{resource group name}/PROVIDERS/Microsoft.SQL/servers/{resource_server}/ databases/{database_name}/y={four-digit numeric year}/m={two-digit numeric month}/d={two-digit numeric day}/h={two-digit 24-hour clock hour}/m=00/PT1H.json

或者,形式更簡單:Or, more simply:

insights-{metrics|logs}-{category name}/resourceId=/{resource Id}/y={four-digit numeric year}/m={two-digit numeric month}/d={two-digit numeric day}/h={two-digit 24-hour clock hour}/m=00/PT1H.json

例如,基本計量的 blob 名稱可能是:For example, a blob name for Basic metrics might be:

insights-metrics-minute/resourceId=/SUBSCRIPTIONS/s1id1234-5679-0123-4567-890123456789/RESOURCEGROUPS/TESTRESOURCEGROUP/PROVIDERS/MICROSOFT.SQL/ servers/Server1/databases/database1/y=2016/m=08/d=22/h=18/m=00/PT1H.json

儲存彈性集區的資料所用的 Blob 名稱,例如:A blob name for storing data from an elastic pool looks like:

insights-{metrics|logs}-{category name}/resourceId=/SUBSCRIPTIONS/{subscription ID}/ RESOURCEGROUPS/{resource group name}/PROVIDERS/Microsoft.SQL/servers/{resource_server}/ elasticPools/{elastic_pool_name}/y={four-digit numeric year}/m={two-digit numeric month}/d={two-digit numeric day}/h={two-digit 24-hour clock hour}/m=00/PT1H.json

資料保留原則和價格Data retention policy and pricing

如果您選取事件中樞或儲存體帳戶,您可以指定保留原則。If you select Event Hubs or a Storage account, you can specify a retention policy. 此原則會刪除早於選取時間期間的資料。This policy deletes data that is older than a selected time period. 如果您指定 Log Analytics,則保留原則取決於所選的定價層。If you specify Log Analytics, the retention policy depends on the selected pricing tier. 在這種情況下,所提供的免費資料擷取單位可以每個月免費監視多個資料庫。In this case, the provided free units of data ingestion can enable free monitoring of several databases each month. 超過免費單位的診斷遙測耗用量可能會收取費用。Any consumption of diagnostics telemetry in excess of the free units might incur costs. 請注意,相較於閒置的資料庫,較繁重工作負載的作用中資料庫會擷取更多資料。Be aware that active databases with heavier workloads ingest more data than idle databases. 如需詳細資訊,請參閱Log analytics 定價For more information, see Log analytics pricing.

如果您使用的是 Azure SQL 分析,您可以藉由選取 Azure SQL 分析導覽功能表上的 [OMS 工作區],然後選取 [使用量] 和 [估計成本],監視您解決方案中的資料擷取使用量。If you are using Azure SQL Analytics, you can monitor your data ingestion consumption in the solution by selecting OMS Workspace on the navigation menu of Azure SQL Analytics, and then selecting Usage and Estimated Costs.

可用的計量和記錄Metrics and logs available

監視適用于 Azure SQL Database、彈性集區和受控實例的遙測,如下所述。Monitoring telemetry available for Azure SQL Database, elastic pools and managed instance is documented below. 您可以使用Azure 監視器記錄查詢語言,將 SQL 分析中收集的監視遙測用於您自己的自訂分析和應用程式開發。Collected monitoring telemetry inside SQL Analytics can be used for your own custom analysis and application development using Azure Monitor log queries language.

基本計量Basic metrics

如需有關資源的基本計量詳細資料,請參閱下表。Refer to the following tables for details about Basic metrics by resource.

注意

[基本計量] 選項先前稱為 [所有計量]。Basic metrics option was formerly known as All metrics. 所做的變更僅限於命名,而且不會變更受監視的計量。The change made was to the naming only and there was no change to the metrics monitored. 已起始這項變更,以允許未來引進額外的計量類別。This change was initiated to allow for introduction of additional metric categories in the future.

彈性集區的基本計量Basic metrics for elastic pools

ResourceResource 計量Metrics
彈性集區Elastic pool eDTU 百分比、使用的 eDTU、eDTU 限制、CPU 百分比、實體資料讀取百分比、記錄寫入百分比、工作階段百分比、背景工作百分比、儲存體、儲存體百分比、儲存體限制、XTP 儲存體百分比eDTU percentage, eDTU used, eDTU limit, CPU percentage, physical data read percentage, log write percentage, sessions percentage, workers percentage, storage, storage percentage, storage limit, XTP storage percentage

Azure SQL 資料庫的基本計量Basic metrics for Azure SQL Databases

ResourceResource 計量Metrics
Azure SQL 資料庫Azure SQL database DTU 百分比、使用的 DTU、DTU 限制、CPU 百分比、實體資料讀取百分比、記錄寫入百分比、成功/失敗/防火牆封鎖的連線、工作階段百分比、背景工作百分比、儲存體、儲存體百分比、XTP 儲存體百分比和死結DTU percentage, DTU used, DTU limit, CPU percentage, physical data read percentage, log write percentage, Successful/Failed/Blocked by firewall connections, sessions percentage, workers percentage, storage, storage percentage, XTP storage percentage, and deadlocks

Advanced 計量Advanced metrics

如需有關 advanced 計量的詳細資訊,請參閱下表。Refer to the following table for details about advanced metrics.

度量Metric 計量顯示名稱Metric Display Name 說明Description
tempdb_data_sizetempdb_data_size Tempdb 資料檔案大小 KbTempdb Data File Size Kilobytes Tempdb 資料檔案大小(Kb)。Tempdb Data File Size Kilobytes. 不適用於資料倉儲。Not applicable to data warehouses. 針對以 DTU 為基礎的購買模型,使用 vCore 購買模型或 100 DTU 和更新版本的資料庫,將可使用此計量。This metric will be available for databases using the vCore purchasing model or 100 DTU and higher for DTU-based purchasing models.
tempdb_log_sizetempdb_log_size Tempdb 記錄檔大小 KbTempdb Log File Size Kilobytes Tempdb 記錄檔大小(Kb)。Tempdb Log File Size Kilobytes. 不適用於資料倉儲。Not applicable to data warehouses. 針對以 DTU 為基礎的購買模型,使用 vCore 購買模型或 100 DTU 和更新版本的資料庫,將可使用此計量。This metric will be available for databases using the vCore purchasing model or 100 DTU and higher for DTU-based purchasing models.
tempdb_log_used_percenttempdb_log_used_percent 使用的 Tempdb 百分比記錄Tempdb Percent Log Used 使用的 Tempdb 百分比記錄。Tempdb Percent Log Used. 不適用於資料倉儲。Not applicable to data warehouses. 針對以 DTU 為基礎的購買模型,使用 vCore 購買模型或 100 DTU 和更新版本的資料庫,將可使用此計量。This metric will be available for databases using the vCore purchasing model or 100 DTU and higher for DTU-based purchasing models.

基本記錄Basic logs

適用于所有記錄的遙測詳細資料記載于下表中。Details of telemetry available for all logs are documented in the tables below. 請參閱支援的診斷記錄,以瞭解特定資料庫類別支援哪些記錄-Azure SQL 單一、集區或實例資料庫。Please see supported diagnostic logging to understand which logs are supported for a particular database flavor - Azure SQL single, pooled, or instance database.

受控實例的資源使用狀況統計資料Resource usage stats for managed instance

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:ResourceUsageStatsAlways: ResourceUsageStats
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:MANAGEDINSTANCESAlways: MANAGEDINSTANCES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 受控執行個體的名稱Name of the managed instance
ResourceIdResourceId 資源 URIResource URI
SKU_sSKU_s 受控執行個體產品 SKUManaged instance product SKU
virtual_core_count_svirtual_core_count_s 可用的虛擬核心數目Number of vCores available
avg_cpu_percent_savg_cpu_percent_s CPU 百分比平均Average CPU percentage
reserved_storage_mb_sreserved_storage_mb_s 受控執行個體上的保留儲存體容量Reserved storage capacity on the managed instance
storage_space_used_mb_sstorage_space_used_mb_s 受控執行個體上已使用儲存體Used storage on the managed instance
io_requests_sio_requests_s IOPS 計數IOPS count
io_bytes_read_sio_bytes_read_s 讀取的 IOPS 位元組IOPS bytes read
io_bytes_written_sio_bytes_written_s 寫入的 IOPS 位元組IOPS bytes written

查詢存放區執行階段統計資料Query Store runtime statistics

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:QueryStoreRuntimeStatisticsAlways: QueryStoreRuntimeStatistics
OperationNameOperationName 作業名稱。Name of the operation. 一律:QueryStoreRuntimeStatisticsEventAlways: QueryStoreRuntimeStatisticsEvent
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
query_hash_squery_hash_s 查詢雜湊Query hash
query_plan_hash_squery_plan_hash_s 查詢計劃雜湊Query plan hash
statement_sql_handle_sstatement_sql_handle_s 陳述式 SQL 控制代碼Statement sql handle
interval_start_time_dinterval_start_time_d 間隔的開始 datetimeoffset 刻度數目從 1900-1-1 起Start datetimeoffset of the interval in number of ticks from 1900-1-1
interval_end_time_dinterval_end_time_d 間隔的結束 datetimeoffset 刻度數目從 1900-1-1 起End datetimeoffset of the interval in number of ticks from 1900-1-1
logical_io_writes_dlogical_io_writes_d 邏輯 IO 寫入總數Total number of logical IO writes
max_logical_io_writes_dmax_logical_io_writes_d 每次執行的邏輯 IO 寫入次數上限Max number of logical IO writes per execution
physical_io_reads_dphysical_io_reads_d 實體 IO 讀取總數Total number of physical IO reads
max_physical_io_reads_dmax_physical_io_reads_d 每次執行的邏輯 IO 讀取次數上限Max number of logical IO reads per execution
logical_io_reads_dlogical_io_reads_d 邏輯 IO 讀取總數Total number of logical IO reads
max_logical_io_reads_dmax_logical_io_reads_d 每次執行的邏輯 IO 讀取次數上限Max number of logical IO reads per execution
execution_type_dexecution_type_d 執行類型Execution type
count_executions_dcount_executions_d 查詢的執行次數Number of executions of the query
cpu_time_dcpu_time_d 查詢所耗用的總 CPU 時間 (毫秒)Total CPU time consumed by the query in microseconds
max_cpu_time_dmax_cpu_time_d 單次執行可耗用的 CPU 時間上限 (毫秒)Max CPU time consumer by a single execution in microseconds
dop_ddop_d 平行處理原則的程度總和Sum of degrees of parallelism
max_dop_dmax_dop_d 單次執行所用之平行處理原則的最大程度Max degree of parallelism used for single execution
rowcount_drowcount_d 傳回的資料列總數Total number of rows returned
max_rowcount_dmax_rowcount_d 單次執行傳回的資料列數目上限Max number of rows returned in single execution
query_max_used_memory_dquery_max_used_memory_d 使用的記憶體總量 (KB)Total amount of memory used in KB
max_query_max_used_memory_dmax_query_max_used_memory_d 單次執行所使用的記憶體數量上限 (KB)Max amount of memory used by a single execution in KB
duration_dduration_d 總執行時間 (毫秒)Total execution time in microseconds
max_duration_dmax_duration_d 單次執行的執行時間上限Max execution time of a single execution
num_physical_io_reads_dnum_physical_io_reads_d 實體讀取總數Total number of physical reads
max_num_physical_io_reads_dmax_num_physical_io_reads_d 每次執行的實體讀取次數上限Max number of physical reads per execution
log_bytes_used_dlog_bytes_used_d 使用的記錄檔位元組總數Total amount of log bytes used
max_log_bytes_used_dmax_log_bytes_used_d 每次執行所使用的記錄檔位元組數量上限Max amount of log bytes used per execution
query_id_dquery_id_d 查詢存放區中查詢的識別碼ID of the query in Query Store
plan_id_dplan_id_d 查詢存放區中計劃的識別碼ID of the plan in Query Store

深入了解查詢存放區執行階段統計資料Learn more about Query Store runtime statistics data.

查詢存放區等候統計資料Query Store wait statistics

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:QueryStoreWaitStatisticsAlways: QueryStoreWaitStatistics
OperationNameOperationName 作業名稱。Name of the operation. 一律:QueryStoreWaitStatisticsEventAlways: QueryStoreWaitStatisticsEvent
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
wait_category_swait_category_s 等候的類別Category of the wait
is_parameterizable_sis_parameterizable_s 查詢是否可參數化Is the query parameterizable
statement_type_sstatement_type_s 陳述式類型Type of the statement
statement_key_hash_sstatement_key_hash_s 陳述式索引鍵雜湊Statement key hash
exec_type_dexec_type_d 執行類型Type of execution
total_query_wait_time_ms_dtotal_query_wait_time_ms_d 特定等候類別的查詢等候總時間Total wait time of the query on the specific wait category
max_query_wait_time_ms_dmax_query_wait_time_ms_d 特定等候類別個別執行時的查詢等候時間上限Max wait time of the query in individual execution on the specific wait category
query_param_type_dquery_param_type_d 00
query_hash_squery_hash_s 查詢存放區中的查詢雜湊Query hash in Query Store
query_plan_hash_squery_plan_hash_s 查詢存放區中的查詢計劃Query plan hash in Query Store
statement_sql_handle_sstatement_sql_handle_s 查詢存放區中的陳述式控制代碼Statement handle in Query Store
interval_start_time_dinterval_start_time_d 間隔的開始 datetimeoffset 刻度數目從 1900-1-1 起Start datetimeoffset of the interval in number of ticks from 1900-1-1
interval_end_time_dinterval_end_time_d 間隔的結束 datetimeoffset 刻度數目從 1900-1-1 起End datetimeoffset of the interval in number of ticks from 1900-1-1
count_executions_dcount_executions_d 查詢的執行計數Count of executions of the query
query_id_dquery_id_d 查詢存放區中查詢的識別碼ID of the query in Query Store
plan_id_dplan_id_d 查詢存放區中計劃的識別碼ID of the plan in Query Store

深入了解查詢存放區等候統計資料Learn more about Query Store wait statistics data.

錯誤資料集Errors dataset

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:ErrorsAlways: Errors
OperationNameOperationName 作業名稱。Name of the operation. 一律:ErrorEventAlways: ErrorEvent
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
訊息Message 純文字的錯誤訊息Error message in plain text
user_defined_buser_defined_b 錯誤是否為使用者定義的位元Is the error user defined bit
error_number_derror_number_d 錯誤碼Error code
嚴重性Severity 錯誤的嚴重性Severity of the error
state_dstate_d 錯誤的狀態State of the error
query_hash_squery_hash_s 失敗查詢的查詢雜湊 (如果有)Query hash of the failed query, if available
query_plan_hash_squery_plan_hash_s 失敗查詢的查詢計劃雜湊 (如果有)Query plan hash of the failed query, if available

深入了解 SQL Server 錯誤訊息Learn more about SQL Server error messages.

資料庫等候統計資料資料集Database wait statistics dataset

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:DatabaseWaitStatisticsAlways: DatabaseWaitStatistics
OperationNameOperationName 作業名稱。Name of the operation. 一律:DatabaseWaitStatisticsEventAlways: DatabaseWaitStatisticsEvent
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
wait_type_swait_type_s 等候類型的名稱Name of the wait type
start_utc_date_t [UTC]start_utc_date_t [UTC] 測量的時段開始時間Measured period start time
end_utc_date_t [UTC]end_utc_date_t [UTC] 測量的時段結束時間Measured period end time
delta_max_wait_time_ms_ddelta_max_wait_time_ms_d 每次執行的等候時間上限Max waited time per execution
delta_signal_wait_time_ms_ddelta_signal_wait_time_ms_d 訊號總等候時間Total signals wait time
delta_wait_time_ms_ddelta_wait_time_ms_d 期間內的總等候時間Total wait time in the period
delta_waiting_tasks_count_ddelta_waiting_tasks_count_d 等候工作數目Number of waiting tasks

深入了解資料庫等候統計資料Learn more about database wait statistics.

逾時資料集Time-outs dataset

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:TimeoutsAlways: Timeouts
OperationNameOperationName 作業名稱。Name of the operation. 一律:TimeoutEventAlways: TimeoutEvent
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
error_state_derror_state_d 錯誤狀態碼Error state code
query_hash_squery_hash_s 查詢雜湊 (如果有)Query hash, if available
query_plan_hash_squery_plan_hash_s 查詢計劃雜湊 (如果有)Query plan hash, if available

封鎖資料集Blockings dataset

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:BlocksAlways: Blocks
OperationNameOperationName 作業名稱。Name of the operation. 一律:BlockEventAlways: BlockEvent
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
lock_mode_slock_mode_s 查詢所使用的鎖定模式Lock mode used by the query
resource_owner_type_sresource_owner_type_s 鎖定擁有者Owner of the lock
blocked_process_filtered_sblocked_process_filtered_s 已封鎖的處理序報告 XMLBlocked process report XML
duration_dduration_d 鎖定的持續時間 (微秒)Duration of the lock in microseconds

死結 (Deadlock) 資料集Deadlocks dataset

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:DeadlocksAlways: Deadlocks
OperationNameOperationName 作業名稱。Name of the operation. 一律:DeadlockEventAlways: DeadlockEvent
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
deadlock_xml_sdeadlock_xml_s 死結報表 XMLDeadlock report XML

自動調整資料集Automatic tuning dataset

屬性Property 說明Description
TenantIdTenantId 您的租用戶識別碼Your tenant ID
SourceSystemSourceSystem 一律:AzureAlways: Azure
TimeGenerated [UTC]TimeGenerated [UTC] 記錄檔記錄時的時間戳記Time stamp when the log was recorded
類型Type 一律:AzureDiagnosticsAlways: AzureDiagnostics
ResourceProviderResourceProvider 資源提供者名稱。Name of the resource provider. 一律:MICROSOFT.SQLAlways: MICROSOFT.SQL
類別Category 類別名稱。Name of the category. 一律:AutomaticTuningAlways: AutomaticTuning
資源Resource 資源名稱Name of the resource
ResourceTypeResourceType 資源類型名稱。Name of the resource type. 一律:SERVERS/DATABASESAlways: SERVERS/DATABASES
SubscriptionIdSubscriptionId 資料庫的訂用帳戶 GUIDSubscription GUID for the database
ResourceGroupResourceGroup 資料庫的資源群組名稱Name of the resource group for the database
LogicalServerName_sLogicalServerName_s 資料庫伺服器的名稱Name of the server for the database
LogicalDatabaseName_sLogicalDatabaseName_s 資料庫名稱Name of the database
ElasticPoolName_sElasticPoolName_s 資料庫的彈性集區名稱 (如果有)Name of the elastic pool for the database, if any
DatabaseName_sDatabaseName_s 資料庫名稱Name of the database
ResourceIdResourceId 資源 URIResource URI
RecommendationHash_sRecommendationHash_s 自動調整建議的唯一雜湊Unique hash of Automatic tuning recommendation
OptionName_sOptionName_s 自動調整作業Automatic tuning operation
Schema_sSchema_s 資料庫結構描述Database schema
Table_sTable_s 受影響的資料表Table affected
IndexName_sIndexName_s 索引名稱Index name
IndexColumns_sIndexColumns_s 資料行名稱Column name
IncludedColumns_sIncludedColumns_s 包含的資料行Columns included
EstimatedImpact_sEstimatedImpact_s 自動調整建議 JSON 的預估影響Estimated impact of Automatic tuning recommendation JSON
Event_sEvent_s 自動調整事件的類型Type of Automatic tuning event
Timestamp_tTimestamp_t 上一次更新的時間戳記Last updated timestamp

Intelligent Insights 資料集Intelligent Insights dataset

深入了解 Intelligent Insights 記錄格式Learn more about the Intelligent Insights log format.

後續步驟Next steps

若要了解如何啟用記錄,並了解各種 Azure 服務支援的計量和記錄類別,請參閱:To learn how to enable logging and to understand the metrics and log categories supported by the various Azure services, see:

若要了解事件中樞,請閱讀:To learn about Event Hubs, read:

若要瞭解如何根據 log analytics 的遙測設定警示,請參閱:To learn how to setup alerts based on telemetry from log analytics see: