Load Test Run results can be populated in a database. Scripts to set up this database are available
here.
In order to send automated reports on new results, we can register a trigger and an xslt transformation like so:
USE PerfResults;
GO
IF OBJECT_ID ('dbo.LoadTestRunCompleted','TR') IS NOT NULL
DROP TRIGGER dbo.LoadTestRunCompleted;
GO
CREATE TRIGGER dbo.LoadTestRunCompleted
ON LoadTestRun
AFTER UPDATE
AS
DECLARE @RunID uniqueidentifier;
SELECT TOP 1 @RunID = RunID from LoadTestRun where Outcome = 'Completed' order by EndTime desc;
DECLARE @msg nvarchar(max);
SELECT @msg = dbo.fn_DisplayLoadTestReport(@RunID);
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Performance Reports Administrator',
@recipients = 'user@xyz.com',
@body_format='HTML',
@body = @msg,
@subject = 'Automated Performance Reports' ;
GO
-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function fn_DisplayLoadReport.SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
Ravishankar Rajamani
-- Create date: 15th July, 2013
-- Description:
function to render an html for summary display
-- =============================================
CREATE FUNCTION dbo.fn_DisplayLoadTestReport
(
-- Add the parameters for the function here
@RunId uniqueidentifier
)
RETURNS nvarchar(MAX)
AS
BEGIN
DECLARE @name nvarchar(255)
DECLARE @description nvarchar(255)
DECLARE @starttime datetime
DECLARE @endtime datetime
DECLARE @warmuptime int
DECLARE @runduration int
DECLARE @controller nvarchar(255)
DECLARE @runsettings nvarchar(255)
SELECT @name = LoadTestName, @description = Description, @starttime = StartTime, @endtime = EndTime, @warmuptime = (WarmupTime/60) , @runduration = (RunDuration/60), @controller = ControllerName, @runsettings = RunSettingUsed from LoadTestRun where RunId = @RunId
DECLARE @ret nvarchar(max)
SET @ret = N'<head>
<style type="text/css">
#fullwidth
{
width:100%;
clear:both
}
#leftcol
{
width:49%;
float:left;
clear:all;
position:relative;
}
#rightcol
{
width:49%;
float:right;
position:relative;
}
.pagetitle
{
font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif;
font-size: .8em;
font-weight: bold;
}
.bodytext
{
font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif;
font-size: .7em;
font-weight: normal;
}
.sectiontitle
{
font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif;
font-size: .7em;
font-weight: bold;
text-align: left;
padding-top: 1.5em;
}
.content table
{
width: 100%;
border-top: 1px solid #D4D0C8;
border-right: 1px solid #D4D0C8;
}
.content td
{
font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif;
font-size: .7em;
text-align: left;
border-bottom: 1px solid #D4D0C8;
border-left: 1px solid #D4D0C8;
padding-top: .2em;
padding-left: .3em;
}
.content th
{
font-family: Tahoma, Verdana, Arial, Helvetica, sans-serif;
font-size: .7em;
font-weight: normal;
text-align: left;
border-bottom: 1px solid #D4D0C8;
border-left: 1px solid #D4D0C8;
background-color: #ECE9D8;
padding-top: .2em;
padding-left: .4em;
}
</style>
<script language="JavaScript">
function clickHandler()
{
var targetId, srcElement, targetElement;
srcElement = window.event.srcElement;
if (srcElement.className == "Expandable")
{
targetId = srcElement.id + "div";
targetElement = document.all(targetId);
if (targetElement.style.display == "none")
{
targetElement.style.display = "";
srcElement.innerText = "6";
} else {
targetElement.style.display = "none";
srcElement.innerText = "4";
}}}
document.onclick = clickHandler
</script>
</head>
<body bgcolor="#ffffff">
<div class="pagetitle">Load Test Summary</div>
<div id="leftcol">
<div class="sectiontitle">Test Run Information</div>
<div class="content">
<table cellspacing="0" id="TestRunInformation">
<tr>
<td nowrap width="50%">Load test name</td>
<td nowrap width="50%">' + @name + '</td>
</tr>
<tr>
<td nowrap width="50%">Description</td>
<td nowrap width="50%">' + @description + ' </td>
</tr>
<tr>
<td nowrap width="50%">Start time</td>
<td nowrap width="50%">' + CONVERT(nvarchar, @starttime) + '</td>
</tr>
<tr>
<td nowrap width="50%">End time</td>
<td nowrap width="50%">' + CONVERT(nvarchar, @starttime) + '</td>
</tr>
<tr>
<td nowrap width="50%">Warm-up duration</td>
<td nowrap width="50%">' + CONVERT(nvarchar, @warmuptime) + '</td>
</tr>
<tr>
<td nowrap width="50%">Duration</td>
<td nowrap width="50%">' + CONVERT(nvarchar, @runduration) + '</td>
</tr>
<tr>
<td nowrap width="50%">Controller</td>
<td nowrap width="50%">' + @controller + '</td>
</tr>
<tr>
<td nowrap width="50%">Run settings used</td>
<td nowrap width="50%">' + @runsettings + '</td>
</tr>
</table>
</div>
</div>
';
RETURN @ret;
END
GO
Remember to set the content-type of the message as
Content-Type: multipart/mixed
so that it can be displayed as desired.
