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
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')
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.