Dissecting the Visual Studio Load Test Results Database (Part 2–Stored Procs and Default Reporting)

  • Article

In Part 1, we visited the high level schema of the Visual Studio Load Test Results Database, showing the tables and views that are a default part of the DB. In Part 2, we will look at the built in Stored Procedures and how some of them get used for building results and reports.

The Sprocs

The following list shows all of the built in stored procedures, broken down by the type of functionality they provide. [NOTE: All sprocs that have a 2 or 3 at the end denote a schema revision change where functionality was ADDED to the results. For any of these sprocs, the Schema Revision number stored with each run is used to determine which sproc to call. If the stored schema revision is higher than any of the sproc versions, the highest available sproc version is used. ]:

  • Create/Delete- These are used to control creation and modification of data
    • Prc_DeleteDataCollectorLogs
    • Prc_DeleteLoadTestRun
    • prc_FindLoadTestReport
    • prc_GrantDBAccess
  • Updating Results - These are called just after all results are written to the database at the end of a run. They perform the updates to the summary tables. See this article for more information.
    • Prc_UpdateInstanceSummaryData
    • prc_UpdateLoadTestReport
    • Prc_UpdatePageByNetworkPercentiles
    • Prc_UpdatePagePercentiles
    • Prc_UpdatePageSummary
    • Prc_UpdatePageSummary3
    • Prc_UpdatePageSummaryByNetwork
    • Prc_UpdatePageSummaryByNetwork3
    • Prc_UpdateSummaryData
    • Prc_UpdateSummaryData2
    • Prc_UpdateSummaryData3
    • Prc_UpdateTestPercentiles
    • Prc_UpdateTestSummary
    • Prc_UpdateTestSummary3
    • Prc_UpdateTransactionPercentiles
    • Prc_UpdateTransactionSummary
    • Prc_UpdateTransactionSummary3
  • Get Misc Info- These [along with various direct queries] grab various pieces of information for generating the results shown inside the Visual Studio IDE
    • Prc_GetAgents
    • Prc_GetAvailableDetailTypes
    • Prc_GetCategories
    • Prc_GetCompletedLoadTestRuns
    • Prc_GetErrorDetail
    • Prc_GetFirstSampleForInstance
    • Prc_GetInitialDetailRange
    • Prc_GetInstanceSummaries
    • Prc_GetLastSampleForInstance
    • Prc_GetRequestMap
    • Prc_GetRequestMap2
    • Prc_GetSamplesForInstance
    • Prc_GetTestCases
    • Prc_GetTestLog
    • Prc_GetTransactionMap
    • Prc_GetUniqueDetailIds
    • Prc_GetUserPageDetail
    • Prc_GetUserTestDetail
    • Prc_GetUserTransactionDetail
  • Error Info- These get information about any errors that occurred during the run, including the details and the actual results, if available.
    • Prc_GetMessagesAll
    • Prc_GetMessagesAll2
    • Prc_GetMessagesByAgent
    • Prc_GetMessagesByAgent2
    • Prc_GetMessagesByRequest
    • Prc_GetMessagesByRequest2
    • Prc_GetMessagesByTest
    • Prc_GetMessagesByTest2
    • Prc_GetMessagesByType
    • Prc_GetMessagesByType2
    • Prc_GetMessagesTypeSummary
  • Insertion Routines- These procs are called as part of the test execution [and 'Writing Results'] in order to store the data
    • Prc_InsertFileAttachmentChunk
    • Prc_InsertLoadTestDetailMessage
    • Prc_InsertLoadTestMessageType
    • Prc_InsertPageDetail
    • Prc_InsertPageDetail2
    • Prc_InsertSample
    • Prc_InsertSqlTraceRow
    • Prc_InsertTestDetail
    • Prc_InsertTestDetail2
    • Prc_InsertTestLog
    • Prc_InsertTransactionDetail
    • Prc_InsertTransactionDetail2
  • Procs for Excel Reporting- These are used by Excel as part of the Excel Reporting AddIn provided by Visual Studio.
    • prc_CreateLoadTestReport
    • Prc_QueryComputedCounterComparison
    • Prc_QueryComputedCounterSummary
    • prc_QueryForInstanceCount
    • prc_QueryForMachinesInRun
    • Prc_QueryLoadTestErrorComparison
    • Prc_QueryLoadTestPageComparison
    • Prc_QueryLoadTestPageSummary
    • prc_QueryLoadTestReports
    • Prc_QueryLoadTestRequestSummary
    • prc_QueryLoadTestRuns
    • prc_QueryLoadTestRunsById
    • Prc_QueryLoadTestScenarioSummary
    • Prc_QueryLoadTestTestCaseSummary
    • Prc_QueryLoadTestTestComparison
    • Prc_QueryLoadTestTransactionComparison
    • Prc_QueryLoadTestTransactionSummary
    • prc_QueryPossibleCountersForReport

Examples

The next section shows an example of the work that Visual Studio performs when opening a result from the database. The information was captured from a run that had been exported from a full test rig and loaded into my Surface Pro 2. I had not added any extra graph objects to the results and all graphing options are still set to default. When you open runs, the queries, sprocs and arguments passed into both will vary. The purpose of this is to allow you to see a fairly representative sample of the data flow between Visual Studio and SQL.

Opening the Manage Results Dialog

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:

select LoadTestRunId, RunId, LoadTestName, Description, Comment, IsLocalRun, ControllerName, StartTime, EndTime, WarmupTime, RunDuration, LoadTest, Outcome, LoadTestSchemaRev,CooldownTime from LoadTestRun where LoadTestRunId = 2156

Opening a Result

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 LoadTestRunId, RunId, LoadTestName, Description, Comment, IsLocalRun, ControllerName, StartTime, EndTime, WarmupTime, RunDuration, LoadTest, Outcome, LoadTestSchemaRev,CooldownTime from LoadTestRun where LoadTestRunId = 2156

Sproc: exec Prc_GetRequestMap2 @LoadTestRunId=2156

Sproc: exec Prc_GetCategories @LoadTestRunId=2156

Query: SELECT TestCaseId,Percentile90,Percentile95 FROM LoadTestTestSummaryData WHERE LoadTestRunId = 2156

Query: SELECT TransactionId,Percentile90,Percentile95 FROM LoadTestTransactionSummaryData WHERE LoadTestRunId = 2156

Query: SELECT PageId,Percentile90,Percentile95 FROM LoadTestPageSummaryData WHERE LoadTestRunId = 2156

Sproc: exec Prc_GetInstanceSummaries @LoadTestRunId=2156

Query: SELECT TOP 1000 category.MachineName, category.CategoryName, [truncated]

Query: exec Prc_GetMessagesTypeSummary @LoadTestRunId=2156

Query: select FileAttachmentId, MachineName, Filename, Filesize from LoadTestFileAttachment where LoadTestRunId = 2156

Query: select DataCollectorLogId, DataCollectorDisplayName, MachineName from LoadTestDataCollectorLog where LoadTestRunId = 2156

Query: SELECT ScenarioName, TestCaseName, TransactionName, TransactionCount, Minimum, Maximum, Average, Median, Percentile90, Percentile95, Percentile99, StandardDeviation, AvgTransactionTime FROM LoadTestTransactionResults2 where LoadTestRunId = 2156 ORDER BY ScenarioName, TestCaseName, TransactionName

Query: SELECT ScenarioName, TestCaseName, RequestUri, NetworkName, PageCount, Minimum, Maximum, Average, Median, Percentile90, Percentile95, Percentile99, StandardDeviation, Goal, PagesMeetingGoal FROM LoadTestPageResultsByNetwork2 where LoadTestRunId = 2156 ORDER BY ScenarioName, TestCaseName, RequestUri, NetworkName

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT TOP 1000 Event = CASE EventClass WHEN 10 THEN 'RPCCompleted'

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Query: SELECT samples.InstanceId, interval.IntervalEndTime, samples.RawValue, [truncated]

Adding a Graph Item

Many of the graph items that you might add will already be stored in the DataSets in Visual Studio. However, if you choose to add a perfmon counter to a graph that has not been loaded, the following will get executed:

Query: SELECT samples.InstanceId,interval.IntervalEndTime, samples.RawValue, samples.BaseValue, samples.CounterFrequency, samples.SystemFrequency, samples.SampleTimeStamp, samples.SampleTimeStamp100nSec, samples.CounterType, samples.ThresholdRuleResult FROM LoadTestPerformanceCounterSample as samples LEFT OUTER JOIN LoadTestRunInterval AS interval ON samples.LoadTestRunId = interval.LoadTestRunId AND samples.TestRunIntervalId = interval.TestRunIntervalId WHERE samples.LoadTestRunId = 2156 AND (samples.InstanceId = 93) ORDER BY samples.InstanceId, samples.TestRunIntervalId

Viewing a Table Item

Table items such as Tests, Transactions, Pages, etc. are already in the DataSets, but a couple of tables (such as Threshold and SQL Trace) are not pre-loaded. The following shows what will execute if you select the Threshold table:

Query: SELECT TOP 1000 category.MachineName, category.CategoryName, counter.CounterName, instance.InstanceName, interval.IntervalEndTime, countersample.ThresholdRuleResult, thresholdMessage.MessageText FROM LoadTestPerformanceCounterCategory AS category INNER JOIN LoadTestPerformanceCounter AS counter ON category.LoadTestRunId = counter.LoadTestRunId AND category.CounterCategoryId = counter.CounterCategoryId INNER JOIN LoadTestPerformanceCounterInstance AS instance ON counter.CounterId = instance.CounterId AND counter.LoadTestRunId = instance.LoadTestRunId INNER JOIN LoadTestPerformanceCounterSample AS countersample ON countersample.InstanceId = instance.InstanceId AND countersample.LoadTestRunId = instance.LoadTestRunId INNER JOIN LoadTestRunInterval AS interval ON interval.LoadTestRunId = countersample.LoadTestRunId AND interval.TestRunIntervalId = countersample.TestRunIntervalId INNER JOIN LoadTestThresholdMessage AS thresholdMessage ON interval.LoadTestRunId = thresholdMessage.LoadTestRunId AND interval.TestRunIntervalId = thresholdMessage.TestRunIntervalId AND countersample.ThresholdRuleMessageId = thresholdMessage.MessageId WHERE countersample.ThresholdRuleMessageId IS NOT NULL AND thresholdMessage.LoadTestRunId = 2156 ORDER BY interval.IntervalEndTime ASC

 

The Details Tab

The following sprocs and queries get executed whenever you switch to the “Details” tab:

Sproc: exec Prc_GetUniqueDetailIds @LoadTestRunId=2156,@DetailType=0

Query: select MessageTypeId, MessageType, SubType from LoadTestMessageType where LoadTestRunId = 2156

Sproc: exec Prc_GetAvailableDetailTypes @LoadTestRunId=2156

Next: Excel…

In the next post, we will dive into the sprocs and queries used for the built in Excel Reporting.