Over time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large workload script that I would run using multiple sqlcmd command line windows to fire off the workload, but one of the problems with this has been that it was incredibly predictable, and it didn’t scale the way I really wanted it to.

When I was working with Distributed Replay, this became somewhat problematic with generating a randomized workload to capture a Replay Trace off of, so I took a few hours and went back to the drawing board with my idea.  What I came up with was a large script file that contains all of the SELECT statement examples from the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms187731.aspx).  This script is divided into separate sections using a delimiter, and then I wrote a PowerShell script that reads the file and breaks it down into individual scripts that are randomly executed against the configured SQL Server using SMO.

The two files required to make use of this are attached to this blog post and can be used with minimal modifications against any SQL Server 2008+ system that has the AdventureWorks2008R2 database attached to it.  To make use of the PowerShell script, you will either have to sign it, or if you work like I do in my VMs, allow unsigned script execution with Set-ExecutionPolicy Unrestricted.

The PowerShell script is incredibly simple code wise.  It loads the SMO assembly, splits the file contents on the delimiter, then inside a infinite loop, it picks a random query and executes it against the SQL Server.

# Load the SMO assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

# Set the server to run the workload against
$ServerName = "SQL2012-DB1";

# Split the input on the delimeter
$Queries = Get-Content -Delimiter "——" -Path "AdventureWorks BOL Workload.sql"

WHILE(1 -eq 1)
{
    # Pick a Random Query from the input object
    $Query = Get-Random -InputObject $Queries;

    #Get a server object which corresponds to the default instance
    $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

    # Use the AdventureWorks2008R2 database
    $srv.ConnectionContext.set_DatabaseName("AdventureWorks2008R2")

    # Execute the query with ExecuteNonQuery
    $srv.ConnectionContext.ExecuteNonQuery($Query);

    # Disconnect from the server
    $srv.ConnectionContext.Disconnect();
   
    # Sleep for 100 miliseconds between loops
    Start-Sleep -Milliseconds 100
}

To generate random workloads, I generally fire up 3-5 copies of this script on a client concurrently and leave it running in the background to generate the load. 

AdventureWorks BOL Workload.zip (6.35 kb)