SQL Server Audit vs Extended Events: Which to Use When

Introduction

When it comes to monitoring and tracking activity in SQL Server, database administrators have two powerful tools at their disposal: SQL Server Audit and Extended Events. While both serve similar purposes in capturing database events, they have distinct architectures, use cases, and capabilities. This blog post will compare these technologies and provide guidance on when to use each one.

SQL Server Audit Overview

SQL Server Audit is a feature specifically designed for compliance and security monitoring. It provides:

• Focused tracking of security-related events

• Granular control over what gets audited

• Integration with Windows Security logs

• Compliance-friendly output format

Key Characteristics

1. Purpose-built for auditing: Designed specifically for compliance requirements

2. Server and database-level auditing capabilities

3. Writes to files, Windows Application/Security logs, or the Windows Event Log

4. Simple configuration through SQL Server Management Studio (SSMS)

Extended Events Overview

Extended Events (XEvents) is a lightweight performance monitoring system that:

• Captures a wide range of SQL Server events

• Minimal performance impact compared to other monitoring tools

• Highly customizable event collection

• Advanced troubleshooting capabilities

Key Characteristics

1. Lightweight and flexible event handling system

2. Broad event coverage (performance, errors, queries, etc.)

3. Multiple output targets (files, ring buffers, event streams)

4. Programmatic interface for advanced scenarios

Head-to-Head Comparison

Feature	SQL Server Audit	Extended Events
Primary Purpose	Compliance auditing	Performance troubleshooting
Performance Impact	Moderate	Lightweight
Event Coverage	Security-focused	Very broad
Configuration	GUI available	Primarily T-SQL
Output Formats	Limited (file, Windows log)	Flexible (file, memory, real-time)
Granularity	Object-level	Very detailed
Compliance Ready	Yes	With customization
Learning Curve	Low	Moderate to high

When to Use SQL Server Audit

Choose SQL Server Audit when:

1. Meeting compliance requirements (SOX, HIPAA, PCI-DSS)

2. Tracking security-related events:

   • Login successes/failures

   • Permission changes

   • Schema modifications

   • Data access patterns

3. Needing Windows Event Log integration

4. Requiring simple, standardized audit trails

5. Preparing for security audits where format matters

Example Audit Scenario

-- Create a server audit
CREATE SERVER AUDIT HIPAA_Compliance_Audit
TO FILE (FILEPATH = 'C:\Audits\')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

-- Enable the audit
ALTER SERVER AUDIT HIPAA_Compliance_Audit WITH (STATE = ON);

-- Create database audit specification
CREATE DATABASE AUDIT SPECIFICATION HIPAA_DB_Audit
FOR SERVER AUDIT HIPAA_Compliance_Audit
ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public);

When to Use Extended Events

Choose Extended Events when:

1. Troubleshooting performance issues

2. Capturing detailed query execution data

3. Monitoring specific conditions or thresholds

4. Needing low-overhead monitoring

5. Requiring custom event correlation

6. Debugging complex application issues

Example Extended Events Scenario

-- Create an Extended Events session for query performance
CREATE EVENT SESSION [QueryPerformance] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],'%Customers%'))),
ADD EVENT sqlserver.rpc_completed(
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],'%Customers%')))
ADD TARGET package0.event_file(SET filename=N'C:\XEvents\QueryPerformance.xel')
WITH (MAX_MEMORY=4096 KB, MAX_DISPATCH_LATENCY=30 SECONDS);

Hybrid Approach: Using Both Together

In many environments, using both technologies together provides comprehensive coverage:

1. SQL Server Audit for compliance-mandated security auditing

2. Extended Events for performance monitoring and troubleshooting

Example Combined Use Case

• Audit: Track all failed logins and permission changes for security compliance

• XEvents: Monitor long-running queries and deadlocks for performance tuning

Performance Considerations

• SQL Server Audit:

  • Higher overhead for detailed auditing

  • Queue delay setting helps mitigate performance impact

  • File target generally performs better than Windows Event Log

• Extended Events:

  • Designed to be lightweight

  • Overhead scales with number of events captured

  • Ring buffer target has minimal impact for troubleshooting

Migration Considerations

If you're currently using older technologies:

• Replace SQL Trace/SQL Profiler with Extended Events

• Replace older audit triggers with SQL Server Audit

• Deprecated features like Change Data Capture may have alternatives in these technologies

Best Practices

1. For compliance: Start with SQL Server Audit

2. For performance: Use Extended Events

3. Be selective: Only capture what you need

4. Test impact: Always evaluate monitoring overhead

5. Retention: Plan for log rotation and archival

6. Security: Protect audit logs with appropriate permissions

Conclusion

SQL Server Audit and Extended Events serve different but complementary purposes:

• Choose SQL Server Audit when you need security-focused, compliance-ready tracking with simple configuration

• Choose Extended Events when you need deep performance insights with minimal overhead and maximum flexibility

Many organizations benefit from implementing both solutions, using each for its strengths. By understanding their differences and appropriate use cases, you can build a comprehensive monitoring strategy that meets both your security compliance requirements and performance troubleshooting needs.