Event APIs Used By SQLBench

SQLBench accesses various APIs, DMVs and system catalogs when collecting telemetry from SQL Server instances or Azure SQL Databases.

XEvents or SQLTrace API are used to collect queries, errors, deadlocks and other events, depending on the version and edition of SQL Server or Azure SQL Database being monitored.

You can choose which API is used to collect these events if the monitored SQL Server system supports both APIs.

This article discusses how to configure SQLBench optimally for SQL Server event APIs.

 

SQLTrace was introduced as an event capture API in SQL 2000, along with its GUI capture tool "SQL Server Profiler". SQLTrace remained the sole event capture API until XEvents was introduced in SQL 2008, and remains a supported feature up to SQL Server 2022 but is marked for deprecation in a future release of SQL Server.

XEvents has been Microsoft's preferred event capture API since its introduction in SQL 2008 and is the only API available in Azure SQL Database.

XEvents offers a few improvements over SQLTrace, including efficiency, more event types, event matching and memory ring buffering.

Security events available in SQLTrace have been removed from XEvents, and XEvents trace files are written XML format and must be read using relatively inefficient XML parsers.

 

XEvents consumes fewer resources and impacts the SQL Server system being monitored less than SQLTrace does, however this difference is not significant in most cases and affects the collection of Stored Procedure Statement Completed and TSQL Batch Statement Completed events (statement events) far more than externally invoked RPC Completed or TSQL Batch Completed events.

We recommend using XEvents when including statement level events in your SQLBench telemetry, if a SQL 2012 or greater version is being monitored. Xevents can not be used to monitor SQL 2000 or SQL 2005 and we discourage using XEvents to monitor SQL 2008 as it was a V1 feature in that version with stability bugs, some of which cause SQL Server to crash.

When choosing to log events to an event log file, XEvents uses an XML format which is less efficient to read than SQLTrace's binary log file format. SQLTrace can sometimes be more efficient overall when taking into account both event capture overhead in the server and also reading captured event log files.

For these reasons, we recommend using XEvents when monitoring SQL Server systems of version 2012 or greater, and potentially comparing the performance of SQLTrace on your system if you wish.

 

Each API can be configured to capture events to a log file or to a "Live Events" stream. XEvents can also be configured to use a memory Ring Buffer to cache events.

Cofiguring a log file allows monitored SQL Server systems to write events immediately to a local file, without any further interaction with our monitoring agent. SQLBench can then collect events asynchronously at a later time with minimal impact of the monitored server or Azure SQL Database.

A challenge with log files is configuring file system permissions that allow SQL Server to write to the log file location and the SQLBench Agent to read from and delete after reading from those same files.

Live Events do not require configuration of file system permissions but the SQLBench Agent must be configured with adequate resources to keep up with consumption of streamed events, to avoid impacting performance on the SQL Server system being monitored.

Memory Ring Buffers are available with XEvents only, and like Live Events, do not require configuration of file system permissions. Ring Buffers can lose events if the SQLBench Agent is not sufficiently resourced to consume events stored in the Ring Buffer before they are over-written by more events.

 

 

Configuration options are context sensitive, with relavent options being displayed based upon the SQL Server system being monitored. For example, SQLTrace is not available when monitoring Azure SQL Database.

Choose SQLTrace or XEvents in the API picker options.

Choose between Trace File, Live Events or Ring Buffer (XEvents only).

If Trace File is selected, choose a directory that the monitored SQL Server system can access and has permission to write event log files to. Also configure a path to the same directory which the SQLBench Agent will use to delete the files after consuming them. Also choose between whether to delete files via NTFS or via the TSQL xp_cmdshell stored procedure.

Before saving configuration changes, click the "Test" button to allow SQLBench to test configuration changes before they are saved.

After saving configuration changes, allow SQLBench a few minutes to implement changes, including consuming events cached in the existing configuration.