Performance issues with Vault

[rizzo]

Hello,

Recently, we've had some severe performance issues with Vault. Check-ins and Undo checkouts take really a long time (sometimes 30-40 minutes). So today I looked at the DB behind it. I obliterated deleted files and ran 'update statistics' on all tables.

However, the tblsessionrellocks table for whatever reason has over 6,000,000 rows. We don't have that large of a project. And if it means anything tblsessions has 4544 rows. The database is about 2GB and the transaction log can go from 1 MB to 2GB when the check-ins take super long.

We have several repositories, but only 2 are used.

The 1st repository information is as follows (this is from the Vault Admin too):

Folders: 142 (+ 83 deleted)
Files: 1455 (+ 568 deleted)
Tree size: 33.43 MB
Disk Space needed: 66.86 MB
Database Size: 28.6 MB
Folder Security: OFF

2nd repository info is as follows:

Folders: 95
Files: 1186
Tree size: 43.6 MB
Disk Space needed: 87.21 MB
Database Size: 14.4 MB
Folder Security: ON

On the server, we are running version 2.0.3.2174 and on clients there is a hodge-podge of 2.0.3 to 2.0.5 (i know, will upgrade to the latest asap).

So my question is whether it is normal to have such a large database? Is it normal for the transaction log to go nuts like that? Is it normal for the tblsessionrellocks tables to be so large?

Thanks.

[jclausius]

Rizzo:

A large tblsessionrellocks indicates that you have a large number of connections which are not logging off correctly. I can't remember which one, but one of the Continuous Integration tools tends not to log off. The Vault IDE client does so as well.

A startup of the Vault server will attempt to clean the files. Additionally, you can run the following SQL to achieve the same results:

Code: Select all

USE sgvault
GO

ALTER TABLE [dbo].[tblsessionlabelqueries] DROP CONSTRAINT [fk_tblsessionlabelqueries_tblsessions]
ALTER TABLE [dbo].[tblsessionrecursivelabelqueries] DROP CONSTRAINT [fk_tblsessionrecursivelabelqueries_tblsessions]
ALTER TABLE [dbo].[tblsessionrellocks] DROP CONSTRAINT [fk_tblsessionrellocks_tblsessions]


TRUNCATE TABLE [dbo].[tblsessionlabelqueries]
TRUNCATE TABLE [dbo].[tblsessionrecursivelabelqueries]
TRUNCATE TABLE [dbo].[tblsessionrellocks]
TRUNCATE TABLE [dbo].[tblsessions]

ALTER TABLE [dbo].[tblsessionrellocks] ADD CONSTRAINT [fk_tblsessionrellocks_tblsessions] FOREIGN KEY ( [sessionid] ) REFERENCES [dbo].[tblsessions] ( [sessionid] ) ON DELETE CASCADE
ALTER TABLE [dbo].[tblsessionrecursivelabelqueries] ADD CONSTRAINT [fk_tblsessionrecursivelabelqueries_tblsessions] FOREIGN KEY ( [sessionid] ) REFERENCES [dbo].[tblsessions] ( [sessionid] ) ON DELETE CASCADE
ALTER TABLE [dbo].[tblsessionlabelqueries] ADD CONSTRAINT [fk_tblsessionlabelqueries_tblsessions] FOREIGN KEY ( [sessionid] ) REFERENCES [dbo].[tblsessions] ( [sessionid] ) ON DELETE CASCADE

By default, the Vault server does not close any web sessions until after 72 hours of activity. When a client does not log out, this table, which is used to help track the checkout list, can grow in size. You may want to look at adjusting the session timeout value in web.config to a more suitable value.

Nothing solid to disclose just yet, but we've started preliminary discussions of how to address this issue in a future release.

[rizzo]

Rizzo:

A large tblsessionrellocks indicates that you have a large number of connections which are not logging off correctly. I can't remember which one, but one of the Continuous Integration tools tends not to log off. The Vault IDE client does so as well.

Thank you. You were right on target. We do use Draco.NET as our Continuous Integration tool. And running the SQL did fix up the problem.

So, as a follow-up question, is it ok to run this sql every night at 5am, say, when no one is using Vault? The reason I ask this is because we rely on Draco.NET for a lot of things and can't simply turn it off.

Thanks

[jclausius]

Yes. After you run the SQL Script, I would also recommend recycling IIS (iisreset) just to make sure any "logged in users" are kicked out of Vault.