The lost time

During a briefing at the beginning of a tuning assignment the IT staff of a Swiss company describes their problem: “Our new application runs in Switzerland and also as a local installation of our subsidiary in the US. Both installations are served by our local database in Switzerland . The Step in question in this application is running with us 10 Seconds in Switzerland, in the US, it takes about 2 minutes. We have a trace of the application, but we can not find the problem. Granted, there are thousands of SQL statements that we work through, but each one is fast and their total time is about 10 seconds.”

What do you think is the cause of the problem? How would you verify your assumption? What could be a short-term solution? How should a long-term solution look like?

Solution to I/O Landscape of an Application

The buffer gets far right show us the strength of the activity on the corresponding segments.
The list is sorted by Buffergets, which means the segments with the highest activity are on top.
We can actually be pretty sure that theses segments are also the most central and most important of the whole application since they are showing the highest activity. (If a database corresponds to an application.)
This should be evident: the numbers  in the report are important.
Normally we would expect that the physical reads behave proportional to the buffer gets. According to the rule of thumb that maximal  10% of buffer gets should be converted into physical reads. In reality, there is very often a concentration of physical reads on a few segments .
These segments, which have a disproportionate number of physical reads exhibit a bottleneck in physical design. Potential reasons are amongst others missing index or non-clustered data.
A exhaustive research of all execution plans which reference the bottleneck segments did  reveal the root causes.