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

Showing content from https://docs.microsoft.com/en-us/azure/azure-sql/database/xevent-db-diff-from-svr below:

Extended Events in Azure SQL - Azure SQL Database & Azure SQL Managed Instance

Applies to: Azure SQL Database Azure SQL Managed Instance SQL database in Fabric

For an introduction to Extended Events, see:

• Extended Events
• Quick Start: Extended events

The feature set, functionality, and usage scenarios for Extended Events in Azure SQL Database, SQL database in Fabric, and Azure SQL Managed Instance are similar to what is available in SQL Server. The main differences are:

• The event_file target always uses blobs in Azure Storage, rather than files on disk.
• In Azure SQL Database and SQL database in Fabric, event sessions are always database-scoped. This means that:
  • An event session in one database can't collect events from another database.
  • An event must occur in the context of a user database to be included in a session.
• In Azure SQL Managed Instance, you can create both server-scoped and database-scoped event sessions. We recommend using server-scoped event sessions for most scenarios.

There are two examples to help you get started with Extended Events quickly:

• Create a session with an event_file target in Azure Storage. This example shows you how to capture event data in a file (blob) in Azure Storage using the event_file target, and includes troubleshooting guidance for common errors. Use this if you need to persist captured event data, or if you want to use event viewer in SQL Server Management Studio (SSMS) to analyze captured data.
• Create a session with a ring_buffer target in memory. This example shows you how to capture the latest events from an event session in memory using the ring_buffer target. Use this as a quick way to look at recent events during ad hoc investigations or troubleshooting, without having to store captured event data.

Extended Events can be used to monitor read-only replicas. For more information, see Read queries on replicas.

Adopt the following best practices to use Extended Events securely, reliably, and without affecting database engine health and workload performance.

• If you use the event_file target:
  • Depending on the events added to a session, the files produced by the event_file target might contain sensitive data. Carefully review RBAC role assignments and the access control lists (ACL) on the storage account and container, including inherited access, to avoid granting unnecessary read access. Follow the principle of least privilege.
  • Use a storage account in the same Azure region as the database or managed instance where you create event sessions.
  • Align the redundancy of the storage account with the redundancy of the database, elastic pool, or managed instance. For locally redundant resources, use LRS, GRS, or RA-GRS. For zone-redundant resources, use ZRS, GZRS, or RA-GZRS. See Azure Storage redundancy for details.
  • Don't use any blob access tier other than Hot.
  • Don't enable the hierarchical namespace for the storage account.
• If you want to create a continuously running event session that starts automatically after each Database Engine restart (for example, after a failover or a maintenance event), include the event session option of STARTUP_STATE = ON in your CREATE EVENT SESSION or ALTER EVENT SESSION statements.
• Conversely, use STARTUP_STATE = OFF for short-term event sessions such as those used in ad hoc troubleshooting.
• In Azure SQL Database, do not read deadlock events from the built-in dl event session. If there is a large number of deadlock events collected, reading them with the sys.fn_xe_file_target_read_file() function can cause an out-of-memory error in the master database. This might impact login processing and result in an application outage. For the recommended ways to monitor deadlocks, see Collect deadlock graphs in Azure SQL Database with Extended Events.

Azure SQL Database and Azure SQL Managed Instance support the following targets:

• event_file target. Writes complete buffers to a blob in an Azure Storage container.
• ring_buffer target. Holds event data in memory until replaced by new event data.
• event_counter target. Counts all events that occur during an extended events session.
• histogram target. Counts the occurrences of different values of fields or actions in separate buckets.
• event_stream. Streams event data to a .NET application.

Note

The event_stream target in Azure SQL Database and Azure SQL Managed Instance is in preview.

When you execute the CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION statements in SQL Server and in Azure SQL Managed Instance, you use the ON SERVER clause. In Azure SQL Database, you use the ON DATABASE clause instead, because in Azure SQL Database event sessions are database-scoped.

Extended Events provides several catalog views. Catalog views tell you about event session metadata or definition. These views don't return information about instances of active event sessions.

Extended Events provides several dynamic management views (DMVs). DMVs return information about started event sessions.

There are additional Extended Events DMVs that are common to Azure SQL Database, Azure SQL Managed Instance, and SQL Server:

• sys.dm_xe_map_values
• sys.dm_xe_object_columns
• sys.dm_xe_objects
• sys.dm_xe_packages

Just like in SQL Server, you can obtain available events, actions, and targets using this query:

SELECT o.object_type,
       p.name AS package_name,
       o.name AS db_object_name,
       o.description AS db_obj_description
FROM sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p
ON p.guid = o.package_guid
WHERE o.object_type IN ('action','event','target')
ORDER BY o.object_type,
         p.name,
         o.name;

In Azure SQL Database and Azure SQL Managed Instance, Extended Events support a granular permission model. The following permissions can be granted:

• SQL Database
• SQL Managed Instance

CREATE ANY DATABASE EVENT SESSION
DROP ANY DATABASE EVENT SESSION
ALTER ANY DATABASE EVENT SESSION
ALTER ANY DATABASE EVENT SESSION ADD EVENT
ALTER ANY DATABASE EVENT SESSION DROP EVENT
ALTER ANY DATABASE EVENT SESSION ADD TARGET
ALTER ANY DATABASE EVENT SESSION DROP TARGET
ALTER ANY DATABASE EVENT SESSION ENABLE
ALTER ANY DATABASE EVENT SESSION DISABLE
ALTER ANY DATABASE EVENT SESSION OPTION

CREATE ANY EVENT SESSION
DROP ANY EVENT SESSION
ALTER ANY EVENT SESSION
ALTER ANY EVENT SESSION ADD EVENT
ALTER ANY EVENT SESSION DROP EVENT
ALTER ANY EVENT SESSION ADD TARGET
ALTER ANY EVENT SESSION DROP TARGET
ALTER ANY EVENT SESSION ENABLE
ALTER ANY EVENT SESSION DISABLE
ALTER ANY EVENT SESSION OPTION

For information on what each of these permissions controls, see CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION.

All of these permissions are included in the CONTROL permission on the database or managed instance. In Azure SQL Database, the database owner (dbo), members of the db_owner database role, and the administrators of the logical server hold the database CONTROL permission. In Azure SQL Managed Instance, members of the sysadmin server role hold the CONTROL permission on the instance.

When you use the event_file target, event data is stored in blobs in an Azure Storage container. The Database Engine running the event session must have specific access to this container. You can grant this access in one of the following ways:

• Assign the Storage Blob Data Contributor RBAC role to the managed identity of the Azure SQL logical server or Azure SQL managed instance on the container, and create a credential to instruct the Database Engine to use managed identity for authentication.

  As an alternative to assigning the Storage Blob Data Contributor RBAC role, you can assign the following RBAC actions:

  Namespace Action Microsoft.Storage/storageAccounts/blobServices/containers/ read Microsoft.Storage/storageAccounts/blobServices/containers/blobs/ delete Microsoft.Storage/storageAccounts/blobServices/containers/blobs/ read Microsoft.Storage/storageAccounts/blobServices/containers/blobs/ write

  Note

  The use of managed identity with extended event sessions is in preview.

• Create a SAS token for the container, and store the token in a credential.

  In Azure SQL Database, you must use a database-scoped credential. In Azure SQL Managed Instance, use a server-scoped credential.

  The SAS token you create for your Azure Storage container must satisfy the following requirements:

  • Have the rwdl (Read, Write, Delete, List) permissions.
  • Have the start time and expiry time that encompass the lifetime of the event session.
  • Have no IP address restrictions.

In Azure SQL Database, memory consumption by extended event sessions is dynamically controlled by the Database Engine to minimize resource contention.

There's a limit on memory available to event sessions:

• In a single database, total session memory is limited to 128 MB.
• In an elastic pool, individual databases are limited by the single database limits, and in total they can't exceed 512 MB.

If you receive an error message referencing a memory limit, the corrective actions you can take are:

• Run fewer concurrent event sessions.
• Using CREATE and ALTER statements for event sessions, reduce the amount of memory you specify in the MAX_MEMORY clause for the session.

Note

In Extended Events, the MAX_MEMORY clause appears in two contexts: when creating or altering a session (at the session level), and when using the ring_buffer target (at the target level). The above limits apply to the session level memory.

There's a limit on the number of started event sessions in Azure SQL Database:

• In a single database, the limit is 100.
• In an elastic pool, the limit is 100 database-scoped sessions per pool.

In dense elastic pools, starting a new extended event session might fail due to memory constraints even when the total number of started sessions is below 100.

To find the total memory consumed by an event session, execute the following query while connected to the database where the event session is started:

SELECT name AS session_name,
       total_buffer_size + total_target_memory AS total_session_memory
FROM sys.dm_xe_database_sessions;

To find the total event session memory for an elastic pool, this query needs to be executed in every database in the pool.

• Extended Events
• Quick Start: Extended events

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