Nice tuning example from the field

· Uncategorized

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: