Contents

Running a Sql Server Workload Using Powershell

Contents

In February 2018, myself and Paul Anderton gave a presentation on how to correlate database deployments with performance issues within the context of a DevOps pipeline. We used Sentry One as our monitoring tool in a Performance Test environment so that we could catch badly performing deployments before they got to production and caused havoc. If you would like to see the recorded video, then you can download it from here: http://info.sentryone.com/partner-webinar-performance-problems

As part of this presentation we had a workload running on a workstation, which executed a couple of stored procedures repeatedly, and we’ve had some requests from people to share this script. It’s a fairly crude script created specifically for this demo, but could be reused with some tweaking.

It creates a number of background jobs, and each background job calls the stored procedure with a randomized parameter value and has a randomized delay between each run.

The code for it can be downloaded here:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
<#
.SYNOPSIS
Execute a stored procedure multiple times to simulate a crude workload

.DESCRIPTION
To easily create a workload by running a stored procedure multiple times in parallel, with optional randomized delays.

.PARAMETER InstanceName
The SQL Server instance name to run on.

.PARAMETER DatabaseName
The database where the stored procedure is located.

.PARAMETER StoredProcName
The name of the stored procedure to execute including the schema name

.PARAMETER ParamName
The name of the parameter to pass to the proc

.PARAMETER Threads
Number of parallel executions of the proc to run at the same time

.PARAMETER MinId
The minimum value to pass to the parameter defined in ParamName. This defines the lower bound of a randomizer

.PARAMETER MaxId
The maximum value to pass to the parameter defined in ParamName. This defines the upper bound of a randomizer

.PARAMETER MinDelay
The lower bound of the delay between executions in milliseconds

.PARAMETER MaxDelay
The upper bound of the delay between executions in milliseconds

.PARAMETER IterateSeconds
The number of seconds to run the procs in a loop before stopping

.NOTES
This proc is quite a crude way to run a simple single-valued stored procedure in a loop for demo purposes.
It could be extended to accept a hashtable of parameters.


Author: Mark Allison, Sabin.IO <home@markallison.co.uk>

.EXAMPLE
.\ExecProc_SQLClient.ps1 `
    -InstanceName "SQL01" `
    -DatabaseName "WideWorldImporters" `
    -StoredProcName "Sales.GetBasket" `
    -ParamName BasketID `
    -Threads 20 `
    -MinId 1 `
    -MaxId 1000 `
    -MinDelay 10 `
    -MaxDelay 150 `
    -IterateSeconds $IterateSeconds

    This will run proc Sales.GetBasket with a BasketId ranging between 1 and 1000, with 20 parallel threads as background tasks. There will be a randomized delay on each thread varying between 10 and 150ms.
#>
   [cmdletbinding()]
param (
    [string][Parameter(Mandatory=$true)]$InstanceName,
    [string][Parameter(Mandatory=$true)]$DatabaseName,
    [string][Parameter(Mandatory=$true)]$StoredProcName,
    [string][Parameter(Mandatory=$true)]$ParamName,
    [string][Parameter(Mandatory=$true)]$Threads,
    [string][Parameter(Mandatory=$true)]$minId,
    [string][Parameter(Mandatory=$true)]$maxId,
    [string][Parameter(Mandatory=$false)]$minDelay=10,
    [string][Parameter(Mandatory=$false)]$maxDelay=100,
    [int]$IterateSeconds # number of Seconds to run in a loop
)

for ($i=1; $i -le $Threads; $i++) {
    Start-Job -ScriptBlock {
        $StartTime = Get-Date
        $conn = New-Object System.Data.SqlClient.SqlConnection
        $conn.ConnectionString = "Data Source=$($using:InstanceName);Initial Catalog=$($using:DatabaseName);Integrated Security=SSPI;Application Name=PowerShell.SabinDataLoader;"
        $conn.Open()
        do {

            $id = (Get-Random -Minimum $using:minId -Maximum $using:maxId)
            $SleepMs = (Get-Random -Minimum $using:minDelay -Maximum $using:maxDelay)
            $cmd = $Null
            $cmd = New-Object System.Data.SqlClient.SqlCommand
            $cmd.connection = $conn
            $cmd.CommandType = [System.Data.CommandType]::StoredProcedure
            $cmd.Parameters.AddWithValue("@$($using:ParamName)",$id) | Out-Null
            $cmd.commandtext = $using:StoredProcName
            $result = $cmd.executenonquery()
            Start-Sleep -Milliseconds $SleepMs
        }
        While ((Get-Date) -le $StartTime.AddSeconds($using:IterateSeconds))
        $conn.close()
    }
}
#Get-Job | Wait-Job
#Get-Job | Remove-Job

For example if you want to execute a procedure called Sales.GetBasket in parallel over 20 processes for an hour: then run it like so:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
.\ExecProc_SQLClient.ps1 `
    -InstanceName "SQL01" `
    -DatabaseName "WideWorldImporters" `
    -StoredProcName "Sales.GetBasket" `
    -ParamName BasketID `
    -Threads 20 `
    -MinId 1 `
    -MaxId 1000 `
    -MinDelay 10 `
    -MaxDelay 150 `
    -IterateSeconds 3600

The output will look something like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Id     Name            PSJobTypeName   State         HasMoreData     Location             Command
--     ----            -------------   -----         -----------     --------             -------
1      Job1            BackgroundJob   Running       True            localhost            ...
3      Job3            BackgroundJob   Running       True            localhost            ...
5      Job5            BackgroundJob   Running       True            localhost            ...
7      Job7            BackgroundJob   Running       True            localhost            ...
9      Job9            BackgroundJob   Running       True            localhost            ...
11     Job11           BackgroundJob   Running       True            localhost            ...
13     Job13           BackgroundJob   Running       True            localhost            ...
15     Job15           BackgroundJob   Running       True            localhost            ...
17     Job17           BackgroundJob   Running       True            localhost            ...
19     Job19           BackgroundJob   Running       True            localhost            ...
21     Job21           BackgroundJob   Running       True            localhost            ...
23     Job23           BackgroundJob   Running       True            localhost            ...
25     Job25           BackgroundJob   Running       True            localhost            ...
27     Job27           BackgroundJob   Running       True            localhost            ...
29     Job29           BackgroundJob   Running       True            localhost            ...
31     Job31           BackgroundJob   Running       True            localhost            ...
33     Job33           BackgroundJob   Running       True            localhost            ...
35     Job35           BackgroundJob   Running       True            localhost            ...
37     Job37           BackgroundJob   Running       True            localhost            ...
39     Job39           BackgroundJob   Running       True            localhost            ...
41     Job41           BackgroundJob   Running       True            localhost            ...
43     Job43           BackgroundJob   Running       True            localhost            ...
45     Job45           BackgroundJob   Running       True            localhost            ...
47     Job47           BackgroundJob   Running       True            localhost            ...

Send a tip. Try it!