Very slow checkin dialog and file open

[mp3-250]

I am preparing to upgrade the company to SW2022 SP5 and I have setup the test server.

Test environment is one windows server 2016 running SQL2019 CU18 and SW Archive server.
We are going to use 2 clients to test the upgrade, but at the moment I am the only one accessing the test vault to re-create our templates from scratch and to check all the configuration on the server and client side before starting the real tests.
Vault users are configured as Active Directory and we have a domain.
Domain controllers are not on my resposibility and likely located on a WAN as we are integrating systems with our head company.

Problem:

- PDM dialogs like check-in and file operation like open are VERY SLOW.
TBH our production servers still on 2021SP5.1 are not that good as well and the performances have degraded seriously over the years. (basically the ex admin was hoping the next version would be magically better)
- My user and another one (both members of admin group) login are VERY slow (up to 1 minute including latest version from 2 folders)
- Working on a DRWDOT file (less than 100KB) it takes 30s to open it from the vault, and 15s to display the check-in dialog hanging SW for a few seconds, but when I log in as "ADMIN" it gets A LOT faster: e.g. log in process takes a few seconds and the check-in screen appears in less than 1~2 seconds.

This seems similar to SPR-1031769 (implemented in SW2018)

SOLIDWORKS PDM - Cmd - Check-out: Check out operation in PDM is very much slower (5x times) for normal users as compared to a default admin user *vault specific*
Check out operation in PDM local vault view is very much slower (5x times) for normal users as compared to a default admin user with no group memberships *vault specific*

Please note the customer is preparing for upgrade to 2017 SP3. The issue is the same in 2016 SP4 which they are currently running.

Reproducible in PDM 2017 SP3 using attached customer vault backup and archives
1. Restore the database as a vault and restore the archives (the customer specific hotfix from SPR 1029943 is already applied to this backup)
2. Log in to the local view as user 'watmai' (a typical user with 'complex' permissions)
3. Cancel the prompt for automatic caching
4. Browse to '<vault>\COLLABORATION\TEST\TEST\03_Engineering & Technology'
5. Start an SQL Server Profiler trace for explorer.exe
6. RMB > check-out on '1013900_Perf_Test1.SLDDRW' to build the reference tree and show the check-out dialog
- this will generate approx. 1.6 million reads and take around 30-40 seconds.
7. Log out and in again as Admin, run the same test
- this will generate 2.5 million reads but will complete in around 5-6 seconds (Note that Admin is a member of LOTS of groups)
8. Log out
9. Create a new user (do not copy a user)
10. Do not assign any groups, assign all folder permissions at the root level, and all state permissions for the workflow ‘PCP’
11. Repeat the checkout test as above
- this should generate 250k reads and complete in 4-5 seconds.

This is one example scenario for 'slow' actions for this customer's PDM users.

What I tried:
Before noticing the admin user behaviour I tried various solution from the KB
- Set every DB on SIMPLE backup and cutting SQL transaction logs which were quite big, (up to 25% the size of the DB) and LDF files went from GBs to KBs
- VAULT DB is running on CL150 (SQL2019) no effect changing it to CL140
- ConisioMasterDB is still on CL120 (SQL2014) and I am not sure if I should touch it or not
- rebuilt DB with update statistics index
Customers running SQL Compatibility Level 130 and 140 delays (minutes) when logging in or browsing folders after upgrading to PDM 2022. This issue is only seen for certain PDM users. Activity monitor on the SQL server shows a lot of processes with a SUSPENDED Task State and Wait Type of CXPACKET and CXCONSUMER while SQL runs 'Usr_GetPopupInfo'.

SPR-1245044

Explorer hang after login or browsing folders for certain Users on SQL 2019,2017,2016 with CL140 or CL130 - browse works with CL150 -SQL Activity monitor shows suspended calls from 'Usr_GetPopupInfo'

Reproduced with PDM 2022 SP5 and PDM 2023 SP1 ( (B102)) with SQL 2019 (15.0.4178.1) with database Compatibility Level (CL) at (130 and 140).
1. Log into PDM local view
- File Explorer hangs while SQL Activity monitor shows suspended calls from 'Usr_GetPopupInfo'.
3. Browse to another folder
- File Explorer hangs while SQL Activity monitor shows suspended calls from 'Usr_GetPopupInfo'.

Workarounds:
- Run a SQL maintenance plan to 'rebuild' with 'update statistics' on indexes.
- If SQL maintenance does not improve, contact PDM Technical support for instructions to create custom index on the db.documents table.
- If running SQL 2019, switch compatibility level to 150

- From SQL Activity monitor no blocks are listed during the client operations (S-076180)

What I am planning to do:
- Running a trace on the SQL server to look at the differences between the AD user and the admin user.
I never touched a SQL server before so any suggestion on how to look at the trace results would be helpful.
- Just to be sure test the open time again, but with PDM disabled
https://www.goengineer.com/blog/solidwo ... -technique

Any other suggestion or idea??

[jcapriotti]

This sounds very much like a latency issue. What are your ping times and "tracert" look like to the PDM SQL server and the PDM Archive server?

[mp3-250]

@jcapriotti
I will run the tests again in monday, but it was like pinging the nearby workstation. I use the troubleshooting utility from SW and ping, telnet, login was cleared without a problem.

Please note if I logout my PDM user and immediately login the admin on the very same machine and vault view the performances with the same files and operations drastically improve and become more than acceptable.

[bnemec]

@jcapriotti
I will run the tests again in monday, but it was like pinging the nearby workstation. I use the troubleshooting utility from SW and ping, telnet, login was cleared without a problem.

Please note if I logout my PDM user and immediately login the admin on the very same machine and vault view the performances with the same files and operations drastically improve and become more than acceptable.

Interesting. So your normal PDM user account (credentials managed by AD) is slow but your Admin account (PDM only account, no AD) is fast? Do you have (or can make) a test PDM user account to see if it behaves like Admin? Don't put that user in any groups, just like Admin account. If the test user is fast like Admin then put it in the same group(s) your user is in and test again. (that should get permissions applied from the group but not the settings). Then try making a new test user but when you make that new user tell it to copy settings from your normal user, test again. I think that should narrow down if it's a settings lag, permissions lag or connection latencies to the AD server. I'd bet on AD.

Thinking about it, never mind that goose chase, try pinging your AD server first and report back the latencies. I get the feeling the local PDM client service contacts the AD server too frequently. This is because our AD is set up for passwords to expire, which will happen why users are logged into the windows session and window's is fine with this, but PDM will start complaining about it; which makes me thing PDM is hitting the AD server for user credentials frequently, I wouldn't think on every get, checkout/in and right click menu, but who knows. If it does and there's some big lag such as DNS confusion or whatever then that could be causing it.

[mp3-250]

@bnemec I do not have the documentation at hand right now but it is actually how PDM works in AD: user authentication is triggered for a lot of operation inside the vault. like every time explorer is opened.
problem is AD troubleshooting is difficult for us as it is going to involve a lot of departments.

BTW is it possible to have AD login coexists with pdm login like admin?

[JSculley]

@bnemec I do not have the documentation at hand right now but it is actually how PDM works in AD: user authentication is triggered for a lot of operation inside the vault. like every time explorer is opened.
problem is AD troubleshooting is difficult for us as it is going to involve a lot of departments.

BTW is it possible to have AD login coexists with pdm login like admin?

Mixed authentication has been possible since PDM 2019.

[jcapriotti]

Mixed authentication has been possible since PDM 2019.

image.png

So much nicer now. I no longer have to get AD account created for testing and training.

[mp3-250]

I have tested adding two local users to our windows login based vault, one is a copy based on PDM admin, the other a copy of a standard engineer user.
they seem better than AD based users, but not as fast as the admin account.
why?

Same machine, same vault view, while logged with my Windows AD account I switched from one user to another testing some basic stuff.

After the login in the vault, I opened an assembly we use as benchmark and single part file with different users.

my test template is
1. PDM login
2. open SW
3. load 1 file
4. do stuff
5. close SW
6. repeat from 2. with next file
7. PDM logoff
8. repeat from 1. with another user

PDM admin account is faster at
login in the vault (10s vs up to 35s)
file open is faster (85s vs 105s)
check-in dialog is shown almost instantly or within a few sec vs a lag up to 7s for the same small part file.

TLDR performance wise PDM admin is the fastest user in our vault or the PDM overhead is too big on the other users.
And we aretalkIng about a test server with me alone without concurrent logins.

I have no previous PDM experience, so I do not know how much lag or overhead is causing on top of SW. ours is just unbearable.

I did some test (we have the checkout dialog turned off and open in read only by default as it is a peformance killer)

1. bypassing the PDM(no user logged in the vault, local vault folder reverted to normal, no pdm addin in SW), the assembly data I used above reads directly from the local cache in around 70s

2. with PDM admin logged in and the PDM addin activated inside SW the load time increase up to 85s

3. same as 2. but with a rather normal PDM user logged in via AD, the load time for the same data set (same pc same user profile same vault view) is over 103s

tested multiple times I got a repeatable load time with +/- 1s error.

[coolkv5]

Has this issue been resolved?
We upgraded from 2022 sp3 to 2023sp2.1
PDM has been slow to the point of giving up altogether.

I've tried the goengg topics on making the vault to 'yellow' to test. yellows' are faster for sure.
current workaround is to use the tree structure to browe but opening the folders are still a pain.

EDIT:
We've resolved the issue. The new pdm installation did something to the sql server credentials.
looking at the sql server event logs showed us the credential failure. Fixing that brought us back to normal.