SQL Server (Tsql) - How to Exec Statements in Parallel

How can I execute an Sql query, but at the same time 100 of it in parallel?

You can use sqlcmd and put it in a batch to spawn 100 parallel processes. As an alternative you can use LinqPad and a simple C# script that uses Tasks and, too, spawns 100 processes.

If you want to have a parallel stored procedure execution, then you need to setup service broker and a queue with the length of 100, but you won't be having exactly 100 parallel activations at once because service broker adds workers as workload increases, so it won't happen instantaneously.

Transact SQL parallel query execution

You don't decide what to parallelise - SQL Server's optimizer does. And the largest unit of work that the optimizer will work with is a single statement - so, you find a way to express your query as a single statement, and then rely on SQL Server to do its job, which it will usually do quite well.

If, having constructed your query, the performance isn't acceptable, then you can look at applying hints or forcing certain plans to be used. A lot of people break their queries into multiple statements, either believing that they can do a better job than SQL Server, or because it's how they "naturally" think of the task at hand. Both are "wrong" (for certain values of wrong), but if there's a natural breakdown, you may be able to replicate it using Common Table Expressions - these would allow you to name each sub-part of the problem, and then combine them together, all as part of a single statement.

E.g.:

;WITH TabA AS (
     SELECT ID FROM Table1 WHERE Name = 'A'
), TabB AS (
     SELECT ID FROM Table2 WHERE Name = 'B'
)
SELECT ID FROM TabA UNION ALL SELECT ID FROM TabB

And this will allow the server to decide how best to resolve this query (e.g. deciding whether to store intermediate results in "temp" tables)

---

Seeing in one of your other comments you discussing about having to "work with" the intermediate results - this can still be done with CTEs (if it's not just a case of you failing to be able to express the "final" result as a single query), e.g.:

;WITH TabA AS (
   SELECT ID FROM Table1 WHERE Name = 'A'
), TabAWithCalcs AS (
   SELECT ID,(ID*5+6) as ModID from TabA
)
SELECT * FROM TabAWithCalcs

How can I run sql server stored procedures in parallel?

sp _ start _ job

I'm doing a similar thing at the moment, and the only way I've found to avoid using SSIS or some external shell is to split my load routine into 'threads' manually, and then fire a single master sqlagent job which in turn executes as many sp _ start _ job's as I have threads. From that point, they all run autonomously.

It's not exactly what we're looking for, but the result is the same. If you test the job status for the sub jobs, you can implement your conditional start of sproc 3 as well.

What's the point in 8 cores if we can't use them all at once?

---

Related Topics