after upgrading from 8.16 to 9.24.1 we're having issues periodically with publishing products in Episerver.
When clicking "Publish" - nothing happens, and after a while we see an error message stating
Something went wrong
Execution timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The wait operation timed out.
Here's the related stack trace:
System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command)
at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, DataParameter commandParameters)
at Mediachase.MetaDataPlus.Configurator.MetaObjectDB.UpdateMetaObject(MetaDataContext context, MetaObject metaObject, Dictionary`2 metaParams, Boolean disableSync)
at Mediachase.MetaDataPlus.MetaObject.AcceptChanges(MetaDataContext context)
at EPiServer.Commerce.Catalog.Provider.Persistence.MetaDataCommitter.CommitMetaAttributes[T](T content, Int32 objectId)
at EPiServer.Commerce.Catalog.Provider.Persistence.EntryContentBaseCommitter.UpdateExisting(EntryContentBase content, Int32 entryId, Boolean syncDraft)
at EPiServer.Commerce.Catalog.Provider.Persistence.EntryContentBaseCommitter.Save(CatalogContentBase content, Boolean syncDraft)
at EPiServer.Commerce.Catalog.Provider.CatalogContentCommitterHandler.SaveVersionInternal(CatalogContentBase content, SaveAction action, String currentUser)
at EPiServer.Commerce.Catalog.Provider.CatalogContentCommitterHandler.Save(CatalogContentBase content, SaveAction action, String currentUser)
at EPiServer.Commerce.Catalog.Provider.CatalogContentProvider.Save(IContent content, SaveAction action)
at EPiServer.Core.DefaultContentRepository.Save(IContent content, SaveAction action, AccessLevel access)
at EPiServer.Cms.Shell.Service.ContentService.Save(IContent content, SaveAction saveAction)
at EPiServer.Cms.Shell.Workspace.Committers.ContentDataCommitter.Commit(EditContentData change, CommitArguments argument)
At first we thought this was solved by creating a missing index (https://world.episerver.com/forum/developer-forum/Episerver-Commerce/Thread-Container/2021/3/spikes-in-sql-cpu-usage-after-upgrading-to-commerce-9-24-1/) but we're still seeing this issue quite often.
Any advice on how to proceed with troubleshooting?
Do you have any post publish events?
Have you upgraded database without issues while upgrading episerver databases?
Are you on dxc? Can you check error telemetry from application insight?
The general approach is to use a profiler to see what happens under the hood. If you can replicate the issue locally, then SQL Profiler can be a very good choice in this case.
Also, how is your SQL Server behaving during the publish? Is it overloaded (CPU/mem) at all?
Yes, we did finish the upgrade without any migration errors.
As far as I can see we do not have any publish events. There's no sign of subscribing to any content event in the initialization modules.
We're not on DXC, we're on-prem.
We're not seeing any high CPU/Memory usage during the periods when publishing fails
I ran a query to get suggested indexes - I should probably not create all of these. Would creating any of these indexes maybe help with our problem publishing content?
I wasn't able to present the data in a more readable way - however, if you copy the table underneath and paster into Excel - it should be readable:
As mentioned above, you wwould neeed to understand why the query is timed out. Sql Profiler is a very good way to capture the query (with parameter), and then you can run that against SQL Server Managment Studio to get the execution plan. Only then you know what tuning you'll need.