SF: have tsf_optimize scheduled by default

Related products: Software Factory

Dear community,

All of a sudden, it seems, my project took 10-20 minutes to open the first document. Subsequent documents open at normal speed.

I was at a loss where to look for the cause of this sudden delay.

 

Some more details:

  • This started happening from when I created a new branch with an application ID that is not lowest, so that I have to switch available applications
  • The initial documents I tested have very limited screen types, these could not be the cause.

It turned out that the delay was caused by out of date statistics on the database server.

After running

exec tsf_optimize

the wait for the initial document dropped from 800 seconds to 1 second. That's what I call improvement!

 

Is it an option to have the deployment center add these optimize scripts to the job scheduler / SQL Agent / Indicium process flow ?

 

Updated idea status New → Open

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. 

Updated idea statusOpen→Working on it!
Updated idea statusWorking on it!→Next release

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!

Updated idea statusNext release→Completed
Terms & ConditionsCookie settings