Reading the manual:

  • *Working with database files
    ... The database server uses the temporary file during a database session when SQL Anywhere needs more space than is available to it in the cache for such operations as sorting and forming unions.*

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

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%


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.

permanent link

answered 07 Aug '12, 15:24

John%20Smirnios's gravatar image

John Smirnios
11.9k396165
accept rate: 37%

edited 07 Aug '12, 15:54

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050

Thanks John - that makes a lot of sense.

In practical terms (for the example I mentioned) I suppose that means that

  • there needs to be enough disk space made available for possibly quite a large temp file,
  • but it's is unlikely to ever actually be written to, and therefore doesn't have to be very fast.
(07 Aug '12, 15:40) Justin Willey
Replies hidden
1

That's like saying "the life preserver is unlikely to ever actually be used, so it doesn't have to float very well" :)

On the contrary, if you have a lot of RAM but you STILL need to use the actual hard drive for temporary space, then you are in REAL TROUBLE and you need all the help you can get.

(07 Aug '12, 15:48) Breck Carter
3

In your specific example, assuming you have more than 4GB cache and at some time during the server's uptime you had 4GB of data associated with the temp file, you could have a 4GB temp file which you have almost never written to. I think that much is pretty accurate. The performance claim is difficult to determine unless you are certain that you're never going to need more temporary space than what your cache can hold.

As always, there are some subtleties. A spot on disk is assigned for each page in the temporary dbspace but the temp file really only grows as far as the filesystem sees it when a page is written. So, it's possible that only one page was written at the 4GB mark and the rest of the file prior to that point was just be materialized by the OS/filesystem. Depending on the OS & filesystem, it could be done as a "sparse file" and still wouldn't take up much space.

(07 Aug '12, 15:50) John Smirnios
1

I take your point :) I could extend the analogy though to the question of whether I need a $100 good quality life preserver or a $250,000 fast rescue boat on 24 hour standby, but we could go on all day!

(07 Aug '12, 16:02) Justin Willey

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.

permanent link

answered 07 Aug '12, 15:43

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

Oh, and then there's this: Defragmenting the Temporary File... not sure if it still applies at the Windows 7 level.

(07 Aug '12, 15:51) Breck Carter

Thanks - I'll have a look and see what it reports compared to what the OS says.

(07 Aug '12, 16:04) Justin Willey
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×119
×26
×23

question asked: 07 Aug '12, 15:10

question was seen: 2,701 times

last updated: 07 Aug '12, 16:04