Running multiple queries at the same time.

8

May 24, 2017 by Kenneth Fisher

I read an interesting question today. Someone wanted to be able to run the same stored procedure multiple times, at the same time, but with different parameters each time. One way to do it would be to open multiple query windows, and run them all as close to at the same time as possible. But with more than a few queries running at a time that gets to be a pain really fast. The easiest way to do this is to use code of some type. T-SQL isn’t going to work because we need these to run asynchronusly and not synchronously (all at the same time not one after the other) and T-SQL doesn’t have that option. You could create a bunch of agent jobs but again, huge pain when you get past a small number of queries at once.

Powershell is the code of choice these days so I figured I would give that a shot. I did a post a little while back about creating a bunch of SQL connections at the same time. That time it was the same command running over and over again. This time we want a different command each time. Close though. With just a few modifications I had what was needed.

T-SQL to set up a test stored procedure

CREATE TABLE LogMe (String varchar(50));
GO
CREATE PROCEDURE sp_LogMe (@String varchar(50)) AS
BEGIN
	INSERT INTO LogMe VALUES (@String);
END;

Powershell to run the stored procedure multiple times at the same time

#This script creates a number of connections (one per entry in 
#the $Commands array) to a SQL Server instance ($Server) and database ($DBName)

#Set Initial collections and objects    
$Server= "(local)\sql2016cs" ; #Server to connect to
$DBName = "Test" ; #Database to connect to

#The individual commands you want to run. In this case the same SP run
#with a different parameter each time.
$Commands = @()
$Commands += "EXEC sp_LogMe 'a'"
$Commands += "EXEC sp_LogMe 'b'"
 
#Loop through commands array, create script block for establishing SMO connection/query
#Start-Job for each script block
foreach ($sql in $Commands ) {
 
#All of that extra information after "Smo" tells it to load just v13 
#(for when you have multiple versions of SQL installed.)  Note: V13 is 2016.
 $cmdstr =@"
`Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=$(13).0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
`[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
`$SqlConn = New-Object Microsoft.SqlServer.Management.Smo.Server ("$Server")
`$SqlConn.Databases["$DBName"].ExecuteNonQuery("$sql")
"@
 
#Uncomment the next like to print the command string for debugging
# $cmdstr
#Execute script block in jobs to run the command asyncronously
$cmd = [ScriptBlock]::Create($cmdstr)
Start-Job -ScriptBlock $cmd
}

8 thoughts on “Running multiple queries at the same time.

  1. Estes, Chad says:

    Why not open multiple query windows and do something like:

    DECLARE @SomePointInTheNearFuture AS TIME = ’10:00:00.000′
    WAITFOR TIME @SomePointInTheNearFuture;
    EXECUTE YourStoredProcedureHere @p1=1, @p2=2, @p3=’etc.’

    Onward and Upward!

  2. Hey Kenneth!
    Any particular reason that you have Add-Type and LoadWithPartialName() there?
    Not nitpicking, actually curious 🙂
    Cheers!

    • It was the code I found online to fix a problem I was having with using SMO. My understanding is I needed it because I have multiple versions of SMO on my machine but lord knows I’m no expert.

      • Ah cool, you had me freaking out for a bit about some strange edge case 🙂
        I believe that it’s one or the other – and LoadwithPartialName() is depreciated – but wasn’t sure. Hence why I asked.
        Thanks for the response and the blog posts 🙂

  3. Sounds nice, this may actually help me for a few test, I will probably refactor to take some scripts on a folder and run them randomly to regenerate a load.

  4. Ernest Libertucci says:

    Service broker would be another option for solving this.

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: