1. General observations during the first test using loadrunner:
– end-user response times very bad
– High pressure on TEMPDB
– High values for waitstats SOS_SCHEDULER_YIELD and SLEEP_TASK and high values for runnable queues indicating pressure on CPU’s
2. After some investigations we found that
– creating/rebuilding statistics, reviewing indexes had no – positive – impact
– The SLEEP_TASK waitstat indicated the potential cause could be a HASH MATCH, and the queryplan indeed showed a HASH MATCH on one table. However, this behavior was different in PR, where a loop join was used for the same query.
3. After some more investigations we found that the reason why the queryplans on TST and PR were different was related to content . Although this table has 5 times the size in PR than in TST, the selectivity of the query results in 9,58% for TST and 0,26% for PR.
This is why in PR the query optimizer has chosen to use a nested loop and a Hash Match in TST, resulting in heavy CPU usage for calculation the hash and tempdb usage because data was spilled from memory to TEMPDB.
So it all came down to bad testdata.