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

本主題中,您將了解如何設定記錄的診斷遙測為 Azure SQL Database 透過 Azure 入口網站、 PowerShell、 Azure CLI、 Azure 監視器 REST API 和 Azure Resource Manager 範本。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 入口網站Azure 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 database 和執行個體的資料庫支援診斷記錄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 database,然後執行個體資料庫: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
QueryStoreRuntimeStatistics:包含關於查詢執行階段統計資料的資訊,例如 CPU 使用率和查詢持續時間統計資料。QueryStoreRuntimeStatistics: Contains information about the query runtime statistics such as CPU usage and query duration statistics. Yes Yes
QueryStoreWaitStatistics:包含 (項目查詢等候) 的查詢等候統計資料的相關資訊這類是 CPU、 LOG 和 LOCKING。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.

注意

(雖然在螢幕上顯示),無法啟用安全性稽核和 SQLSecurityAuditEvents 記錄從資料庫的診斷設定。Security Audit and SQLSecurityAuditEvents logs can't be enabled from the database diagnostics settings (although showing on the screen). 若要啟用稽核記錄資料流,請參閱設定資料庫的稽核,並稽核記錄中 Azure 監視器記錄檔和 Azure 事件中樞To enable audit log streaming, see Set up auditing for your database, and auditing logs in Azure Monitor logs and Azure Event Hubs.

Azure 入口網站Azure 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 Monitor 日志。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 AnalyticsSelect 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 AnalyticsSelect 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.

注意

(雖然畫面所示),無法啟用安全性稽核和 SQLSecurityAuditEvents 記錄從資料庫的診斷設定。Security Audit and SQLSecurityAuditEvents logs can't be enabled from the database diagnostics settings (although shown on the screen). 若要啟用稽核記錄資料流,請參閱設定資料庫的稽核,並稽核記錄中 Azure 監視器記錄檔和 Azure 事件中樞To enable audit log streaming, see Set up auditing for your database, and auditing logs in Azure Monitor logs and Azure Event Hubs.

提示

針對您想要監視的每個 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 AnalyticsSelect 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. 勾選執行個體診斷遙測的核取方塊:ResourceUsageStatsSelect the check box for instance diagnostics telemetry: ResourceUsageStats. 設定受管理的執行個體的診斷Configure diagnostics for managed instance

  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 AnalyticsSelect a destination resource for the streaming diagnostics data: Archive to storage account, Stream to an event hub, or Send to Log Analytics.

  6. 勾選資料庫診斷遙測的核取方塊:SQLInsightsQueryStoreRuntimeStatisticsQueryStoreWaitStatistics錯誤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.

重要

PowerShell Azure 资源管理器模块仍受 Azure SQL 数据库的支持,但所有未来的开发都是针对 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,请参阅 AzureRM.SqlFor 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:

    PS C:\> $WSID = "/subscriptions/<subID>/resourcegroups/<RG_NAME>/providers/microsoft.operationalinsights/workspaces/<WS_NAME>"
    PS C:\> .\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 v2.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 Monitor REST API 使用诊断设置来启用日志分析。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

您可以使用 SQL 分析做為階層式儀表板,檢視您的 SQL Database 資源。You can use SQL Analytics as a hierarchical dashboard to view your SQL Database resources. 若要深入了解如何使用 SQL 分析解決方案,請參閱使用 SQL 分析解決方案監視 SQL DatabaseTo learn how to use the SQL Analytics solution, see Monitor SQL Database by using the SQL Analytics solution.

串流至事件中樞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:

  • 透過將最忙碌路徑串流至 PowerBI 以檢視服務健康情況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

從儲存體下載計量和記錄Download metrics and logs from Storage

了解如何從儲存體下載計量和診斷記錄Learn how to download metrics and diagnostics logs from Storage.

資料保留原則和價格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. 在 SQL Analytics 收集的監視遙測可用於您自己自訂的分析和應用程式開發使用Azure 監視器的記錄檔查詢語言。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 database 的基本計量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

基本的記錄檔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 租户 IDYour 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 租户 IDYour 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 租户 IDYour 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 租户 IDYour 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.SQAlways: MICROSOFT.SQ
類別Category 類別名稱。Name of the category. 一律:ErrorsAlways: Errors
OperationNameOperationName 作業名稱。Name of the operation. 一律:錯誤事件Always: 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 租户 IDYour 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 租户 IDYour 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. 一律:逾時Always: 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 租户 IDYour 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. 一律:區塊Always: 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 租户 IDYour 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. 一律:死結Always: 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 租户 IDYour 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:

若要深入了解 Azure 儲存體,請參閱如何從儲存體下載計量和診斷記錄To learn more about Azure Storage, see how to download metrics and diagnostics logs from Storage.