Look out for lock contention:
Parallel queries are slower than queries bench marked without contention. If the scans find many pages with exclusive locks due to update transactions, performance can change.
If parallel queries return a large number of rows using network buffer merges, there is likely to be high contention for the network buffer. Queries hold shared locks on data pages during the scans and cause data modifications to wait for the shared locks to be released. You may need to restrict queries with large result sets to serial operation.
If your applications experience deadlocks when DSS queries are running in serial, you may see an increase in deadlocks when you run these queries in parallel. The transaction that is rolled back in these deadlocks is likely to be the OLTP query, because the rollback decision for deadlocks is based on the accumulated CPU time of the processes involved.
See “Deadlocks and concurrency” on page 81 in the book Performance and Tuning: Locking for more information on deadlocks.
