Initial processing after login timeouts

[robe070]

We are experiencing frequent spikes in sql server 2005 CPU activity of 50% in total and mostly (approx 100%) on just one of the two CPUs for around 8 minutes. During this time only one process is active on SQL Server and its running:

Code: Select all

sgvault.dbo.spgetrepositorydelta;1

(I checked a couple of times from about the 5 minute mark on and each time this was running)

In SQL Server Activity Monitor this process is reported as having CPU=259858 and Physical IO = 2075.

This example is from 21 April 2011 between 7:50am and 8:11:02am (Anthony)

One example of the symptoms from the client side is:
(All VS solutions in same source tree.)
Take 1
Switched on computer on arrival at work.
Started Vault Client and did a GetLatest - OK
Started Visual Studio Solution 1 - hung. Killed that instance, restarted it - hung
Whilst previous instance hung, started a 2nd instance of VS using solution 2 - hung
Killed both VS instances and rebooted

Take 2
Started VS Solution 3 - a 3rd different solution - OK
Vault Client - OK and looked at some history
Closed VS Sln 3
Started VS Sln 1 - hung. Killed it and rebooted

Take 3
Started VS Sln 1 - OK
Closed and opened Sln 1 again - OK
Closed Sln 1, started Vault Client, started Solution 1, all OK

sgvault.log attached. You'll see many timeouts. Mostly occurring during login.

ps An observation. SQL Server seems to be using either 1 or other of the CPUs but never 2 at the same time. When one peaks at 100% the other goes down to 0 and vice versa. The result is a total CPU that is pretty consistently at 50% during this hiatus. Is there a configuration option that can get SQL Server using both at the same time? I have seen various numbers of SourceGear processes at different times so it seems that multiple actions are supported from the Vault side, though only one of those processes seems to be doing anything at any one time - most are sleeping and in general there is just 1 SourceGear process and its sleeping.

[robe070]

I can't see the attachment so I'm adding that again - zipped it up this time

[robe070]

It seems like the first login after re-starting Vault Server is taking 6 minutes or more. This is per Client. If multiple people attempt the initial; login at the same time, it takes even longer.

Also, its generally slow. Check Ins have increased from a few seconds to 30 seconds or so. This seems to be coincidental with adding a large number of files to Vault, many of which are binary files Yes I know its a bad idea, but the owner is adamant they are needed. Please give me some ammo to get these binary files out of Vault, if indeed they may be the cause.

[robe070]

Some more statistics are attached to this post - formatting is retained:

Repository Statistics.txt

(416 Bytes) Downloaded 323 times

The last Repository - VLF - has grown to this enormous size in the last 2 months, coincident with the slowdown in performance of other repositories. e.g. Check In. Maybe this is a separate issue to the login problem? VLF is the repository with many binary files - ppt, gif, zip, dll, exe and pdb. What kind of percentage I've been unable to determine, but eyeballing it, it may be 90% of the files.

[robe070]

Another possible cause...
At the end of last week we started using Test Complete with Vault. This seems to be coincident with the login issue. The Test Complete interface to Vault is named "VS .Net 2003". I therefore presume its a classic client interface. Do you know if this is the case? If it is the Classic Client, what effect may it have on the performance of other users of Vault?

[Beth]

As I'm going through the information here, the first thing that is going to help you is to turn off the option in the client called "Request database delta on repository cache miss." That is found in the Vault Tools - Options - Network Settings.

After changing that setting, let me know what performance issues remain.

For your SQL Server, are you performing regular maintenance on the databases? A KB article that lists our recommendations can be found here: http://support.sourcegear.com/viewtopic.php?t=2924.