Slow performance when DB has many objects

[wtjones]

I am working to get our team on SSC 2.0.10.4 / TFS 2010 and I am having pretty severe performance issues with our largest db of 8000+ objects. I am not linking static data yet.

It took over 20 minutes to bring up the get latest screen and the breakdown is roughly as follows:

~4 minutes "Waiting for pending operations" (moderate to high ssms.exe cpu)
~3 minutes "Registering..."
~8 minutes "Calculating changes" (low cpu)
~6 minutes (? maybe more I walked away for a while) "Accepting updates from source control" (fluctuating cpu usage from 1/4th of a core to 100%)

I watched ssms with procmon and at one point (I think registering or "calculating") it was writing many small files to \local settings\temp\Red Gate. The files were mostly 0 k and at most 18 mb. It didn't seem like it should tax the system so I am puzzled.

At first I suspected the VM client that I was using but I switched to a physical Core 2 duo 2G of ram with similar results (VM had 3). SSMS eats 800mb or so during these operations so I might slap some more in and retest.

Checking for commit takes about as long.

[wtjones]

I was thinking about ways to solve this and database level schema triggers could be a solution:[url] http://chrismay.org/CommentView,guid,a21c17f9-e200-4033-8309-75d6ebbc293a.aspx[/url]

The client could first pull all schema from db and then refresh + compare to TFS only on schema changes noted by the trigger.

The slight annoyance of requiring us to add a trigger to each db + maybe a red gate "audit db" could make the product scalable.

On a side note I noticed that someone posted a feedback item on this issue. Please vote!: http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/1591845-not-scalable?ref=title