How Are Perfmon Results Stored

When Visual Studio collects perfmon data during a load test, it stores the data in the SQL database using four tables that allow it to normalize the data into a relational hierarchy. The four tables are:

Dbo.LoadTestPerformanceCounterCategory - holds the list of machine names and counter category names (such as Process, Memory, etc.) that were collected for each run.

Dbo.LoadTestPerformanceCounter - holds the list of counters (such as '% Processor Time' and 'Available MBytes') that were collected for each category in the LoadTestPerformanceCounterCategory table.

Dbo.LoadTestPerformanceCounterInstance – holds the list of instance names (including _Total_ or any named instance) collected for each counter in the LoadTestPerformanceCounter table. NOTE: Visual Studio defines a special named instance ('systemdiagnosticsperfcounterlibsingleinstance') that it uses for counters that do not have a named instance. A good example of this is 'Available MBytes', which does not have any named instances. Because of this, the routines below will do name substitution so that the results are compatible with perfmon.

Dbo.LoadTestPerformanceCounterSample – holds the actual recorded values for every sampling interval for each of the instances in the LoadTestPerformanceCounterInstance table.

Visual Studio uses this data, along with some built in views to build the graphs it shows as part of the load test results. It does NOT use this data to build any of the tables shown in the results (see the example below to understand what results I am describing here. For more information on the LoadTest2010 DB Schema, see this post from Sean Lumley).

I am guessing that the number of unique counters stored for the run in question is too large for the type of SQL I am using to export the results. Since I have to build a dynamic query and then pivot it, I am limited to a total of 4096 unique columns in the final set.

The best thing to try is to narrow the scope of the result set by making the instance name more specific. You'd limit the number of counters you get back that way. Maybe something like '\COMPUTERNAMEASP%' or '\COMPUTERNAMEProcess%'