Dissecting the Visual Studio Load Test Results Database (Part 1–Intro to the Schema)

  • Article

Sean Lumley wrote a good blog post years ago about the actual schema for the Visual Studio Load Test results database, but even with that post, navigating the DB and figuring out how things tie together can be confusing at the least, and sometimes almost impossible to figure out. I have been working inside the database for several years and have developed a pretty good understanding of how the schema works and how to navigate it. In this series of posts, I will share information about how the data is stored, why it is shaped the way it is, and how to extract the information you need for reporting.

The High Level Schema

The main schema for the database was created for the first version of VSTS in 2005. Since then the schema has been extended twice, but not changed. This is important because it means that any results from load tests run in VSTS 2005 are still valid and can still be viewed inside Visual Studio.

Every table in the database has a column for LoadTestRunId. This Id is generated for every single load test execution that is stored in the database. Before Visual Studio starts the execution of a test, it adds a row to the LoadTestRun table, which triggers the creation of a unique integer via SQL’s Identity property. Then it grabs that value and uses it throughout the rest of the execution and the writing of the results. (NOTE: You can use this step to query the results database inside a LoadTest plugin and capture the Id so you can add your own custom items to the database during a run. Bill Barnett helped me back in 2008 to do this with console messages for a SQL CE simulation load test harness using unit tests. The LoadTestRun table is the primary “list” for all runs available. There is one row for each load test that has been executed (or is in the process of being executed).

The rest of the data is heavily normalized and most tables use a combination of the LoadTestRunId and other columns to create multi-column primary keys. The data tables are broken down into a few categories below, and the views (listed separately) are used to “un-normalize the data for reporting purposes. The stored procedures will be covered in the next article.

The Built In Tables
  • Core Run Tables
    • LoadTestRun: Primary Run Table - One row per load test run
    • LoadTestSchemaRevision: For VS 2010 - 2015, this will have a single entry with the value of 3
    • LoadTestCase: One row for each inner test referenced in the .loadtest file
    • LoadTestFileAttachment: Used to store info about attachments from things like data collectors, etc
    • LoadTestFileAttachmentChunk: Used to store the actual attachments from the above table
    • LoadTestDataCollectorLog: Used to store info about the data collectors used during the run
    • LoadTestTestLog: Stores the full testcase results of a test iteration that is logged
    • LoadTestSqlTrace: Stores SQL Proifile data from tests where SQL Tracing is enabled
  • Core Detailed Timings Tables (These tables may not contain data for some runs. See this postfor more information)
    • LoadTestTestDetail: Stores timing and outcome information for every iteration of every test executed.
    • LoadTestPageDetail: Stores timing and outcome information for every iteration of every primary level request executed.
    • LoadTestTransactionDetail: Stores timing information for every iteration of every transaction that successfully completed. If a request fails while inside a transaction, the transaction will not have an entry.
    • LoadTestDetailMessage: One row for every message that occurs during a test run. This is the master list of errors.
  • Core Timings Summary Tables (Qty, Min, Avg, Max, 90%, 95%, 99%, Mean, Std Dev)  
    • LoadTestTestSummaryData: Stores the summary of the LoadTestTestDetail data
    • LoadTestTransactionSummaryData: Stores the summary of the LoadTestTransactionDetail data
    • LoadTestPageSummaryData: Stores the summary of the LoadTestPageDetail data
    • LoadTestPageSummaryByNetwork: Stores the summary of the LoadTestPageDetail data, broken out by network emulator (if applicable)
  • Tables to map names to IDs
    • WebLoadTestErrorDetail: (normalized data) Maps error messages to the web pages that caused the errors
    • WebLoadTestRequestMap: (normalized data) Maps the request/page name to the PageId for the above tables
    • WebLoadTestTransaction: (normalized data) Maps the transaction name to the transactionID for the above tables
  • Error Message Tables
    • LoadTestMessage: The details for any message (error, etc) captured during execution
    • LoadTestMessageType: (normalized data) the type of message for the above table
    • LoadTestThresholdMessage: The details for any threshold violation captured during execution
  • Perfmon Tables (NOTE: The way the data is stored for the pseudo counters [e.g. Test, Transaction, Page, etc] will be discussed in a separate post as part of this series.)
    • LoadTestPerformanceCounterCategory: (normalized data) Stores the perfmon CATEGORY and MACHINE for each perfmon category collected (One row per Machine/Category pair)
    • LoadTestPerformanceCounter: (normalized data) Stores one row for each COUNTER collected inside each LoadTestPerformanceCounterCategory entry
    • LoadTestPerformanceCounterInstance: (normalized data) Stores one row for each counter INSTANCE collected inside each LoadTestPerformanceCounter entry
    • LoadTestPerformanceCounterSample: The actual perfom sampled values. Contains one row for each item in the LoadTestPerformanceCounterInstance table for each LoadTestRunInterval
  • SUT and RIG settings tables
    • LoadTestRunAgent: (normalized data) One entry for each agent that was used during test execution
    • LoadTestRunInterval: (normalized data) One entry for each sample interval during the test
    • LoadTestScenario: (normalized data) One entry for each Scenario defined in the .loadtest file
    • LoadTestBrowsers: (normalized data) One entry for each browser type emulated in the test
    • LoadTestNetworks: (normalized data) One entry for each network type emulated in the test
    • LoadTestSystemUnderTest: (normalized data) One entry for each machine that had perfmon data collected and that was NOT a part of the test rig
    • LoadTestSystemUnderTestTag: (normalized data) One row for each machine in LoadTestSystemUnderTest that contains a custom identifier tag
  • Excel Reporting historical data tables
    • LoadTestReport: Stores configuration info about any Excel reports that have been created tyhrough the Visual Studio IDE
    • LoadTestReportRuns: (normalized data)
    • LoadTestReportPage: (normalized data)
The Built In Views
  • Summary Views
    • LoadTestMessageSummary: Combines the LoadTestMessage, LoadTestCase, LoadTestScenario, LoadTestRunAgent, WebLoadTestRequestMap and WebLoadTestErrorDetail tables to provide a summary of the errors and threshold messages (Defines: TestError, Exception, HttpError, ValidationRuleError, ExtractionRuleError, Timeout, DataCollectionError, DataCollectionWarning, Unknown).
    • LoadTestMessageView: Combines the LoadTestMessage, LoadTestCase, LoadTestScenario, LoadTestRunAgent, WebLoadTestRequestMap and WebLoadTestErrorDetail tables to provide details about the errors and threshold messages.
    • LoadTestMessageView2: Combines the LoadTestMessage, LoadTestCase, LoadTestScenario, LoadTestRunAgent, WebLoadTestRequestMap, WebLoadTestErrorDetail and LoadTestTestLog tables to provide details about the errors and threshold messages.
    • LoadTestPageResults: Combines the LoadTestPageSummaryData, WebLoadTestRequestMap, LoadTestCase and LoadTestScenario tables to provide a summary of page performance based on actaul page data.
    • LoadTestPageResultsByNetwork: Combines the LoadTestPageSummaryByNetwork, WebLoadTestRequestMap, LoadTestCase, LoadTestScenario and LoadTestNetworks tables to provide a summary of page performance split into the different networks emulated based on actaul page data.
    • LoadTestPageResultsByNetwork2: Provides the same info as LoadTestPageResultsByNetwork, except it adds 99%, Std Dev and Mean
    • LoadTestTestResults: Combines the LoadTestTestSummaryData, LoadTestCase and LoadTestScenario tables to provide a summary of test performance based on actaul test data.
    • LoadTestTransactionResults: Combines the LoadTestTransactionSummaryData, WebLoadTestTransaction, LoadTestCase and LoadTestScenario tables to provide a summary of transaction performance based on actaul transaction data.
    • LoadTestTransactionResults2: Provides the same info as LoadTestTransactionSummaryData, except it adds 99%, Std Dev and Mean
  • Perfmon Data Views
    • LoadTestComputedCounterSample: Combines the LoadTestPerformanceCounterCategory, LoadTestPerformanceCounter, LoadTestPerformanceCounterInstance, LoadTestPerformanceCounterSample and LoadTestRunInterval tables to provide perfmon sample values.
    • LoadTestComputedCounterSummary: Combines the LoadTestRun, LoadTestPerformanceCounterCategory, LoadTestPerformanceCounter and LoadTestPerformanceCounterInstance tables to provide perfmon cumulative (summary) values.
    • LoadTestPageSample: Combines the necessary tables to provide a sampled data of page performance based on sampled Perfmon Counters (Used for graphing results).
    • LoadTestPageSummary: Combines the necessary tables to provide a summary of page performance based on sampled Perfmon Counters.
    • LoadTestRequestSummary: Combines the necessary tables to provide a summary of request performance based on sampled Perfmon Counters.
    • LoadTestScenarioSummary: Combines the necessary tables to provide a summary of scenario performance based on sampled Perfmon Counters.
    • LoadTestTestCaseSample: Combines the necessary tables to provide a sampled data of test case performance based on sampled Perfmon Counters (Used for graphing results).
    • LoadTestTestCaseSummary: Combines the necessary tables to provide a summary of test case performance based on sampled Perfmon Counters.
    • LoadTestTransactionSample: Combines the necessary tables to provide a sampled data of transaction performance based on sampled Perfmon Counters (Used for graphing results).
    • LoadTestTransactionSummary: Combines the necessary tables to provide a summary of transaction performance based on sampled Perfmon Counters.
    • LoadTestWebPageAndUserLoadCounterSample: Combines the necessary tables to provide a summary of request Throughput based on sampled Perfmon Counters.
    • LoadTestWebRequestCounterSample: Combines the necessary tables to provide a sampled data of request performance based on sampled Perfmon Counters (Used for graphing results).
    • LoadTestWebRequestCounterSummary: Combines the necessary tables to provide a summary of total vs. passed request counts.