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

Error: 17803, Severity: 20, State: 14

Had a SQL Server go buck wild this morning. Here are some entries from the log:

2010-02-23 06:29:27.08 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:27.08 server Insufficient memory available..
2010-02-23 06:29:29.37 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:29.37 server Insufficient memory available..
2010-02-23 06:29:31.63 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:31.63 server Insufficient memory available..
2010-02-23 06:29:33.90 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:33.90 server Insufficient memory available..
2010-02-23 06:29:36.16 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:36.16 server Insufficient memory available..
2010-02-23 06:29:36.38 spid1 LazyWriter: warning, no free buffers found.
2010-02-23 06:29:36.38 spid1 Buffer Distribution: Stolen=1722 Free=0 Procedures=24
2010-02-23 06:29:36.38 spid1 Buffer Counts: Commited=2245 Target=2245 Hashed=499
2010-02-23 06:29:36.38 spid1 Procedure Cache: TotalProcs=6 TotalPages=24 InUsePages=24
2010-02-23 06:29:36.38 spid1 Dynamic Memory Manager: Stolen=1746 OS Reserved=504
2010-02-23 06:29:36.38 spid1 Global Memory Objects: Resource=692 Locks=41
2010-02-23 06:29:36.38 spid1 Query Memory Manager: Grants=0 Waiting=0 Maximum=333 Available=333
2010-02-23 06:29:38.43 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:38.43 server Insufficient memory available..
2010-02-23 06:29:40.71 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:40.71 server Insufficient memory available..
2010-02-23 06:29:42.98 server Error: 17803, Severity: 20, State: 14
2010-02-23 06:29:42.98 server Insufficient memory available..
2010-02-23 06:29:45.26 server Error: 17803, Severity: 20, State: 14

I simply gave this Server a quick reboot. Apparently he server was starved for memory. Fortunately it was a DEV server so no production down time. I am working on getting our monitoring software updated. Once that is done I will be able to fire off alerts based on memory conditions. Should be good times.

X app is slow…fix it

So an application is reported as slow. What do you check first? Here is how I checked an application today that was reported as running slow.

  1. Check the SQL Server Logs
  2. Check the Current Activity/Process Info (SQL Server 2000)
  3. Check to make sure no SQL Jobs are running
  4. Check my sysProcess Tracker. A little database/web app I wrote to go out and collect the sysprocesses table every hour for all servers. Can help show spikes or trends, but only on the hourly level. Would like to get more granular in the future.
  5. DatabaseServerPerf. Another custom solution that will go out and look at CPU, RAM and Disk for all servers on an hourly basis
  6. Then I start again from Step 1 and repeat
  7. I also fire up a Perf Mon Counter log with my presets to start collecting performance data every minute. I do this from a remote server as to hopefully limit the load on the database server. I leave the counter running well after the issue is reported as fixed so I can a clear view of the perf stats during and after. This helps me ensure my performance baseline is set appropriately.

There is  probably a tool out there or single web app that could provide a one stop shop for all this, but what fun is that.

SQL Job Monitor

Stumbled on this cool little tool yesterday: SQLJobVis. SQLJobVis is a desktop client application that allows you to view the SQL Agent jobs on your servers. You just add the server you want to connect to and it will connect and show you a timeline of the jobs. Pretty easy.

The one thing I wish the app  would do is allow you to view multiple servers. You have to view the servers one by one. It would be pretty gangster if the allowed multiple servers and then a way to export the timeline. Then you could have a timeline of all your SQL Agent jobs. Anyway, it is still a pretty cool tool and worth checking out.

http://www.sqlsoft.co.uk/

Lazywriter detailed

Based on this article from sql-serverperformance.com, I created this graphical representation the Lazywriter and how it interacts with the Memory Pool.

From SQLServerPedia.com:

The Lazy writer serves two purposes:

  1. Ensure that a specified number of buffers are free in the Buffer Pool so they can be allocated for use by the server.
  2. Monitor the usage of committed memory by the Buffer Pool and adjust it as necessary so that enough physical memory remains free to prevent Windows from paging.

The Lazy writer can adjust the number of buffers in the buffer pool if dynamic memory management is enabled. SQL Server estimates the number of necessary Buffer Pool buffers based on system activity and based on the number of stalls. A stall occurs when a request for memory has to wait on a free buffer page.

To monitor the Lazy Writer use this perf counter: SQLServer:Buffer Manager\Lazy writes/sec. This counter represents the number of buffers written by buffer manager’s lazy writer. This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Usually this should not be a high value, say more than 20 per second or so. Of course you will need to compare it with your established baseline. Ideally, it should be close to zero. If it is zero, then the buffer cache is big enough and SQL Server doesn’t have to free up dirty pages. If this value is high, then you may be experiencing a memory bottleneck.

SQL Server 2005 Alerts

Well the good news is that the issue causing SQL Server 2005 to restart itself is fixed in SP3. The bad news is that I do not have a test server to test the upgrade on. While working to get a test server setup, I decided to create some SQL Server Alerts. At least then I will know just a few moments after the event happens. Currently I would find out about the error if users reported a problem or during my daily review of the SQL Server Logs.

Here is how I setup SQL Server 2005 alerts.

1. Setup Database Mail. Just follow the wizard it will lead you to safety. When finished you can right click on Database Mail and Choose Send Test Email to make sure you have configured it properly.


2. Right Click on Database Mail and click Configure. Choose the Manage Profile Security option and click next. Make sure your public profile is set as the Default Profile.


3. Setup some operators. Give the operator a name and an email address at minimum.


4. Create your new alert. Right click alerts under SQL Server Agent and pick new alert.

  • Give your alert a name.
  • Under the Alerts will be raised based on section choose the error number.\
  • In my instance the following error message would signal the service restart: Error: 3449, Severity: 21, State: 1 So for error number I typed: 3449
  • On the response tab check the Notify Operators and choose who gets notified.
  • On the Options tab check the include alert error text in Email option. Click OK. You have now created the alert.

5. Right click on the SQL Server Agent and choose Properties. Click on the Alert System option and enable the Mail Profile. Choose Database Mail for your mail system and then choose the profile you setup in the step above. Click OK. You will then need to restart your SQL Server Agent Service.

There are tons of performance conditions and error messages you can monitor using the method above. You can also set alerts up to fire on you SQL Server Jobs. Handy if you have a job that has started failing and you want to keep a close eye on it. However if you have 100s of jobs to monitor you probably want to setup some kind of Centralized Management Server to monitor that. You do not want to go add alerts to every single one of your jobs.

Once you have the Database Mail, Operator, and Alert system setup you can explore the space and use it for all sorts of monitoring scenarios. Run wild.

Update: You can use the RAISERROR system function to test your alerts.

Did you check the ERRORLOG

Are you checking your SQL Server ERRORLOGS at least daily? If not you should. If you have 10 gazillion servers then I would suggest creating some kind of central script or repository so you can easily check them.  The other day I stumbled across the following error in one of our servers:

Error: 602, Severity: 21, State: 30.

Come to find out this error caused an application outage. Of course people were scratching their heads looking for answers when a quick inspection of the ERRORLOG would have clued them in on what happened to the server. This incident reminded me that I need to be more diligent about checking the ERRORLOGs on our critical servers throughout the day.

I wrote a handy script that I can execute using my Executor Keyboard Launcher. The script takes in a parameter and then will run a series of WMI queries against the server you pass. It is a combo between WMI calls and calls to a central database that stores some other information about the Server.  The script will also display the current ERRORLOG on the screen. The data is dumped onto a single HTML file report that I open up for a quick snapshot of the server.

The Data

  1. Ping the Server (WMI)
  2. Location of Server and IP Address (WMI/DB Query)
  3. Apps on the server (DB Query)
  4. Type Physical or Virtual (DB Query)
  5. Operating System version and build (WMI)
  6. Hard drives TotalSize/FreeSpace/%Free (WMI)
  7. Memory: Total/Free/%Free (WMI)
  8. Processor ProcNumber/Type/MaxClockSpeed (WMI)
  9. Processor Utilization %Util/Total Util (WMI)
  10. SQL Info Version/Port Number (DB Query)
  11. SQL Server Logs (VB Script Parse of physical File)

This is just the On Demand script. I also have a script that runs each morning that I check first thing when I get in. This is where I discovered the Error mentioned above.

Here are some of the Subs used in the VBScript to do the WMI calls

Disk Space

Sub DiskSpace
On Error Resume next
Set colItems = objWMIService.ExecQuery (“Select * from Win32_LogicalDisk where MediaType = 12”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if

htmlfile.Writeline (“<table style=’font-size: 10pt’ border=1><tr style=’background-color: #DDDDDD; font-weight: bold;’><td>Drive</td><td>%Free</td><td>TotalSize(MB)</td><td>FreeSpace(MB)</td></tr>”)
For Each objItem in colItems
freeSpace = Round((objItem.FreeSpace/objItem.Size) * 100, 2)
if freeSpace < 10.00 then
freeSpaceDS = “<font color=’red’>” & freeSpace & “</font>”
elseif freespace > 10.00 and freespace < 30 then
freeSpaceDS = “<font color=’orange’>” & freeSpace & “</font>”
else
freeSpaceDS = “<font color=’green’>” & freeSpace & “</font>”
end if

htmlfile.writeLine(“<tr><td>” & objItem.Name & “</td><td>” & freespaceDS & “</td><td>” & Round(((objItem.Size/1024)/1024),2) & “</td><td>” & Round(((objItem.FreeSpace/1024)/1024),2) & “</td></tr>”)
Next
htmlfile.writeLine(“</table”)
End Sub

On the Disk Space and Memory I added some color coding. If a certain threshold then write the data out in Red. This should help notify of any problem.

Processor

Sub Processor
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_Processor”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if
htmlfile.WriteLine(“<table style=’font-size: 10pt’ border=1><tr style=’background-color: #DDDDDD; font-weight: bold;’><td>Processor</td><td>Name</td><td>MaxClockSpeed</td></tr>”)
For Each objItem5 in colItems5
htmlfile.WriteLine(“<tr><td>” & objItem5.DeviceID & “</td><td>” & objItem5.Name & “</td><td>” & objItem5.MaxClockSpeed & “</td></tr>”)
Next
htmlfile.WriteLine(“</table><br>”)
End Sub

Operating System

Sub OS
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_OperatingSystem”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if
For Each objItem5 in colItems5
htmlfile.WriteLine(“OS: ” & objItem5.Caption & ” — ” & objItem5.CSDVersion & ” — ” & objItem5.BuildNumber & “<br>”)
Next
End Sub

Hardware

Sub Hardware
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_ComputerSystem”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if

For Each objItem5 in colItems5
htmlfile.WriteLine(“Hardware: ” & objItem5.Manufacturer & ” — ” & objItem5.Model & “<br>”)
Next
End Sub

Returns the Make and Model of the hardware of the server.