performance problems with large repository

mikep · Post by **mikep** » Wed Apr 14, 2004 7:59 am

I have a large repository that is killing the vault server when trying to check in a large amount of files. The repository contains 9069 files and 515 folders. Due to an issue we had, it was necessary to check out about half of the files so that we could overwrite them with the code from production. After the files were overwritten, we went to the pending change set, selected all the unmodified files and undid the checkout. This used up a bit of CPU but did not last that long. When we went to check in the modified files, around 200 or so files, the sql process alternates cpu usage with the Vault Client which is installed on the same machine as the server. It's been trying to checkin these files for 15 minutes or so.

I fired up a sql profile and noticed that this set of statements would consistently take about 3.5 seconds to run:

declare @P1 bit
set @P1=0
exec dbo.spgetlockedfilechangeswithsecurity @txid = 10393, @userid = 5, @repid = 1, @sessionid = N'phwgr0ezlhgt0u55alp0wj45', @lastsecuritychange = 'Apr 14 2004 1:45:29:740PM', @refreshlist = @P1 output
select @P1

Given the frequency of this request, I'm guessing that it is the cause of the SQL spikes.

Is there anything that can be done to improve the performance of this proc? I'm going to rebuild the indexes when it's done checking the files in. I'll write back if that helps at all. I attached a file showing the number of rows in each table. tblfolderentries has almost 1.3 million records.

VERSION: 2.0.1

UPDATE: rebuilding the tblfolderentries index seemed to drop the duration down to about 2.2 seconds and still consumes about the same amount of CPU usage.

Installing 2.0.2 now however it may be difficult to reproduce. It took about an hour to finish running before so I don't know if I can commit the time to try it again.

Post by **jclausius** » Wed Apr 14, 2004 8:53 am

mikep:

some quick questions:

how are you checking in these files? are you using the gui client or using the ide client within vs.net?
of the 200+ files, how many different folders/projects are they in?
can you give a layout of your folder/project layout? (eg $, $/dirA/(25 files), $/dirB/(20 files), etc.

fwiw, when refreshing your checkout list in a repository using folder security, the stored procedure - spgetlockedfilechangeswithsecurity - builds the repositories folder skeleton, and then applies rights to specified folders for that given user, and finally pushes the security rights down inherited paths.

in our testing on moderate hardware (1GHz P4 w/ 1 GB Mem) in trees with about 10,000 folders, this took on average about 2-3 seconds. since the refresh is only to be called sparingly, you should only see a minimal hit during a modification transaction.

on repositories not using folder securityspgetlockedfilechanges usually runs in about 200 ms.

answering the questions above may help us track this problem down.

mikep · Post by **mikep** » Wed Apr 14, 2004 9:12 am

how are you checking in these files? are you using the gui client or using the ide client within vs.net?

Vault client

of the 200+ files, how many different folders/projects are they in?
can you give a layout of your folder/project layout? (eg $, $/dirA/(25 files), $/dirB/(20 files), etc.

There are many different folders. It's hard to say how many, but I'd guess 60-70. I can't really provide a layout for a number of reasons.

fwiw, when refreshing your checkout list in a repository using folder security, the stored procedure - spgetlockedfilechangeswithsecurity - builds the repositories folder skeleton, and then applies rights to specified folders for that given user, and finally pushes the security rights down inherited paths.

We are using folder security. It seemed to loop over spgetlockedfilechangeswithsecurity hundreds of times. Each time taking 2-3 seconds.

in our testing on moderate hardware (1GHz P4 w/ 1 GB Mem) in trees with about 10,000 folders, this took on average about 2-3 seconds. since the refresh is only to be called sparingly, you should only see a minimal hit during a modification transaction.

We're using a dual 1GHz with 1 GB mem.

Post by **jclausius** » Wed Apr 14, 2004 9:26 am

ok.

1) what is the setting for the following vault option - Tools -> Options -> checkin -> auto commit after each operation ?

2) did you commit these in one transaction or a transaction for each item?

3)

mikep wrote: It seemed to loop over spgetlockedfilechangeswithsecurity hundreds of times

are you looking at a sql server trace? if so, can you examine the session variable and user id- are they the same session / user id combo?

the gui client is programmed to call a refresh once before the transaction, and once after the transaction. you should only see two calls for that given user during a transaction. during any transaction, there should be no calls to spgetlockedfilechangeswithsecurity for the session / user making the transaction.

Guest · Post by **Guest** » Wed Apr 14, 2004 10:29 am

1) what is the setting for the following vault option - Tools -> Options -> checkin -> auto commit after each operation ?

the box is checked

2) did you commit these in one transaction or a transaction for each item?

We highlighted all of the modified files in the pending change set and pushed the 'commit' button. One thing to note and slightly off-topic is that during the lengthy operation, the status bar said something like 'undoing selected operations' which was somewhat nerve racking.

are you looking at a sql server trace? if so, can you examine the session variable and user id- are they the same session / user id combo?

yes, they are the same

sterwill · Post by **sterwill** » Wed Apr 14, 2004 1:17 pm

The client may be undoing the checkouts of all of the "Unmodified" items in the pending change set. You can change this behaviour through the Options dialog's "Check In" section, if you'd like unmodified files to create a new version (with an empty delta, of course) when they're checked in.

A quick look at the code that performs the undo checkout in this situation shows that they're all sent to the server as one batch. Maybe the server is calling that stored procedure many times while it processes all the undo requests?

mikep · Post by **mikep** » Wed Apr 14, 2004 1:30 pm

That's an interesting point although I'm not sure it applies. First we highlighted all of the unmodified files in the pending change set and selected undo. That ran for a while, but not an unreasonable amount of time given the large numbers. After that completed, we ran the remaining files which were all modified. We did this by highlighting the files in the pending change set and clicked on the commit button. It was at this point the loop over the slow stored proc began. The profiler was not running during the undo check out as there weren't any problems until we tried checking in the modified files.

sterwill · Post by **sterwill** » Thu Apr 15, 2004 9:16 am

I found the cause. Starting with Vault 2.0.0, Modified change set items are given an extra bit of scrutiny before they're checked in. If the file on disk is the same size as it was when it was checked out, the 32-bit CRC of the file is calculated and compared with that of the baseline. If the CRCs match, the file is considered to be truly Unmodified, and the checkout is undone.

The problem is these files are undone one at a time, instead of all at once, and the check out list is refreshed after each. I've checked in a fix for this bug, which will be included in an upcoming Vault release.

One workaround is to change your Vault options to force unmodified files to be checked in, not undone. When a Modified item is recognized to be Unmodified through CRC checks, the "what to do with unmodified files" option is still consulted.