Using Extended Event Session (XE) to track queries going against Always On AG primary

Using Extended Event Session, once we add filters, we can see specifics – queries that are hitting a specific database on Server A (primary) from a specific server (and many excluding the monitoring software).

Introduction

The Always On Availability Group (aka AG) feature in SQL Server provides a great HADR (high-availability and disaster recovery) solution. This HADR solution can also be used to alleviate the pain from a single database server. AG allows the user to add new servers to offload reporting needs (read replicas) without any prior domain knowledge.

To start with a new read replica, you would need a new Database Server and AG as a data synchronization mechanism (synchronous mode for time sensitive needs and asynchronous mode for everything else). Read replica provides a database on a new server that will help to lift the pressure from a single Server A (AG primary) by making Server B (AG secondary/read replica) available for all the read queries.

Problem

AG is relying on a connection string directive called Read-Intent only which instructs SQL Server to redirect all the calls to a read replica. Unfortunately, all the existing connections were defined before AG introduction, so what do we do now? How would we identify all the read queries that need a connection string change? Again, without changing connection strings, all the existing queries will still be hitting Server A, hence AG by itself would not solve the problem.

Solution

Introducing Extended Event sessions (aka XE session). XE is essentially a replacement for SQL Profiler, which enables us to see everything that is hitting the SQL Server while offering better interface and scripting capabilities. Once we add filters, we can see specifics – queries that are hitting a specific database on Server A (primary) from a specific server (and many excluding the monitoring software).

The following script creates a new XE session that ignores system queries, includes queries that are going against a Database called Database from a server called DW while excluding queries from SentryOne monitoring software.

Source code

CREATE EVENT SESSION [DW going against PRIMARY] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)
 
WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)) 
 
AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%OLTP_Database%') 
 
AND [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'DW') 
 
AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'SentryOne 20.0-Server')))
 
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU
,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Disclaimer

This blog post is partially based on the following sources:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server

https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/sql-server-extended-events-sessions