Showing posts with label parallel. Show all posts
Showing posts with label parallel. Show all posts

Friday, March 23, 2012

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.

Wednesday, March 7, 2012

performace issue

In my company, we have a central database and 4 remote databases. the
central database replicate same database into 4 remote database in parallel.
the replication is transaction replication and the data is input in the
central database and replicated into remote databases. there are some
triggers in the remote database that retrieve data that was replicated from
central database into other table. the design is like this. it is wierd that
one of remote database is very slow and replication locking the table and
client application is blocked out. the client application is hang. the
replication is very slow, 4 rows/minute. other 3 remote db are ok. I don't
know why only one remote db is ofen happened such thing. The other remote
databases were no problem, and the replication doesn't lock the table. I know
the triggers are bad, especially work with replication. But why other remote
db were ok. I don't know if there are some other problem. can dbcc dbreindex
be help? Thanks.
I would make sure indexes on the keys exist. Replication is not magic
either, you can monitor it just like normal database activity. I would
suggest running the profiler. Use the duration template, but filter
for say >2000ms.