Query processor ran out of internal resources and could not produce a query plan (error 8623, 16, 1)

Hi,

we are experimenting strange errors (Event code: 3005 Event message: An unhandled exception has occurred. Exception type: SqlException) when some crawler require this URL type:

https://[domain]/[category]?orderby=11&viewmode=grid&specs=25,111,114,128,132,135,140,143,371,495,496,501,508,512,513,517,521,523,525,527,504

SQL cannot calculate query plan 'cause internal resources missing. After some tracing in SQL (2016), we think sp 'ProductLoadAllPaged' produce so many lookup to stuck SQL query planner.

How we can avoid this behavior?

Thanks a lot

=======

This's the full error message:
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   in System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   in System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   in System.Data.SqlClient.SqlDataReader.get_MetaData()
   in System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   in System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   in System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   in System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   in System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   in System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   in System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   in System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   in System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   in System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   in System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   in System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64()
   in System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   in System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63()
   in System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   in System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   in System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)
   in System.Data.Entity.Internal.InternalContext.<>c__DisplayClass14`1.<ExecuteSqlQuery>b__13()
   in System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   in System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   in System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   in Nop.Data.NopObjectContext.ExecuteStoredProcedureList[TEntity](String commandText, Object[] parameters)
   in Nop.Services.Catalog.ProductService.SearchProducts(IList`1& filterableSpecificationAttributeOptionIds, Boolean loadFilterableSpecificationAttributeOptionIds, Int32 pageIndex, Int32 pageSize, IList`1 categoryIds, Int32 manufacturerId, Int32 storeId, Int32 vendorId, Int32 warehouseId, Nullable`1 productType, Boolean visibleIndividuallyOnly, Boolean markedAsNewOnly, Nullable`1 featuredProducts, Nullable`1 priceMin, Nullable`1 priceMax, Int32 productTagId, String keywords, Boolean searchDescriptions, Boolean searchManufacturerPartNumber, Boolean searchSku, Boolean searchProductTags, Int32 languageId, IList`1 filteredSpecs, ProductSortingEnum orderBy, Boolean showHidden, Nullable`1 overridePublished)
   in Nop.Web.Controllers.CatalogController.Category(Int32 categoryId, CatalogPagingFilteringModel command)
   in lambda_method(Closure , ControllerBase , Object[] )
   in System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   in System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   in System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.<BeginInvokeSynchronousActionMethod>b__39(IAsyncResult asyncResult, ActionInvocation innerInvokeState)
   in System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)
   in System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase`1.End()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3d()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass46.<InvokeActionMethodFilterAsynchronouslyRecursive>b__3f()
   in System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass33.<BeginInvokeActionMethodWithFilters>b__32(IAsyncResult asyncResult)
   in System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult)
   in System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResultBase

We fix the issue inserting a clause TOP in ProductLoadAllPaged.

Snippet before:

SELECT sao.SpecificationAttributeId, fs.SpecificationAttributeOptionId    
FROM #FilteredSpecs fs INNER JOIN SpecificationAttributeOption sao ON sao.Id = fs.SpecificationAttributeOptionId
ORDER BY sao.SpecificationAttributeId

Snippet after:

SELECT TOP 25000 sao.SpecificationAttributeId, fs.SpecificationAttributeOptionId    
FROM #FilteredSpecs fs INNER JOIN SpecificationAttributeOption sao ON sao.Id = fs.SpecificationAttributeOptionId
ORDER BY sao.SpecificationAttributeId

Now all it's running fine. We don't know if 25000 it's a right limit for all nop site, but for us it's enough.