One of our customers has a SQL Anywhere 12 (12.1.4142) database, about 24 Gb in size. This morning, the tmp-file is 34 Gb. We're wondering about the reason why this tmp-file grows so much? We are using #-tables and some heavy queries against the database, but for the most part it's just normal usage from a Powerbuilder 12.5 application. The service is started with these parameters:
-n maritech -x tcpip -gn 40 -ca 0 -ch 14000M -cl 14000M -c 14000M
The server has 24 Gb of RAM.
Is there anything we can adjust in the parameters, or is it normal that the tmp-file grows to that size?
Bjarne Anker Maritech Systems AS Norway
asked 12 Jan '15, 03:05
In addition to what Volker and Martin say, one of the easiest ways to find runaway queries is to run Foxhound. This example shows a runaway connection using all the CPU time, but in your case it would show which connection used a lot of temporary space.
The decision to fix the RAM cache size is sometimes made to guarantee adequate resources for the database server, rather than let the server spend time dynamically adjusting the cache size, when the database server is the most important (or only important) process on the computer.
Also note: the physical temporary file will not shrink in size when the temporary space requirements shrink... until the database is restarted (which is when the temporary file is created).
If the temporary file is heavily used, on purpose, it might be a good idea to defragment it after it grows in size.
You will have to check the plans of the queries you execute. One common thing which grows the temp file is a query plan including a temporary table, they all go into the temp file. So probably you will find a cross join of tables in your plans which is leading to the observed effect.
answered 12 Jan '15, 03:33