Incredibly (astronimically) slow performance on new RC of TFS

Incredibly (astronimically) slow performance on new RC of TFS

• Shanewho

  Another customer has run into this issue as well. I'm working on a fix.

  
  

• Jordan Flink

  'TfsVersionControl.dbo.prc_QueryItemsExtendedLocal;1' on the SQL server is slow.

  Procedure details:

  VS 2k5 calls said procedure on end of solution load.

  ---

  exec prc_QueryItemsExtendedLocal

  @identityId=8,

  @workspaceName=N'KRS_ECMELAR',

  @localItem=N'C:\TFS\MyProject\MyProject-setup\',

  @deletionId=0,

  @depth=120,

  @deleted=0,

  @itemType=NULL

  ---

  Notice depth 120!

  What controls this parameter

  depth 1 takes 3 seconds, depth 2 takes 22 seconds, depth 3 takes 1:24 mins

  What does this tell me That something smells fishy with that procedure.

  I didn't tell it to get anything, I simply (try to) load the solution from disk. Is TFS doing something I am not aware of I don't have autoget on sln load enabled.

  My guess is that it's probably querying source control status on all items, and depth=120 (probably recursivity), but you know best!

  This happens on 3 machines. Excact same behaviour. All of the machines have the newest TFS RC Explorer client.

  WS info:

  Workspace: KRS_ECMELAR (ecmelar)
  Server   : http://nor-app04:8080/
   $/: C:\TFS

  What does workspace mapping have to do with a procedure that seems to take forever

  We have a huge amount of files. ( Spread over 33 projects )

  Total Files Listed:

  8718 File(s) 125,972,669 bytes

  2912 Dir(s) 7,784,726,528 bytes free

  - Thanks in advance, forgot to be polite.

  
  

• Cyber22

  Yes, we got the other customer to try DBCC FREEPROCCACHE, but it did not fix the problem. I've seen it work wonders before, but in this case it would be a waste of time :-)

  Fixing the problem requires a code patch to force SQL server to use a specific index for one of the queries.

  Our other customer had around 7,000 files in their solution and it was taking 2 minutes. Was your solution also large

  
  

• MattyP

  We will try this next week!

  Thanks so far!

  
  

• rjwelte

  Justin Pinnix wrote:

  Yes, we got the other customer to try DBCC FREEPROCCACHE, but it did not fix the problem.  I've seen it work wonders before, but in this case it would be a waste of time :-) Fixing the problem requires a code patch to force SQL server to use a specific index for one of the queries.

  Thanks for clearing that up! You saved us a lot of work!

  Justin Pinnix wrote:

  Our other customer had around 7,000 files in their solution and it was taking 2 minutes.  Was your solution also large

  8718 File(s) 125,972,669 bytes. This is all the files (including *.*scc etc)

  --

  Do you by any chance have an estimate on when this patch could be done We are kinda itching for the RC.

  
  

• Frederic Feytons

  I have edited my post while you probably were writing yours, could you look at the SQL profiler output I have updated that post with

  Thanks in advance!

  
  

• Rushi Desai

  Allright!
  
  Did you tell that other customer to try "DBCC FREEPROCCACHE" etc
  If so, we don't have to test that. I am waiting for Mr. Teinum to get back on Wednesday.
  
  Knowing if your instructions did / did not work on the other customer would save us a lot of work!
  

  You wrote: How many rows does the @depth=120 query that you posted return

  Answer: It took so long that we didn't have time to wait for it. So we never managed to easure it.
  
  Thanks in advance!

  
  

  
  

• SValentine

  What does workspace mapping have to do with a procedure that seems to take forever

  It's the main variable in the performance of operations like Get and Status that use QueryItemsExtendedLocal. If you were mapping (or cloaking) folders with several million items underneath then you'd want to adjust them to a scope that wasn't so broad.

  I didn't tell it to get anything, I simply (try to) load the solution from disk. Is TFS doing something I am not aware of I don't have autoget on sln load enabled.

  It still has to query the status of each item so it can display the correct glyphs and context-menu options in Solution Explorer.

  Needless to say you shouldn't see this problem with only ~10k files in your database. I'll see if someone is available to debug the problem in more detail.

  
  

• pure159112

  Emil,

  I checked in the fix for this on Tuesday. It will be part of the RTM release. Unfortunately, there's not a way I can get you a fix before that time and I don't know of a workaround other than making your solutions smaller. I apologize.

  I would like to thank you for reporting this. Without your report, we probably would not have gotten this fixed for RTM.

  Thanks,

  Justin Pinnix

  SDE - Version Control Server

  
  

• Leif J

  What exactly is going on here We are seeing the same thing. We have a solution with 37 projects, 196 dirs and ~1500 files and it now takes 3-5 minutes to load. What is very weird is that I have a branch of the solution that loads in ~35 seconds which is how long the original solution took under B3R. This particular issue is making us more than a bit nervous.
  

• chris olsen

  The depth of 120 is full recursion on C:\TFS\MyProject\MyProject-setup\. Depth of 1 is one-level recursion (e.g., equivalent to the wildcard '*').

  I'll forward this on to someone familiar with that procedure.

  How much memory does the data tier have and does it appear to be running out when processing that query

  Buck

  
  
  

• Greg Post-Graduate Eco Student

  Hi. I will try to answer your question.

  First, here is the HW Spec:

  
  1. hardware application tier

  FUJITSU SIEMENS
  PRIMERGY BX600

  Intel(R) Xeon(TM) CPU 3.06GHz (x2)
  2GB RAM

  
  http://www.fujitsu-siemens.com/products/standard_servers/blade/primergy_bx_600.html

  
  2. hardware data tier

  Dell Computer Corporation
  PowerEdge 4400

  Intel(R) Pentium(R) III Xeon 927MHz

  How much memory does the data tier have

  3.68 GB RAM

  and does it appear to be running out when processing that query

  Its only the CPU that peeks (99%) - The sql server process uses around 80-100MB. There is not set any upper limit on the memory usage for the SQL server instance.

  Second. When we started with B3R we did some test migration of code from VSS to TFSC. That code was later deleted and we did a final import on top of this. So, we might have many deleted files in the team project.

  We have a changeset that is the current baseline, so all code that is belonging to changesets before that is not / will never be used.

  The procedure prc_QueryItemsExtendedLocal in B3R have no problem with this, it seams like this is a RC problem.

  There is another solution below the same root that has no problem of showing up in Solution Explorer (TFS-RC).

  Our TfsVersionDatabase have the following size:

  data: 971 MB
  Log: 21 116 MB

  Rows in all tables:

  dbo.tbl_Annotation 6
  dbo.tbl_ChangeSet 1081
  dbo.tbl_Conflict 4
  dbo.tbl_Content 40113
  dbo.tbl_Counter 2
  dbo.tbl_Extension 145
  dbo.tbl_File 39879
  dbo.tbl_FileType 24
  dbo.tbl_Identity 57
  dbo.tbl_Label 12
  dbo.tbl_LabelEntry 12
  dbo.tbl_LocalVersion 437620
  dbo.tbl_Lock 63
  dbo.tbl_Merges 5
  dbo.tbl_Namespace 42032
  dbo.tbl_PendingChange 66
  dbo.tbl_PendingDelta 0
  dbo.tbl_PendingMerge 0
  dbo.tbl_Permission 55
  dbo.tbl_PolicyFailure 0
  dbo.tbl_PolicyOverride 0
  dbo.tbl_ReleaseNote 1008
  dbo.tbl_ReleaseNoteDefinition 13
  dbo.tbl_ReleaseNoteDetails 3905
  dbo.tbl_Repository 1
  dbo.tbl_RevertTo 134
  dbo.tbl_tmpAssociatedItem 0
  dbo.tbl_tmpContent 0
  dbo.tbl_tmpExtension 0
  dbo.tbl_tmpFieldValue 0
  dbo.tbl_tmpFileType 0
  dbo.tbl_tmpItem 19550
  dbo.tbl_tmpLink 0
  dbo.tbl_tmpLocalVersion 0
  dbo.tbl_tmpPendingMerge 19544
  dbo.tbl_tmpPermission 0
  dbo.tbl_tmpPolicyFailure 0
  dbo.tbl_tmpReleaseNoteDefinition 0
  dbo.tbl_tmpServerItem 0
  dbo.tbl_tmpWorkingFolder 0
  dbo.tbl_Version 75744
  dbo.tbl_VersionedItem 54198
  dbo.tbl_WorkingFolder 46
  dbo.tbl_Workspace 48
  dbo.tbl_WorkspaceLink 0

  ---

  select count(*) from tbl_Version returns 75744

  select fullpath, count(*) from tbl_Version group by fullpath

  returns 14505 rows.

  ---

  I had to uninstall the RC and reinstall B3R (backup is a nice thing :). I will soon setup a new environment so i can test the RC before we hand it out the the developers. If needed I can provide a backup of the TfsVersionControl database (i will zap the tbl_Content.Content column and shrink it).

  
  Regards
  Morten Teinum
  

  
  

• Freeweight

  Which operation is slow   If I understand correctly, it's Get  

  If so: can you paste in your workspace mappings (the output of 'tf workfold') and indicate approximately how many files are underneath each mapping

  
  

• enetter

  There are a couple of more things you can try.

  First of all, run the "DBCC FREEPROCCACHE" command from SQL Management Studio. This will cause all of your stored procedures to be recompiled with up to date statistics.

  If you're still having problems, I would suspect that your indexes are not set up correctly. What are the defined indexes on tbl_Version, tbl_LocalVersion, and tbl_PendingChange

  Since you seem to be well versed in SQL, you could also try executing the sproc directly from SQL management studio an preceeding it with "SET STATISTICS IO ON". That should tell us which table is getting the massive number of reads.

  How many rows does the @depth=120 query that you posted return

  Thanks,

  Justin Pinnix

  SDE - Version Control Server Team

  
  
• Incredibly (astronimically) slow performance on new RC of TFS