Sql-server – Problem tracing Error: 8623 with extended events

extended-eventssql server

From time to time we are getting SQL Server error:

Error: 8623, Severity: 16, State: 1
The query processor ran out of
internal resources and could not produce a query plan. This is a rare
event and only expected for extremely complex queries or queries that
reference a very large number of tables or partitions. Please simplify
the query. If you believe you have received this message in error,
contact Customer Support Services for more information.

To be able catch what is causing this error I have created an extended event (XE) session on XE event error_reported. The XE session seems to be working as other errors are logged but not 8623.

My XE session is created like this:

CREATE EVENT SESSION [ErrorReported] 
ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([severity]> 15))
ADD TARGET package0.event_file(SET filename=N'C:\trace\ErrorReported.xel',max_file_size=(250),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

Anyone that help me to get XE to log also error 5623? Any help hugely appreciated.

We are currently running Microsoft SQL Server 2016 (SP2-CU10).

Best Answer

Thanks to Ronen Ariely over at MSDN and Jonathan Kehayias article on SQL Performance I was able to solve the problem. The problem was that event size was to big so that it was dropped. You can check this by querying sys.dm_xe_sessions as e.g.

   SELECT
   s.name, 
   s.total_regular_buffers,
   s.regular_buffer_size,
   s.total_large_buffers,
   s.large_buffer_size,
   s.dropped_event_count,
   s.dropped_buffer_count,
   s.largest_event_dropped_size
FROM sys.dm_xe_sessions AS s;

If any event has been dropped you will se the number of dropped events in column dropped_event_count and the max size of any dropped event in largest_event_dropped_size. If this value is larger that the regular_buffer_size then you need to increase the regular buffer size.

This was the case for me and when I did change the Max Memory size from the default to 64MB the regular buffer size was increased so much that I was able to catch the error!

As a side note the problematic query was a entity framework generated SELECT with an huge (> 38 000 values) IN clause! No wonder that the database engine got enough.

Related Question