PowerShell to Simulate Load

Gathering metrics is quite difficult if there are no queries. So, if you’re working in non-production environments, but you still want to see some sort of load on the server, how can you do it? I use a simple PowerShell script to simulate load.

Simulate Load

I’ve posted a sample of the meat of the script before. It’s a simple way to test a procedure. However, I’ve modified it to do a little more than just run the procedure forever. Here’s the script:

# connect to the database
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 'Server=WIN-8A2LQANSO51;Database=AdventureWorks2017;trusted_connection=true'

# gather values 
$RefCmd = New-Object System.Data.SqlClient.SqlCommand
$RefCmd.CommandText = "SELECT th.ReferenceOrderID,
       COUNT(th.ReferenceOrderID) AS RefCount
FROM Production.TransactionHistory AS th
GROUP BY th.ReferenceOrderID;"
$RefCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $RefCmd
$RefData = New-Object System.Data.DataSet
$SqlAdapter.Fill($RefData)

# Execute a stored procedure
$Sniffcmd = New-Object System.Data.SqlClient.SqlCommand
$Sniffcmd.CommandType = [System.Data.CommandType]'StoredProcedure'
$Sniffcmd.CommandText = "dbo.ProductTransactionHistoryByReference"
$Sniffcmd.Parameters.Add("@ReferenceOrderID",[System.Data.SqlDbType]"Int")
$Sniffcmd.Connection = $SqlConnection

# Optionally, clear the cache
$Freecmd = New-Object System.Data.SqlClient.SqlCommand
$Freecmd.CommandText = "DECLARE @plan_handle VARBINARY(64);

SELECT @plan_handle = deps.plan_handle
FROM sys.dm_exec_procedure_stats AS deps
WHERE deps.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference');

DBCC FREEPROCCACHE(@plan_handle);"
$Freecmd.Connection = $SqlConnection

# Count the executions
$x = 0

# Run forever
while(1 -ne 0)
{
    foreach($row in $RefData.Tables[0])
        {

        # Establish an occasional wait
        $check = get-random -Minimum 7 -Maximum 20
        $wait = $x % $check

        if ($wait -eq 0)
        {
            #set a random sleep period in seconds
            $waittime = get-random -minimum 3 -maximum 13
            start-sleep -s $waittime
            $x = 0}

        # Up the count
        $x += 1

        # Execute the procedure
        $RefID = $row[0]
        $SqlConnection.Open()
        $Sniffcmd.Parameters["@ReferenceOrderID"].Value = $RefID
        $Sniffcmd.ExecuteNonQuery() | Out-Null
        $SqlConnection.Close()

        # clear the cache on each execution
        #$SqlConnection.Open()
        #$Freecmd.ExecuteNonQuery() | Out-Null
        #$SqlConnection.Close()

        # clear the cache based on random
        $check = get-random -Minimum 7 -Maximum 20
        $clear = $x % $check
        if($clear -eq 4)
        {
            $SqlConnection.Open()
            $Freecmd.ExecuteNonQuery() | Out-Null
            $SqlConnection.Close()
        }
    }
}

The idea is to be able to easily do one of several different things. By commenting out different sections of the code, I can change the general behavior. Most of the work is done in the  # Run forever section of the code.

First, I’ll randomly pick a modulus comparison. When that hits and the remainder is 0, then I randomly wait between 3 and 13 seconds. Clearly, any of these can be adjusted.

The query gets executed. Then, I have to options for dealing with the query in cache. I can clear cache on every execution. I’ve found this very useful when dealing with bad parameter sniffing (testing or generation). Or, I can use another random set of code to occasionally remove the procedure from cache.

The numbers I’ve picked are somewhat arbitrary. If you wanted to adjust things, change any of the numbers and you’ll see different behaviors. Change the start-sleep to -m instead of -s to get milliseconds.

Adding parameters is just a question of getting more from the database and then supplying them to the necessary procedure.

Just for an example, this is what the batch requests/sec look like after running the script for an hour:

Conclusion

I’ve also done expanded versions of this code with multiple procedures. It’s just a question of duplicating the core set-up as needed and then expanding the choices. It’s just a little labor to get it set up and running any way you need it to go.