Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events below:

Extended Events overview - SQL Server, Azure SQL Database, and Azure SQL Managed Instance - SQL Server

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

The Extended Events (XEvents) architecture enables users to collect as much or as little data as is necessary to monitor, identify, or troubleshoot performance in SQL Server, Azure SQL Database, and Azure SQL Managed Instance. Extended Events is highly configurable, lightweight, and scales well. For more information, see Extended Events Architecture.

Extended Events replace the deprecated SQL Trace and SQL Server Profiler features.

To get started with Extended Events, use Quickstart: Extended Events.

Extended Events is a lightweight performance monitoring system that uses minimal system resources while providing a detailed, in-depth view of the database engine. SQL Server Management Studio provides a graphical user interface for Extended Events to create, modify, and drop event sessions and to display and analyze session data. To learn more about Extended Events support in Management Studio, see:

• Manage Event Sessions in the Object Explorer
• Use the SSMS XEvent Profiler

Extended Events builds on existing concepts from Event Tracing for Windows, such as event and event consumer, and introduces new concepts such as action and predicate.

The following table provides documentation references to understand the concepts in Extended Events.

Extended Events is a name for a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from the database engine, and under certain conditions, the correlation of data from the operating system and database applications. In the operating system case, Extended Events output must be directed to Event Tracing for Windows (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 can 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 users either administering or supporting a product by doing performance monitoring or by user developing applications on a product for debugging purposes. Data is consumed or analyzed using tools such as SQL Server Management Studio, XEvent Profiler and Performance Monitor, and T-SQL or Windows command line tools.

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 isn't constrained by event content. For more information about the Extended Events engine, see 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. For more information, see Targets for Extended Events.
• 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. For more information, see 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.
• Integration with, and support for existing ETW tools.
• A fully configurable event handling mechanism that uses Transact-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. For more information, see Use the system_health session.

Using Management Studio or Transact-SQL to execute Transact-SQL Data Definition Language (DDL) statements, consume dynamic management views and functions, or catalog views, you can create simple or complex SQL Server Extended Events troubleshooting solutions for your SQL Server environment.

Use the following T-SQL query to return all possible events and their descriptions:

SELECT obj1.name AS [XEvent-name],
    col2.name AS [XEvent-column],
    obj1.description AS [Descr-name],
    col2.description AS [Descr-column]
FROM sys.dm_xe_objects AS obj1
INNER JOIN sys.dm_xe_object_columns AS col2
    ON col2.object_name = obj1.name
ORDER BY obj1.name,
    col2.name

Some Transact-SQL code examples written for SQL Server need small changes to run in Azure. One category of such code examples involves catalog views whose name prefixes differ depending on the database engine type:

• server_ - prefix for SQL Server and Azure SQL Managed Instance
• database_ - prefix for Azure SQL Database and SQL Managed Instance

Azure SQL Database supports only database-scoped event sessions. SQL Server Management Studio (SSMS) fully supports database-scoped event sessions for Azure SQL Database: an Extended Events node containing database-scoped sessions appears under each database in Object Explorer.

Azure SQL Managed Instance supports both database-scoped sessions and server-scoped sessions. SSMS fully supports server-scoped sessions for SQL Managed Instance: an Extended Events node containing all server-scoped sessions appears under the Management folder for each managed instance in Object Explorer.

Note

Server-scoped sessions are recommended for managed instances. Database-scoped sessions aren't displayed in Object Explorer in SSMS for Azure SQL Managed Instance. Database-scoped sessions can only be queried and managed with Transact-SQL when using a managed instance.

For illustration, the following table lists and compares two subsets of catalog views. For brevity, the subsets are restricted to view names that also contain the string _event. The subsets have differing name prefixes because they support different database engine types.

The two lists in the preceding table were accurate as of March 2022. For an up-to-date list, run the following Transact-SQL SELECT statement:

SELECT name
    FROM sys.all_objects
    WHERE
        (name LIKE 'database[_]%' OR
         name LIKE 'server[_]%' )
        AND name LIKE '%[_]event%'
        AND type = 'V'
        AND SCHEMA_NAME(schema_id) = 'sys'
    ORDER BY name;

• Extended Events Dynamic Management Views
• Extended Events Catalog Views (Transact-SQL)
• SQL Mysteries: Causality tracking vs Event Sequence for XEvent Sessions
• Analyze and prevent deadlocks in Azure SQL Database
• Quickstart: Extended Events
• Event File target code for Extended Events in Azure SQL Database
• Extended events in Azure SQL Database
• XELite: Cross-platform library to read XEvents from XEL files or live SQL streams
• Read-SQLXEvent PowerShell cmdlet

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4