Today's Client Question: Temp File Size

Q: Why does DB_PROPERTY ( 'TempTablePages' ) say that only 16K of space is used when our SQL Anywhere Version 9 temporary file has grown to 9G in size? Even Foxhound doesn't get the number right, it says the "Temp File" is only 4M.

A: All the numbers are correct, but some are more correct than others; it depends on what you're looking for.

Some background: The temporary file is created when the server starts, is deleted when it shuts down, and in between it grows in size but never shrinks. In your case, at some point since the server started it needed 9G of space, and that might indicate a problem... perhaps one or more runaway queries. Be warned: the server will most likely crash if the temporary file grows to fill the disk.

The DB_PROPERTY ( 'TempTablePages' ) value tells you how much temporary file space is currently used for temporary tables. Note the phrase "currently used" instead of "maximum space required". Also, "temporary tables" is not the only use for the temporary file; a runaway query might use huge amounts of temporary file space even when no temporary tables exist at all.

You can calculate the SUM() of all CONNECTION_PROPERTY ( 'TempFilePages' ) values to see a bigger picture: the total amount of space currently used in the temporary file. Here is a runaway query that uses a lot of temporary file space, followed by a query that shows the difference between TempTablePages and TempFilePages:

SELECT COUNT(*)
FROM ( SELECT *
FROM SYSTABLE CROSS JOIN SYSCOLUMN
UNION
SELECT *
FROM SYSTABLE CROSS JOIN SYSCOLUMN ) AS fat;

SELECT CAST ( TempFilePages * PageSize / 1024.0 AS BIGINT )
AS TempFilePagesK,
CAST ( TempTablePages * PageSize / 1024.0 AS BIGINT )
AS TempTablePagesK
FROM ( SELECT SUM ( CAST ( CONNECTION_PROPERTY ( 'TempFilePages',
                                       sa_conn_info.Number )
           AS DECIMAL ( 15, 6 ) ) ) AS TempFilePages,
    CAST ( DB_PROPERTY ( 'TempTablePages' )
           AS DECIMAL ( 15, 6 ) ) AS TempTablePages,
    CAST ( DB_PROPERTY ( 'PageSize' )
           AS DECIMAL ( 15, 6 ) ) AS PageSize
FROM sa_conn_info() ) AS properties;

In your case, that is where the 4M figure is coming from; Foxhound computes the SUM() for you. It's a bigger number than the 16K, but it's still the "currently used" number, not the temporary file size.

In the case of the SELECT COUNT(*) above, Figure 1 shows how the SUM() of TempFilePages grows and shrinks:

Figure 1: Foxhound Temp File History

Figure 2 shows the connection-level detail when the peak Temp File value of 226M was reached: one connection was using all the space to do the SELECT COUNT(*).

Figure 2: Foxhound Connection Detail


Figure 3 shows that after SELECT COUNT(*) finished, most of the temporary file is free space.

Figure 3: Foxhound DBSPACE Detail


You can run your own query to show the actual temporary file size, which is the "high water mark" for temporary file usage:

SELECT CAST ( temp_file_pages * page_size / 1024.0 / 1024.0
AS BIGINT ) AS TempFileK
FROM ( SELECT CAST ( DB_EXTENDED_PROPERTY ( 'FileSize', 'temp' )
       AS DECIMAL ( 15, 6 ) ) AS temp_file_pages,
    CAST ( DB_PROPERTY ( 'PageSize' )
       AS DECIMAL ( 15, 6 ) ) AS page_size ) AS sizes;

What Can Be Done About Temp File Usage?

SQL Anywhere Version 9 introduced the following database-level option to keep runaway queries from crashing the server:

SET OPTION PUBLIC.TEMP_SPACE_LIMIT_CHECK = 'ON';

Version 10 extended the idea (and here's another reason to upgrade to V10): You can specify different limits on the amount of temporary space allocated to different connections.

For example, if you have a lot of high-speed low-overhead OLTP connections, you can give them a low limit on temporary space to prevent fat queries from affecting response and throughput:

SET TEMPORARY OPTION MAX_TEMP_SPACE = '1M'; -- small

At the same time, if you have some connections doing important OLAP queries, you might want to give them a high limit so that they will be able to get their work done:

SET TEMPORARY OPTION MAX_TEMP_SPACE = '10G'; -- large

Some notes: The MAX_TEMP_SPACE option is available at the connection level even though the V10 help implies it is available only at the "PUBLIC" database level. Also, the MAX_TEMP_SPACE option only works if you also have TEMP_SPACE_LIMIT_CHECK set 'ON'.