Help with slow response time in Archer v.6.9 SP1

[SteveDavis]

We just installed Archer v6.9 SP1 in a new environment with a database "converted" from an older Archer instance. The Archer v6.9 UI is really slow; maybe 2x to 3x slower than older v6.7 instance. Since the new database was converted to MSSQL 2019 from the older instance on MSSQL 2012 , I suspect the culprit in the response time lag MAY be the new database. Something I would like to show.

But I work in a  large organization with separate teams of DBAs, Sys Admins, and me...a developer. It is hard to prove anything in terms of what is causing the Archer v6.9 UI to lag just being a developer. 

What I need help with is some SQL (select only) that I can run  in a tool like SQLDeveloper against the Archer databases (new and old) so can compare response times.  I am just trying to hopefully demonstrate to our DBAs that this SQL ran in X msecs on the old database but now runs on the database it runs in X+ msecs. 

I realize this could be a total failure if there are no differences in response time of the SQL. But I got to try.

Thanks.

[DavidPetty]

Steve, you could load the browser developer tools and go to the network tab and observe the load times for the specific items to see which are taking a long time to load.

The other think to take a look at is how big some of the tables are in Archer specifically the history log and messaging ones.  That you can find out via the configuration report in the Archer Control Panel.

Lastly check to see if Archer's SQL maintenance jobs are loaded and running against the instance database

If not, the file can be found in \Program Files\RSA Archer\Tool\jobDeployScript.sql and run it against the instance database.

[SteveDavis]

Thanks for the response.

I sent an email to our sys admins and DBAs asking it these Archer jobs are running. I cannot see them myself or I do not know how to check if they are working . See what they say. May take a while. I do not think Archer is high priority on their task list at the moment.

Thanks again.

[Mahe_Ganesan]

Make sure the MDOP is to 1 for the Archer instance database.

[SteveDavis]

Heard back from our sys admins/DBAs, they said these jobs are running but they are checking when they runs and how often.

Thanks.

[SteveDavis]

I sent this issue to our sys admins/DBAs. See what they say. BTW, what does MDOP mean?

I checked the RSA Control panel and compared all the entries from the previous V6.7 P8 install and new V6.9 SP1 instance. They are identical except the URL for the advanced workflows (installation tab) was changed from HTTP:// localhost to HTTPS://computername:8443 so they could use certs. A requirement from Cyber I would imagine.

[SteveDavis]

One my colleagues mentioned that Archer stores a lot of history and that it may be negatively impacting response time if it gets too big. Since this a development v6.9 SP1 database we asked our DBAs to prune the Archer history tables. See what happens.

[ScottHagemeyer]

@SteveDavis,

MDOP stands Max Degrees Of Parallelism, and is a SQL Server database setting. For more info, check out this KB Article: https://community.rsa.com/t5/archer-knowledge-base/how-to-set-sql-server-max-degree-of-parallelism-m...

[DougSwarts]

I would make sure that the database compatibility level has been changed from 2012 to 2019. 

The other items do impact performance but it would be useful to have particular database calls that are slow. Updating indexes or changing max dop settings will force rebuilding query plans. This may provide temporary improvement but frequently the performance problem returns.

Here are some helpful articles troubleshooting database related performance problems.

Profiling SQL Server 

Bad Query Plan Workaround 

[SteveDavis]

Our sys admins/DBAs are rebuilding the databases indexes as I am writing this. They also are doing something with the advance workflow configuration. I not sure what they are doing. Maybe changing it back to http://localhost from https:// and certs. We will see.

Also, I have asked our DBAs to clear out the logs stored in the database. I was told those can hinder performance once they get over a 100K.  I am not sure that is good info or not. 100K is kind of small in terms of enterprise database compacity. 

Thanks for reply.