SQL Server (TSQL) - Is it possible to EXEC statements in parallel?

Question

SQL Server 2008 R2

Here is a simplified example:

EXECUTE sp_executesql N'PRINT ''1st '' + convert(varchar, getdate(), 126) WAITFOR DELAY ''000:00:10'''
EXECUTE sp_executesql N'PRINT ''2nd '' + convert(varchar, getdate(), 126)'

The first statement will print the date and delay 10 seconds before proceeding. The second statement should print immediately.

The way T-SQL works, the 2nd statement won't be evaluated until the first completes. If I copy and paste it to a new query window, it will execute immediately.

The issue is that I have other, more complex things going on, with variables that need to be passed to both procedures.

What I am trying to do is:

• Get a record
• Lock it for a period of time
• while it is locked, execute some other statements against this record and the table itself

Perhaps there is a way to dynamically create a couple of jobs?

Anyway, I am looking for a simple way to do this without having to manually PRINT statements and copy/paste to another session.

Is there a way to EXEC without wait / in parallel?

Answer 1

Yes, there is a way, see Asynchronous procedure execution.

However, chances are this is not what you need. T-SQL is a data access language, and when you take into consideration transactions, locking and commit/rollback semantics is almost impossible to have a parallel job. Parallel T-SQL works for instance with requests queues, where each requests is independent and there is no correlation between jobs.

What you describe doesn't sound at all like something that can, nor should, actually be paralellized.

Answer 2

If you want to lock a record so you can execute statements against it, you may want to execute those statements as a transaction.

To execute SQL in parallel, you need to paralellize SQL calls, by executing your SQL from within separate threads/processes in Java, C++, perl, or any other programming language (hell, launching "isql" in shell script in background will work)

Answer 3

If after reading all above about potential problems and you still want to run things in parallel, you probably can try sql jobs, put your queries in different jobs, then execute by calling the jobs like this

EXEC msdb..sp_start_job 'Job1'

EXEC msdb..sp_start_job 'Job2'