Test management warehouse: Improve deletion performance

  • Article

Last week we received a complaint from an internal customer that the test management warehouse adapter is taking a lot of time to process the data. Dhruv took a look at the problem and found that it is slow because the indexes on the test management tables (FactTestResult, DimTestResult, DimTestRun) is highly fragmented. When he rebuilt the indexes on these tables, the adapter started processing at a good speed & problem went away !!

 

Here are the steps he did to nail this down.

  • He copied this powershell script on the tfs server machine and ran following command to find out which data the test management adapter is currently processing.

PS C:\temp> .\WarehouseProgress.ps1  [:8080/tfs/https://<servername>:8080/tfs](https://%3cservername%3e:8080/tfs) /<collection name> ‘<collectiondb m/c name>;<collectiondbname> ' '<warehoused m/c name>;<warehousedbname>’

Example: - .\WarehouseProgress.ps1 https://aseembtfs:8080/tfs/DefaultCollection ‘aseembtfs;Tfs_DefaultCollection' 'aseembtfs;Tfs_Warehouse’

 

  • The script produced an output similar to the one mentioned below.

Time                    ResultsToImport PointHistoryToProcess   RunsToDelete
03:35:30.3533979        149570          139932          1629
03:38:31.8023053        149570          139932          1629
03:41:33.3410470        149570          139932          1629
03:44:34.5204298        149570          139932          1628   

Since in the last record, RunsToDelete count decreased from 1629 to 1628, Dhruv assumed that the adapter is processing test runs deletion.

 

  • Also Dhruv observed that it look at-least 9 min to process a test run deletion which is not expected. (On this setup after the fix, it processed 10 runs in 1 min)

 

  • Since test runs are processed by a stored procedure named prc_destroyResults, Dhruv checked the average execution time of that stored procedure(Tfs_Warehouse db ->  Reports -> Standard Reports -> Object Execution Statistics) and observed that it took almost 9 min to execute it. This essentially means that the one of the query inside this stored procedure is taking most of this time and he confirmed that from the individual execution time of the statements of this stored procedure using the object execution statistics.

 

  • Since none of these queries were expected to take much time, Dhruv checked the fragmentation of the indexes on FactTestResult/DimTestResult/DimTestRun. He did that using the following query.

 

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'Tfs_Warehouse'), OBJECT_ID(N'FactTestResult' ), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'Tfs_Warehouse'), OBJECT_ID(N'DimTestResult' ), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'Tfs_Warehouse'), OBJECT_ID(N'DimTestRun' ), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;

  • He observed that the fragmentation came out to be around 80%-90% which is too high and should ideally be around 10-20%.

 

 

  • He then rebuilt the indexes on these tables which brought down the fragmentation to less than 1% and after that he enabled the job back using the above asmx file.

 

And now he observed that the adapter was processing at a much better pace and the RunsToDelete count was coming down very fast.

Enjoy !!