Parallel Processing in SQL ?

Question

Post reply

Parallel Processing in SQL ?

SQL_By_Chance

Hall of Fame

Points: 3915
More actions
October 2, 2011 at 11:00 pm

#249237

Hi,
I wanted to know if there is any way in T -SQL where we can execute >2 queries (Insert/Delete/Update) in parallel to one another.
Thanks,
Ankit
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

cindyaz Ten Centuries Points: 1229 More actions · Answer 1

May be you can elaborate more on your requirements?

the simplest way is to execute queries in multiple query windows.

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 2

There is no requirement as such. I just wanted to know how can I execute the 2 queries together

( Insert into xyz values (1,2,3)

GO

Insert into uvw values (1,2,3)

GO

)

and not one after another.

The way we have Task in SSIS where we can execute multiple task in parallel.

Thanks for your help 🙂

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

cindyaz Ten Centuries Points: 1229 More actions · Answer 3

There has to be a mechanism to initiate both transactions at the same time. May be via a scheduled job. Even then no one can guarantee that both transactions will be executed exactly at the same time. What if the server has just one cpu? Even if there are multiple cpus/cores, you cannot define that 2 threads execute 2 queries submitted by you at the same time. It's for SQL Server db engine to decide.

prithvi.m Mr or Mrs. 500 Points: 561 More actions · Answer 4

This would mean that you need to use Degree's of Parallelism (MAXDOP). But it will complicate your code and has hardware dependency (multiple cores).

Check this to get better idea

http://msdn.microsoft.com/en-us/library/ms178065.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

prithvi.m (10/2/2011)
This would mean that you need to use Degree's of Parallelism (MAXDOP).

Err, no.

MAXDOP sets the maximum number of cores that operators in a single query can run. What the OP is asking for is to run two separate queries in parallel and the answer to that is simple - run them on two separate connections

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 6

Thanks Gila Monster for the clarification.

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

SQL_By_Chance (10/2/2011)
There is no requirement as such. I just wanted to know how can I execute the 2 queries together
( Insert into xyz values (1,2,3)
GO
Insert into uvw values (1,2,3)
GO
)
and not one after another.
The way we have Task in SSIS where we can execute multiple task in parallel.
Thanks for your help 🙂

You could:

1) Create two separate jobs and start them in parallel

2) Code a CLR procedure that runs the queries in parallel. Please note that the use of multithreading makes the assembly marked as UNSAFE.

3) (seriously) Tune the code so that it runs so fast that running serially is not an issue.

Hope this helps

Gianluca

-- Gianluca Sartori

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 8

Thanks Mr. Gianluca,

It does give me some insight. What I was looking for through this question was as to "How the SSIS tasks work at the backend during parallel execution?"

I hope your question is in line with the internal architecture of SSIS parallel task execution.

Regards,

Ankit

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

I see. Here you will find an interesting (beta) project by Adam Machanic that I think will help you with your assignment: http://sqlblog.com/files/folders/beta/entry29021.aspx.

-- Gianluca Sartori

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 10

Thanks Sir 🙂

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

MrBool.US Grasshopper Points: 18 More actions · Answer 11

Hello Ankit,

Try this post, I believe it can help.

http://mrbool.com/p/Parallel-processing-of-SQL-instructions-Part-1/22596

Pedro da Cunha