The factors that have the maximum potential to slow down a TDV Instance to the point of unresponsiveness are listed below in decreasing order of importance.

(1) Statistics gathering

If your data source is going to be used to introspect tables containing millions of rows, it is advisable not to configure the data source with full statistics gathering (i.e. do not set Data Source Statistics Gathering  to Gather all statistics). Full statistics gathering is highly CPU-intensive as well as highly memory-intensive when it is polling statistics on tables containing a lot of rows.

(2) A single problem query

A single, rogue query could be pulling a lot of data (millions of rows) using a poorly optimized query plan. In this case, we would need to identify the problem query and analyze its query plan.

(3) TDV is running out of memory

This can happen if Memory >> Managed Memory >> Maximum Memory per Request is set to 100%. It is advisable to check that:

• There is adequate physical memory on the machine.
• Maximum Memory per Request to is set to 25% (or less).

This safeguards against a single request consuming all available JVM Heap memory.

Example: if your JVM Heap size is 10 GB and Maximum Memory per Request is set to 20%, then each request can take a maximum of 2 GB from the heap. On the other hand, if Maximum Memory per Request is set to 100%, then each request has permission to take the entire JVM Heap memory. If a request takes all the available heap, it causes TDV to run out of memory.

(4) The user load has increased over time

 We have found that in many instances, as the TDV user community gets used to sending requests, over a period of time users start sending a higher load to TDV. In this situation, it may be necessary to add more RAM to the machine, or to distribute the load over multiple TDV servers (multiple cluster nodes).

(5) XML transformations that are transforming a high data volume (1 GB or more)

NOTE: In most cases, (1) or (2) is the cause.

What are the things that have the maximum potential to make TDV completely unresponsive?