Collecting daily LT Metrics

Last night I wrote a stored procedure to automagically collect the latest LT results and insert the values into a database that I created on my local SQL Server 2005 Express.

This SP finds the latest LT that has been run, crunches the numbers and inserts them into two tables, LoadTestHistory and LoadTestHistoryDetails. I used the GUID generated by VS2008 in the Load Test Repository Store as the primary/foreign key between the two tables.

It's a bit rough and could probably use some more polish:

   1:  set ANSI_NULLS ON

   2:  set QUOTED_IDENTIFIER ON

   3:  GO

   4:  ALTER PROCEDURE [dbo].[UpdateLoadTestHistory]

   5:  AS

   6:  BEGIN

   7:   

   8:    SET NOCOUNT ON;

   9:   

  10:    declare @AlreadyExists    as int

  11:   

  12:    declare @latestLoadTest   as int

  13:    declare @loadTestGUID     as nvarchar(36)

  14:    declare @StartTime        as datetime

  15:    declare @EndTime          as datetime

  16:    declare @Duration         as int

  17:   

  18:    select @latestLoadTest = max(LoadTestRunId)

  19:    from LoadTest.dbo.LoadTestRun

  20:    where EndTime is not null

  21:   

  22:    select @loadTestGUID   = RunId,

  23:           @StartTime      = StartTime,

  24:           @EndTime        = EndTime,

  25:           @Duration       = RunDuration

  26:    from LoadTest.dbo.LoadTestRun

  27:    where LoadTestRunId = @latestLoadTest

  28:   

  29:    select @AlreadyExists = count(*)

  30:    from dbo.LoadTestHistory

  31:    where LoadTestGUID = @loadTestGUID

  32:   

  33:    if (@AlreadyExists = 0) 

  34:      begin

  35:   

  36:        begin transaction

  37:   

  38:        begin try

  39:   

  40:          insert into dbo.LoadTestHistory

  41:          values (@LoadTestGUID,

  42:                  @StartTime,

  43:                  @EndTime,

  44:                  @Duration)

  45:   

  46:          SELECT @loadTestGUID                                as LoadTestGUID,

  47:                 WebLoadTestTransaction.TransactionName, 

  48:                 LoadTestTransactionSummaryData.Average, 

  49:                 STDEV(LoadTestTransactionDetail.ElapsedTime) as StdDev, 

  50:                 LoadTestTransactionSummaryData.Minimum, 

  51:                 LoadTestTransactionSummaryData.Maximum, 

  52:                 LoadTestTransactionSummaryData.Percentile90, 

  53:                 LoadTestTransactionSummaryData.Percentile95, 

  54:                 LoadTestTransactionSummaryData.TransactionCount

  55:          INTO   #tmpLoadTestHistory

  56:          FROM   LoadTest.dbo.LoadTestTransactionDetail INNER JOIN

  57:                 LoadTest.dbo.WebLoadTestTransaction ON LoadTest.dbo.LoadTestTransactionDetail.LoadTestRunId = LoadTest.dbo.WebLoadTestTransaction.LoadTestRunId AND 

  58:                 LoadTest.dbo.LoadTestTransactionDetail.TransactionId = LoadTest.dbo.WebLoadTestTransaction.TransactionId INNER JOIN

  59:                 LoadTest.dbo.LoadTestTransactionSummaryData ON LoadTest.dbo.WebLoadTestTransaction.LoadTestRunId = LoadTest.dbo.LoadTestTransactionSummaryData.LoadTestRunId AND 

  60:                 LoadTest.dbo.WebLoadTestTransaction.TransactionId = LoadTest.dbo.LoadTestTransactionSummaryData.TransactionId

  61:          WHERE  (LoadTestTransactionDetail.LoadTestRunId = @latestLoadTest)

  62:          GROUP BY WebLoadTestTransaction.TransactionName, LoadTestTransactionSummaryData.Average, LoadTestTransactionSummaryData.Minimum, 

  63:                   LoadTestTransactionSummaryData.Maximum, LoadTestTransactionSummaryData.Percentile90, LoadTestTransactionSummaryData.Percentile95, 

  64:                   LoadTestTransactionSummaryData.TransactionCount

  65:          ORDER BY WebLoadTestTransaction.TransactionName

  66:   

  67:          insert dbo.LoadTestHistoryDetails

  68:          select LoadTestGUID, TransactionName, Average, StdDev, Minimum, Maximum, Percentile90, Percentile95, TransactionCount

  69:          from #tmpLoadTestHistory

  70:   

  71:          drop table #tmpLoadTestHistory

  72:   

  73:          commit transaction

  74:        end try

  75:   

  76:        begin catch

  77:          rollback transaction

  78:        end catch

  79:      end

  80:  END

And it works like a champ!

I tested it with this bit of SQL:

   1:  use LoadTestResults

   2:  go

   3:   

   4:  truncate table dbo.LoadTestHistory

   5:  truncate table dbo.LoadTestHistoryDetails

   6:  go

   7:   

   8:  dbo.UpdateLoadTestHistory

   9:  go

  10:   

  11:  select dbo.LoadTestHistory.LoadTestGUID, 

  12:         StartTime, 

  13:         EndTime, 

  14:         Duration, 

  15:         TransactionName, 

  16:         Average, 

  17:         StdDev,

  18:         Minimum,

  19:         Maximum,

  20:         [90th],

  21:         [95th],

  22:         TransactionCount

  23:  from dbo.LoadTestHistory, dbo.LoadTestHistoryDetails

  24:  where dbo.LoadTestHistory.LoadTestGUID = dbo.LoadTestHistoryDetails.LoadTestGUID

And the results come out for all to enjoy:

   1:  LoadTestGUID                         StartTime               EndTime                 Duration TransactionName            Average          StdDev            Minimum Maximum 90th  95th  TransactionCount

   2:  0ebdf821-454f-4c50-8e3a-a82a291adb97 2008-07-03 16:21:33.280 2008-07-03 16:31:33.280 600      someTransaction.Details    3.4155221238938  0.583565372475793 1.526   4.952   4.161 4.35  226

   3:  0ebdf821-454f-4c50-8e3a-a82a291adb97 2008-07-03 16:21:33.280 2008-07-03 16:31:33.280 600      someTransaction.FirstHit   3.42468584070796 0.79565610855916  1.636   11.888  4.053 4.285 226

   4:  0ebdf821-454f-4c50-8e3a-a82a291adb97 2008-07-03 16:21:33.280 2008-07-03 16:31:33.280 600      someTransaction.LookupName 3.46306194690266 0.606631255863035 2.087   5.15    4.285 4.411 226