What size is considered 'big' for the tbl_version table in TFS_Main database

Question

We are currently experiencing significant waits in TFS database, and are trying to understand if these are as a consequence of the size of the tbl_Version version history table in the database.

Currently this table contains just over 20 million records, and is taking up approximately 6GB of storage space (total index space is just over 10GB). Looking at the queries that SQL Server is having to deal with, we have high PAGEIOLATCH_SH waits whenever this table is accessed. Obviously we don't have control over the queries being thrown at the database (all part of TFS).

Currently we have TFS on a Virtual Machine, and in essence want to get to understand whether we should (a) move to a physical machine, (b) attempt to reduce size of tbl_version or (c) follow a combination of these.

In our organisation it will be non-trivial to move to a physical server, so I'd like to get a feel for whether our table sizes are 'normal' or not before making any such decision.

Answer 1

Based on some 2007 stats from Microsoft: http://blogs.msdn.com/b/bharry/archive/2007/03/13/march-devdiv-dogfood-statistics.aspx probably not the biggest.

But MS (as documented on that blog) had done some DB tuning, this I believe is in TFS 2010, but for earlier versions you'll probably need to talk to MS direct.

Answer 2

Caveat: We're using TFS 2008.

We're currently sitting with about 9GB of data (18GB index) with 31M rows. This is after about a year and a half of usage in an IS shop with 50-60 active developers.

Part of our problem, which we still need to address, is large binaries stored in the version control system. The answer to my question here may provide some information as to whether or not there are a few major offenders that are causing the size of that table to be bigger than you want.

Answer 3

PageLatch_SH typically indicates a wait for a page to be loaded from disk to memory. From the sounds of it tbl_Version is not being kept around in memory. There are 2 things you can do to improve the situation:

a. Get more RAM (not sure how much RAM you have on the server). b. Get a faster disk subsystem.

In TFS 2010 we enable page compression if you have Enterprise Edition of SQL. This should help with the problem.