Azure Limitation on XEvent Memory for Slow Queries

[jacobgexigo]

So Microsoft has decided to limit the amount of memory that can be used for Extended Events, not only at the database level, but also at the elastic pool level. If it were only at the database level then we would be good, but we have an elastic pool with 52 databases in it sharing resources and even if we set the memory to be used in the XEvent to 256KB it still isn't enough to do 52 databases. We were looking to do some DML capture at one time and ran into the same thing and worked with Microsoft to understand the issue, so this is confirmed directly with Microsoft.

https://docs.microsoft.com/en-us/azure/azure-sql/database/xevent-db-diff-from-svr#performance-considerations

I'm wondering if anyone has an idea on how this could still be accomplished? I don't know if it would be feasible to pull some of this from the Query Store DMV's directly, though I know it wouldn't be as complete as we use Auto to only grab queries that are causing waits...but maybe that's okay?

Error (Abbreviated):
"Microsoft.Data.SqlClient.SqlException (0x80131904): Operation failed. Operation will cause database event session memory to exceed allowed limit. Event session memory may be released by stopping active sessions or altering session memory options. Check sys.dm_xe_database_sessions for active sessions that can be stopped or altered. If no sessions are active on this database, please check sessions running on other databases under the same logical server.
The event session has already been stopped. Error Number:25747,State:1,Class:16"

[DavidWiseman]

I've bumped against this limitation also - it's a bit frustrating. My recommendation to workaround it is to be selective about which DBs you capture slow queries for.

For AzureDB you just need to set a connection to the master DB and it will add the other DBs without you needing to add individual connections. If you have slow query capture on master - all the databases inherit this setting. I would turn off slow query capture on master then add explicit connections to the most critical databases - turning on slow query capture as required (within the limitations). In the service config tool there is a button to scan for azure db connections to add the individual DBs to the config.

Query store doesn't capture the same data. The extended events are capturing individual rpc & batch completed events. The query store data is captured at the plan level and the data is aggregated rather than capturing individual executions. Query store data is still very useful - it's just different from the slow query capture. DBA Dash doesn't do any statement/plan level capture (except the running query snapshots) - Query store is good to compliment what is collected by DBA Dash.

If there is a better workaround I would be interested.

Hope this helps

[jacobgexigo]

I wonder if we might try including the "MAX_EVENT_SIZE" parameter in the GUI so we can set it as well? I think it's a combination of MAX_MEMORY and MAX_EVENT_SIZE that will dictate the total amount of memory requested when trying to start the session. Otherwise there is something wrong with their calculations in Azure at the product level. I have done testing against a vCore elastic pool with 31 databases in it and set the MAX_MEMORY to 192KB and it still throws the error, which is impossible mathematically. I've opened a support ticket with them to review it.

[jacobgexigo]

And if we are able to do that...can you put a check in the GUI to not allow a value for MAX_EVENT_SIZE that is less than MAX_MEMORY? That is a stipulation of its use...that it must be greater, even if it's by 1KB I imagine.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-event-session-transact-sql?view=sql-server-ver15#max_event_size-size--kb--mb-

[jacobgexigo]

I got feedback from Microsoft and have tested their advice successfully. Looks like all we need to do is add "(SET max_memory = ????)" to the ADD TARGET section (replacing ???? with the value for max memory). So maybe in the admin GUI we add a column for "Target Max Memory" or just reuse the "Slow Query Session Max Memory (KB)" value for this one as well.

CREATE EVENT SESSION [DBADash_1] ON DATABASE
ADD EVENT sqlserver.rpc_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.session_id,sqlserver.username)
WHERE ([duration]>(1000000) AND ([sqlserver].[client_app_name]<>N'DBADashXE' AND [object_name]<>N'sp_readrequest'))),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.session_id,sqlserver.username)
WHERE ([duration]>(1000000) AND [sqlserver].[client_app_name]<>N'DBADashXE'))
ADD TARGET package0.ring_buffer (SET max_memory = ????)
WITH (MAX_MEMORY=???? KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=2 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

[DavidWiseman]

Hi,

Thank you - that's useful to know. I'll have a look into it and will likely update the app as you have suggested. The setting doesn't seem to be very well documented other than this note:

If you are using the ring buffer target, we recommend that you set the max_memory target parameter to 2048 kilobytes (KB) to help avoid possible data truncation of the XML output. For more information about when to use the different target types, see Targets for Extended Events in SQL Server.

I'll take a proper look when I get time.

[DavidWiseman]

#240

[DavidWiseman]

As a workaround, there is an option in the service config tool to "Persist XE Sessions". The app won't drop/re-create the event sessions with this option enabled which allows you to alter the configuration of the event session as needed. For example, adding the max_memory setting or adding some filters.
The downside to persisting sessions is that you might not benefit from changes to the event session in new versions of the application.

[jacobgexigo]

Oh, that's outstanding. I did one server in full to test but didn't do it on the rest because I figured it would overwrite on the next service restart. I am subscribed to all changes here so I will keep an eye out for this change to go in at a future date and remove the persistence before the update at that time. Thank you!

[DavidWiseman]

Note: The setting is saved in the config which is only read when the service starts. If you change the config and restart the service it would drop the session when the service is stopped (as it's using the old config which doesn't have the persist option).
If you didn't want to re-create the sessions for the server you have modified you could kill the "DBADashService.exe" in task manager so it doesn't run the session cleanup like it would if it was stopped normally. Then you can enable the Persist XE sessions option for the connection and start the service.

[DavidWiseman]

Fix is included in the latest release.

[AlreadyPicked]

I'm still confused as to how much max memory I should configure when encountering this problem. The doc (https://dbadash.com/docs/help/slow-queries/#azure-db) doesn't mention it either.

Also, is "-1" a default value? If I run this "SELECT * FROM sys.dm_xe_database_sessions", the total_target_memory is 500KB. Should I put 32 in the slow Query Target Max Memory inDBA Dash Service Config?

[DavidWiseman]

The value of -1 is the default value where it's not configured. This should result in 33,554,432 bytes (32,768KB / 32MB). I'm not sure what setting will work for your environment. I would maybe start by reducing it to 16,384KB. Then try 8,192KB, 4096KB. Limiting the slow query capture to only the databases you are most interested in would also help.

[AlreadyPicked]

Thank you for the explanations.

After verification, leaving -1 indeed gives a whooping value value of 33,554,432 bytes (32,768KB / 32MB) on each db. I'll be tweaking it to see how it reacts.