ASG RD DB performance issues

[Shaw_S]

Hi

Unfortunately we are currecntly experiencing quite some performance issues. When opening the application, entering changes or closing the application it completely freezes or pops out sql errors. Our DBA have looked at it and suggest the following:

/QUOTE
We see the below procedure is executed multiple times when the application is started and the procedure is running with the poor plan.
We also see the table Itemproperties has encrypted data, this may also be the reason for slowness, maybe the application is trying to Decrypt the data.
Kindly fine tune the application to resolve the issue.
   
   
   
/UNQUOTE

Can you please assist us?

Thanks in advance

Shaw

[Shaw_S]

Closing the app for instance made the app freeze for a dozen of minutes and the pops up the following message about 15 times:
   

[DevOma]

You should run the DataOptimizer - if you run Fix for any category, try again to find more - a lot of data could be "optimized" normally...

The data structure has a lot of advantages for internal use - and perhaps you should look at your logs - if you do not cut your logs it could cost also much time to add new entries or read content at the beginning (check Settings=>Logs)

[Shaw_S]

Hi DevOma

Thanks for the fast reply!
The DataOptimizer is the first thing I check. There's nothing to fix except a few dependencies.

Concerning the logs, this is what we have set:
   

Is 500 too much?

Regards

Shaw

[DevOma]

No - that's no problem - I know that some customers didn't set a limit and reached some GB of log data :-)

So I need some more details...

Which version do you use?
How many objects are in the database? Just about...
Any error logs that shows the error(s)?

[Shaw_S]

The DataOptimizer mentions 22870 objects in it's analysis. The version we use is 12.0.6504.1.

Here's the errorlog:
   

I received an update from our DBA's as well:
The DB is hosted on a shared SQL Server with 12 cores en 96GB memory, housing only about 50 DBs. The load is <50%CPU.
I'm an ASG RD user myself and to build my list of about 300 servers in ASG RD the application had to perform about a half a million reads !
The wait we experience is because the requested data isn't being picked up in a timely fashion by the application.

[DevOma]

Ok - timeout is always a problem when some data is corrupt - and perhaps in a recursive loop. Is it possible to update to version 2020? There is no database update, so just update one client to see if the behavior continues?!?

Else we would like to analyse the data - could you make a copy of your database - afterwards you can clear all credential properties

UPDATE ItemProperties SET Data = '' WHERE RolePropertyId = '5B5AA970-E8EA-4162-BECE-6A366E150C92'

And perhaps remove all logs

TRUNCATE Logs

Then you can send me the backup of the database for analyzing to asg.rd@asg.com or send me a link (via Private message) where I can download the file - and tell me your username so I can access your data (without creds)

[Shaw_S]

Alas, no change with the 2020 version. I installed it on one of our Citrix servers located in the same DC as the DB server to exclude the possibility of network issues due to excessive home working colleagues clogging up the VPN...
   

I'm trying to get a DB export from our DBA, if they're willing to do it...

[DevOma]

Ok