If I had to pick the most controversial property in all of Windchill, it would be the db.property wt.pom.queryLimit. This property limits the number of rows the method server will accept from the database. When the limit is reached an exception is generated to the user indicating that their operation has exceeded the query limit, often this will be a PartialResultException displayed to the user, but depends how the code in that area handles the exception. When the exception is generated the operation the user was running doesn't complete and rolls back (if it was part of data change operation) and this is where the problem comes in and why this property is so controversial. Unfortunately when systems contain a large enough amounts of data, this property needs to be set to keep the MethodServers from crashing with 'OutofMemory' errors. The default shipped by PTC and recommended by the Windchill Configuration Assistant (WCA) is to not set the property (wt.pom.queryLimit=-1 or the property is not present in the db.properties file) which will allow for unlimited rows to be returned to the MethodServer by the database and put the system at risk for OOME (Out Of Memory Errors) because eventually all of rows returned will consume all available heap in the method server leading to an out of memory error in the method server crashing.
Without exaggeration I would say the number one cause of MethodServer outages I've seen over the years are out of memory problems caused by not having the wt.pom.queryLimit set. But the challenge is what to set this property to so that most/all necessary operations run and don't trip the limit causing the operation to fail but at the same time set it low enough it's not likely the Method Server will crash either. There is unfortunately no easy answer, the point at which a method server will crash depends on the size of the rows being returned from the DB, the other operations running in the method server and their memory use, the GC settings and a couple of other things. The common recommendation is to set the property to between 100K and 150k and I've seen it set has high as 400k on one system. As MethodServer heap sizes increase and generally getting into the 4G range routinely, I think increasing the queryLimit setting to the 250k-400k is probably reasonable. The key thing is to prevent unlimited numbers of rows from coming back, I have yet to see a MethodServer that can survive a million or more rows being returned although a few have tried :-). I also think that an operation shouldn't ever really need 300K rows returned from a single SQL statement unless it's some sort of large reporting operation. Anything more than 100-150K rows being returned is represents a potential scalability bug, although there are exceptions.
The bottom line is that db.property wt.pom.queryLimit property needs to be set for a stable system, if the limit is hit for an operation, that may not be bad thing if that operation really shouldn't have been running, but if needs to complete a stack trace will be generated to the MethodServer log showing where the problem originated from which can be used for investigation and possibly lead to an SPR. Unfortunately to raise the limit the MethodServer needs to be restarted. Lastly, there are several other query limit properties which all limit rows to returned from specific areas of the code (see link for the others) although in vast majority of the cases it's the wt.pom.queryLimit which is the key property to have set.
