TFS Performance Issues–Useful SQL Queries


tags: , , , , ,
no comments

Today one of my customers called and asked how to identify what causing slowness in TFS.

Well, you should start with the basics, check TFS Application Tier and make sure CPU, Disk Usage and Network are in the normal levels.

Once you’re sure the IIS (App Tier) is working we move to TFS DB.

The first query you should run (Using SSMS) is the following:

1 SELECT top 20 SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1, 2 ( ( CASE statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE s.statement_end_offset END - s.statement_start_offset ) / 2 ) + 1) AS statement_text, 3 text, objtype, cacheobjtype, usecounts, last_execution_time, total_worker_time, total_worker_time / execution_count AS [Avg CPU Time], execution_count , 4 qp.query_plan FROM sys.dm_exec_query_stats AS s 5 inner join sys.dm_exec_cached_plans cp on s.plan_handle = cp.plan_handle 6 CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t 7 CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp order by total_worker_time desc

This query will give you the heaviest queries, you can see the worker time and CPU time – very BIG!

Looking at the text field I can assume this huge query was executed from SSRS (Reporting Services)


So, How to identify which Report is responsible for that query?

It’s pretty simple, all the reports are saved in RDL files (XML) so you can open one by one and check if the report is running the above query or you can use the following query to extract all RDL files – especially the Content, which contains the query text itself.

1 USE ReportServer 2 select 3 convert(varchar(max), 4 convert(varbinary(max), Content)) 5 from catalog 6 where Content is not null

Export the data to text editor and look the the query we found above (just part of the query)


If you get a hit then you should be able to see the Report Name, now you can go and refactor, in my case I couldn’t find this query in the RDL’s content…

So I want to know who is running this query and stop it!

Run the following query to get more details about who and where each query is “Currently Running

1 SELECT s.session_id 2 ,r.STATUS 3 ,r.blocking_session_id 'blocked by' 4 ,r.wait_type 5 ,wait_resource 6 ,r.wait_time / (1000.0) 'Wait Time (in Sec)' 7 ,r.cpu_time 8 ,r.logical_reads 9 ,r.reads 10 ,r.writes 11 ,r.total_elapsed_time / (1000.0) 'Elapsed Time (in Sec)' 12 ,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, ( 13 ( 14 CASE r.statement_end_offset 15 WHEN - 1 16 THEN Datalength(st.TEXT) 17 ELSE r.statement_end_offset 18 END - r.statement_start_offset 19 ) / 2 20 ) + 1) AS statement_text 21 ,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + 22 Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text 23 ,r.command 24 ,s.login_name 25 ,s.host_name 26 ,s.program_name 27 ,s.host_process_id 28 ,s.last_request_end_time 29 ,s.login_time 30 ,r.open_transaction_count 31 FROM sys.dm_exec_sessions AS s 32 INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id 33 CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st 34 WHERE r.session_id != @@SPID 35 ORDER BY r.cpu_time DESC 36 ,r.STATUS 37 ,r.blocking_session_id 38 ,s.session_id

After I ran the query I saw the query I was looking for and that it belongs to Analysis Services and not SSRS. (The original Elapsed Time was 3 hours)



In case you want to Kill the process to release resources just write the following command:

1 KILL [Session ID]

So, for now I changes the OLAP refresh frequency to 6 hours instead of 2 hours, our DB is 800GB and processing OLAP takes time and causing TFS DB to spend many resources on that.

To change the refresh frequency:

1. Open the link on Application tier server. This will open WarehouseControlWebService page


2. From the WarehouseControlWebService page, click ChangeSetting

3. In the SettingID box, type IncrementalProcessIntervalSeconds

4. In the NewValue box, type 21600 (6 hours) and click Invoke


Add comment
facebook linkedin twitter email

Leave a Reply