spgetlockedfilechangeswithsecurity proc SLOOOOW

[shenderson]

At Match.com we are running on Vault 2.0.4, getting ready to upgrade to 3.0. We are having massive blocking problems with the spgetlockedfilechangeswithsecurity stored procedure. It is sometimes causing Visual Studio to hang up on a file checkin for at least 5 minutes for some users. Also, some users are reporting timeouts on their clients when doing get's and checkout/checkins. Most of the problems occur in the mornings and in the evenings when the most activity is happening on the server. The Vault server is maxed out at 100% processor and we can't do anything while these procs are causing blocking.

We are running folder level security. It is not an option to turn this off.

Any help would be appreciated and let me know if you need more info.

Shane Henderson
Technical Architect
shane.henderson@match.com

[jclausius]

Shane:

Concurrency was a major improvement in Vault 3.0.x. Assuming the "waits" you are seeing are the result of a transaction blocking other users, Vault 3.0 should solve most of these problems. Although ending two concurrent transactions must still occur in a synchronized manner.

As for the stored proc, this was drastically changed in Vault 3.0.0. However, we've been trying to improve this stored procedure for Vault 3.1, so there is still more room for improvement.