Slow performance when DB has many objects

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace

Slow performance when DB has many objects

Postby wtjones » Tue Mar 15, 2011 7:44 pm

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
 
Posts: 14
Joined: Tue Jan 08, 2008 4:27 pm

Postby wtjones » Fri Mar 25, 2011 9:18 pm

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

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
wtjones
 
Posts: 14
Joined: Tue Jan 08, 2008 4:27 pm


Return to SQL Source Control 2

Who is online

Users browsing this forum: No registered users and 0 guests