擴充事件概觀Extended events overview

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL ServerSQL Server「擴充事件」架構可讓使用者收集進行疑難排解或識別效能問題所需的資料 (盡可能多或少)。The Extended Events architecture enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem. 擴充事件是可設定的,而且它會非常妥善地調整。Extended Events is configurable, and it scales very well.

您可以在下列位置找到「擴充事件」的詳細資訊:快速入門:SQL Server 中的延伸事件You can find more information about Extended Events at Quickstart: Extended events in SQL Server.

SQL ServerSQL Server 擴充事件的優點Benefits of SQL ServerSQL Server Extended Events

「擴充事件」是一種使用小量效能資源的一種輕量型效能監視系統。Extended Events is a lightweight performance monitoring system that uses minimal performance resources. 擴充事件提供兩個圖形化使用者介面,用以建立、修改、顯示及分析您的工作階段資料。Extended Events provides two graphical user interfaces to create, modify, display, and analyze your session data. 這些介面的名稱如下:These interfaces are named:

  • 新增工作階段精靈New Session Wizard
  • 新增工作階段New Session

擴充事件概念Extended Events Concepts

SQL ServerSQL Server「擴充事件」是以現有概念 (例如,事件或事件取用者) 為建置基礎、使用 Windows 事件追蹤的概念,並引進新的概念。Extended Events builds on existing concepts, such as an event or an event consumer, uses concepts from Event Tracing for Windows, and introduces new concepts.

下表描述擴充事件的概念。The following table describes the concepts in Extended Events.

主題Topic DescriptionDescription
SQL Server 擴充的事件套件SQL Server Extended Events Packages 描述包含物件的「擴充事件」套件。Describes the Extended Events packages that contain objects. 當「擴充事件」工作階段在執行時,系統會使用這些物件來取得和處理資料。These objects are used to obtain and process data when an Extended Events session is running.
SQL Server 擴充的事件目標SQL Server Extended Events Targets 描述事件工作階段期間可以接收資料的事件取用者。Describes the event consumers that can receive data during an event session.
SQL Server 擴充的事件引擎SQL Server Extended Events Engine 描述可實作和管理擴充事件工作階段的引擎。Describes the engine that implements and manages an Extended Events session.
SQL Server 擴充的事件工作階段SQL Server Extended Events Sessions 描述擴充事件工作階段。Describes the Extended Events session.
   

擴充事件架構Extended Events Architecture

「擴充事件」是我們對用於伺服器系統的一般事件處理系統的稱呼。Extended Events is our name for a general event-handling system for server systems. 擴充的事件基礎結構可支援 SQL ServerSQL Server中資料的相互關聯,而在某些條件下,則可支援作業系統和資料庫應用程式中資料的相互關聯。The Extended Events infrastructure supports the correlation of data from SQL ServerSQL Server, and under certain conditions, the correlation of data from the operating system and database applications. 在作業系統案例中,「擴充事件」輸出必須導向 Windows 事件追蹤 (ETW)。In the operating system case, Extended Events output must be directed to Event Tracing for Windows (ETW). ETW 可將事件資料與作業系統或應用程式事件資料相互關聯。ETW can correlate the event data with operating system or application event data.

所有應用程式都有執行點,這些執行點在應用程式內部和外部都很實用。All applications have execution points that are useful both inside and outside an application. 在應用程式內,可以使用工作最初執行期間所收集的資訊將非同步處理加入佇列。Inside the application, asynchronous processing may be enqueued using information that is collected during the initial execution of a task. 在應用程式外,執行點會提供資訊給監視公用程式。Outside the application, execution points provide monitoring utilities with information. 該資訊是關於受監視應用程式的行為和效能特性。The information is about the behavioral and performance characteristics of the monitored application.

擴充的事件可支援在處理序外使用事件資料。Extended Events supports using event data outside a process. 這些資料通常是由以下項目所使用:This data is typically used by:

  • 追蹤工具,例如 SQL 追蹤和系統監視器。Tracing tools, such as SQL Trace and System Monitor.

  • 記錄工具,例如 Windows 事件記錄檔或 SQL ServerSQL Server 錯誤記錄檔。Logging tools, such as the Windows event log or the SQL ServerSQL Server error log.

  • 管理產品或是開發產品上之應用程式的使用者。Users administering a product or developing applications on a product.

「擴充事件」具有下列重要的設計層面:Extended Events has the following key design aspects:

  • 「擴充事件」引擎無法得知事件。The Extended Events engine is event agnostic. 此引擎可將任何事件繫結至任何目標,因為此引擎不受到事件內容限制。The engine can bind any event to any target, because the engine is not constrained by event content. 如需有關擴充的事件引擎的詳細資訊,請參閱< SQL Server Extended Events Engine>。For more information about the Extended Events engine, see SQL Server Extended Events Engine.

  • 事件會與事件取用者區隔,後者在擴充的事件中稱為 「目標」Events are separated from event consumers, which are called targets in Extended Events. 這表示,任何目標都可以接收任何事件。This means that any target can receive any event. 此外,目標可以自動耗用任何引發的事件,這樣可以記錄或提供其他事件內容。In addition, any event that is raised can be automatically consumed by the target, which can log or provide additional event context. 如需詳細資訊,請參閱< SQL Server Extended Events Targets>。For more information, see SQL Server Extended Events Targets.

  • 事件與事件發生時所要採取的動作不同。Events are distinct from the action to take when an event occurs. 因此,任何動作都可以與任何事件產生關聯。Therefore, any action can be associated with any event.

  • 述詞可以動態篩選何時應該擷取事件資料。Predicates can dynamically filter when event data should be captured. 動態篩選會增加「擴充事件」基礎結構的彈性。Dynamic filtering adds to the flexibility of the Extended Events infrastructure. 如需詳細資訊,請參閱< SQL Server Extended Events Packages>。For more information, see SQL Server Extended Events Packages.

擴充的事件可以同步產生事件資料 (以及非同步處理該資料),這樣可為事件處理提供彈性的方案。Extended Events can synchronously generate event data (and asynchronously process that data) which provides a flexible solution for event handling. 此外,擴充的事件還提供下列功能:In addition, Extended Events provides the following features:

  • 在伺服器系統上處理事件的統一方式,同時也可讓使用者隔離特定的事件來進行疑難排解。A unified approach to handling events across the server system, while enabling users to isolate specific events for troubleshooting purposes.

  • 與現有的 ETW 工具整合並支援這些工具。Integration with, and support for existing ETW tools.

  • 可根據 Transact-SQLTransact-SQL完整設定的事件處理機制。A fully configurable event handling mechanism that is based on Transact-SQLTransact-SQL.

  • 能夠動態監視使用中處理序,同時對這些處理序有最少的影響。The ability to dynamically monitor active processes, while having minimal effect on those processes.

  • 執行的預設系統健康工作階段,而不會有任何顯著的效能影響。A default system health session that runs without any noticeable performance effects. 此工作階段會收集系統資料,讓您能夠用來協助排除效能問題。The session collects system data that you can use to help troubleshoot performance issues. 如需詳細資訊,請參閱 使用 system_health 工作階段For more information, see Use the system_health Session.

擴充事件工作Extended Events Tasks

使用 Management StudioManagement StudioTransact-SQLTransact-SQL 執行 Transact-SQLTransact-SQL 資料定義語言 (DDL) 陳述式、動態管理檢視和函數或目錄檢視時,您可以針對您的 SQL ServerSQL Server 環境建立簡單或複雜 SQL ServerSQL Server 「擴充事件」疑難排解方案。Using Management StudioManagement Studio or Transact-SQLTransact-SQL to execute Transact-SQLTransact-SQL Data Definition Language (DDL) statements, dynamic management views and functions, or catalog views, you can create simple or complex SQL ServerSQL Server Extended Events troubleshooting solutions for your SQL ServerSQL Server environment.

工作描述Task Description 主題Topic
使用 [物件總管] 管理事件工作階段。Use the Object Explorer to manage event sessions. 在物件總管中管理事件工作階段Manage Event Sessions in the Object Explorer
描述如何建立擴充事件工作階段。Describes how to create an Extended Events session. 建立擴充事件工作階段Create an Extended Events Session
描述如何檢視及重新整理目標資料。Describes how to view and refresh target data. 進階檢視 SQL Server 中擴充事件的目標資料Advanced Viewing of Target Data from Extended Events in SQL Server
描述如何使用擴充事件工具來建立和管理 SQL ServerSQL Server 「擴充事件」工作階段。Describes how to use Extended Events tools to create and manage your SQL ServerSQL Server Extended Events sessions. 擴充事件工具Extended Events Tools
描述如何改變擴充事件工作階段。Describes how to alter an Extended Events session. 更改擴充事件工作階段Alter an Extended Events Session
描述如何取得與事件有關之欄位的資訊。Describes how to get information about the fields associated with the events. 取得所有事件的欄位Get the Fields for All Events
描述如何在註冊的封裝中查明哪些事件可用。Describes how to find out what events are available in the registered packages. 檢視已註冊之套件的事件View the Events for Registered Packages
描述如何判斷哪些擴充事件目標可在註冊的封裝中使用。Describes how to determine what Extended Events targets are available in the registered packages. 檢視已註冊之套件的擴充事件目標View the Extended Events Targets for Registered Packages
描述如何檢視同等於每一個 SQL 追蹤事件及其關聯資料行的「擴充事件」事件和動作。Describes how to view the Extended Events events and actions that are equivalent to each SQL Trace event and its associated columns. 檢視同等於 SQL 追蹤事件類別的擴充事件View the Extended Events Equivalents to SQL Trace Event Classes
描述當您在 CREATE EVENT SESSION 或 ALTER EVENT SESSION 中使用 ADD TARGET 引數時,如何尋找可以設定的參數。Describes how to find the parameters you can set when you use the ADD TARGET argument in CREATE EVENT SESSION or ALTER EVENT SESSION. 取得 ADD TARGET 引數的可設定參數Get the Configurable Parameters for the ADD TARGET Argument
描述如何將現有的 SQL 追蹤指令碼轉換為擴充事件工作階段。Describes how to convert an existing SQL Trace script to an Extended Events session. 將現有的 SQL 追蹤指令碼轉換為擴充事件工作階段Convert an Existing SQL Trace Script to an Extended Events Session
描述如何判斷哪些查詢持有鎖定、查詢的計畫,以及取得鎖定時的 Transact-SQLTransact-SQL 堆疊。Describes how to determine which queries are holding the lock, the plan of the query, and the Transact-SQLTransact-SQL stack at the time the lock was taken. 判斷哪些查詢持有鎖定Determine Which Queries Are Holding Locks
描述如何識別阻礙資料庫效能的鎖定來源。Describes how to identify the source of locks that are hindering database performance. 尋找持有最多鎖定的物件Find the Objects That Have the Most Locks Taken on Them
描述如何搭配 Windows 事件追蹤來使用擴充事件,以監視系統活動。Describes how to use Extended Events with Event Tracing for Windows to monitor system activity. 使用擴充事件監視系統活動Monitor System Activity Using Extended Events
使用擴充事件的目錄檢視和動態管理檢視 (DMV)Using the Catalog views and the Dynamic management views (DMVs) for extended events SQL Server 擴充事件系統檢視表中的 SELECT 和 JOINSELECTs and JOINs From System Views for Extended Events in SQL Server
   

適用於 Azure SQL Database 的程式碼範例可能有所不同Code examples can differ for Azure SQL Database

某些針對 SQL Server 內部部署所撰寫的 Transact-SQL 程式碼範例,需要進行小幅變更,才能在雲端的 Azure SQL Database 服務上執行。Some Transact-SQL code examples written for SQL Server on-premises need small changes to run on Azure SQL Database service in the cloud. 這類程式碼範例的其中一個類別牽涉到系統檢視,而系統檢視的名稱前置詞在這兩個資料庫系統之間稍有不同:One catagory of such code examples involves system views whose name prefixess differ slightly between the two database systems:

  • server_   -   內部部署的前置詞 server_   -   prefix for on-premises
  • database_   -   雲端中 Azure SQL DB 服務的前置詞 database_   -   prefix for Azure SQL DB service in the cloud

為了示範,下表列出並比較兩個系統檢視子集。For illustration, the following table lists and compares two subsets of the system views. 為求簡潔,這些子集限制為同時包含字串 _event 的檢視名稱。For brevity, the subsets are restricted to view names that also contains the string _event. 子集具有不同的名稱前置詞,因為它們來自兩個不同的資料庫系統。The subsets have differing name prefixes because they come the two different database systems.

內部部署 2017 中的名稱Name from on-premises 2017 雲端服務中的名稱Name from cloud service
server_event_notificationsserver_event_notifications
server_event_session_actionsserver_event_session_actions
server_event_session_eventsserver_event_session_events
server_event_session_fieldsserver_event_session_fields
server_event_session_targetsserver_event_session_targets
server_event_sessionsserver_event_sessions
server_eventsserver_events
server_trigger_eventsserver_trigger_events
database_event_session_actionsdatabase_event_session_actions
database_event_session_eventsdatabase_event_session_events
database_event_session_fieldsdatabase_event_session_fields
database_event_session_targetsdatabase_event_session_targets
database_event_sessionsdatabase_event_sessions
   

上表中兩個清單截至 2019 年 6 月為止是準確的。The two lists in the preceding table are accurate as of June 2019. 但這裡的資料表內容可能已經過時,因為其內容不會在此處進行維護。But the table contents here may become outdated, because its content will not be maintained here. 如需準確的清單,請執行下列 T-SQL SELECT 陳述式。For accurate lists, run the following T-SQL SELECT statement. 執行 SELECT 兩次,在每個資料庫系統上各一次。Run the SELECT twice, once on each database system.

SELECT name
    FROM sys.all_objects
    WHERE
        (name LIKE 'database\_%' { ESCAPE '\' } OR
         name LIKE 'server\_%' { ESCAPE '\' })
        AND name LIKE '%\_event%' { ESCAPE '\' }
        AND type = 'V'
    ORDER BY name;

另請參閱See Also

資料層應用程式Data-tier Applications
SQL Server 物件與版本的 DAC 支援DAC Support For SQL Server Objects and Versions
部署資料層應用程式Deploy a Data-tier Application
監視資料層應用程式Monitor Data-tier Applications
 
擴充事件動態管理檢視Extended Events Dynamic Management Views
擴充事件目錄檢視 (Transact-SQL)Extended Events Catalog Views (Transact-SQL)
 
XELite:跨平台程式庫,用來讀取 XEL 檔案或即時 SQL 串流中的 XEvent,於 2019 年 5 月發行。XELite: Cross-platform library to read XEvents from XEL files or live SQL streams, released May 2019.
Read-SQLXEvent PowerShell Cmdlet,發行日期 2019 年 6 月。Read-SQLXEvent PowerShell cmdlet, released June 2019.
SQL 謎團:XEvent 工作階段的原因追蹤與事件順序 (2019 年 4 月 1 日發佈於部落格) (英文)SQL Mysteries: Causality tracking vs Event Sequence for XEvent Sessions (blog published April 1, 2019)