General Guidelines of troubleshooting performance issues in Team Foundation Server


I think this is worth sharing for those of you who are handling performance issues. Some things to check for that I collated with SQL/ TFS Product team while troubleshooting performance issues.

 –  Where any updates installed on the machine (check the Control Panel -> Updates)

– Any changes in the storage where the SQL files are (DATA, LOG, TEMPDBs)?

–  How many tempdb files do customers have? Make sure they have exactly same number of TEMPDB data files and that the files are of the same size and there’s enough free space in the drive where the TEMPDB files are

Some data to collect:

(a)Long running SQL operations. On the TFS Config DB run

 

DECLARE @Events TABLE(

Session_Id SMALLINT,

EventType NVARCHAR(30) NULL,

Parameters INT NULL,

EventInfo NVARCHAR(255) NULL

)

 

DECLARE @Inputbuffer
TABLE (

EventType NVARCHAR(30) NULL,

Parameters INT
NULL,

EventInfo NVARCHAR(255) NULL

)

 

DECLARE ibcursor
CURSOR

   FOR

   select session_id from sys.dm_exec_requests

OPEN ibcursor;

DECLARE @sid
sysname;

FETCH NEXT
FROM ibcursor INTO @sid;

WHILE (@@FETCH_STATUS <>
-1)

BEGIN;

      
DELETE FROM @Inputbuffer

   INSERT INTO @Inputbuffer

   EXEC (‘dbcc inputbuffer(‘ + @sid + ‘) WITH NO_INFOMSGS’)

  

   INSERT INTO @Events

   SELECT @sid, * FROM @InputBuffer

 

   FETCH NEXT FROM ibcursor INTO @sid;

END;

CLOSE ibcursor;

DEALLOCATE ibcursor;

 

SELECT  e.EventInfo,

             
a.session_id,

        datediff(ss, a.Start_Time, getdate()) as Seconds,

       
a.total_elapsed_time /
1000.0 as ElapsedTime,

       
m.requested_memory_kb,

       
m.max_used_memory_kb,

       
m.dop,

       
a.command,

       
d.Name AS DBName,        

       
a.blocking_session_id,

        a.wait_type,

        a.wait_time,

       
a.last_wait_type,

       
a.wait_resource,

       
a.reads,

       
a.writes,

       
a.logical_reads,

       
a.cpu_time,

       
a.granted_query_memory

             
, convert(xml, c.query_plan)

FROM    sys.dm_exec_requests a with (nolock)

JOIN    @Events e

ON           
e.Session_Id = a.session_id

OUTER APPLY
sys.dm_exec_sql_text(a.sql_handle) b 

OUTER APPLY
sys.dm_exec_text_query_plan (a.plan_handle, a.statement_start_offset, a.statement_end_offset) c

LEFT JOIN
sys.dm_exec_query_memory_grants m (nolock)

ON      m.session_id = a.session_id

       
AND m.request_id = a.request_id

JOIN    sys.databases d

ON      d.database_id = a.database_id

WHERE   a.session_id > 50

       
and a.session_id <> @@spid

             
and e.EventType = ‘RPC Event’

ORDER BY
a.Start_Time 

–         

 

(b)TFS command duration. Run this against the config DB and each of the collection DBs (replace the highlighted below with the DB names)

declare @startDate DATETIME = DATEADD(hour,-24,GETUTCDATE())

declare @endDate DATETIME = DATEADD(minute,-1,GETUTCDATE())

 

SELECT Command, Sum(ExecutionCount) as CommandCount,

sum(ExecutionTime)/1000 as TotalExecTimeInMS,

sum(ExecutionTime)/Sum(ExecutionCount)/1000 as ExecTimeInMSPerCmd

  FROM [<db>].[dbo].[tbl_Command] (nolock)    where starttime > @startDate and startTime <= @endDate

   GROUP BY Application, Command

   ORDER BY Command desc

 What TFS jobs are executing? (replace the highlighted with the name of one of the collection DBs)

             
SELECT T1.JobCount,

             
case T1.JobState

             
when -1 THEN ‘Dormant’

             
when 0 THEN ‘QueuedScheduled’

             
when 1 THEN ‘Running’

             
when 2 THEN ‘Paused’

             
when 3 THEN ‘Pausing’

             
when 4 THEN ‘Resuming’

             
when 5 THEN ‘Stopping’

             
end as JobState,          

             
T2.JobName, T2.JobId, T1.Priority

             
from

      
(SELECT COUNT(JQ.JobId) as JobCount, JQ.JobId, JQ.JobState, JQ.Priority

  FROM Tfs_Configuration.[dbo].[tbl_JobQueue] JQ (nolock)

  group by JQ.JobId, JQ.JobState,JQ.Priority) as T1

   join (select JD.JobId, JD.JobName from Tfs_Configuration.[dbo].[tbl_JobDefinition] JD (nolock) group by JD.JobId, JD.JobName) as T2   on T1.JobId = T2.JobId

 

             
SELECT T1.JobCount,

             
case T1.JobState

             
when -1 THEN ‘Dormant’

             
when 0 THEN ‘QueuedScheduled’

             
when 1 THEN ‘Running’

             
when 2 THEN ‘Paused’

             
when 3 THEN ‘Pausing’

             
when 4 THEN ‘Resuming’

             
when 5 THEN ‘Stopping’

             
end as JobState,          

             
T2.JobName, T2.JobId, T1.Priority  from (SELECT COUNT(JQ.JobId) as JobCount, JQ.JobId, JQ.JobState, JQ.Priority

  FROM Tfs_Configuration.[dbo].[tbl_JobQueue] JQ (nolock)   group by JQ.JobId, JQ.JobState,JQ.Priority) as T1 left join (select JD.JobId, JD.JobName from <collectionDB>.[dbo].[tbl_JobDefinition] JD (nolock) group by JD.JobId, JD.JobName) as T2

  on T1.JobId = T2.JobId   order by JobState DESC

 –         
Mitigation

Disable reporting ->  http://blogs.msdn.com/b/tfssetup/archive/2013/08/06/how-to-disable-a-tfs-warehouse-or-analysis-job.aspx

Note that it might be necessary to rebuild the warehouse and cube so do not do this if you are not ok with rebuilding

 Written by:Nitish Nagpal, Support Escalation Engineer


Comments (0)

Skip to main content