Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Parallel Processing Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 2:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:21 AM
Points: 11, Visits: 97
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
Post #1562552
Posted Thursday, April 17, 2014 3:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 13,741, Visits: 10,716
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1562553
Posted Thursday, April 17, 2014 3:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:21 AM
Points: 11, Visits: 97
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?
Post #1562562
Posted Thursday, April 17, 2014 3:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 13,741, Visits: 10,716
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1562567
Posted Thursday, April 17, 2014 3:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:21 AM
Points: 11, Visits: 97
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
Post #1562569
Posted Thursday, April 17, 2014 3:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 13,741, Visits: 10,716
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1562571
Posted Thursday, April 17, 2014 6:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,249, Visits: 6,699
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.
Post #1562620
Posted Thursday, April 17, 2014 6:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:21 AM
Points: 11, Visits: 97
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.
Post #1562629
Posted Thursday, April 17, 2014 6:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:12 PM
Points: 1,249, Visits: 6,699
See if this helps.
Skip down to the loading partitioned table.
Post #1562631
Posted Thursday, April 17, 2014 7:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:21 AM
Points: 11, Visits: 97
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 :-(
Post #1562660
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse