Performance decline in parallel SPs execution

Hi,

We have a process that builds our data warehouse.

The processes execute SPs in serial order.

Each SP builds separate table.

Each table is build destructive (truncate and Insert Into).

I've tried to change the configuration by running 4 SP in parallel by SSIS to shorten the update time.

I've noticed in two declines in performance:

1. Each SP execution time is higher in the parallel execution in around 50% then in the serial execution. The CPU utilization is the same.

2. On each parallel execution we have a decline in performance of around 5 -10% compare to the previous parallel execution.

Do you have any directions to inquire?

Btw – we have Itanium 64bit x8 with 32GB memory

Thanks,

Assaf

It sounds like the stored procedures may be blocking each other when running in parallel.

You can verify if there is blocking by querying sys.dm_exec_requests and looking at the blocking_session_id column.

If you do see blocking you will want to look at the locks that stored procedure is taking, and determine if there is any way to reduce the locks held.

|||

Hi,

Sorry that I forgot mention it.

There is no locks between tables.

each SP right to only single table and Other SP do not read from other written tables.

Also validated it on server level.

Any other ideas?

Thanks,

Assaf

|||

If the stored procedures are inserting into tables in the same database then they could be contending for both physical IO on the data device and also be writing to the same log devices. The storage devices are quite often the bottlenecks in systems like this, and regardless of how fast the processors can run the disk can only write at a certain rate.

As for the 10% degredation I'm not so sure. Is the 10% cumulative run on run or are the subsequent run all just 10% faster than the first? What is the recovery model of your database? If it is not Simple then it may be having to extend the logs if they are not being cleared between runs. On a data warehouse which is completely rebuilt and does not then have data written to it you can probably just use Simple recovery and do a full backup after the load if necessary.