February 10, 2010

Reporting on VSTS and external perfmon data

Given an ideal world, after any test run I would have just one centralised store of data the held all the performance related information. So, while the VSTS Load test data collation works really well,there are situations where you can’t avoid reporting across multiple data-sources.

For instance, when testing locked down servers from outside the firewall (with no option to tunnel through), the only real option is to log the counters on the server-side and get these sent back for processing and correlation with the response stats(Grant H shows you here how to re-format a binary log file into another format e.g. SQL via the ReLog tool, which is really handy for this kind of thing ). Another situation could be correlating a test run log file with some type of information (text etc) that isn’t suited to placing in a custom performance counter.

For reporting across multiple datasources I have been extremely impressed by Tableau – a data visualisation tool, but I’m sure that this could be done with other charting software too.

The key issues are –

  • creating a Union select statement that draws the results together across the two databases

  • ensuring that the timestamps play nicely together.

I had issues with an invalid (line return or somesuch) character getting appended to the perfmon date time (hence the “LEFT([CounterDateTime], LEN([CounterDateTime])-1” to trim that off) and then needed to convert the formatted result into a datetime datatype for consistency with the VSTS data.

If you have the Desktop professional version of Tableau you can manage the custom SQL directly within the Tableau data connection – I only have the personal version, so the data from the following query was drawn into excel as an intermediate step.

To get the VSTS data into a suitable form, here is a query that’ll compile a good data-set for this sort of view, it takes some trawling through the VSTS Beta 2 database schema to link this up, so here is a something to get you going....

--Select the perfmon data from the SQL database [PDB]
SELECT Convert(datetime, LEFT([CounterDateTime], LEN([CounterDateTime])-1) ) AS time_stamp, ObjectName, CounterName, CounterValue
FROM [PDB].[dbo].[CounterData] Inner join [PDB].[dbo].CounterDetails on [CounterDetails].CounterID = [CounterData].CounterID


--Select the VSTS data from the load test database [2010ResultsDB]
Select pd.TimeStamp as time_stamp, wrm.RequestUri as ObjectName, 'Response Time' as CounterName, pd.ResponseTime as CounterValue from [2010ResultsDB].dbo.LoadTestPageDetail as pd
Inner Join [2010ResultsDB].dbo.WebLoadTestRequestMap as wrm on wrm.RequestId = pd.PageId
Where pd.LoadTestRunId =
::your test run id here::
order by time_stamp Asc

Here you can see the response time stats and (from VSTS) against the server CPU from perfmon.

If you have a lot of data it can really pay to create the suggested Tableau data extract which will speed up the rate at which the display refreshes when you dig around the results with the visualisations.