2

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).

5
  • 1
    Add the output of SELECT @@VERSION; to your question. I ran your trace and the error is captured with SQL Server 2019 CU4.
    – Dan Guzman
    Jun 26 '20 at 11:16
  • I did add our current version to the question. We are running 2016 and will not be able to upgrade in the near future. Seems strange that the trace should skip an error in 2016 but I guess it is not the first bug in SQL Server. Any advice about how to make sure this is the issue and if so is it fixed in the latest 2016 CU?
    – Peter Å
    Jun 26 '20 at 12:32
  • FWIW, I ran your trace under a SQL 2016 test instance (SP2-CU11-GDR KB4535706) and it captured the error. I'm not suggesting it's a bug fixed in CU11+ since there may be other factors involved.
    – Dan Guzman
    Jun 26 '20 at 13:04
  • 1
    Good day @PeterÅkerlund, I answered your question on MSDN already, and anyway I cannot publish long answers here in the comment. Please check the thread you opened there. Jun 26 '20 at 16:57
  • Thank you dba.stackexchange.com/users/73449/ronen-ariely, I did see your answer on MSDN and did add my comments. As stated there the reason why I try to use extended events are that we have not yet identified the query that is causing the issue.
    – Peter Å
    Jun 27 '20 at 17:17
2

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.

0

Thanks a lot Peter for giving the feedback on how your problem was solved. I'm in the ecaxt same situation with lots of 8623 errors occurring every evening and no clue what's causing them. Since extended events failed, I tried with a trace but never caught the actual query - only the error message and all queries before and after. Thanks to your info I now have a way to proceed. will have to be tomorrow though, as I'm past the time when the errors occur now...

Edit: I checked for dropped buffers - and there were none. Gaaahh Edit2: Maybe they didn't show because I deleted the event. Will see tomorrow

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.