Spotfire Ideas Portal
It is possible to set ridiculous values for fetch size and batch size on a database connection in Information Designer. Very high settings cause the Spotfire server to suffer "Out of Java heap space" errors because some JDBC drivers (we've specifically seen it with Oracle) allocate enough memory to fulfil a particular fetch size before issuing queries to database servers. It would be good to set a maximum value for this setting within a Spotfire environment - e.g. it is fairly useless to put the value higher than 10,000.
In our particular case, some of our users had set their fetch size to 1,000,000 which was enough that Spotfire could sometimes allocate memory and sometimes fail - which meant that we had intermittent errors when those users ran their queries, but also meant that our entire environment was suffering excessive garbage collection because sometimes the request to allocate memory for 1,000,000 rows was succeeding.
We plan on putting an automated process in place to update the settings on connections where they are too high (by nasty SQL updates on the Spotfire database) but would much prefer that users couldn't set them too high in the first place.
We actually deliberately set ours to 8GB. Higher values result in slower garbage collection because there's more to process, so we found that allowing garbage collection to happen more often on less memory (and using the G1 garbage collector) was better. What sort of in-memory joins are you getting that run on Server? We have loads more memory on our Nodes because that's where most of the in-memory stuff happens - never had out of memory problems with Server other than those caused by the bad fetch size setting and a few we've seen where it just crashes because something obviously went out of control (although haven't seen many of those in last year or so since 7.7).
A bit obvious but have you increased the Tomcat Java heap size? We set ours to 30 GB as we get some big spikes from time to time (mainly in-memory Server data joins).