Slow performance when DB has many objects
wtjones
Posts: 21
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.
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.
Comments
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