Reading the manual:
I get the impression that the temporary file will be pretty irrelevant if there is enough space in cache to do everything required. Is that the whole story, or are there occasions when the temporary file will be used in preference to cache?
The reason I'm asking is that I have seen a server where there is four times as much cache as the size of the db and no evidence that it is all being used, but there is also a 4 GB temp file (but not it seems used heavily - judging by disk statistics). Based on older experience where the available cache was much smaller, and the temp file was certainly very important to performance, it had been allocted its own RAID 1 array, and I was asked if it was still necessary.
The install in question was 10.0.1, but I'm also in 12.0.1 (if the answer is different)
asked 07 Aug '12, 15:10
In some cases, the documentation you have cited is completely correct: the server may use a completely in-memory approach and only start putting things into the temporary dbspace when sufficient memory is not available.
In other cases, it is a bit misleading. The temporary dbspace is cached much the same way permanent dbspaces are cached so even if data is "put into the temporary dbspace", it might just sit in cache and never be written to disk. For example, if a user creates a local temporary table then the data inserted into the table "goes into the temporary dbspace" but might just sit in cache if there is no cache pressure to push the pages out. If the temporary table is dropped, the pages are just freed and it's possible that none were ever written to disk.
Note, however, that space is immediately allocated within the temporary dbspace for any pages that might eventually be written there. In that respect, it is very much like backing-store/swap-file for virtual memory (particularly the way Windows manages backing-store since Windows always pre-allocates backing store for all memory that is allocated). In the case of the temporary table, space is allocated in the file whether or not the pages are ever written. The space in the file is, of course, freed when the table is dropped or the rows are deleted.
No, if there is enough RAM cache then there should be no need to use the temporary file.
A large temporary file may indicate a runaway connection... perhaps a cross join with a missing predicate and a user who took a long lunch while it churned away for several hours... it is quite possible to run out of space on the hard drive and crash the server in such an event (there are options to prevent that).
What does Foxhound tell you? Have a look at the Temp Space column in the server and connection frames.
answered 07 Aug '12, 15:43