When running the reindex process, what is the normal procedure? Do you shutdown the task agent first or can you run the reindex while the task agent is running and then restart the task agent after when the reindex is complete?
Even restarting the task agent afterwards when the reindex has completed, our multi-company was not processing inbound multi-company processes which will require another restart of the task agent.
Unless you have the Enterprise SKU of SQL server and also rebuild your indexes ONLINE, youâll have to restart the processserver and taskagent appservers after a rebuild activity.
I would recommend against an ONLINE rebuild. It is resource intensive and requires more space than an OFFLINE (not really offline). I would use offline and instead of a post restart of the process and task I would recommend shutting them down ahead of time as they can delay how long it takes the index rebuild to run. You can script the commands all into one agent job to stop process & task, rebuild, and then start process and task.
We did do a post-process restart but inbound transactions are failing and only outbound transactions are still processing. Weâre not sure why it would fail after a post process restart of the task agent.
Does this include the appservers needing to be shutdown also or just the task agent? It took about 5 hours to do the reindex with it online.
I would recommend it. A weekly restart of the application processes (app, process & task) along with a rebuild will keep things running a little better in E9. I would expect your re-index times to be less this way. Maybe baseline it with everything shut down and then make a decision from there.
Are you rebuilding all indexes in your database every time? If so, Iâd recommend looking into only rebuilding the indexes that actually need it which could save a lot of time (and if you have your SQL data files on SSDs, some wear and tear on your drives as well). I recommend Ola Hallengrenâs maintenance script which looks at individual index fragmentation levels and only rebuilds if it exceeds a threshold. Itâs really a handy script and itâs one of the first scripts I put into my maintenance SQL database whenever I install a new SQL server instance. It can be downloaded here: https://ola.hallengren.com/
+1 on the Hallengren scripts Nate mentioned.
likewise, Iâve used these for a couple years now and they are bullet proof.
Just a couple more points for this thread
1 - Make sure you have enough contiguous free space in the database to arrange the index consecutively or you will never get the fragmentation to an acceptable level and the re-index (regardless of method) will always run against the index wasting valuable resources
2 - Smaller indexes will always use mixed extents and report as fragmented. You can create a custom reindex to look at page sizes and determine whether or not to reindex based on the page size. This will help reduce your overall reindex time whether using a basic reindex or the Hallengren scripts.
3 - Make sure you account for log space needed so you do not cripple your SQL server.
If you take 1 and 2 into account you can start to trust fragmentation numbers and take a better approach to managing.
We are currently running the Hallengrenâs maintenance script. Our database is over 20GB which could be why itâs taking over 2+ hrs to reindex.
that doesnât sound right at all⌠Iâve indexed 100gb DBâs in less than 20 minutes. Something environmental or setup related going on?
for what itâs worth, this is a parameter dataset i run (modify for your own use, iâve had success with this is all)
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'Epicor10',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@FillFactor = 90,
@LogToTable = 'Y'" -b
This is our parameters. Are you running it offline or online? We run 24/7 so the reindex is done online which could be causing the long reindex times.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q âEXECUTE dbo.IndexOptimize @Databases = âEpicorPilot905â,
@FragmentationLow = NULL,
@FragmentationMedium = âINDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINEâ,
@FragmentationHigh = âINDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINEâ,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30, @UpdateStatistics = âALLâ,
@OnlyModifiedStatistics = âYâ,
@MAXDOP = â4ââ -b
i should just remove the rebuild online reference as its useless (left over from when i copied it). All rebuilds are done offline unless you have enterprise version of SQL. Likely you donât unless your company loves to give Bill money. A really busy system will slow the re indexing/rebuilding down. Just schedule it during a less busy time. We all have those.
We use to do them monthly on Sundayâs at 2am but went to doing them âas-neededâ. Even at the less busy times, it is still taking a long time. Does doing the reindexing more frequently reduce the time it takes to run?
i run nightly, you end up reorganizing more and rebuilding less (theoretically) if you stay on top of it. I know there are more knowledgeable SQL experts out there and probably several schools of thought. This shouldnât take the time it is though on a 20gb db. Iâm not sure if when you say 20gb you are only referencing your tables strictly or the general .mdf file size (which includes data tables, schemas, indexing, allocated space, etc etc). If you are referencing the mdf file size, keep in mind a respectable chunk of that size is the indexing itself. Based on some of the questions you are asking i think it would benefit you greatly to do some reading on indexing. There is a ton of reading out there on indexing that goes far beyond what ozar presents. MSDN, stack exchange and tons of other sites offer insight on the inâs and outâs of indexing. This can help you get your arms around it, it seems simple on the face but itâs a complex tool. I did the same when i battled my own issues with it a couple years ago.