Tool to calculate 90th percentile for common transactions on a visual studio Load test run

As a performance tester, I need to calculate 90th percentile for common transactions, from the Transaction summary of Load Test Runs

Problem Statement
In the visual studio Test transaction summary report, we have scenarios where in same transaction reported multiple times with
different response times(due to load and application behavior).
Example: Login transaction which is used as first transaction in every scenario, can report different response times at different scenarios.
In such case we usually calculate 90th percentile or AVG of common login transactions and report to developers or customer.
This will ensure consistency and provide accurate results.

Problem Solution:
Below is the generic utility, a Sql stored procedure that will automatically calculate 90th percentile for all common
transactions, on various response times(Avg/90thpercentile)

Steps to Execute the Store procedure
1. Connect to LoadTest results database. Create & Execute below Store Procedure
2. Execute below TSQL to create Stored Procedure.

Create Procedure Calc90thPercentileForCommonTransactions @loadtestid int

—- Get visual studio test results to Temptable
Select * into #TempTable
(select distinct LTC.TestCaseName,LTTSD.LoadTestRunId, WLTT.TransactionName, LTTSD.Percentile90,
OVER ( partition by WLTT.TransactionName ) as ‘CalculatedPercentile90th’
from LoadTestTransactionSummaryData LTTSD
Join WebLoadTestTransaction WLTT on LTTSD.TransactionId = WLTT.TransactionId , LoadTestCase LTC
where LTTSD.LoadTestRunId = @loadtestid
and LTTSD.LoadTestRunId = WLTT.LoadTestRunId
and LTC.TestCaseId = WLTT.TestCaseId and LTTSD.TransactionId = WLTT.TransactionId
and LTC.LoadTestRunId = @loadtestid) as result;

—- Calculate 90th percentile for commonTrasactions
AS (
SELECT TransactionName
FROM #TempTable
GROUP BY TransactionName

SELECT t.TestCaseName,t.TransactionName, t.Percentile90 as ’90thPercentileFromTestResult’,t.CalculatedPercentile90th as ’90thPercentileForCommonTransaction’,
WHEN DUP.TransactionName IS NOT NULL
THEN ‘Yes’
END AS IsCommonTransaction,
WHEN DUP.TransactionName IS NOT NULL
THEN CalculatedPercentile90th
ELSE Percentile90
END AS Consolidated90thPercentileToReport
FROM #TempTable T
LEFT JOIN DUP ON T.TransactionName = DUP.TransactionName;


3. Stored procedure execution

Exec Calc90thPercentileForCommonTransactions @loadtestid
where loadtestid is the runid of the test.
Example:  Exec Calc90thPercentileForCommonTransactions 1555

4. Below is the result of store procedure
TestCaseName: Name of the TestCase
TransactionName: Name of the Transaction
90thPercentileFromTestResult: 90thPercentile Response from Transaction summary of the Test(Additional Info for debugging purpose)
90thPercentileForCommonTransaction: Calculated 90thPercentile of all Common Transactions on the Response time. (Additional Info for debugging purpose)
IsCommonTransaction: ‘Yes’ in case if it is common transaction(Present more than once), ‘No’ in case if it is not a common Transaction(Unique)(Additional Info for debugging purpose)
Consolidate90thPercentileToReport: Final Response time to developers or customer which has 90thpercentile calculated value for all Common Transactions(Present more than once)

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.