Extract and analyze test data in Project Server 2013


Applies to: Project Server 2013

Topic Last Modified: 2013-12-18

Summary: Learn about extracting test data from a Project Server 2013 test environment into an Excel worksheet.

In addition to using Visual Studio Team System Test Edition itself to open and analyze data from past test runs, you should also analyze test data with more powerful tools such as Excel.

Deploying the lab environment in an isolated network is a best practice. This approach affects a SQL Server database that contains test data that is collected through Visual Studio Team System 2008 Test Edition. It will typically be in a separate network from the one that you are connected to when you perform most of the data analysis work. For this reason, it is common to have a process to move test data from the lab environment to a test analysis environment. This can be done with a SQL Server backup and recovery operation of the LoadTest database from one environment to another.

The first thing to know when you are dealing with the LoadTest database is the test run IDs for the test runs stored in the database. The GetTestRunIDs.sql file in the "Project Server 2013 Performance Lab Kit" contains a Transact-SQL query that can be used to get the list of test run IDs from the database, together with other information useful to identify the test runs.

Once you have the test run ID of a test that you want to analyze, you then have to extract relevant information from the database. The main table for that purpose is LoadTestComputedCounterSample, which contains all the data samples for performance-counter data and key performance indicators from all the test runs. A sample Transact-SQL query that can be used to extract relevant data is included in the ExtractData.sql file in the "Project Server 2013 Performance Lab Kit." This query returns several result sets that can be easily copied and then pasted into an Excel worksheet.