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

Trigger and BCP

Working on a cludgy interface between two software vendors, I will call them Vendor A and Vendor B. Vendor B wanted a CSV file every time a certain condition happened in a Vendor A table. First thought was to write a trigger on the table in Vendor A’s database. After Insert run a query to see if the condition is met…if the condition is met then send data over to a stage table.

Then run a job every minute or so to query the stage table, run some BCP commands to extract the data in a CSV format.

TRIGGER

CREATE TRIGGER Trigger on TEST_TABLE
after insert
as
IF EXISTS (SELECT * from TEST_TABLE as c JOIN inserted AS I ON c.column = i.column where i.columnb = ‘Condition’)
BEGIN
INSERT INTO stage(column1, column2, column3) SELECT column1, column2, column3 from inserted
END
GO

Export Call

declare @cmd varchar(2000)
select @cmd = ‘bcp “select * from stage” queryout c:\audit_’+convert(varchar(8),getdate(),112)+’_’+Replace(CONVERT(CHAR(8),(CONVERT(DATETIME,CURRENT_TIMESTAMP,113)),114), ‘:’, ”)+’.csv -t, -T -c’
EXEC xp_cmdshell @cmd
GO
delete from stage

The Replace(CONVERT(CHAR(8),(CONVERT(DATETIME,CURRENT_TIMESTAMP,113)),114), ‘:’, ”) gives me miltary time…like that a lot. I will store that for later use.

Anyway, this works pretty well in testing. May need to make some tweaks before going live. I first tried running the BCP in the trigger itself, but had the trigger freeze problem that others reported and read some articles about how this is probably not the best idea. This way is not as realtime, but works well for what I need.

Changed database context to

It has been a while since my last post. Ill use the standard excuses of “i was busy” and “i’m on vacation”. Ran across an interesting error message today working on a VBScript.

That blackout portion is my database name. The script is a simple script to query a database and dump some data into a .csv. Well the error started driving me crazy. The code looked correct. I kept pouring over the code again and again looking for an answer.

Did some google searches and did not find a whole lot of information. Finally the answer hit me. While running the script I kept opening it up in a text editor. The resulting CSV is really wide and my header column data is longer than the data in the rows. So one time I opened the CSV in Excel to see the data lined up a little better.

Well I forgot to close the CSV in Excel before running the script again. The error message threw me off. Instead of saying something like access denied or permission denied, it gives me some wild context changed error.

Oh well, I closed down Excel and badda-bing.