Extended events capture bare minimum events for better performance

Question

We are having legacy application, where there are so many users connecting to our legacy system. We know about our jobs and our DB maintenance activities. But, we see so many different users also accessing the production system. We want to capture bare minimal extended events, to see what are the different third party users and what queries are being run by them.

Our Extended Events Session Current Configuration:

We added below events. We have applied filters for our databases in server. We are writing to disk file target with 5 GB limit and recycling the files, to avoid file system bloating.

• module_end ( additional event field: statement)
• rpc_completed (additional event field: statement)
• sql_batch_completed (additional event field: batch text)

We are capturing below Global fields.

• client_app_name
• database_id
• nt_username
• sql text
• username

But, even the above one is overwhelming for the production system. So, We are trying to reduce the amount of capture.

Our Planned Changes for minimal extended events capture:

• Apply filter for removing the known users from the events capture, in addition to database filters
• Just capture rpc_completed, sql_batch_completed events
• Just capture client_app_name, database_id, username global fields, as we can get sql statement from event field: statement

Our Question: Please suggest, whether we have configured our extended events session in the minimal configuration mode. Or do you suggest more changes to the event session.

Thanks for your help.

UPDATE: Our modification script for reference

ALTER EVENT SESSION [Audit_UserActivities] ON SERVER 
DROP EVENT sqlserver.module_end, DROP EVENT sqlserver.rpc_completed, DROP EVENT sqlserver.sql_batch_completed
ALTER EVENT SESSION [Audit_UserActivities] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.username)
    WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[database_name],N'DBPrefix%')) OR (([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'DBName')) AND ([sqlserver].[username]<>N'DBSysadminUser')))), ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.username)
    WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[database_name],N'DBPrefix%')) OR (([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'DBName')) AND ([sqlserver].[username]<>N'DBSysadminUser'))))
GO

Answer 1

I would not expect the Extended Events session in your question, with a file target, to generally be impactful on a healthy server. There are additional considerations you should consider to mitigate impact, though.

There is a known issue capturing TVP RPC events that's fixed in SQL Server 2016+, including Azure SQL Database. I believe the problem still exists in older versions and is very costly with large TVPs. Your recourse in SQL Server 2012 is to exclude TVP RPC events with a filter.

Consider specifying a larger buffer size (e.g. MAX_MEMORY=100MB, depending on your instance memory). Also specify ALLOW_MULTIPLE_EVENT_LOSS, to mitigate impact of tracing on your workload for high-frequency events since some event loss is acceptable for this tracing scenario.