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.