We have experienced really bad performance issues in subsession "Assign Credit Notes to Invoices". It takes about 5 minutes or more to show the first record, it opens the session but not data is shown.
First time I tried to investigate the problem it ran just fine (of course the user thinks I did something
). I did run the session with TT_SQL_TRACE and I saved the file "just in case". Two days after, I ran the session again and I experienced a 10 min delay! , so I saved the TT_SQL_TRACE again.
These are some of the facts I have encounter:
1. In the server, the "sqlservr.exe" process is using 1.7 GB of Memory and is specific to this transaction.
2. The first log file is 464 KB, the second is 30.6 MB ! for the same transaction.
3. There is no queries where the fetch time for 1st row is greater than 1 sec.
4. The CPU utilization is minimum (10-15%)
5. I run same session in test company and it runs fine.
Has anyone experince problems with this session? Or any ideas on how to fix this issue?
Thanks in advance!