Thank this author by sharing:
By Kristian Wedberg,
You need to run a number of benchmarks, testing each with different parameters
sizes, not to mention algorithms, while documenting run times and run parameters, over
night unattended operation is a given, and just about now your task is getting
more than a bit painful, and (hey, it sure has happened to me), if you're not
fully automated, by now you're starting to loose some run results out of either
exhaustion or boredom.
To the rescue, this Swiss Army Knife Gizmo provides a framework that gives
you a compact way to specify what needs to happen, and then executes the
benchmarks accordingly. In keeping with its Swissness, it can of course be
equally useful for any production work that has similar requirements. Let's cut
our teeth on
The solution builds upon a previous article, so if the
Getting silly for loop doesn't ring a bell, you probably want to check that
out. The reusable gizmo consists of a for loop and the Logic script:
NoBitsSet = 0
ExitLoop = 1
Benchmark = 2
Public Sub Main()
If FSM.Empty Then
FSM.AddState("Benchmark", Bits.Benchmark, "NumberOfRecords", 100000)
FSM.AddState("Benchmark", Bits.Benchmark, "NumberOfRecords", 500000)
FSM.ExecuteState() ' Perform the actions specified for the current state
Dts.TaskResult = Dts.Results.Success
On package execution:
2005-09-08 17:47:01.000000,2005-09-08 17:47:01.667174,"Start",0,,""
2005-09-08 17:47:01.000000,2005-09-08 17:47:01.667174,"RunInfo",0,,"Article final run."
2005-09-08 17:47:01.000000,2005-09-08 17:47:01.667174,"BenchmarkInfo",0,,"Run a script
twice with different values for the variable NumberOfRecords."
2005-09-08 17:47:01.000000,2005-09-08 17:47:01.667174,"SystemInfo",0,,"Windows XP SP2.
SQL Server 2005 Developer Edition, June CTP. Single CPU AMD 1800+. 1.5GB memory.
Data and log files on a single disks, simple recovery model."
2005-09-08 17:47:01.000000,2005-09-08 17:47:03.910400,"Benchmark",100000,2.2231968,""
2005-09-08 17:47:01.000000,2005-09-08 17:47:05.662920,"Benchmark",500000,1.75252,""
2005-09-08 17:47:01.000000,2005-09-08 17:47:05.662920,"FinishElapsed",500000,4.66292,""
Tip: The results are easy to import into other tools, such as
Excel, for further processing and presentation, although you might need to
adjust the date format to match with what your tool expects.
This simulates a more realistic set of benchmarks, with placeholders for
creating and deleting test data, resetting the environment between benchmarks,
and varying multiple benchmark parameters, including using a property expression
on a key data flow property.
Laid out serially without reuse, this still fairly simple example would
require 53 tasks!
Enum Bits ' Separate the harness states from your benchmark states
NoBitsSet = 0
ExitLoop = 1
Initialize = 2
Cleanup = 4
ResetEnvironment = 8
FirstBenchmark = 1024
SecondBenchmark = 2048
Public Sub Main()
If FSM.Empty Then
Dim records, bufferRows As Int32
For records = 100000 To 1000000 Step 300000
FSM.AddState(Nothing, Bits.Initialize, "NumberOfRecords", records)
For bufferRows = 20000 To 200000 Step 90000
FSM.AddState("FirstBenchmark", Bits.FirstBenchmark, _
As we've seen, very little effort is needed to configure, run and output
benchmark information. If you're absolutely dying to start using this,
skip ahead and download the gizmo, otherwise check out
the gory details making this possible, i.e. the
NB: The word State is sprinkled throughout the code - a state can be
seen simply as one iteration of the for loop, together with the variables to set
etc. for that iteration.
The Logic script needs to remember settings between invocations by the
for loop. The script task does not allow static script variables that keep their
value between invocations, so instead we're using a Class FSMData object
stored in a package variable called User::FSMData to keep the necessary
With StateList As New List(Of State) on the other hand, we can
generics, new in .NET v2.0, removing the need for type casting when
accessing the objects.
FSMData.StateList has one item of Class State for each state
added with FSM.AddState(), and the FSMData.CurrentState() property
is the iterator for these states.
Private Shared ReadOnly Property Data() As FSMData
If Dts.Variables("FSMData").Value.GetType().ToString() = "System.Object" Then
Dts.Variables("FSMData").Value = New FSMData()
Return CType(Dts.Variables("FSMData").Value, FSMData)
Friend Class FSMData
Private CurrentStateValue As Int32
Friend StateList As New List(Of State)
Friend StateStartTime As DateTime
Friend VariablesToLog As String()
Friend LogFilename As String
CurrentState = 0
LogFilename = Dts.Variables("ReportBaseFilename").Value.ToString() & _
Public Property CurrentState() As Int32
Set(ByVal value As Int32)
CurrentStateValue = value
Friend Class State
Friend Name As String
Friend BitMask As Bits
Friend Variables As Object()
Sub New(ByVal _n As String, ByVal _b As Bits, ByVal ParamArray _v As Object())
Name = _n
BitMask = _b
Variables = _v
In A minimal benchmark we saw how
AddState() was used to add benchmark runs and variable values. Note that the setVariables
parameter can contain many <Variable name, Variable value> pairs, even
with different value types!
ExecuteState() is responsible for calling appropriate logging
subroutines, setting package variables as specified by AddState() calls,
and incrementing the state iterator CurrentState.
' stateName = Nothing => Don't report elapsed time for this state
Public Shared Sub AddState(ByVal stateName As String, ByVal bitMask As Bits, _
ByVal ParamArray setVariables As Object())
Data.StateList.Add(New State(stateName, bitMask, setVariables))
Public Shared Sub ExecuteState()
If Data.CurrentState = 0 Then
' Log elapsed time of previous state
If Not FSM.Empty And Data.CurrentState > 0 Then
LogLine(Data.StateList(Data.CurrentState - 1).Name, _
If Data.CurrentState >= Data.StateList.Count Then
LogLine("FinishElapsed", (Now() - _
Dts.Variables("BitMask").Value = Bits.ExitLoop
Dts.Variables("BitMask").Value = Data.StateList(Data.CurrentState).BitMask
Dim i As Int32
For i = 0 To Data.StateList(Data.CurrentState).Variables.Length - 1 Step 2
.Value = Data.StateList(Data.CurrentState).Variables(i + 1)
Data.StateStartTime = Now()
Data.CurrentState += 1 ' Set the next state
That covers the core functionality, and there's just a few additional helper
routines such as the logging of info to disk, check just the
Logic script for those,
or the whole
More ways to use or extend the framework:
In short, don't leave home without it !-)
Kristian Wedberg is a Business Intelligence consultant living outside London,
UK. He can be reached at this email address: trk2061 [at] wedberg.name
Package level variable not in dts.variables in script
How to use a variable populated by a script task later in the data flow.
This week Steve Jones looks at the new TPC benchmark, TPC-VMS.
Typically, I am a big advocate of performance monitor but one place I commonly see performance monit...
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.