Dissecting the Visual Studio Load Test Results Database (Part 1–Intro to the Schema)
Premier Developer Consultant Geoff Gray comes to us this week with the first post in a new series from his awesome ‘Testing the Testing Strategy’ blog. This series will focus on the structure of the Visual Studio Load Test results database and how you can derive insight on the performance of your application from this valuable data source.
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 Visual Studio Team System (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 de-normalize the data for reporting purposes. The stored procedures will be covered in the next article.