Hi Jim, looking through the Knowledge Base, it looks like this was a known issue in EPDM 2011 and 2012, but was marked as fixed in 2013 - see SPR 629573
If you're still experiencing this behaviour, I would suggest raising a case with your VAR, I would imagine they have some more information or can raise it with SW.
Is your SQL DB server virtual? If so, I have one client with daily versions and transitions have created hundreds of their core files with 250+ versions and their vault was slow!
Are you experiencing head blocks when that file's contains tab is activated or if a transition dialog has to build the reference tree? If you were to trace it, the offending SP is probably dbo.XRef_GetTree. Check out SPR#792546, 788790, 802067, 901322, and 758929 We found this SP to be the root symptom while searching for the root cause. At one point we tried a un-encrypted version of that SP provided by SW and it helped but not significantly.
We researched for about a month and finally talked with my VM guru guy who asked the client a few questions as he had encountered this before.
"How many hypervisors are on the cluster? Is the SQL DB HV in a cluster of mutliple VMs? If so, the DB may be ready to run but can't get processor time so your VM guy can set up a configuration in the cluster to migrate the other workloads off that host for 24 hours to see if there is sufficient improvement before committing those changes. It's a delicate balancing act but should work."
Basically SQL DB is in a ready state ready to run but it is always fighting for processor time on a cluster where other servers are getting all the processor time and when there is a huge query hitting the DB by way of the XRef_GetTree.
LOL I'm totally in over my head here. Charley Saint can probably shed some light on what I'm trying to articulate. My IT resource guy showed my client how to fix it and they ran a test for a 24 hour period and it worked but I don't know how it is doing today.
Hope this helps,
I've fought with XRef_GetTree more than I care to admit, there are a lot of variables that get blended into that SP and nearly everything in EPDM relies on it. Processor latency typically affects smaller jobs that run a lot more than longer running ones. Think of it this way, if you have to wait 15ms each time for a task that runs in 5ms then it'll run 300% slower, but 15ms on a 150ms job will only run 10% slower. I often run test db's with some unencrypted SP's because it unlocks a bunch of performance monitoring tools, specifically the Database Engine Tuning Advisor.