SQL Server Load testing

WARNING: I am not responsible for an out of control pack of wild honey badgers destroying your database. Harness the power of the Honey Badger at your own risk.

I wanted a quick and easy way to do some SQL Server load testing. So I came up with the Honey Badger Load Tester . It is a set of powershell scripts that will start a user-determined amount of sessions to connect and execute custom query payloads against your SQL Server database.

releaseHoneyBadgers.ps1
This is the main caller script. Here you specify a badger count…as in how many sessions would you like to generate.

$badgerCount = 15
$scriptFile = "c:\scripts\query.ps1"

$i = 1
while ($i -le $badgerCount) {
start-job $scriptFile
$i++
}

query.ps1
The query script will execute the query. The following variables can be set to meet your needs:

  • loopCount – how many times you want each honey badger to loop through the payload
  • Query1,2,3,x – Query you want each honey badger to execute. You can add as many as you want here.
  • ServerInstance – set this to your SQL Server name
  • Database – name of the database you want to connect to
/*
LOOP COUNT
Indicates how many times you want to execute the query loop. Default
is 5000 meaning it will run your query payload 5000 times
*/
$loopCount = 5000
/*
DATABASE CONNECTION INFO
Set your Database Properies here
*/
$ServerInstance = "SERVER1 "
$Database = "Db1 "
$ConnectionTimeout = 30
$QueryTimeout = 120

/*
QUERY LIST
Use this to direct your payload. You can of course add more queries here and then
call them in the while loop below.
*/
$Query1 = "SELECT * FROM Db1.[dbo].[table1]"
$Query2 = "SELECT * FROM Db1.[dbo].[table2]"
$Query3 = "SELECT * FROM Db1.[dbo].[table3]"
$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()

$i = 1
while ($i -le $loopCount) {
# QUERY 1
$cmd=new-object system.Data.SqlClient.SqlCommand($Query1,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

# QUERY 2
$cmd=new-object system.Data.SqlClient.SqlCommand($Query2,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

# QUERY 3
$cmd=new-object system.Data.SqlClient.SqlCommand($Query3,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
$ds.Tables

$i++

}

Here are some pics of the HoneyBadger in Action:

Executed the releaseHoneyBadgers.ps1 script with Badger count of 15.

Then I fired up 15 more badgers:

Laptop holding its own against the badgers:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s