How to run stored procedure 1000 times

I have a stored procedure that I'm using to populate a table with about 60 columns. I have genereated 1000 exec statements that look like this:

exec PopulateCVCSTAdvancement 174, 2开发者_开发技巧13, 1, 0, 7365
exec PopulateCVCSTAdvancement 174, 214, 1, 0, 7365
exec PopulateCVCSTAdvancement 175, 213, 0, 0, 7365

Each time the stored procedure will be inserting anywhere from 1 to 3,000 records (usually around 2,000 records). The "server" is running desktop hardware with 4 gigs of available memory on a server OS. The problem I have is that after the first 10-15 executes of an average of 1-2 seconds each time, the next 10-15 seem to never finish. Am I doing this correctly? How should I do this?

Thanks! Top 10 waiters:

LAZYWRITER_SLEEP
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
REQUEST_FOR_DEADLOCK_SEARCH
XE_TIMER_EVENT
FT_IFTS_SCHEDULER_IDLE_WAIT
CHECKPOINT_QUEUE
LOGMGR_QUEUE
SLEEP_TASK
BROKER_TO_FLUSH
BROKER_TASK_STOP

Random thoughts:

1. Make sure your database size is large enough to take autogrow out of the equation. The default growth is 10% and it's possible that you are growing the database. This applies to both MDF and LDF.

2. As well as checking the LDF size, change the recovery model to SIMPLE too for the duration of the load

3. You may have parameter sniffing. Can you add the optimise for unknown hint to the stored proc

Wrap it in a transaction - it will load significantly faster

Turned out the cause was from a bug in my stored procedure when running with certain parameters.

Specifically, I had a cte that I joined back to another table, however, some of the join criteria from the cte contained null values and caused it to go on forever. A simple isnull() in the cte select criteria solved the problem. Thanks to all that helped.

P.S. It took 4 minutes to run once I wrapped it in a transaction.