Performance problems with labelling

[joel]

We have a folder which is labelled for every build we do. Consequently, it has literally thousands of labels on it. I've noticed with Vault 3.0.2 that it now takes up to 15 minutes to label this particular folder. Is the labelling time dependent on how many labels already exist on the folder? I had thought they were supposed to be very lightweight.

Thanks,
Joel

[jclausius]

Is the labelling time dependent on how many labels already exist on the folder? I had thought they were supposed to be very lightweight.

Thanks,
Joel

Joel:

No, label time is not dependent on how many labels exist on a particular folder. However, label time will be dependent on a couple of different factors:

1) The size of folder being labeled ( file / folder node count only )
2) The general health / statistics of the database. In particular the tables required to build branches and the tables used to store label information.
3) Hardware involved.
4) The general health of the I/O subsystem where the database is located.

When describing labeling as light weight, this means the actual contents of any file within the label is never "copied". A reference to the file is stored, and that is used to retreive a label's file contents.

A couple of questions:

- Has any maintenance been run against the database to update statistics or rebuild any indices?
- Are the sgvault database files on a badly fragmented disk?
- Do you know the node count of the folder?

[jclausius]

A follow up question. What are you reporting for the following queries -

SELECT COUNT(*) FROM sgvault.dbo.tblfsobjectlabels
GO
SELECT COUNT(*) FROM sgvault.dbo.tblfsobjectlabelviews
GO

[joel]

A follow up question. What are you reporting for the following queries -

SELECT COUNT(*) FROM sgvault.dbo.tblfsobjectlabels
GO
SELECT COUNT(*) FROM sgvault.dbo.tblfsobjectlabelviews
GO

I have not done defrag or database maintenance on the server yet, but will try that this weekend. For the queries you mentioned, the results are, respectively:

14647
62707554

[jclausius]

62 million rows is a bit large, but shouldn't be too much of a problem on current hardware for just adding the data to the end of the table.

One theory about your label speeds - If your database is expanding the file to make room for the new label definition, and the database exists on a conventional I/O subsystem ( normal disk ), it is possible that disk fragmentation is one point of the bottle neck.

The other problem would be it is taking a long time to build the tree folder to create the label definition itself. Any maintenance on the database would help. Here is a small KB article related to some quick tips - Maintenance: Vault/Dragnet database(s)

[joel]

It's on a RAID5 system, so I/O shouldn't be too much of a problem. I'll try those suggestions.

However, it is odd that it takes nearly twice as long for this particular label as compared with other automated labels we do on other trees. Are labels all stored in the same table?

[jclausius]

It's on a RAID5 system, so I/O shouldn't be too much of a problem. I'll try those suggestions.

In this case, disk fragmentation shouldn't really be too much of a problem due to the striping.

However, it is odd that it takes nearly twice as long for this particular label as compared with other automated labels we do on other trees. Are labels all stored in the same table?

Yes, all labels and their definitions go into the tables I mentioned earlier. Are the sizes of the trees (node count only), roughly the same size as the automated labels?

When a label is applied, the folder structure of that label is created within the database's temp memory. This temp structure is then saved within the database to create the label's view or definition. If SQL Server's statistics are out of date, and the folder structure is tens of thousands of items big, that might also be one place causing the bottleneck - the time it takes to build the label's view.

[shodgetts]

I'm not having similar - very slow performance/access- problems with vault. The vault DB is only 500mb and appears to need some tuning of the stored procedures/indexes. I've ran the suggested 'fixes' - but the problems still persist. The box is a 3ghz xeonDP 1GB ram and raid5 disks.

Please advise what else can be done.

[jclausius]

For labelling, the size of the database is irrelevant. When you say you "ran the suggested fixes", what did that entail?

[shodgetts]

The suggested fixes relate to sql maintenance plans, eg rebuild index, defrag etc etc

Having ran the SQL profiling tool, the stored procedures are CPU bound - ie they take an excessive amount of time for the number of rows returned. Typicall, 5-10 seconds of CPU time to return 10 rows.

We had similar problem with our own sql apps that use large in-memory temporary tables.

[jclausius]

Building a "sub-tree" is CPU bound (and on items not within SQL Server's cache I/O bound). The stored procedure to build a folder structure doesn't use a cursor, but since SQL Server 2000 doesn't support recursive queries, there are many iterations used to build a folder and all of its sub entries. Due to this restriction, keeping the tables optimized is strongly suggested.

-Can you give an indication of how many nodes are being labeled? If you want to know for sure, running dbo.ufngetbranchstructure with some of the same parameters in the stored proc for labelling should give an indication of the timings.

In any case, I'll log a bug to see if there is some way to speed up building sub-folders within any given tree.