SF: have tsf_optimize scheduled by default

Preferably, implement this using a standard system flow, rather than a SQL server job. This way, SQL Server Express will also be supported.

If this does happen then there should also be an option to disable this. Rebuilding indexes daily is not always best practice and should be tuned/considered depending on the project scale. Also for larger projects the settings used for index rebuild or update statistics might have to be tweaked. 

From platform version 2022.2, the Software Factory (sqlserver_sf) application in the Intelligent Application Manager (IAM) will have a system flow called "system_flow_run_tsf_optimize”. This system flow will run the tsf_optimize procedure in accordance to its active custom schedule.

By default, this system flow does not have any schedules. If you would like to use this system flow, you can disable the prefilter "Active” in the Scheduled system flows screen, then selecting the system flow and creating a custom schedule. Set the desired schedule Active to let Indicium run this system flow at the indicated intervals.

@Mark Jongeling sounds great! I suppose this is for the SF database only. Unfortunately the documentation on both tsf_optimize() and IAM task Optimize indexes is virtually non-existent. Therefore, some follow up questions:

• How about running this system flow for custom application databases?
• Does it do the same as the Optimize indexes Task in IAM?
• And does the Optimize indexes IAM task run on the IAM database or Custom Application database?

Hi @Arie V,

The tsf_optimize procedure is a procedure inside the DB base project. In Functionality, if you disable the Hide generated prefilter and search for "sql_tsf_optimize”, you can see the exact code the tsf_optimize procedure runs.

You can of course use this procedure for your own end product databases by creating a Task that executes the tsf_optimize procedure. This Task can then be used inside a system flow using the Execute system task process action. We will create a Thinkstore solution for this for all others to download. This will be similar with the implementation as I described in my previous reply in this topic. When this solution will be available, I cannot say for sure at the moment.

The Optimize indexes task in IAM does indeed use a Task that executes the tsf_optimize procedure. This runs tsf_optimize for the IAM database, not for any other databases. We could also schedule this to run, the only problem is that the IAM application is not visible to users so no other schedule could be used. Therefore we would have to create an option. If this is really desired, feel free to create an Idea. Alternatively, you can create a system flow running a Application connector for the IAM application. Then as code, exec tsf_optimize

What does the tsf_optimizer do:

It does two things:

1. It collects all indexes of the database which have an average fragmentation of 15% of higher and have 1000 or more pages. For each collected index, the index will be Reorganized if the fragmentation is lower than 30% to use minimal system resources. When the fragmentation is 30% or higher, the index will be rebuild.
2. It collects all Statistics of all User created tables which have been modified at least once. These collected statistics will then be updates using FULLSCAN. 

Hope this helps!

@Mark Jongeling great clarification, thanks! Perhaps also good to add this to Docs!