Dissecting the Visual Studio Load Test Results Database (Part 6–Data Warehousing)

  • Article

In the previous post, I showed how I use a couple of stored procedures to recalculate data based on various criteria. In this post, I am going to share a few data tables that I use to store “Warehousing” data to make reports faster and easier to create. In the next post I will show you how to start using this data, and how we can automate some of the sprocs I already posted about.

NOTE1: I have included a sproc in this post that you can download and run against a LoadTest2010 database. It will add all of the below info to the DB.

NOTE2: The last two sprocs will NOT install properly on any version of SQL Server older than 2012. If you experience any errors running the code, make sure your version of SQL is at least 2012.

About the sample code

The code here is all a work in progress. Please play with it, use it, test it and provide me feedback. At the end of this series, I will post the entire SQL codeset with any updates and fixes that get reported.

Data Warehousing

The idea of creating Data Warehouses is nothing new, and strictly speaking I do not know if what I am creating is officially a data warehouse. However, the idea fits so that’s what I am calling it. I add a few tables to the LoadTest2010 database and then populate them with aggregated and recalculated data. The beauty of the data is that it can all be gotten from existing data in the DB and does not break the current schema in any way. I will explain the table schemas first, then I will explain the stored procedures I use to populate the data into the tables.

The Tables

  • VSLT_LoadTestCoreInfoThis table contains a single row for each LoadTestRun stored in the database. The main purpose is to extend the data stored in the LoadTestRun table. I will not show all of the columns in this table. You can see the columns in the SQL code and the values are self explanitory. I will explain the FLAG columns though. These are used by my front end reporting tool and help the tool decide what functionality should be exposed for any given run:
    • FLAG_ContainsPages: TRUE if the run contains web page requests.
    • FLAG_ContainsTransactions: TRUE if the run contains Transaction Timers
    • FLAG_ContainsDetailedTimingInfo: TRUE if the test run contains the actual details for any of the three 'LoadTest***Detail' tables
    • FLAG_ContainsStepLoadProfile: TRUE if the test run contains more than 1 constant value for the "User Load" counter.
    • FLAG_TestAborted: TRUE if the Outcome column of the LoadTestRun is equal to 'Aborted'.
    • FLAG_LoadTestParsingFailed: This is set to TRUE if any type of error or exception occurs during the execution of the 'VSLT_prc_Update***' stored procedures
    • FLAG_LoadTestIntervalProfile: Contains the value associated with the interval profile from the next table below.
  • VSLT_IntervalProfileTypes[Normalization Table] Contains the list of interval types that could exist in a given load test run. There are two columns: The ID and Description for each type
    • 'Data May not be valid' : Indicates that the routines I use to calculate profiles did not have a proper checksum
    • 'use ENTIRE RUN' : Indicates that there was only a single User Load for the entire run (including any warmup)
    • 'use INTERVAL VALUES (only one interval exists)' : This run contains a warmup, or a steady state step ramp, then goes constant, so there is only one interval stored for the run. Use it.
    • 'Indeterminant, multiple intervals exist' : This run contains multiple valid intervals. Use the full set of interval tools to process it.
  • VSLT_LoadTestIntervalsThis table contains the data about any of the constant load "intervals" discovered in a run. There is one row of data for each interval.
    • LoadTestRunId: What run is this interval associated with.
    • StartOffset: How many seconds from the beginning of the run does this constant interval start
    • EndOffset: How many seconds from the beginning of the run does this constant interval end
    • UserLoad: How many users were executing tests at this interval
    • NumberOfDataPoints: How many perfmon collection intervals occurred during this interval (if the test's sample rate is every 5 seconds and the interval is 60 seconds long, then there will be 12 data points
  • VSLT_ScoreCardMachineCPU This table contains a single row of data per machine per run for any machine that had Perfmon CPU info collected. It is used to easily build summary and comparison reports. It is based on the Perfmon counter Processor\(_Total)\% Processor Time
    • LoadTestRunId: What run is this data associated with.
    • MachineName: The Computer Name
    • MachineTypeId: The Id for the type of machine this is. This field is currently not used, but exists to allow for aggregating data based on the roles of the machines
    • InstanceId: This is the Id contained in the LoadTestPerformanceCounterSample table and allows you to easily pull all data points for building charts or aggregating data
    • Qty: The number of perfmon samples collected from this machine during this run.
    • Min: The aggregated Minimum value from the perfmon data.
    • Avg: The aggregated Avg value from the perfmon data.
    • Max: The aggregated Maximum value from the perfmon data.
  • VSLT_ScoreCardMachineAvailableMB This table contains a single row of data per machine per run for any machine that had Perfmon Memory info collected. It is used to easily build summary and comparison reports. It is based on the Perfmon counter Memory\Available MBytes
    • LoadTestRunId: What run is this data associated with.
    • MachineName: The Computer Name
    • MachineTypeId: The Id for the type of machine this is. This field is currently not used, but exists to allow for aggregating data based on the roles of the machines
    • InstanceId: This is the Id contained in the LoadTestPerformanceCounterSample table and allows you to easily pull all data points for building charts or aggregating data
    • Qty: The number of perfmon samples collected from this machine during this run.
    • Min: The aggregated Minimum value from the perfmon data.
    • Avg: The aggregated Avg value from the perfmon data.
    • Max: The aggregated Maximum value from the perfmon data.
  • TSL_DbChangeLogsThis table tracks any changes that my stored procedures makes to the DB
    • LoadTestRunId: What run is this data associated with.
    • LogId: This is an Id for each entry in the table. This Id PLUS the LoadTestRunId form a unique key.
    • ChangeTypeId: This Id is used to map to the TSL_DbChangeLogTypes table to describe what type of change occurred.
    • ChangeTime: The timestamp when the change occurred.
    • LogComment: Any additional comments about the change will get stored here.
  • TSL_DbChangeLogTypes This table holds the descriptions for the types of changes that can be made to the DB by my sprocs. Valid values (shown as [ID], '[description]' ) are:
    • 1, 'VSLT_prc_UpdateLoadTestCoreInfoTable'
    • 2, 'VSLT_prc_UpdateScoreCardMachineCpuTable'
    • 3, 'VSLT_prc_UpdateScoreCardMachineAvailableMBTable'
    • 4, 'VSLT_prc_GetStartingValuesForImportingPerfmon'
    • 5, 'VSLT_prc_UpdatePerfmonTablesFromImport'
    • 6, 'VSLT_prc_UpdateLoadTestIntervalTable'
    • 7, 'VSLT_prc_UpdateLoadTestCoreInfoTable_IntervalProfileFlag'

 

The Update Sprocs

These stored procedures are used to update the values in the above tables. There are a few things to note about these sprocs and their usage:

  • These sprocs use CURSORS to go through all load test runs and only process the runs that have not already been processed. It is safe to run these sprocs as often as you would like.
  • The main sproc in this group will call ALL OTHER sprocs in the group so that you can get a full update from only a single command.
  • These sprocs will update the ChangeLogs info every time a change is made.

 

VSLT_prc_UpdateLoadTestCoreInfoTable - updates all of the data for the VSLT_LoadTestCoreInfo table. It also calls the two below sprocs.

VSLT_prc_UpdateScoreCardMachineAvailableMBTable updates all of the data for the VSLT_ScoreCardMachineAvailableMB table.

VSLT_prc_UpdateScoreCardMachineCpuTable updates all of the data for the VSLT_ScoreCardMachineCPU table.

VSLT_prc_UpdateLoadTestIntervalTable updates all of the info for the VSLT_LoadTestIntervals table. It also calls the below sproc.

VSLT_prc_UpdateCoreInfoWithIntervalFlags updates the VSLT_LoadTestCoreInfo table with the following values:

  • FLAG_LoadTestIntervalProfile
  • MainIntervalStart
  • MainIntervalEnd

 

Automatically Updating Data

the following two built in sprocs can be safely modified using the attached ALTER PROCEDURE. This will allow Visual Studio to automatically update all of the VSLT data warehousing tables at the end of every run. I added a brief description below, but study the download to fully understand the changes I make. (NOTE: I am working on a routine that will automatically handle interval data on LoadTest2010 databases that are on versions of SQL Server older than 2012. The automatic Interval Update sprocs will not work on earlier versions. Therefore I currently do not automatically call the UpdateLoadTestIntervalTable sproc from the built in sprocs. I will post that in a future blog entry.)

  • Prc_UpdateSummaryData3 – Adds the following code at the very end of the stored procedure:

----------------------------------------------------------------
-- Added by the VSLT Reporting Tool
EXEC VSLT_prc_UpdateLoadTestCoreInfoTable
----------------------------------------------------------------

  • Prc_DeleteLoadTestRun – Adds the data tables to the list of all tables in the DB (in the proper order). It then adds the calls to delete data from the new tables using the same criteria as the main delete portion.

Warehousing tables and update sprocs.sql