Image
{{'2018-02-15T12:19:41.8847041Z' | utcToLocalDate }}
Mark Allison

Running a SQL Server workload using PowerShell

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: https://raw.githubusercontent.com/sabinio/SentryOne/master/Scripts/DataLoad/ExecProc_SQLClient.ps1

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:

.\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:

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            ...


If you would like to download and try Sentry One, we are partners which enables you to get an extended trial! Download it here: https://www.sentryone.com/Sabin

comments powered by Disqus