is there a way to call a sproc concurrently using t-sql?

Question

is there a way to call a sproc concurrently using t-sql?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
November 30, 2016 at 11:44 am

#315382

I want to load test a sproc. Is there any way to call a sproc concurrently <n> times in ssms using only t-sql? Or do I have to use an external program to do this?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Eric M Russell SSC Guru Points: 125524 More actions · Answer 1

Years ago, Adam Machanic developed a load testing tool for running either batches of T-SQL statements or stored procedure calls.

http://sqlblog.com/blogs/adam_machanic/archive/2016/01/04/sqlquerystress-the-source-code.aspx

https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress/

Also, you can leverage the WAITFOR command to execute a T-SQL batch at a specific time or pause a specified number of minutes or seconds.

For example:

WAITFOR TIME '13:25:00';

PRINT 'The time is now 1:25pm.';

WAITFOR DELAY '00:00:05';

PRINT 'It is now 5 seconds later.';

What you can do is open multiple SSMS query windows and use WAITFOR to execute your stored procedure either simultaneously at a specific HH:SS time or at various overlapping intervals.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

You can use SQL Agent to do it, by creating run-once jobs on the fly.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);