Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parallel Processing


Parallel Processing

Author
Message
mr_effe
mr_effe
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 120
Hi,

I have designed and implemented a classic datawarehouse system to load data for our different stores.. It processes the data from the staging tables into the core dwh via t-sql procedures I mostly used merge syntax as well as insert as select to load the data from staging into core dwh.

The jobs works fine if i load the data for each store seperatly. But if I want to execute the load processes in parallel for each store i get a multiplicity of Locks mostly LCM_M_IX and LCM_M_S on the target tables although I have partitioned each of the tables by the StoreId and also filtering to the StoreId in the merge and insert scripts. Addtionially I've put in each procedure the command "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED".

So what can I do more? Is there perhaps any guideline what I have to do to implement a parallel processing with SQL Server 2008R2?

I look forwardto your answers!

Many thanks
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16538 Visits: 13210
You can load a data warehouse in parallel, but I would try to avoid loading a table by multiple processes at once.
Try loading different tables at the same time.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
mr_effe
mr_effe
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 120
Hi Koen,

thanks for the fast answer. But is there no other chance to load the table at the same time? Because i thought that because of the partioned tables that no locks appear on the entire table but only on the relevant partition. The lock escalation of the table is also set to "AUTO".

So you say that it isn't possible with sql server to load data into differnt partitions of one table at the same time without locks? So can you explain me what is the reason therfore?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16538 Visits: 13210
mr_effe (4/17/2014)
Hi Koen,

thanks for the fast answer. But is there no other chance to load the table at the same time? Because i thought that because of the partioned tables that no locks appear on the entire table but only on the relevant partition. The lock escalation of the table is also set to "AUTO".

So you say that it isn't possible with sql server to load data into differnt partitions of one table at the same time without locks? So can you explain me what is the reason therfore?


I'm not saying that it isn't possible (it is btw: Enabling Partition Level Locking in SQL Server 2008). Just saying it is much easier to avoid locks and load different tables.

Maybe it is MERGE that is giving troubles?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
mr_effe
mr_effe
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 120
Yes you're right. The most locks occur by the merge statements. Do you have an idea how to prevent it?

So can you also give me some hints what i have to consider when i want to update / insert in one table (with different partitions) at the same time?

Many thanks
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16538 Visits: 13210
mr_effe (4/17/2014)
Yes you're right. The most locks occur by the merge statements. Do you have an idea how to prevent it?

So can you also give me some hints what i have to consider when i want to update / insert in one table (with different partitions) at the same time?

Many thanks


I'm a bit out of my league here, so I asked some other experts if they can take a look at this.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 8267
Can you give a few more details of how the tables are partitioned?
And have you tried single threading this process?

We had a mix of parallel and single threading loading in ours.
Performance was still very good, although a lot of that can be very dependent on the overall design.
mr_effe
mr_effe
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 120
Hi,

most of the tables are partionend by our StoreId. In sum we have 6 stores (1,2,...6), so 6 partitions per table. And all our stored procedures have a input-parameter StoreId which then only process the data of the specific store. We have insert as select expressions as well as merge statements.

And each of the 6 stores are processed at the same time. And then we got a lot of locks and the processing of one store have to wait until the other store have finished the process. If i started the processing of the stores in sequence no locks occur.

And now I want to know what I've to do to minimize the locks and thereby increase the performance.
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 8267
See if this helps.
Skip down to the loading partitioned table.
mr_effe
mr_effe
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 120
I think this solution doesn't help me. Because a switch partition leads to a Schema-M lock. And if all of my parallel processes wants a schema-m lock nothing works anymore.

I already had this situation with my "truncate partition" function which uses switch partition to delete the table partition. It works fine if only one process is active. If i started the jobs in parallel all of them waits for each other. So I have already switched the logic from "truncate partition" to a delete logic.

So I think this solution will not help me :-(
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search