Dissecting the Visual Studio Load Test Results Database (Part 4–Building Better Views)


In the previous posts of this series, I focused on showing you the built in features of the Load Test Results Database. The next few posts will focus on customizing and extending the database.

The Mystery of the Perfmon Page Counters

There are two ways you can look at the details for pages, transactions, etc in the results DB. You can look at the detailed timing tables or you can look at the perfmon data (see this post for details on the differences). Each has a distinct advantage. However, getting data from the perfmon counters can be tricky since the database combines two distinct columns into one for the Instance value.

When I customize my reporting, I use a lot of stored procedures that rely heavily on pulling data from perfmon. In order to make this easier, I create a view that exposes the counters in a way that allows me to use the perfmon values the same way I would use the Page Details. Below is the code for creating the view (note, in the attached zip, I have included some other views that will get used in future posts.

CREATE VIEW VSLT_View_PageNamesWithIDs AS
    SELECT a.LoadTestRunId
        , c.ScenarioId
        , ScenarioName
        , a.TestCaseId
        , TestCaseName
        , RequestId
        , RequestUri
        ,RequestUri + '('
            + RIGHT ('000'
            + CAST(RequestId AS nvarchar(3)), 3)
           + ')' AS RequestNameWithId
    FROM dbo.WebLoadTestRequestMap AS a

    INNER JOIN LoadTestCase AS b
        ON a.LoadTestRunId = b.LoadTestRunId
        AND a.TestCaseId = b.TestCaseId

    INNER JOIN LoadTestScenario AS c
        ON b.LoadTestRunId = c.LoadTestRunId
        AND b.ScenarioId = c.ScenarioId
GO

When we use this in a query, we get the proper names and IDs to use elsewhere:

SELECT * FROM VSLT_View_PageNamesWithIDs WHERE LoadTestRunId = 4

View_PageNamesWithIds

In the table above, you can see how you get the proper request ID, which can be used to get info from the LoadTestPageDetails table, and you can determine which test or scenario the page was a part of. The key to this is to build the RequestNameWithId out of the request URI and the request ID. Visual Studio stores the page names and ID as a combined coluimn in the LoadTestPerformanceCounterInstance table. I will show how I incorporate this into creating results in the next post.

Getting Formatted Perfmon Names From The DB

The next view set I use is for populating some core tables and for Perfmon Import/Export tool. This view lays out the data in the three Perfmon naming tables in a way that is ready to add to graphs and tables, and makes adding filters to your queries easier when you are trying to get the Perfmon Instance ID that maps to the actual samples in the LoadTestPerformanceCounterSample table. The view does the typical INNER JOINs of the tables, but it also performs one other critical piece. Visual Studio uses a reserved word to indicate the instance for any counter that does not have instances available.

For example, the perfmon counter Memory/Available MBytes does not define instances. Because of this, the instance name would be NULL and that is not a good thing in this type of schema. So Visual Studio inserts the keyword systemdiagnosticsperfcounterlibsingleinstance in the instance name. In order to make items look good in reports, you need to remove that name, therefore the VIEW has the code to handle it.

Finally, the view also sets the order of the combined name in a format that is compatible with Perfmon CSV naming conventions:

Machine\Category(Instance)\Counter

Here is the code for the view:

CREATE VIEW VSLT_View_PerfmonInstanceNamesAndIds AS
    SELECT
        instance.LoadTestRunId
        ,instance.InstanceId
        ,(
            '\\' + category.MachineName
            + '\' + category.CategoryName
           +    case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
                        then ''
                        else '(' + instance.InstanceName  + ')'
                        end
            + '\' + counter.CounterName
        ) AS InstanceName
        ,category.MachineName
        ,category.CategoryName
        ,counter.CounterName
        ,(case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
                        then ''
                        else instance.InstanceName
                        end) AS Instance
        ,instance.LoadTestItemId

    FROM LoadTestPerformanceCounterCategory AS category

    INNER JOIN LoadTestPerformanceCounter AS counter
        ON category.LoadTestRunId = counter.LoadTestRunId
        AND category.CounterCategoryId = counter.CounterCategoryId

    INNER JOIN LoadTestPerformanceCounterInstance AS instance
        ON counter.CounterId = instance.CounterId
        AND counter.LoadTestRunId = instance.LoadTestRunId
GO

Here is a query that uses the view:

SELECT * FROM VSLT_View_PerfmonInstanceNamesAndIds WHERE LoadTestRunId = 4 AND CategoryName IN ('Memory','Processor')

View_PerfmonInstanceNamesWithIds

Conclusion

In the next post, I will show you some stored procedures that take advantage of these views, as well as some of the other views in the attached sql file. The sql file contains the following views:

  • VSLT_View_PerfmonInstanceNamesAndIds
  • VSLT_View_PageSummary
  • VSLT_View_PageNamesWithIDs
  • VSLT_View_TestNamesWithIDs
  • VSLT_View_TransactionNamesWithIDs

To use it, simply open it in a SQL editor (Visual Studio or SSMS) and execute it against the LoadTest2010 database.

VSLT_Views.sql

Comments (0)

Skip to main content