0

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
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • 1
    Add the XE DDL with the proposed changes to your question. There may be additional options you can tweak to mitigate impact. – Dan Guzman Jun 10 '20 at 12:51
  • @DanGuzman, I have updated the question with our modification script for extended events – Venkataraman R Jun 11 '20 at 05:28
  • I'll assume the file target is present in your XE actual session. Do you use table-valued parameters? There is a known issue capturing TVP RPCs that's [fixed in SQL Server 2016+](https://support.microsoft.com/en-us/help/4051359/fix-sql-server-runs-out-of-memory-when-table-valued-parameter-captured) but I believe it still exists in older versions and is very costly with large TVPs. Consider specifying a larger buffer size (e.g. `MAX_MEMORY=100MB`, depending on your instance memory) along with `ALLOW_MULTIPLE_EVENT_LOSS` to mitigate impact. – Dan Guzman Jun 11 '20 at 09:56
  • @DanGuzman, Thanks for your answer. As it is legacy system, which is taken over by our team, need to check, if there is TVP usage. We will implement your suggestions. Apart from this, do you foresee any issue with our configuration ? – Venkataraman R Jun 11 '20 at 10:28
  • Those are the only suggestions I have. Barring TVPs, I am surprised the trace impact is noticeable unless the server is under stress. – Dan Guzman Jun 11 '20 at 11:05
  • @DanGuzman, there is a blocking issue is happening for DBCC SHRINKDB is running on schedule , due to SSIS package based job (which uses script component and ADO.NET code) and this ADO.NET code is running COUNT(*) for a table. This COUNT(*) table is created in tempdb in the background and blocking the SHRINKDB job. Once we stopped our extended events, all are running smooth – Venkataraman R Jun 11 '20 at 11:35
  • @DanGuzman, thanks for your suggestions. Please add this as answer. I will accept the same. – Venkataraman R Jun 11 '20 at 11:35

1 Answers1

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.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71