Running benchmarks in SQL Server can be a tedious and time-consuming task. You often need to test different parameters, data sizes, and algorithms, while documenting the run times and parameters. This can become overwhelming, especially if you have to run the benchmarks overnight and keep track of the results manually. But fear not, there is a solution to your problem – a Swiss Army Knife Gizmo that provides a framework for automating and executing benchmarks in SQL Server.

A Minimal Benchmark

Let’s start with a simple example to demonstrate how the Swiss Army Knife Gizmo works. Suppose you want to run a benchmark twice, using different settings for the variable “NumberOfRecords”. You also want to document the runs by writing benchmark and system information, as well as elapsed times, to a CSV text file.

The solution involves using a for loop and a Logic script. The for loop iterates twice, corresponding to the two benchmark runs. The Logic script sets the value of “NumberOfRecords” for each run and outputs the necessary information to the CSV file.

Public Sub Main()
    If FSM.Empty Then
        FSM.SetVariablesToLog("NumberOfRecords")
        FSM.AddState("Benchmark", Bits.Benchmark, "NumberOfRecords", 100000)
        FSM.AddState("Benchmark", Bits.Benchmark, "NumberOfRecords", 500000)
    End If
    FSM.ExecuteState()  ' Perform the actions specified for the current state
    Dts.TaskResult = Dts.Results.Success
End Sub

On package execution, the for loop will run twice, and the Logic script will ensure that the benchmark runs with the appropriate settings for “NumberOfRecords”. The script will also output descriptive information and elapsed times to the CSV file.

A Larger Benchmark

Now let’s consider a more realistic scenario where you have multiple benchmarks with different data sizes and parameters. You also want to reset the environment between benchmarks and measure the performance of a data flow property. This example demonstrates how the Swiss Army Knife Gizmo can handle more complex benchmark requirements.

The implementation involves adding additional tasks to the Gizmo and setting up the necessary variables and precedence constraints. The for loop iterates 53 times, corresponding to the 53 benchmark runs. Each iteration involves running multiple tasks, resetting the environment, and varying the benchmark parameters.

Public Sub Main()
    If FSM.Empty Then
        FSM.SetVariablesToLog("NumberOfRecords", "DefaultBufferMaxRows")
        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(Nothing, Bits.ResetEnvironment)
                FSM.AddState("FirstBenchmark", Bits.FirstBenchmark, "DefaultBufferMaxRows", bufferRows)
                FSM.AddState(Nothing, Bits.ResetEnvironment)
                FSM.AddState("SecondBenchmark", Bits.SecondBenchmark)
            Next
        Next
        FSM.AddState(Nothing, Bits.Cleanup)
    End If
    FSM.ExecuteState()  ' Perform the actions specified for the current state
    Dts.TaskResult = Dts.Results.Success
End Sub

On package execution, the for loop will run 53 times, and the Logic script will execute the appropriate tasks and set the necessary variables for each benchmark run. The script will also output the benchmark information and elapsed times to the CSV file.

Conclusion

The Swiss Army Knife Gizmo provides a powerful framework for automating and executing benchmarks in SQL Server. It allows you to easily specify different parameters, data sizes, and algorithms, while documenting the run times and parameters. With this Gizmo, you can save time and effort by automating the benchmarking process and ensuring consistent and accurate results.

So don’t hesitate to give the Swiss Army Knife Gizmo a try in your SQL Server projects. It will make your benchmarking tasks much more manageable and efficient. Happy benchmarking!

Disclaimer: The opinions and comments expressed in this article are solely those of the author and do not necessarily reflect the views of the employer.