SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunny / Parallel Processing / Latest Posts

RE: Parallel Processing

michael.broennimann — Tue, 10 May 2011 15:26:51 GMT

Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).Therefore you may have a look into the approach of [url=http://www.ibax.ch/-IBX-/plain.simpleimagetitletextlinklist.en.solutions.products.parallelboost/default.aspx]SQL Parallel Boost[/url].This approach can also be used to execute multiple SQL statements in parallel.[b]UPDATE:[/b] A free Community Edition of SQL Parallel Boost can be downloaded at [url=http://sqlparallelboost.codeplex.com] CodePlex / SQL Parallel Boost[/url].A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 !In case you don't want to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks.

RE: Parallel Processing

Polon Tang — Mon, 19 Oct 2009 23:19:43 GMT

Thanks a lot.

RE: Parallel Processing

RBarryYoung — Mon, 19 Oct 2009 22:07:27 GMT

[quote][b]Polon Tang (10/19/2009)[/b][hr]I am new to T-SQL.Is there a way to spawn a pre-determined number of the processing threads from one script or stored procedure? So that, there is no need to use mutiple database connections (such as multiple SQL panes in SQL Server Management Studio).Many thanks.[/quote]Service Broker is the way to do that.You could also make multiple duplicate SQL Agent jobs with different names and then submit them simultaneously from the SSMS interface. Kind of kludgey though.

RE: Parallel Processing

Polon Tang — Mon, 19 Oct 2009 20:59:50 GMT

I am new to T-SQL.Is there a way to spawn a pre-determined number of the processing threads from one script or stored procedure? So that, there is no need to use mutiple database connections (such as multiple SQL panes in SQL Server Management Studio).Many thanks.

RE: Parallel Processing

Paul White — Fri, 16 Oct 2009 09:58:57 GMT

[quote][b]RBarryYoung (10/14/2009)[/b][hr]I have tried it Simon, and when I implemented it, SB eliminated about half of the code in your article and was considerably simpler. What SB gets rid of is all of the Event Queuing, Dequeuing and Locking worries, and code to handle all of that, it's all built in with SB.[/quote]Absolutely. While the article is a good one (full marks for effort and thoroughness!) and Simon's snippet of code is a valid use of the OUTPUT clause, both are looking to re-invent things you get for free with Service Broker.Many years ago I remember settling on READPAST and UPDLOCK for queueing - but I was never 100% happy with it as a solution. Service Broker provides a robust and scalable framework for free, so it seems daft not to take advantage.Paul

RE: Parallel Processing

Sunny-138471 — Wed, 14 Oct 2009 23:18:26 GMT

[quote][b]qiyuef (10/14/2009)[/b][hr]Can you explain why there will be a deadlocks when SQL Server doesn't perform a lock escalation even when the batch size is high?Each process will lock different sets of key(rows) , I don't understand how deadlock could happen.Thanks[/quote]HiI would request you to try out the example given in the article. It can be directly executed.If you see the keys locked during the process, you'd find that there is some key which is locked in common by both the processes. Ideally that should not happen and it doesn't happen when the batch size is as small as 5 in the example. But for a relatively bigger batch size there is a conflict.ThanksSatish More

RE: Parallel Processing

qiyuef — Wed, 14 Oct 2009 14:34:49 GMT

Can you explain why there will be a deadlocks when SQL Server doesn't perform a lock escalation even when the batch size is high?Each process will lock different sets of key(rows) , I don't understand how deadlock could happen.Thanks

RE: Parallel Processing

RBarryYoung — Wed, 14 Oct 2009 00:00:35 GMT

[quote][b]Simon Facer (10/13/2009)[/b][hr]I did originally design the system using Service Broker Queues, but that just added complexity without functionality, I finally realised that KISS applied, and stopped trying to using SB Queues just because I thought they were cool, and used a number of Agent jobs instead. You can get back as many rows as are returned in the OUTPUT clause, the processing I was designing only handles a single row /value at a time (RBAR by design), I suggest you try it, it would be easier than trying to explain!![/quote]I have tried it Simon, and when I implemented it, SB eliminated about half of the code in your article and was considerably simpler. What SB gets rid of is all of the Event Queuing, Dequeuing and Locking worries, and code to handle all of that, it's all built in with SB.

RE: Parallel Processing

Adam Seniuk — Tue, 13 Oct 2009 08:13:52 GMT

Well I have learned something new today, I think I can go home now. :D Thanks for the information.

RE: Parallel Processing

Simon Facer — Tue, 13 Oct 2009 07:47:02 GMT

[code="other"]ProcessNextServer:[/code] the : is a label identifier, in this case the label is a target of a GOTo statement - [code="other"]ProcessNextServer:----Code goes here--GOTO ProcessNextServer[/code]

RE: Parallel Processing

Adam Seniuk — Tue, 13 Oct 2009 07:39:47 GMT

I get everything in your post... but what does the colon do? -- Clear the temp table ...ProcessNextServer: DELETE #DRT_Update

RE: Parallel Processing

Simon Facer — Tue, 13 Oct 2009 05:13:28 GMT

I did originally design the system using Service Broker Queues, but that just added complexity without functionality, I finally realised that KISS applied, and stopped trying to using SB Queues just because I thought they were cool, and used a number of Agent jobs instead. You can get back as many rows as are returned in the OUTPUT clause, the processing I was designing only handles a single row /value at a time (RBAR by design), I suggest you try it, it would be easier than trying to explain!!

RE: Parallel Processing

ChiragNS — Mon, 12 Oct 2009 23:41:07 GMT

Very good article.Couple of things i would like to know about the update statement with output clause. What type of locks would be involved with update statement and how to get more than one record with the update statement since you have used MIN function.

RE: Parallel Processing

RBarryYoung — Mon, 12 Oct 2009 19:41:28 GMT

Very nice article. One suggestion would be to look into using Service Broker, as I believe that you will find that it can simplify or even enhance several aspects of your approach.

RE: Parallel Processing

Simon Facer — Mon, 12 Oct 2009 09:23:21 GMT

The article was about how to process a single data set using multiple processes. The concept is how to start multiple independant non-overlapping processes against a single table. My example shows a use of the given technique (or my alternative) that cannot be accomplished with SMP.

RE: Parallel Processing

wiltc-836148 — Mon, 12 Oct 2009 09:11:14 GMT

Simon,I see that your discussion post mentions multiple servers...But in the article in question, http://www.sqlservercentral.com/articles/Locking/67952/ only seems to be talking about one server.So what does the article's technique have that MS SQL Server doesn't out of the box?Thanks!Charles Wilt[quote][b]Simon Facer (10/12/2009)[/b][hr][p]Charles, I think you missed the point ... [/p]SQL will SMP parallel-process out of the box, automatically generating query plans to take advantage of multiple available processors as appropriate. The article was about manually setting up parallel processing tasks. In the example I gave, the parallel tasks are querying multiple independant unrelated servers simultaneously; this is not an SMP task as you were referring to.[/quote]

RE: Parallel Processing

Simon Facer — Mon, 12 Oct 2009 08:59:51 GMT

[p]Charles, I think you missed the point ... [/p]SQL will SMP parallel-process out of the box, automatically generating query plans to take advantage of multiple available processors as appropriate. The article was about manually setting up parallel processing tasks. In the example I gave, the parallel tasks are querying multiple independant unrelated servers simultaneously; this is not an SMP task as you were referring to.

RE: Parallel Processing

wiltc-836148 — Mon, 12 Oct 2009 08:22:02 GMT

Interesting article....I'm surprised at the hoops you have to jump through.On DB2 for i, all I need to do is set QQRYDEGREE for the system or for individual jobs to something besides *NONE (or *NBRTASKS 1).http://www-03.ibm.com/servers/enable/site/education/abstracts/4aea_abs.htmlFrom the above course:How SMP worksThe DB2 SMP feature provides the optimizer and database engine with additional methods and strategies for retrieving data and processing data in a parallel manner. SMP enables database parallelism on a single system or LPAR - where multiple (CPU and I/O) processors that share memory and disk resources - to work simultaneously toward achieving a single end result. This parallel processing means that the database engine can have more than one (or all) the processors working on a single query at the same time. You can significantly improve the performance of a processor-bound query with this feature on multiple-processor systems by distributing the processor load across more than one processor.Although using SMP does not require the presence of more than one processor, database parallelism is most effective when more than one physical processor is available to run the tasks or threads.Given that SMP is achieved through the use of the System i server and its i5/OS advanced architecture, table partitioning is not required for database parallelism.Charles Wilt

RE: Parallel Processing

Sunny-138471 — Mon, 12 Oct 2009 07:06:08 GMT

[quote][b]Kevin.McEvoy (10/12/2009)[/b][hr]Really interesting article.Could the clean up process use something like sp_who to find out if a process was currently running or orphaned. I'm always a bit dubious about things that depend on timings which would need to be tuned for each installation depending upon resources, data sizes etc.[/quote]Thanks Kevin. Yes I too tried digging into any such readily available command which could tell whether the process is currently active. Hope to get more leads in this forum. The UPDATE..OUTPUT statement suggested by Simon appears to be very helpful. Thanks Simon :-)ThanksSatish

RE: Parallel Processing

Simon Facer — Mon, 12 Oct 2009 06:41:18 GMT

[p]Scott was exactly right. The OUTPUT clause of the UPDATE allows us to get data back from the inserted and deleted tables (the same as we reference in DML triggers). In the case of my code snippet, I always want to process the first record in the table with Status = 1 (Ready to Process) and a StartTime < Now. I contemplated using Transactions and Setting Lock hints, but it was a lot of work compared to using the UPDATE ... OUTPUT statement. This way the record is updated to Status = 2 (In Process) and the row ID value returned to the code in a single statement - no extended or manually coded locking required. [/p][p]BOL has a good article on the OUTPUT clause; but a couple of points - (1) you can get back any data from the inserted or deleted tables, not just a single column and (2) the INTO table has to pre-exist, hence the CREATE TABLE statement at the top of the code.[/p][p]The Code snippet is written as part of a loop (using the [i]forbidden[/i] GOTO statement - lets see who jumps on that, but that's a different discussion), hence the DELETE statement.[/p]Hope that helps.

RE: Parallel Processing

Kevin.McEvoy — Mon, 12 Oct 2009 05:22:11 GMT

Really interesting article.Could the clean up process use something like sp_who to find out if a process was currently running or orphaned. I'm always a bit dubious about things that depend on timings which would need to be tuned for each installation depending upon resources, data sizes etc.

RE: Parallel Processing

scott mcnitt — Mon, 12 Oct 2009 05:08:43 GMT

Simon's post about using the OUTPUT clause on the update becomes available for us to use in SQL 2005 databases. It is an awesome feature (Oracle has had it for a while).

RE: Parallel Processing

iermis 42311 — Mon, 12 Oct 2009 04:39:36 GMT

Can you explain the code?

RE: Parallel Processing

Simon Facer — Mon, 12 Oct 2009 04:33:15 GMT

I recently (within the last 2 years) created a system to monitor our SQL instances from a central instance. Without going into details about the whole process, I had a requirement to run multiple tasks to retrieve data from the monitored instances in parallel (one or more instances unavailable would cause delays in retrieving data from all the other instances).There's an easier way to do this than messing with lock hints, use the UPDATE ... OUTPUT format of the UPDATE query. Here's a snippet of the code that I use:[code="plain"] -- ****************************************************************************************** -- Create the local temp tables used in this proc CREATE TABLE #DRT_Update ( DataRetrievalTaskID INT) -- ****************************************************************************************** -- ****************************************************************************************** -- Clear the temp table ...ProcessNextServer: DELETE #DRT_Update -- ****************************************************************************************** -- ****************************************************************************************** -- Get the next Data Retrieval Task to be processed - set the status to 2 for the record selected. UPDATE DataRetrievalTask SET [Status] = 2 OUTPUT inserted.DataRetrievalTaskID INTO #DRT_Update WHERE DataRetrievalTaskID = (SELECT MIN(DataRetrievalTaskID) FROM DataRetrievalTask WHERE [Status] = 1 AND StartTime <= GETDATE() ) -- ******************************************************************************************[/code]This process is easily scalable - I just have to create another job to execute the Stored Proc to add another process. This way, you dont have to worry about Locks etc - the SQL Engine does it for you.

RE: Parallel Processing

iermis 42311 — Mon, 12 Oct 2009 04:02:48 GMT

Thank you all for your fast replies.:-)

RE: Parallel Processing

Sunny-138471 — Mon, 12 Oct 2009 03:52:36 GMT

[quote][b]mohd.nizamuddin (10/12/2009)[/b]I think Sunny is using this "WHERE 1 = 2" for creating the #LockHelp table only, having no data.But not sure why, because he has not refer #LockHelp in the SP.[/quote]Thanks Nizamuddin. #LockHelp is optional. You can as well have only a SELECT query instead of SELECT INTO. The only difference is that it returns an empty result set back to the SP callerThanksSatish More

RE: Parallel Processing

Sunny-138471 — Mon, 12 Oct 2009 03:16:21 GMT

Hi iermisThanks for the comments.The query you mentioned is only for getting a Table Lock on the table. We dont actually need any records from the table but at the same time we want to have the table locked from the Rest_OrphanLocks SP until the records are marked as processed. The condition "1=2" does exactly that. It does not spend time retrieving records but locks the table. #LockHelp Temporary table is optional. We could as well have a SELECT query instead of SELECT INTOThanksSatish More

RE: Parallel Processing

mohd.nizamuddin — Mon, 12 Oct 2009 03:14:30 GMT

Nice article. Keep it up.

RE: Parallel Processing

mohd.nizamuddin — Mon, 12 Oct 2009 03:12:15 GMT

[quote][b]iermis (10/12/2009)[/b][hr]As I am new to SQL i cannot understand why you are using the statementSELECT * INTO #LockHelp FROM Rainfall_Events WITH (TABLOCKX) WHERE 1=2. This statement does not fetch any rows? It must be something about the lock time, bit can you explain me why you are using "WHERE 1=2"?Thank you[/quote]I think Sunny is using this "WHERE 1 = 2" for creating the #LockHelp table only, having no data.But not sure why, because he has not refer #LockHelp in the SP.

RE: Parallel Processing

iermis — Mon, 12 Oct 2009 02:36:18 GMT

As I am new to SQL i cannot understand why you are using the statementSELECT * INTO #LockHelp FROM Rainfall_Events WITH (TABLOCKX) WHERE 1=2. This statement does not fetch any rows? I't must be somenthing about the lock time, bit can you explain me why you are using "WHERE 1=2"?Thank you

RE: Parallel Processing

iermis — Mon, 12 Oct 2009 02:30:10 GMT

This is an excellent article the gave an idea have to solve some problems that arise from the need of paraller processing. Thank you very very much.As all we want is right resource planning I show in the following article that its better to create temporary tables and use INSERT INTO instead of SELECT INTO. As the article says its a resources matter.The article is here:http://www.sqlservercentral.com/articles/Basic+Querying/temptablesinsqlserver/1279/Thank you.

Parallel Processing

Sunny-138471 — Sun, 11 Oct 2009 22:08:53 GMT

Comments posted to this topic are about the item [B]Parallel Processing[/B]