Serious performance issues

tafountain · Post by **tafountain** » Thu Jun 08, 2006 7:41 am

We have been experiencing performance issues with Vault since we implemented it two weeks ago. Most of them we have resolved by addressing issues in the backend database by defragmenting the entire sgvault database and then breaking the data file and the log file onto two separate drives. This was Tuesday night these tasks where performed and yesterday Vault appeared to run like a charm. However, today everyone is having issues connecting again. This time everyone gets hung at the point the Vault client displays "Retrieving repository structure information from the server...". If I look at the backend, all connections are executing the procedure spgetrepositorydelta and just hanging. The health and defragmentation of the database appear to be fine at this point. Another point of interest, the performance meters show that the % Disk Read Time and % Disk Write Time are next to nothing on the drive with the log file (C), however the same meters are through the ceiling on the drive that contains the data file (E). The machine in question is a beefy machine, as we have other items that run on this box at well and they are all accessible. What I translate this to mean is there is something only affecting the performance of Vault, but everything else on this box runs fine.

Please advise.

Post by **jclausius** » Thu Jun 08, 2006 8:16 am

spgetrepositorydelta is one of those areas that shouldn't be firing too often. There are plans to optimize this for a future release, but unfortunately that doesn't help you in the present day.

In order to make a helpful suggestion, we're going to need to get some background information:

On average, how many change sets are comitted within an hour? 10? 100? 1000?
How much memory is in the machine running the Vault Server?
Are Vault Server and SQL Server on the same machine?
How many objects (files and folders) would you guess are in your largest repository?
How many repositories are hosted by the Vault Server?

We'll need to do is take a look at the <TreeManagerSize> xml element in the Vault Server's vault.config file. The default setting is -1, which computes a number based on the amount of memory within your Vault Server. We'll change that to a positive number for caching trees, thus reducing calls to spgetrepositorydelta.

But, before we make the change, we'll need the information to come up with a tree cache size that doesn't cause your Vault server to consume too much memory.

tafountain · Post by **tafountain** » Thu Jun 08, 2006 8:30 am

jclausius wrote:spgetrepositorydelta is one of those areas that shouldn't be firing too often. There are plans to optimize this for a future release, but unfortunately that doesn't help you in the present day.

In order to make a helpful suggestion, we're going to need to get some background information:
On average, how many change sets are comitted within an hour? 10? 100? 1000?

How much memory is in the machine running the Vault Server?

Are Vault Server and SQL Server on the same machine?
How many objects (files and folders) would you guess are in your largest repository?

How many repositories are hosted by the Vault Server?
We'll need to do is take a look at the <TreeManagerSize> xml element in the Vault Server's vault.config file. The default setting is -1, which computes a number based on the amount of memory within your Vault Server. We'll change that to a positive number for caching trees, thus reducing calls to spgetrepositorydelta.

But, before we make the change, we'll need the information to come up with a tree cache size that doesn't cause your Vault server to consume too much memory.

On average, how many change sets are comitted within an hour? 10? 100? 1000?
Not sure, we just started using Vault. We have 3 teams in my office, and we have 3 teams located in India. The changes can vary day to day. I'll throw a guess and say approximately 100 on a busy day.
How much memory is in the machine running the Vault Server?
1 GB. We are increasing it to 2 GB either tomorrow or Monday.
Are Vault Server and SQL Server on the same machine?
Yes.
How many objects (files and folders) would you guess are in your largest repository?
1,500 (+/-)
How many repositories are hosted by the Vault Server?
One. This repository contains approximately 40,000 files and 2,900 folders.

Post by **jclausius** » Thu Jun 08, 2006 8:56 am

I need to verify one other thing before we get started. Can you examine your Vault Server Log file? We're looking for restarts. The Vault Server relies on caching of trees for performance. If for some reason there are numerous restarts (which will be logged within the file), then that is the problem that we must solve.

Assuming the Vault server is not restarting, let's move ahead with the tree manager size change.

1) Modify the Vault Server's vault.config file. Set the xml element, TreeManagerSize to 200.

2) Restart the Vault server - (iisreset.exe)

Keep an eye on the memory of the Vault Server. If there are too many trees cached, you may run out of memory condition or create thrashing for virtual memory. In that case, we'll need to decrease the tree manager size a bit.

After the change, everyone will hit spgetrepositorydelta the first time, and then later refreshes should be handled by the Vault Server's cache.

tafountain · Post by **tafountain** » Thu Jun 08, 2006 10:48 am

I will be doing this shortly. However, a quick note, I found a ton of errors in my event log for the same error (just for different @objverid values):

Code: Select all

Error in Parsing Event:
exec dbo.spgetdeltachainforfullfile @objverid = 1979512.

Does this have any impact on the change you suggested or does this shed more light on another problem perhaps?

On another note, what is the impact of breaking the repository into multiple repositories. We can break out the 1 into at least 3, maybe 4 as it stands today. Will having smaller repositories result in better performance even though they all share the same database?

tafountain · Post by **tafountain** » Thu Jun 08, 2006 11:11 am

One thought came to mind. Will this fix work for all the clients that access the Vault repository? What I mean is, all of our developers use the Vault client but we have 2 sets of developers - Delphi and .NET developers. The Delphi developers use a product called SourceConneXions to work with Vault from within the Delphi IDE, while the .NET developers use the VS .NET IDE addin you supply.

Post by **jclausius** » Thu Jun 08, 2006 11:11 am

This is different from spgetrepositorydelta, but does indicate something is definitely wrong.

I did a search on "Error in Parsing Event". I could only find one entry from google, and the solution there was to re-install SQL Server on another machine.

Also, is it possible when you defragged the indexes, people were still using Vault? Do the log file entries coincide with your index changes? If the database was rebuilding indices, I could see that spgetdeltachainforfullfile would be affected.

tafountain · Post by **tafountain** » Thu Jun 08, 2006 11:33 am

jclausius wrote:This is different from spgetrepositorydelta, but does indicate something is definitely wrong.

I did a search on "Error in Parsing Event". I could only find one entry from google, and the solution there was to re-install SQL Server on another machine.

Also, is it possible when you defragged the indexes, people were still using Vault? Do the log file entries coincide with your index changes? If the database was rebuilding indices, I could see that spgetdeltachainforfullfile would be affected.

After futher reviewing, this appears to be unrelated to Vault. Other queries from other systems had the same error reported.

However, any chance you reviewed the last two questions regarding (1) will breaking the repository into smaller repositories help performance even though they share the same database and (2) will the TreeManagerSize value I changed be taken advantage of by SourceConneXions and the VS .NET IDE? So far, the Vault client is now opening lightening fast, however the .NET IDE still takes 45 seconds to 2 minutes to load. I have yet to validate the Delphi IDE as they are all out to lunch.

Post by **jclausius** » Thu Jun 08, 2006 1:03 pm

tafountain wrote:(1) will breaking the repository into smaller repositories help performance even though they share the same database

Yes, when changes are made within one repository, there are concurrency issues within the tree. If you spread the development of isolated projects into different repositories, then things will definitely speed up.

Be careful with this, as there are restrictions this may place on other types of Vault clients. For instance, the VS.Net Vault IDE client requires that all projects and the solution exist within the same repository.

tafountain wrote:(2) will the TreeManagerSize value I changed be taken advantage of by SourceConneXions and the VS .NET IDE?

This will work for ALL clients.

tafountain wrote:however the .NET IDE still takes 45 seconds to 2 minutes to load.

So the standalone Vault GUI client operates just fine against the same repository?

tafountain · Post by **tafountain** » Thu Jun 08, 2006 1:19 pm

jclausius wrote:
tafountain wrote:(1) will breaking the repository into smaller repositories help performance even though they share the same database
Yes, when changes are made within one repository, there are concurrency issues within the tree. If you spread the development of isolated projects into different repositories, then things will definitely speed up.

Be careful with this, as there are restrictions this may place on other types of Vault clients. For instance, the VS.Net Vault IDE client requires that all projects and the solution exist within the same repository.

tafountain wrote:(2) will the TreeManagerSize value I changed be taken advantage of by SourceConneXions and the VS .NET IDE?
This will work for ALL clients.

tafountain wrote:however the .NET IDE still takes 45 seconds to 2 minutes to load.
So the standalone Vault GUI client operates just fine against the same repository?

This last one might have been a fluke. Other developers are noticing a considerable improvement loading projects through the IDE in both .NET and Delphi. The developer that was having this issue is no longer experiencing it.