Dissecting the Visual Studio Load Test Results Database (Part 3–Excel Reporting)

Article
11/13/2015

In Part 3, we look at the sprocs and calls used to build reports in Excel.

The Excel Reporting Feature

In the 2010 release of Visual Studio Ultimate, a new feature was added that allows you to use Microsoft Excel to create either a Run Comparison or Trend Analysis report. You can read the standard documentation to learn more about how to use the feature.

The feature uses Office COM Interop to interact with Excel by walking through a wizard, asking questions about how to report, letting you choose which perfmon counters to display, then creating different worksheets for each category chosen.

Caveats

The reporting add-in has a few noteable limitations:

You can only select runs that were all created with the same loadtest name.
Once a report is created, you cannot change the inputs to it. You will need to create a new instance of the report to modify it (NOTE: You can use the existing report as a template to make creating the new report faster).
The templates that get created save the data categories to collect, but do NOT save any EXCEL formatting.
You cannot choose the time range(s) for the reports.
You must have access to Excel 2010 or newer, and it must be installed on the computer where you are viewing the test results.

Examples

The next section shows an example of the SQL calls made from Excel for each of the report types. I selected a bare minimum of counters to use for each report.

Building a Run Comparison Report

When you select the “Open and Manage Results” dialog box, Visual Studio queries the LoadTestRun table and retrieves a list of available runs to choose from:

Query: SELECT Name FROM sys.Databases ORDER BY 1 ASC

Sproc: exec prc_QueryLoadTestReports

Sproc: exec prc_FindLoadTestReport @reportId=-1,@name=N'LOADTEST_RUNCOMPARISON_REPORT_DEFAULT'

Query: SELECT DISTINCT LoadTestName FROM LoadTestRun ORDER BY 1 ASC

Sproc: exec sp_executesql N'SELECT COUNT(*) FROM LoadTestReport WHERE Name = @ReportName',N'@ReportName nvarchar(16)',@ReportName=N'Some Report Name'

Sproc: exec prc_QueryLoadTestRuns @loadTestName=N'LoadTest1'

Sproc: exec prc_QueryPossibleCountersForReport @runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_CreateLoadTestReport @name=N'Some Report Name',@reportType=2,@description=N'',@loadTestName=N'LoadTest1', @lastModifiedBy=N'NORTHAMERICA\geoffgr',@lastRunId=2172, @selectNewReports=1,@runsXml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2158" /><r i="2157" /></runs>',@pageXml=N'<?xml version="1.0" encoding="utf-16"?><pages><p i="0" c="LoadTest:Page" t="Avg. Page Time" />...[truncated]...</pages>'

Sproc: exec Prc_QueryLoadTestTestComparison @Baseline=2157,@ComparisonRun=2158

Sproc: exec Prc_QueryLoadTestPageComparison @Baseline=2157,@ComparisonRun=2158

Sproc: exec Prc_QueryLoadTestTransactionComparison @Baseline=2157,@ComparisonRun=2158

Sproc: exec prc_QueryForMachinesInRun @Baseline=2157,@ComparisonRun=2158

Sproc: exec Prc_QueryComputedCounterComparison @ReportId=4,@Baseline=2157,@ComparisonRun=2158

Sproc: exec Prc_QueryLoadTestErrorComparison @Baseline=2157,@ComparisonRun=2158

Building a Trend Report

Once you select a run and open it, there are a number of queries and Stored Procs that get called, filling DataSets in Visual Studio and allowing you to look at different graphs and tables. The bulk of core data is grabbed immediately and there are only a few instances where more trips to SQL will occur. Below is a list SQL calls made by selecting to open a single run. A few of the longer queries were truncated to save room on this post. You can run a SQL Profiler trace and capture all of the text yourself if you need to dig in deeper.:

Query: SELECT Name FROM sys.Databases ORDER BY 1 ASC

Sproc: exec prc_QueryLoadTestReports

Sproc: exec prc_FindLoadTestReport @reportId=-1,@name=N'LOADTEST_TREND_REPORT_DEFAULT'

Query: SELECT DISTINCT LoadTestName FROM LoadTestRun ORDER BY 1 ASC

Sproc: exec sp_executesql N'SELECT COUNT(*) FROM LoadTestReport WHERE Name = @ReportName',N'@ReportName nvarchar(5)',@ReportName=N'Trend'

Sproc: exec prc_QueryLoadTestRuns @loadTestName=N'00_LoadTest1'

Sproc: exec sp_executesql N'SELECT COUNT(*) FROM LoadTestReport WHERE Name = @ReportName',N'@ReportName nvarchar(5)',@ReportName=N'Trend'

Sproc: exec prc_QueryLoadTestRuns @loadTestName=N'LoadTest1'

Sproc: exec prc_QueryPossibleCountersForReport @runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_CreateLoadTestReport @name=N'Trend',@reportType=1,@description=N'',@loadTestName=N'LoadTest1', @lastModifiedBy=N'NORTHAMERICA\geoffgr',@lastRunId=2172, @selectNewReports=1,@runsXml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>',@pageXml=N'<?xml version="1.0" encoding="utf-16"?><pages><p i="0" c="LoadTest:Page" t="Avg. Page Time" /><p i="0" c="LoadTest:Page" t="Pages/Sec" />...[truncated]...</pages>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Page',@counterName=N'Avg. Page Time',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Page',@counterName=N'Pages/Sec',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Request',@counterName=N'Requests/Sec', @runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Scenario',@counterName=N'User Load',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Test',@counterName=N'Avg. Test Time',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Test',@counterName=N'Total Tests',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Transaction',@counterName=N'Avg. Response Time',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Transaction',@counterName=N'Total Transactions',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'LoadTest:Transaction',@counterName=N'Transactions/Sec', @runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'Memory',@counterName=N'Available MBytes',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'Network Interface',@counterName=N'Bytes Total/sec',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'PhysicalDisk',@counterName=N'% Idle Time',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'Process',@counterName=N'% Processor Time',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: exec prc_QueryForInstanceCount @categoryName=N'Processor',@counterName=N'% Processor Time',@runsxml=N'<?xml version="1.0" encoding="utf-16"?><runs><r i="2160" /><r i="2159" /><r i="2158" /><r i="2157" /></runs>'

Sproc: Prc_QueryLoadTestPageSummary "5" , "Avg. Page Time"

Sproc: Prc_QueryLoadTestPageSummary "5" , "Pages/Sec"

Sproc: Prc_QueryLoadTestRequestSummary "5" , "Requests/Sec"

Sproc: Prc_QueryLoadTestScenarioSummary "5" , "User Load"

Sproc: Prc_QueryLoadTestTestCaseSummary "5" , "Avg. Test Time"

Sproc: Prc_QueryLoadTestTestCaseSummary "5" , "Total Tests"

Sproc: Prc_QueryLoadTestTransactionSummary "5" , "Avg. Response Time"

Sproc: Prc_QueryLoadTestTransactionSummary "5" , "Total Transactions"

Sproc: Prc_QueryLoadTestTransactionSummary "5" , "Transactions/Sec"

Sproc: Prc_QueryComputedCounterSummary "5" , "Available MBytes" , "Memory"

Sproc: Prc_QueryComputedCounterSummary "5" , "Bytes Total/sec" , "Network Interface"

Sproc: Prc_QueryComputedCounterSummary "5" , "% Idle Time" , "PhysicalDisk"

Sproc: Prc_QueryComputedCounterSummary "5" , "% Processor Time" , "Process"

Sproc: Prc_QueryComputedCounterSummary "5" , "% Processor Time" , "Processor"

Next: “Scalpel Please”

In the next post, we will start our deep dive into “Slicing and Dicing” the data to show you what really happened.