How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs

The Goal

It’s often useful to be able to run a bunch of stored procedures in the background over a period of time against a test instance.

This can be nice for:

  • Demos and presentations.
  • Populating DMVs with data you can slice and dice.
  • Learning to use things like extended events and server side trace (which are much more interesting with something to look at).
  • Testing a variety of automation scripts.

This post shows you how to create several stored procedures for AdventureWorks2008R2 which will provide different result sets and have slightly different run times when run with a variety of parameters– in this case, individual letters of the alphabet.

You can then run PowerShell commands which start jobs in the background. Each job runs a stored procedure and loops through all letters of the alphabet, providing each one as a parameter. You can set the job to do that loop a configurable amount of times (the commands are set to 100). In other words, as given, each stored procedure will be run 2600 times. Since you’re running multiple jobs and they’re all going asynchronously in their own threads, you’ll have a variety of commands trying to run at the same time.

Optional: you can start the PowerShell jobs under different credentials if you need.

Alternatives: In the past, I’ve typically done things like this with T-SQL loops (often with dynamic SQL) and multiple Management Studio windows. This works OK, but it’s a little time consuming to open each window, paste everything in (or open multiple files), and start them all up. I find it much more convenient now to use scripts.

Step 1: Create Stored Procedures with a single alphabet-based parameter

Let’s get one thing clear: these procedures aren’t designed to run optimally, and they aren’t coded nicely.

You’ll notice these procedures have all sorts of problems. And that’s by design– my goals are for testing things around these, so it’s really a little better for me if they don’t play perfectly nice.

In other words, these sure ain’t for production. :)

/****************
Jump in the kiddie pool
********************/
USE AdventureWorks2008R2;
go

/****************
CREATE THE SCHEMA
********************/
IF SCHEMA_ID(N'test')  IS NULL
	EXEC sp_executesql N'CREATE SCHEMA test AUTHORIZATION dbo'
GO

/****************
CREATE Silly Stored Procedures in the Schema
********************/
IF OBJECT_ID(N'test.EmployeeByLastName', 'P') IS NULL
	EXEC sp_executesql N'CREATE PROCEDURE test.EmployeeByLastName as return 0'
GO
ALTER PROCEDURE test.EmployeeByLastName
	@lName nvarchar(255)
AS
	SELECT @lName = N'%' + @lName + N'%'

	select *
	FROM HumanResources.vEmployee
	WHERE LastName LIKE @lName
GO

IF OBJECT_ID(N'test.EmployeeByFirstName', 'P') IS NULL
	EXEC sp_executesql N'CREATE PROCEDURE test.EmployeeByFirstName as return 0'
GO
ALTER PROCEDURE test.EmployeeByFirstName
	@fName nvarchar(255)
AS
	SELECT @fName = '%' + @fName + '%'

	select *
	FROM HumanResources.vEmployee
	WHERE FirstName LIKE @fName
GO

IF OBJECT_ID(N'test.EmployeeDepartmentHistoryByLastName', 'P') IS NULL
	EXEC sp_executesql N'CREATE PROCEDURE test.EmployeeDepartmentHistoryByLastName as return 0'
GO
ALTER PROCEDURE test.EmployeeDepartmentHistoryByLastName
	@lName nvarchar(255)
AS
	SELECT @lName = N'%' + @lName + N'%'

	select *
	FROM HumanResources.vEmployeeDepartmentHistory
	WHERE LastName LIKE @lName
GO

IF OBJECT_ID(N'test.EmployeeDepartmentHistoryByFirstName', 'P') IS NULL
	EXEC sp_executesql N'CREATE PROCEDURE test.EmployeeDepartmentHistoryByFirstName as return 0'
GO
ALTER PROCEDURE test.EmployeeDepartmentHistoryByFirstName
	@fName nvarchar(255)
AS
	SELECT @fName = '%' + @fName + '%'

	select *
	FROM HumanResources.vEmployeeDepartmentHistory
	WHERE FirstName LIKE @fName
GO

IF OBJECT_ID(N'test.ProductAndDescriptionByKeyword', 'P') IS NULL
	EXEC sp_executesql N'CREATE PROCEDURE test.ProductAndDescriptionByKeyword as return 0'
GO
ALTER PROCEDURE test.ProductAndDescriptionByKeyword
	@keyword nvarchar(255)
AS
	SELECT @keyword = '%' + @keyword + '%'

	select *
	FROM Production.vProductAndDescription
	WHERE Name LIKE @keyword OR ProductModel like @keyword OR description LIKE @keyword
GO

Once you’ve got the procedures written, you just need to set up your PowerShell commands.

Step 2: Create PowerShell Jobs to Run the Procedures in Loops

These commands use PowerShell background jobs.

Even if you don’t know PowerShell, if you look at these commands you can pretty easily pick out where the 1 to 100 loop is, where the a to z loop is, and what commands are being run.

Since the jobs are running to create load in the background and I don’t care about collecting query results, I pipe the output all to Out-Null.

#test.EmployeeByLastName
Start-Job -ScriptBlock {Import-Module sqlps; foreach($_ in 1..100) {foreach ($_ in [char]"a"..[char]"z") {Invoke-Sqlcmd -Query "exec test.EmployeeByLastName '$([char]$_)'" -ServerInstance "YOURMACHINE\YOURINSTANCE" -Database AdventureWorks2008R2 | Out-Null }}}

#"test.EmployeeByFirstName"
Start-Job -ScriptBlock {Import-Module sqlps; foreach($_ in 1..100) {foreach ($_ in [char]"a"..[char]"z") {Invoke-Sqlcmd -Query "exec test.EmployeeByFirstName '$([char]$_)'" -ServerInstance "YOURMACHINE\YOURINSTANCE" -Database AdventureWorks2008R2 | Out-Null }}}

#"test.EmployeeDepartmentHistoryByFirstName"
Start-Job -ScriptBlock {Import-Module sqlps; foreach($_ in 1..100) {foreach ($_ in [char]"a"..[char]"z") {Invoke-Sqlcmd -Query "exec test.EmployeeDepartmentHistoryByFirstName '$([char]$_)'" -ServerInstance "YOURMACHINE\YOURINSTANCE" -Database AdventureWorks2008R2 | Out-Null }}}

#"test.EmployeeDepartmentHistoryByLastName"
Start-Job -ScriptBlock {Import-Module sqlps; foreach($_ in 1..100) {foreach ($_ in [char]"a"..[char]"z") {Invoke-Sqlcmd -Query "exec test.EmployeeDepartmentHistoryByLastName '$([char]$_)'" -ServerInstance "YOURMACHINE\YOURINSTANCE" -Database AdventureWorks2008R2 | Out-Null }}}

#"test.ProductAndDescriptionByKeyword"
Start-Job -ScriptBlock {Import-Module sqlps; foreach($_ in 1..100) {foreach ($_ in [char]"a"..[char]"z") {Invoke-Sqlcmd -Query "exec test.ProductAndDescriptionByKeyword '$([char]$_)'" -ServerInstance "YOURMACHINE\YOURINSTANCE" -Database AdventureWorks2008R2 | Out-Null }}}

Each command will start an asynchronous background job.

Step 3: Manage Jobs (if needed)

Once the jobs are running in the background, you may want to check on their status. You can do so by running:

get-job

if you want to remove a job from the list, you can use Remove-Job with the job number, or you can remove all jobs (whether or not they are running) with:

Remove-Job * -Force

If you want to see the output of a job, you can use Receive-Job– supply the jobnumber. If you’re troubleshooting and want to see errors, you probably want to remove | Out-Null from the command that starts the job, and use a fewer number of loops. Then you can can receive the job’s output and see any errors.

Receive-Job JOBNUMBER

Tags:

About Kendra Little

Kendra specializes in high availability and performance tuning. She is a Microsoft Certified Master in SQL Server-- the highest technical SQL Server Certification available. Kendra loves databases and software development more than long walks on the beach. Those cartoons in her blog posts? She draws 'em all. You should follow Kendra on Twitter: http://twitter.com/kendra_little

10 Responses to “How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs”

  1. Claire March 2, 2011 at 7:33 am #

    Oh PowerShell–what can’t you do? This looks much nicer than running T-SQL loops!
    Also, I’m digging the robot on the right :)

    • Kendra Little March 2, 2011 at 8:52 am #

      Powershell, as far as I’m aware, can’t whistle. At least not without recording files, and that’s cheating.

  2. Jay December 14, 2011 at 2:27 pm #

    Hi Kendra, Thanks for the post If I am calling the invoke-sqlcmd using the for-each loop is connection to sql server opened and closed every singe time?
    Thanks

  3. basaj November 21, 2012 at 9:20 am #

    Hi Kendra,

    Thanks for the post
    I try something similar just for fun, but I failed:

    start-job -InitializationScript {Add-PSSnapin SqlServerCmdletSnapin100} -Scriptblock {Invoke-Sqlcmd -Query “select getdate()” -ServerInstance “SQL2000″ -Database “master” | Out-Null}

    The background job runs and running indefinitely. I run sql profiler the query does not even reach the server. Of course I manage to run the scriptblock alone, so I reach the server. The background session is also aware of the invoke-sqlcmd. I checked it by putting the get-command invoke-sqlcmd in the scriptblock.

    Thanks Jozsef

Trackbacks/Pingbacks

  1. Something for the Weekend – SQL Server Links 04/03/11 | John Sansom - SQL Server DBA in the UK - March 4, 2011

    [...] How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs – I just LOVE automation, as regular readers will know already and this week Kendra Little (Blog|Twitter) is sharing a real treat with us. Great stuff, who knew automation could be this much fun. I’m off to annoy the Developers now by using this technique to bring their servers to a grinding┬áhalt, not that they need my help of course. [...]

  2. @Kendra_Little posts How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs | sqlmashup - March 7, 2011

    [...] @Kendra_Little posts How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs Posted on March 7, 2011 by sqlmashup http://www.littlekendra.com/2011/03/02/poweshelljobs/ [...]

  3. Something for the Weekend – SQL Server Links 04/03/11 - March 5, 2012

    [...] How To: Automate Continuous SQLServer Activity with Stored Procedures and Powershell Jobs – I just LOVE automation, as regular readers will know already and this week Kendra Little (Blog|Twitter) is sharing a real treat with us. Great stuff, who knew automation could be this much fun. I’m off to annoy the Developers now by using this technique to bring their servers to a grinding┬áhalt, not that they need my help of course. [...]

  4. Generating Concurrent Activity | Michael J. Swart - January 23, 2014

    […] should be able to do this in one line right? Yep. Adapted from a post from Kendra Little I came up with […]

  5. Generating Concurrent Activity - SQL Server - SQL Server - Toad World - January 23, 2014

    […] should be able to do this in one line right? Yep. Adapted from a post from Kendra Little I came up with […]

Leave a Reply