nop 4.1 can't re-index DB through GUI

3 anni tempo fa
Just upgraded from 4.0 to 4.1. When I try to re-index the DB through the GUI (in the maintenance area) I get the error. Site is hosted in Azure as a web app.

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (258): The wait operation timed out
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlCommand(DatabaseFacade databaseFacade, RawSqlString sql, IEnumerable`1 parameters)
   at Nop.Data.NopObjectContext.ExecuteSqlCommand(RawSqlString sql, Boolean doNotEnsureTransaction, Nullable`1 timeout, Object[] parameters) in E:\nopCommerce\sources\src\Libraries\Nop.Data\NopObjectContext.cs:line 143
   at Nop.Services.Common.MaintenanceService.ReIndexTables() in E:\nopCommerce\sources\src\Libraries\Nop.Services\Common\MaintenanceService.cs:line 203
   at Nop.Web.Areas.Admin.Controllers.CommonController.ReIndexTables(MaintenanceModel model) in E:\nopCommerce\sources\src\Presentation\Nop.Web\Areas\Admin\Controllers\CommonController.cs:line 229
ClientConnectionId:8f5ef3f4-8f0c-4cb4-a6ef-43651fd81575
Error Number:-2,State:0,Class:11
ClientConnectionId before routing:cf4dae22-baa0-4093-835c-8543479bf5db
Routing Destination:d66d01d05f2e.tr30.centralus1-a.worker.database.windows.net,11222
1 mese tempo fa
I would recommend the following. It provides instructions on how to rebuild indexes in a SQL Azure Environment.

https://www.beyondtrust.com/docs/privilege-management/reporting/install/db-maintenance/rebuild-indexes-sql-azure.htm

In particular the, "Rebuild All Database Indexes".

You have to create a stored procedure in SSMS. I followed the steps from here:
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15

I followed the stops up to step 3 (skipping steps 4 to 7) as after that step 3, I pasted the entire query from the following link into the new stored procedure.

https://raw.githubusercontent.com/yochananrachamim/AzureSQL/master/AzureSQLMaintenance.txt

I skipped step 12 as I ran with "EXEC AzureSQLMaintenance 'index'" which was inserted into the third row.

I was a little nervous running this as I have very little database experience. However our Nop site was plagued with query timeouts and maxing out the DTU several times a day. When I ran a query to see how fragmented the indexes were, many were > 99% with average page space being small some less than 4%.

After making a copy of the database (just in case). I set up the stored procedure and ran it. It ran for approx. 90 minutes with DTU at 100%. Once complete, I performed some tests to be sure everything was working and now the site is much faster with no query timeouts and  DTU hasn't been higher then 27%.