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