February 2010 Edition

How to Manage Temporary Files in SQL Anywhere 11

Some background

A tempfile (sqla####.tmp) is a file created by SQL Anywhere server on startup and deleted on shutdown. As its name suggests, it is used to hold temporary information while the database server is running. It does not hold information that needs to be kept between sessions. It is used by the server for operations like sorting|distinct|unions when there is not enough cache available.

The location of the file is specified by one of the TMP,TMPDIR or TEMP environment variables or by '-dt' server option. (e.g. dbeng11 -dt "." - creates a temporary file in the current directory ). The location might be important for performance consideration. Similarly to .db and transaction log file, if this file is heavily used by the server (e.g. queries with lots of sorts/unions), for better IO concurrency, it is recommended to place it on a separate physical drive.

Unlike other DBMS systems (e.g. ASE, SQL Server) in SQL Anywhere there is no such thing as a temporary database (tempdb). Temp objects are created and stored on tempfile, but they don’t need to be explicitly accessed via a “tempdb” prefix.

In the event of system crash or server being killed, a tempfile will remain until is deleted manually or until another server starts up at which time it deletes any remaining tempfiles.

SQL tips related to tempfile:

To find out:
which directory where they were created in:
select property('TempDir');

the actual file name :
select Value From sa_db_properties() where propname = 'TempFileName';

the current size of tempfile in bytes:
select DB_EXTENDED_PROPERTY( 'FileSize', 'temporary') * PROPERTY('PageSize')

Possible issues with temporary files

It is not uncommon for inexperienced SQL developer to create a request that will quickly cause server to allocate large amounts of temporary space. When in many cases this might be necessary in order to obtain required results, quite often a user may create a so called 'run-away query' causing the file system to fill up. This usually creates all sorts of problems e.g. system hang/system failure.

A simple example involves a SELECT statement from 3 tables with a missing JOIN condition.  This shows how easy it is to create a SQL statement that will not only run for minutes/hours, but also use gigabytes of tempfile space.

   SELECT top 1 *
     FROM SYSDEPENDENCY,
          SYSOBJECT, 
          SYSPROCPARM
    ORDER BY 1, 2, 3, 4, 5;

It is important for system administrators to know that once a temporary file has grown there is no way, other than restarting the server, to automatically shrink it. Therefore, it is recommended for a dba to implement some measures that would prevent such incidents from happening.

How to prevent an unexpected growth

SQL Anywhere provides 2 server options which control space usage for a tempfile. "TEMP_SPACE_LIMIT_CHECK" option causes server to respect space limit and, 'MAX_TEMP_SPACE' allows setting the actual space limit in terms of size.

e.g.
            SET OPTION PUBLIC.TEMP_SPACE_LIMIT_CHECK = 'On';
            SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G'; -- sets the limit to 1Gb

A request, which will cause temporary file to grow over 'MAX_TEMP_SPACE' limit, will stop and generate the following error:

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Temporary space limit exceeded
SQLCODE=-1000, ODBC 3 State="HY000"

a failure with such error should be taken care of within an application.

Additionally to setting 'hardcoded' limits on the tempfile, which might be machine specific, it is recommended to setup a monitoring tool that would notify administrator about possible issues, so then he can analyze it and take preventive steps in order to stop the file system from being filled up and subsequently cause system failure.

Monitoring tempfile space usage

Additionally to using above server options, it is good practice to a have a way of monitoring the database server, and therefore act proactively on possible space issues.

One of the features of SQL Anywhere are system events. System events are simply events that are triggered by the server when a certain condition is true. For more information see http://dcx.sybase.com/index.php#1100en/dbadmin_en11/events-events.html.

SQL Anywhere provides different types of events, and one of them is 'TempDiskSpace' event.

Here is an example of an event that will log a message to server's console log every time the amount of available free space for a temporary file is less than 50%.

CREATE EVENT "evt_monitor_temp_space" TYPE "TempDiskSpace"
WHERE EVENT_CONDITION('TempFreePercent') < 50
HANDLER
BEGIN
-- this event will write a message to console log
-- when there is less than 50% of free space on the device
-- where tempfile is located
declare tf varchar(128);
declare free varchar(128);
declare crn varchar(128);
declare prc varchar(3);

    IF event_parameter( 'NumActive' ) <= 1 THEN
            -- find out temp file name
        select convert(varchar(128),value) into tf From sa_db_properties() where propname = 'TempFileName';;
            -- find out current size in Mb
        select convert(varchar(128),(convert(bigint,DB_EXTENDED_PROPERTY( 'FileSize', 'temporary'))*convert(bigint,PROPERTY('PageSize'))/1024/1024)) into crn from dummy;
            -- find out number of available free space
        select convert(varchar(128),convert(int,free_space/1024/1024)) into free From sa_disk_free_space() where dbspace_name = 'temporary';
            -- calculate % of free space
        if free <> 0 then select convert(varchar(4),convert(integer,convert(float,crn)/convert(float,free)*100)) + '%' into prc from dummy end if;
        -- write space information to console log
        MESSAGE prc + ' (' + crn + ' Mb). of free space (' + free + ' Mb)' + ' have been used by ' + tf;
    END IF;

END;

Note: that this can be modified to execute any SQL statement or use an external call (e.g. 'xp_cmdshell') to send an email etc.

Here is an example of messages generated by this event:

0% (214 Mb). of free space (22165 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp
2% (558 Mb). of free space (21820 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp
Cache size adjusted to 1362112K
4% (974 Mb). of free space (21404 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp
4% (1038 Mb). of free space (21340 Mb) have been used by C:\DOCUME~1\lchmura\LOCALS~1\Temp\asat0001.tmp

How to identify a 'runaway' request

SQL Anywhere provides a set of dynamic properties, per database and per connection which is useful in identifying a runaway request. Analyzing these property values over time gives a good indication of tempfile usage per connection, and helps to quickly isolate the ‘runaway’ connection.

Below is an example of a SQL statement which will create (if  it doesn’t exist) the sa_tempfile_monitor table, and store database and connection properties. (Note, the same statement could be added to the event above, so instead of being run manually, it could run automatically whenever the amount of free space on disk is less than 50%).

First make sure -zl and -zp options are turned on the server. This will cause server to capture the most recent prepared SQL statement. This can be done on server startup command line (dbsrv11 ... -zl -zp .. ) or from dbisql:

     call sa_server_option('RememberLastStatement','ON');
     call sa_server_option('RememberLastPlan','ON');

In order to collect necessary information about run-away process, from dbisql run:

     if not exists (select * from sysobjects where name like 'sa_tempfile_monitor')
         select getdate() as d,db_property('ExtendTempWrite') as ExtendTempWrite,db_property('TempTablePages') as TempTablePages,
         prop.Number,prop.PropNum,prop.PropName,prop.Value,
         inf.Name,inf.Userid,inf.LastReqTime,inf.BlockedOn
         into sa_tempfile_monitor
         from sa_conn_properties() prop,sa_conn_info() inf
         where prop.Number = inf.Number and
         prop.PropName in ('TempTablePages','TempFilePages','LastStatement','LastPlanText','CurrentProcedure','CurrentLineNumber')
     else
         insert into sa_tempfile_monitor
         select getdate(),db_property('ExtendTempWrite'),db_property('TempTablePages'),
         prop.Number,prop.PropNum,prop.PropName,prop.Value,
         inf.Name,inf.Userid,inf.LastReqTime,inf.BlockedOn
         from sa_conn_properties() prop,sa_conn_info() inf
         where prop.Number = inf.Number and
         prop.PropName in ('TempTablePages','TempFilePages','LastStatement','LastPlanText','CurrentProcedure','CurrentLineNumber')

where :

'TempTablePages','TempFilePages' - reports tempfile usage per connection (in pages)
'LastStatement','LastPlanText','CurrentProcedure','CurrentLineNumber' - will help identify which SQL request is run at the time

Once data is collected, with a number of simple SQL statements the dba should be able to find out which connection was causing tempfile growth and what was executing at the time.

Here are some examples:

% usage of tempfile per connection over time:

            select
d,ExtendTempWrite,TempTablePages,Number,UserID,
convert(Integer,convert(Bigint,Value)/TempTablePages*100) PerUsage
             From sa_tempfile_monitor
where PropName = 'TempTablePages'
order by d,number

            where 'Number' represents connection ID, and UserID login id.

Once a connection causing the problem has been identified, by running :

            select * from sa_tempfile_monitor where Number = 2

where '2' is the connection id, the dba should be able to find out what SQL statement has been executed, and then work on fixing it.

Here is a quick example

Start demo11 database:

"C:\Program Files\SQL Anywhere 11\Bin32\dbsrv11.exe"  -zl -zp -m -c 50P -n demo11 "C:\Documents and Settings\All Users\Documents\SQL Anywhere 11\Samples\demo.db"

Open 2 dbisql windows (C:\>"%sqlany11%\bin32\dbisql.exe" -c uid=dba;pwd=sql;eng=demo11).

In first dbisql simulate 'run-away' request by running:

   SELECT top 1 *
     FROM SYSDEPENDENCY,
          SYSOBJECT, 
          SYSPROCPARM
    ORDER BY 1, 2, 3, 4, 5;

From the second dbisql window, run the following :

            select * From  sa_conn_properties() where PropName like 'TempTablePages'
            order by convert(integer,value) desc

this will find out the request's 'Connection ID' which uses the most of TempTablePages.

Then, running

            select * From sa_conn_info() where Number =
            (   select top 1 Number From  sa_conn_properties() where PropName like 'TempTablePages' order by convert            (integer,value) desc)

            select * from sa_conn_properties() where Number =
            (   select top 1 Number From  sa_conn_properties() where PropName like 'TempTablePages' order by convert            (integer,value) desc)

will give more information about what this connection is actually doing.

Having this information a developer/administrator should be able to isolate which portion of the code triggers a run-away process, and fix it.