Vault startup suddenly very slow

Jerry R · Post by **Jerry R** » Wed Aug 04, 2004 12:08 pm

Today, all of a sudden, it's taking Vault between 1 and 2 minutes to start up (time between repository selection and when Vault window displays folder tree and files). It is doing this both for myself and other users. Yesterday, late in the day, I added a user in the Vault administrator with Read-only access to the main repository and granted him r/w access to two folders within the repository. Prior to that, all users had had r/w access to the whole repository. I tried removing the individual folder access but the problem remained.

Looking at the vault log, it seems like the delay occurs between a "Getting list of checkout changes" and a GetCheckOutListChanges returned: Success". Here is a section of the log for when I started up an instance of the vault client:

----04-Aug-04 13:55:46 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
Login
----04-Aug-04 13:55:46 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetUserList returned: Success
----04-Aug-04 13:55:46 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetUserOptions returned: Success
----04-Aug-04 13:55:46 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetRepositories returned: Success
----04-Aug-04 13:55:46 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
Getting repository Structure.
----04-Aug-04 13:55:46 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetRepositoryStructure returned: Success
----04-Aug-04 13:55:47 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
Getting list of checkout changes.
----04-Aug-04 13:57:29 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetCheckOutListChanges returned: Success
----04-Aug-04 13:57:29 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
Getting list of checkout changes.
----04-Aug-04 13:57:29 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetCheckOutListChanges returned: Success
----04-Aug-04 13:57:29 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
ListCloakedObjects returned: Success
----04-Aug-04 13:57:29 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetRepositoryOptions returned: Success
----04-Aug-04 13:57:29 jerry--rubi1j-xpg.wn.ui.net(10.1.30.52)--SSL Disabled
GetRepositories returned: Success

Any ideas?

Post by **jclausius** » Wed Aug 04, 2004 12:33 pm

Jerry:

A couple of things:

1) Are you running with folder security enabled on the repository?

2) Has it been a while since you have ran any database optimizations on the Vault database?

From your log file, GetCheckOutListChanges seems to be taking over 1.5 minutes. This problem can usually be resolved with updating the indices for some of the tables within sgvault.

If I've made the correct diagnosis, see http://support.sourcegear.com/viewtopic.php?t=1456 for more information.

Jerry R · Post by **Jerry R** » Wed Aug 04, 2004 12:51 pm

Jeff: Folder security was on for that repository. Turning it off eliminated the delay. So at least we know the cause of the problem. We do need to use the folder security though, so a solution other than disabling it is required.

The thread you linked to mentions upgrading to 2.0.4, defragging things, and rebuilding some table in the database. I don't know that we've ever run any database optimizations, where "ever" is a couple of months now (since we started using Vault). How ofter should optimizations be done (and by optimizations, do you mean those DBCC commands in the thread you linked to?)?

Btw, one more piece of data - there are only about 20 to 30 files checked out right now from the repository.

Jerry R · Post by **Jerry R** » Wed Aug 04, 2004 1:23 pm

I just talked to my IT/Database guy and I was wrong - optimizations are run weekly. He says:

Running standard SQL 2000 SP3 Maintenance Plan
* Optimizations: Reorganize data and index pages with the original amount of free space. (Once a week)
* Integrity: Check database integrity (including indexes), before each backup.
* Backup: Always

Also, specifically what tables should we run those DBCC commands on?

Jerry R · Post by **Jerry R** » Wed Aug 04, 2004 1:46 pm

One more item: I realized that we had been running with folder security even before yesterday with no problems. The only thing that changed yesterday is adding a new user with different security rights than all the other users.

Post by **jclausius** » Wed Aug 04, 2004 1:54 pm

I prefer DBCC DBREINDEX as it completely rebuilds the index. However, it is an "offline" operation, that should be done when no-one is using the system.

To be honest, looking at all of the indices would be optimal. However, for this special case, I've included a listing of tables used to get the checkout list:

tblsessionrellocks*
tblfsobjects*
tblfsobjectversions*
tbltransactions
tblfulltreerevisionfolders
tbltreerevisionfolders*
tbltreerevisionfolderdeltas*
tblsecurityassignments*
tblsecurityassignmentspaths*
tblgroupmembers
tblfslocks*
tblfolderentries*

Please give extra attention to the tables marked with asterisks.

HTH

Post by **jclausius** » Wed Aug 04, 2004 1:59 pm

Jerry R wrote:One more item: I realized that we had been running with folder security even before yesterday with no problems. The only thing that changed yesterday is adding a new user with different security rights than all the other users.

Did anyone commit any change sets to the database? If so, then it is possible that one change caused SQL Server to alter is Query Plan.

If you want to try a less aggressive approach, you could have the DB guy update the statistics on all tables:

Code: Select all

USE sgvault
GO

-- update ALL table statistics for tbl%
DECLARE @@name nvarchar(256)
DECLARE @@sql nvarchar(512)
DECLARE curtables CURSOR FOR SELECT name FROM sysobjects WHERE xtype = N'U' AND name LIKE 'tbl%'
OPEN curtables
FETCH NEXT FROM curtables INTO @@name
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT '---'
   PRINT '--- Updating statistis on: ' + @@name

   SET @@sql = N'UPDATE STATISTICS ' + @@name + N' WITH FULLSCAN'
   EXEC sp_executesql @stmt = @@sql

   -- if you want to turn auto-statistics on for the tables, 
   -- uncomment the next section
   -- SET @@sql = N'sp_autostats ' + @@name + N', ''ON'''
   -- EXEC sp_executesql @stmt = @@sql

   FETCH NEXT FROM curtables INTO @@name
END

CLOSE curtables
DEALLOCATE curtables

GO
CHECKPOINT

USE master
GO

Once stats have been updated, see if that made any changes in the refresh time.

Jerry R · Post by **Jerry R** » Wed Aug 04, 2004 2:29 pm

I don't really understand what you're talking about in re: a database Query Plan being altered, but we tried running your DB script to update the statistics on all tables, and it had no effect on the delay time.

We won't be able to try DBREINDEX on the tables until tonight when people are offline.

Post by **jclausius** » Wed Aug 04, 2004 3:12 pm

Jerry R wrote:I don't really understand what you're talking about in re: a database Query Plan being altered,...

Sometimes, an update of a table's statistics can help SQL Server choose a more optimal route when retrieving data from a table.

In regards to the DBREINDEX, I'd also suggest looking at the fragmentation of the drive where the sgvault database resides. I briefly mention this in http://support.sourcegear.com/viewtopic.php?t=1456.

There have been times where performance problems have disappeared after both a full disk defrag and a DBREINDEX.

Post by **jclausius** » Tue Aug 10, 2004 9:09 pm

Did this end up solving the problem?

Jerry R · Post by **Jerry R** » Fri Aug 13, 2004 9:19 am

We did a DBREINDEX yesterday and this morning I turned the folder security back on, and Vault doesn't seem to be running slower anymore, at least on my machine. So that seems to have done the trick.

Post by **jclausius** » Fri Aug 13, 2004 9:39 am

Thanks for the feedback. Now if we can only get that build script going...