Share via

Description of Tables and Columns in VS 2010 Load Test Database

  • Article

In this blog post I am going to update Bill Barnett's post which describes the tables and columns in the load test database.  Here is Bill's original post which applies to VS 2005 and VS 2008:  2008 Database

First, in VS 2010 the default load test database name has been changed from LoadTest to LoadTest2010.  Now let's review the tables. 

*** Each new table or column will be prefixed with ***

Table: LoadTestRun: Contains one row for each load test that is executed.

Columns:

  • LoadTestRunId (int): A unique Id for this load test run in this load test results store.   The value is 1 for the first load test and increments for each load test that is executed.   All of the tables in the load test results store use the LoadTestRunId identify the load test run to which the data belongs.
  • LoadTestName (nvarchar(255)): The name of the load test
  • RunId (char (36)) : A Guid that identifies the test run in which the load test was run.
  • Description (nvarchar (255)): A description of the load test from the Analysis dialog in the Load Test Results Viewer.
  • StartTime (datetime): The time at which the load test started.
  • EndTime (datetime): The time at which the load test completed or was stopped by the user.  EndTime is NULL while the test is in progress.
  • RunDuration (int): The load test run duration (in seconds) that was specified in the active Run Settings for the load test.
  • WarmupTime (int): The warmup time (in seconds) that was specified in the active Run Settings for the load test.
  • RunSettingUsed (nvarchar (32)): The name of the active Run Settings at the time the load test was run.
  • IsLocalRun (bit): Indicates whether the load test was run locally or a rig (controller & agent(s)): 1 for local; 0 for rig.
  • ControllerName (nvarchar (255)): Name of the controller computer (or local computer name for a local run).
  • Outcome (nvarchar (32)): The outcome of the load test, which will be one of: "InProgress", "Completed", "Aborted", or "Error".
  • LoadTest (image): An encoded version of the LoadTest that was run.
  • Comment (ntext): A potentially long comment about the load test from the Analysis dialog in the Load Test Results Viewer.
  • ***LoadTestSchemaRev(int): An integer indicating what version of product the run was created with.  If you use VS2008 against this DB, this column will be null.  If you use VS2010, it will be 3.
  • ***CooldownTime(int): This is amount of time spent in cooldown phase.  This time is in seconds.

Table: LoadTestCase: Stores data on the inner tests included in the load test (one row per inner test)

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • ScenarioId (int) : The unique Id of the Scenario containing the test (from the LoadTestScenario table)
  • TestCaseId (int): A unique Id for the test within this load test run
  • TestCaseName (nvarchar (64)): The name of the test
  • ***TestElement (image): Identifier as to which test type the test case is(Web Test, Unit Test, etc...)
  • ***TestType (nvarchar(64)): Readable version of what test type the test case is

Table: LoadTestMessage: Stores data for each error that occurs during the load test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run.
  • AgentId (int): The unique Id of the load test agent on which the error occurred (-1 for errors not specific to an agent)
  • MessageId (int): A unique Id for the message on the agent during this load test run
  • MessageType (tinyint): An integer that encodes the type of error; the values used and their meanings are: 0: TestError, 1: Exception, 2; HttpError, 3: ValidationRuleError, 4: ExtractionRuleError, 5: Timeout
  • MessageText nvarchar (2048): The error message text
  • SubType (nvarchar (64)): The sub type of the error.  The values for this vary depending on the MessageType.  For example, if MessageType is Exception, SubType is the name of the Exception class.
  • StackTrace (ntext): A stack trace for the code location where the error occurred (used when MessageType is Exception or TestError)
  • MessageTimeStamp (datetime): The time at which the error occurred
  • TestCaseId (int): The unique Id of the inner test (from the LoadTestCase table) in which the error occurred, or NULL for errors not associated with a specific inner test such as performance counter collection errors.
  • RequestId (int): The unique Id of the web test request (from the WebLoadTestRequestMap table) on which the error occurred, or NULL for errors not associated with web test request.
  • ***TestLogId(int): The unique Id of the test log (from the LoadTestTestLog table).  Test log contains the entire testcase and not just the request that failed.

Table: ***LoadTestMessageType: Stores a row for each type of message that occurs.  Whereas LoadTestMessage stores data for each occurence of a message.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run.
  • MessageTypeId (int): A unique Id for the message
  • MessageType (tinyint): An integer that encodes the type of error; the values used and their meanings are: 0: TestError, 1: Exception, 2; HttpError, 3: ValidationRuleError, 4: ExtractionRuleError, 5: Timeout
  • SubType (nvarchar (64)): The sub type of the error.  The values for this vary depending on the MessageType.  For example, if MessageType is Exception, SubType is the name of the Exception class.

Table: LoadTestThresholdMessage: Stores one row of data for each threshold rule violation

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TestRunIntervalId (int): Id of the collection interval during which the threshold rule violation occurred
  • CounterInstanceId (int): Id of the performance counter instance that had the threshold rule violation
  • MessageId (int): Unique Id of the threshold rule violation within this load test run
  • MessageText (nvarchar (2048)): Text describing the threshold rule violation

Table: ***LoadTestFileAttachment: Stores one row of data for each file that is beign saved with a run.  Typically these will be files that are returned from datacollectors.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • FileAttachmentId(int): Unique id for the file
  • MachineName (nvarchar (255)): Machine where file came from.
  • FileName (nvarchar (260)): Name of file being stored
  • FileSize(bigint): Size of file being stored

Table: ***LoadTestFileAttachmentChunk: Stores the file content.  File is broken into chunks and stored in multiple rows..

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • FileAttachmentId(int): Unique id for the file.  Corresponds to row in LoadTestFileAttachment
  • StartOffset(bigint): Location in file that where this chunk starts
  • EndOffset(bigint): Location in file that where this chunk ends
  • ChunkLength(bigint): Length of bytes being stored
  • ChunkBytes(image): Actual bytes being stored

Table: ***LoadTestDataCollectorLog: Contains one row for each data collector that is running during a loadtest

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • DataCollectorId(int): Unique id for the data collector. 
  • DataCollectorDisplayName(nvarchar(255)): Name of data collector
  • MachineName(nvarchar(255)): Name of machine the data collector is running on
  • TimestampColumnName(nvarchar(128)): Name of column in data collector table which corresponds to time stamp data
  • DurationColumnName(nvarchar(128)): Name of column in data collector table which corresponds to duration data
  • CreateTableFormatString(nvarchar(max)): SQL statement used to create table to store data for this data collector

Overview of performance counter tables

        The metadata for all of the performance counters collected during a load test is stored in three related tables: LoadTestPerformanceCounterCategory, LoadTestPerformanceCounter, and LoadTestPerformanceCounter.

        This metadata includes all four components of performance counter identifier: computer name, category name, counter name, and instance name.

        Note that these three tables only store metadata about the performance counters; the sampled performance counter values are stored in LoadTestPerformanceCounterSample.

Table: LoadTestPerformanceCounter: Stores one row for each performance counter that is collected during the load test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • CounterCategoryId (int): The CounterCategoryId from the LoadTestPerformanceCounterCategory row for the catgory containing this counter name
  • CounterId (int): The unique Id of this performance counter for this load test run
  • CounterName (nvarchar (255)): The name of the performance counter (for example "Available MBytes")
  • ***HigherIsBetter (bit): A value indicating wheter an increase in this performance counter is a good thing.  For example an increase in Available MBytes is typically a good thing.

Table: LoadTestPerformanceCounterCategory: Stores one row for each performance counter category and computer on which the category is collected

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • CounterCategoryId (int): The unique Id of this performance counter category for this load test run
  • CategoryName (nvarchar (255)): The name of the performance counter category (for example "Memory")
  • MachineName (nvarchar (255)):   The name of the computer on which the category was collected
  • StartTimeStamp100nSec (bigint): A timestamp indicating when the category was first collected during the load test 

Table: LoadTestPerformanceCounterInstance: Stores one for each performce counter instance that is collected during the load test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • CounterId (int): The CounterId from the LoadTestPerformanceCounter table for the perf counter of which this is an instance
  • InstanceId (int): Unique Id of this perf counter instance in this load test run
  • LoadTestItemId (int): For performance counters in the "LoadTest:*" categories, this uniquely identifies the item in the load test with which this performance counter is associated.   For example, for performance counters in the "LoadTest:Request" category, the LoadTestItemId is the value of the RequestId in the WebLoadTestRequestMap table. 
  • InstanceName (nvarchar (255)): The name of the performance counter instance (for example "_Total")
  • CumulativeValue (real): The final calculated value for this performance counter instance over the entire load test run
  • OverallThresholdRuleResult (tinyint): Indicates the most severe threshold rule result for this performance counter instance during the load test; 0 for Ok, 1 for Warning, 2 for Critical

Table: LoadTestPerformanceCounterSample: Stores the performance counter samples.  There is one for each performance counter instance collected for each sampling interval during the loadtest. Many of the columns in this table are fields of the .Net class System.Diagnostics.CounterSample which is used internally by the load test implementation, and their values don't make much sense outside that context.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TestRunIntervalId (int): The sequence number of the collection interval in which this sample was collected
  • InstanceId (int): The InstanceId from the LoadTestPerformanceCounterInstance table row containing for the performance counter instance of which this is a sample
  • ComputedValue (real): A value computed for the perf counter based on this sample and the previous sample (or NULL if there was no sample in the previous interval)
  • RawValue (bigint): The RawValue field of the System.Diagnostics.CounterSample value
  • BaseValue (bigint): The BaseValue field of the System.Diagnostics.CounterSample value
  • CounterFrequency (bigint): The CounterFrequency field of the System.Diagnostics.CounterSample value
  • SystemFrequency (bigint): The SystemFrequency field of the System.Diagnostics.CounterSample value
  • SampleTimeStamp (bigint): The SampleTimeStamp field of the System.Diagnostics.CounterSample value
  • SampleTimeStamp100nSec (bigint): The SampleTimeStamp100nSec field of the System.Diagnostics.CounterSample value
  • CounterType (int): The PerformanceCounterType field of the System.Diagnostics.CounterSample value
  • ThresholdRuleResult (tinyint): The result of any threshold rule applied to this sample: 0 for Ok, 1 for Warning, 2 for Critical
  • ThresholdRuleMessageId (int): The MessageId from the LoadTestThresholdMessage row with more details on any threshold violation thrown for this sample, or NULL if there was no threshold violation

Table: LoadTestRunAgent: Stores the names of the agent machines used for a load test run

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • AgentId (int): A unique Id of this agent during this load test run
  • AgentName (nvarchar (255)): The computer name of the agent

Table: LoadTestRunInterval: Stores one row for each performance counter collection interval during the load test run

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TestRunIntervalId (int): The unique Id of this interval in this load test run
  • IntervalStartTime (datetime): The start time of the collection interval
  • IntervalEndTime (datetime): The end time of the collection interval

Table: LoadTestScenario: Stores the names of the Scenarios defined for a load test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • ScenarioId (int) : The unique Id of the Scenario within this load test
  • ScenarioName (nvarchar (64)): The name of the Scenario

Table: WebLoadTestErrorDetail: Stores detailed data about a web test request when there is an error associated with a web test request

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • AgentId (int): The Id of the agent on which the error occurred
  • MessageId (int): The MessageId from the row in the LoadTestMessage table for the error with which this error detil is associated
  • WebTestRequestResult (image): A binary encoded version of the object that stores all of the data captured regarding the result of a web test request

Table: ***LoadTestTestLog: Stores the entire test case result assoicated with an execution of a testcase.  By default just failed tests will be logged, but you can also
configure successful tests to be logged as well.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • AgentId (int): The Id of the agent on which testcase was executed
  • TestCaseId (int): The id of the test case for which log is being stored
  • TestLogId(int): Unique id for the test log being saved
  • TestLog(image): A binary encoded version of the object that stores all of the data captured regarding the result of a web test

Table: WebLoadTestRequestMap: Stores one row for each unique Uri to which a web test request is sent during the load test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • RequestId (int): The unique Id of this request in this load test run
  • TestCaseId (int): The Id of the web test that generated this request
  • RequestUri (nvarchar (2048)): The Uri for the request.   This excludes the query string parameters except for those query string paramters with the property "
  • ***ResponseTimeGoal(float): If you set a goal for a web test request, that value will be storded in this column

Table: WebLoadTestTransaction: Store the names of all transactions defined in the load test (this includes transactions defined in web tests, and transaction defined in unit tests using the BeginTimer()/EndTimer() methods.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TransactionId (int): The unique Id of the transaction in this load test run
  • TestCaseId (int): The TestCaseid from the LoadTestCase table for the test that defined the transaction
  • TransactionName (nvarchar (64)): The name of the transaction

Table: LoadTestSqlTrace: Stores collected when performing SQL tracing during a load test; the field correspond to SQL trace fields

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TextData (ntext): The text of the SQL operation
  • Duration (bigint): The duration of the SQL operation
  • StartTime (datetime): The start time of the SQL operation
  • EndTime (datetime): The end time of the SQL operation
  • Reads (bigint): The number of reads that occurred while performing the SQL operation
  • Writes (bigint): The number of writes that occurred while performing the SQL operation
  • CPU (int): The CPU time required to perform the SQL operation
  • EventClass (int): An integer identifying the type of SQL operation traced

Table: LoadTestBrowsers: Stores the names of all browser types used during the load test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • BrowserId (int): The unique Id of this browser
  • BrowserName (nvarchar (255)): The name of the browser

Table: LoadTestNetworks: Stores the names of all network types used during the load test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • NetworkId (int): The unique Id of the network type
  • NetworkName (nvarchar (255)): The name of the network type

Tables used to store Timing Details

All of the following tables only have data stored in them when the "Timing Details Storage" option on the Load Test Run Settings is set to a value other than "None".

Table: LoadTestTestDetail: Stores the results of individual inner tests; there is one row for each test completed during the load test

Columns:

  • LoadTestRunId (int) :The unique Id of the load test run
  • TestDetailId (int): A unique Id for this row in the load test run
  • TimeStamp (datetime): The time that the test completed
  • TestCaseId (int): The TestCaseId from the LoadTestCase table for the row that identifies the test
  • ElapsedTime (float): The elapsed time to run the test in seconds
  • AgentId (int): The AgentId from the LoadTestAgent table row that identifies the agent on which the test was run
  • BrowserId (int): The BrowserId from the LoadTestBrowser table that identifies the browser type used for a web test
  • NetworkId (int): The NetworkId from the LoadTestNetwork table that identifies the network type used for a web test
  • ***Outcome (tinyint): A value indicating the outcome of the test case detail
  • ***TestLogId (int): A unique id indicating associated test log from the LoadTestTestLog table
  • ***UserId (int): An int indicating which virtual user executed the test
  • ***EndTime (datetime): The time the test completed
  • ***InMeasurementInterval(bit): 0 if test completed during warmup or cooldown.  1 if test completed during actual duration of load test.  Only tests with a value of 1 will be included when computing statistics.

Table: LoadTestPageDetail: Stores timing results of individual web test pages; there is one row for each web test page completed during the load test

Columns:

  • LoadTestRunId (int) : The unique Id of the load test run
  • PageDetailId (int) : A unique Id for this row in the load test run
  • TestDetailId (int): The TestDetailId from LoadTestTestDetail row with the result for the test containing this page
  • TimeStamp (datetime) : The time at which the page completed
  • PageId (int): The RequestId from the WebLoadRequestMap table (join on this to identify the Uri for the page)
  • ResponseTime (float): The response time for the page in seconds
  • ResponseTimeGoal (float): The response time goal for the page in seconds
  • GoalExceeded (bit): Bool indicating whether or not the response time goal was exceeded by the response time
  • ***EndTime (datetime): The time the page completed
  • ***Outcome (tinyint): A value indicating the outcome of the page. 0 for pass, 1 for fail
  • ***InMeasurementInterval(bit): 0 if page completed during warmup or cooldown.  1 if page completed during actual duration of load test.  Only pages with a value of 1 will be included when computing statistics.

Table: LoadTestTransactionDetail

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TransactionDetailId (int): A unique Id for this row in the load test run
  • TestDetailId (int): The TestDetailId from LoadTestTestDetail row with the result for the test containing this transaction
  • TimeStamp (datetime): The time at which the transaction completed
  • TransactionId (int): The TransactionId from the WebLoadTestTransaction table (join on this to identify the transaction name)
  • ElapsedTime (float): The elapsed time to complete the transaction in seconds
  • ***EndTime (datetime): The time the transaction completed
  • ***InMeasurementInterval(bit): 0 if transaction completed during warmup or cooldown.  1 if transaction completed during actual duration of load test.  Only transactions with a value of 1 will be included when computing statistics.
  • ***ResponseTime(float): The difference between this column and the ElapseTime column is that this value does not include thinktime assoicated with a request

Table: LoadTestTestSummaryData: Summarizes the detailed Test result data that is stored in LoadTestTestDetail; there is one row for each test in the load test run which rolls up the results for that test

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TestCaseId (int): The TestCaseId from the LoadTestCase table for the row that identifies the test
  • TestsRun (int): The number of tests completed
  • Average (float): The average time to run the test in seconds
  • Minimum (float): The minimum time to run the test in seconds
  • Maximum (float): The maximim time to run the test in seconds
  • Percentile90 (float): The 90% percentile test time (90% of the tests completed in less than this time)
  • Percentile95 (float): The 95% percentile test time (95% of the tests completed in less than this time)
  • ***Percentile99 (float): The 99% percentile test time (99% of the tests completed in less than this time)
  • ***Median(float): The median time to run the test in seconds
  • ***StandardDeviation(float): The standard deviation of the time to run the test

Table: LoadTestTransactionSummaryData: Summarizes the detailed transaction data that is stored in LoadTestTransactionDetail; there is one row for each transaction name used during the load test run which rolls up the results for that transaction.  The average column is calculated based on the elapsed time.  All other stats are calculated base on the response time of transaction.  The response time does not include thinktime.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • TransactionId (int): The TransactionId from the WebLoadTestTransaction table (join on this to identify the transaction name)
  • TransactionCount (int): The number of tests completed
  • Average (float): The average time to run the transaction in seconds
  • Minimum (float): The minimum time to run the transaction in seconds
  • Maximum (float): The maximim time to run the transaction in seconds
  • Percentile90 (float): The 90% percentile transaction time (90% of the transactions completed in less than this time)
  • Percentile95 (float): The 95% percentile transaction time (95% of the transactions completed in less than this time)
  • ***Percentile99 (float): The 99% percentile transaction time (99% of the transaction completed in less than this time)
  • ***Median(float): The median time to run the transaction in seconds
  • ***StandardDeviation(float): The standard deviation of the time to run the transaction
  • ***AverageTransactionTime (float): The average time to run the transaction in seconds.  This value does not include thinktime.

Table: LoadTestPageSummaryData: Summarizes the detailed page data that is stored in LoadTestPageDetail; there is one row for each page Uri invoked during the load test run which rolls up the results for that page.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • PageId (int): The RequestId from the WebLoadRequestMap table (join on this to identify the Uri for the page)
  • PageCount (int): The number of times this page was completed
  • Average (float): The average time to run the page in seconds
  • Minimum (float): The minimum time to run the page in seconds
  • Maximum (float): The maximim time to run the page in seconds
  • Percentile90 (float) : The 90% percentile page time (90% of the pages completed in less than this time)
  • Percentile95 (float): The 95% percentile page time (95% of the pages completed in less than this time)
  • ***Percentile99 (float): The 99% percentile page time (99% of the pages completed in less than this time)
  • ***Median(float): The median time to run the page in seconds
  • ***StandardDeviation(float): The standard deviation of the time to run the page

Table: LoadTestPageSummaryByNetwork: Summarizes the detailed page data that is stored in LoadTestPageDetail by network type; there is one row for each page Uri for each network type invoked during the load test run which rolls up the results for that page on each network type.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • PageId (int): The RequestId from the WebLoadRequestMap table (join on this to identify the Uri for the page)
  • NetworkId (int): The NetworkId from the LoadTestNetwork table that identifies the network type used for a web  test
  • PageCount (int): The number of times this page was completed (on the the network type identified by NetworkId)
  • Average (float): The average time to run the page in seconds (on the the network type identified by NetworkId)
  • Minimum (float): The minimum time to run the page in seconds (on the the network type identified by NetworkId)
  • Maximum (float): The maximim time to run the page in seconds (on the the network type identified by NetworkId)
  • Percentile90 (float): The 90% percentile page time (on the the network type identified by NetworkId)
  • Percentile95 (float): The 95% percentile page time (on the the network type identified by NetworkId)
  • Goal (float): The response time goal for the page
  • PagesMeetingGoal (int): The number of pages run (on the the network type identified by NetworkId) with a response time less than the goal.
  • ***Median(float): The median time to run the page in seconds (on the the network type identified by NetworkId)
  • ***StandardDeviation(float): The standard deviation of the time to run the page (on the the network type identified by NetworkId)
  • ***Percentile99 (float): The 99% percentile page time (on the the network type identified by NetworkId)

Table: ***LoadTestDetailMessge: This table will show the links between a test or page detail and an error message.  For example, you can use this to figure out what page details failed with a HTTP Status Code of 404.

Columns:

  • LoadTestRunId (int): The unique Id of the load test run
  • LoadTestDetailMessageId (int): The unique id for this message/test/page combination.
  • TestDetailId(int): The TestDetailId from LoadTestTestDetail table which corresponds to this test
  • PageDetailId(int): The PageDetailId from LoadTestPageDetail table which corresponds to this page
  • MessageTypeId (int): The unique id for the message assoicated with this test/page detail.  This corresponds to a row in LoadTestMessageType.

Comments

  • Anonymous
    March 15, 2010
    Hi, I would recommend that you cross link this the VSTS Quality Tools blog. That is the one I read first. That for the updated info.

  • Anonymous
    May 25, 2010
    Why are the obsolete datatypes 'ntext' and 'image' still being used with this database? From MSDN: "ntext, text, and image data types will be removed in a future version of Microsoft SQL Server."

  • Anonymous
    April 07, 2011
    how to clean up (delete )the data from loadtest database?

  • Anonymous
    May 12, 2011
    G+ 14 floor column size is 900mm*300mm

  • Anonymous
    June 05, 2013
    How to create load test database .. how this tables get created

  • Anonymous
    April 22, 2014
    Hi Slumley Am seeing -1 values for PageID in LoadTestPageDetail can you explain what they mean? Thanks

  • Anonymous
    May 20, 2015
    The comment has been removed

  • Anonymous
    July 09, 2015
    Hello Sean, This is an excellent blog and I visit it often to make progress in analyzing my Load test data. Can you help on something like extracting the actual page response for each request? or at least the failed request? Where do I find that? I want to be able to capture that because my application under test has custom error codes thrown on screen when an API errors out. Thanks in advance! Regards, Shuchita

  • Anonymous
    November 15, 2015
    when I am running from MTM, there is no data getting stored in loadtest2010 db which indicates the performance counter. Though there is data in some master tables like LoadTestRun but nothing in LoadTestDataCollectorLog