Defragmenting The Temporary File

All you Microsoft conspiracy theorists out there, get out your tinfoil hats! It turns out that Windows XP and 2003 do a really bad job of allocating contiguous disk space for new and growing files. It doesn't matter if you have just defragmented your hard drive, and here's the reason why:

... it turns out that Windows XP/2003 fragments files whether or not all the free space is defragmented ... "consolidating every piece of free space" is no guarantee that newly arriving files won't be fragmented by the file system. That was a maxim on FAT volumes in DOS, but certainly is not applicable to NTFS or FAT volumes under Windows XP/2003. - How File Fragmentation Occurs On Windows XP / Windows Server 2003

This has serious implications for SQL Anywhere's temporary file. This file is deleted and recreated each time the database engine is shut down, and it grows in size as needed to satisfy requirements as the server runs. Because of the way Windows works, this file becomes fragmented as soon as it is created, and it gets worse as the file grows.

Let's consider an empty SQL Anywhere 10 database that has just been started on a freshly defragmented hard drive. Foxhound shows that the database and transaction log files are both completely contiguous (only 1 fragment), but the temporary file already has 16 fragments:

Now let's run a couple of queries that use a lot of temporary space; the temporary file grows to 524M, with 523M of that becoming free space when the queries finish. The database and log files are still contiguous (no surprise there), but the temporary file now has 72 fragments:

For some databases this won't be a problem: If there aren't a lot of connections, or there isn't a lot of temp space usage, a heavily fragmented temporary file won't slow things down. Also, if a disk defragmenter is run periodically, and the server is never shut down, the temporary file will eventually get defragmented after it has grown to its "highwater mark".

But, if you are concerned, there is a way to ensure the temporary file is contiguous. The solution is to force the temporary file to grow to its maximum size each time the database starts, and then immediately defragment it. The ALTER DBSPACE statement is used to force it to grow, the contig.exe utility is executed via xp_cmdshell() to defragment the file as soon as it has grown, and all this code is placed inside a SQL Anywhere "DatabaseStart" event.

ALTER DBPACE and CREATE EVENT are SQL Anywhere statements; the contig.exe utility is available from Microsoft.

Here's what the code looks like, including some diagnostic output to the database engine console log (MESSAGE ... TO CONSOLE) and a text file "C:\temp\contig_log.txt":

CREATE EVENT database_start TYPE DatabaseStart 
HANDLER BEGIN

   DECLARE @temp_file_spec  LONG VARCHAR;
   DECLARE @return_code     INTEGER;
   DECLARE @sqlcode         INTEGER;
   DECLARE @sqlstate        VARCHAR ( 5 );
   DECLARE @errormsg        VARCHAR ( 32767 );

   MESSAGE STRING ( CURRENT TIMESTAMP, 
      ' DatabaseStart event...' ) TO CONSOLE;

   ALTER DBSPACE TEMPORARY ADD 600MB;

   SET @temp_file_spec = DB_PROPERTY ( 'TempFileName' );

   MESSAGE STRING ( CURRENT TIMESTAMP, 
      ' Running contig on ',
      @temp_file_spec ) TO CONSOLE;

   @return_code = CALL xp_cmdshell ( 
      STRING ( 
         'C:\\download\\sysinternals\\contig\\contig.exe -v ',
         @temp_file_spec,
         ' >>C:\\temp\\contig_log.txt' ),
      'no_output' );

   MESSAGE STRING ( CURRENT TIMESTAMP, 
      ' xp_cmdshell @return_code = ', 
      @return_code ) TO CONSOLE;

EXCEPTION WHEN OTHERS THEN
   SELECT SQLCODE, SQLSTATE, ERRORMSG()
         INTO @sqlcode, @sqlstate, @errormsg;
   MESSAGE STRING ( CURRENT TIMESTAMP, 
         ' DatabaseStart exception...' ) TO CONSOLE;
   MESSAGE STRING ( 'SQLCODE = ', @sqlcode ) TO CONSOLE;
   MESSAGE STRING ( 'SQLSTATE = ', @sqlstate ) TO CONSOLE;
   MESSAGE STRING ( 'ERRORMSG() = ', @errormsg ) TO CONSOLE;

END;

Now all three files are contiguous after the database is stopped and restarted:

This extra processing comes at a price: an extra 2 minutes is added to the database startup time as shown by the console log:

2008-02-20 11:13:22.781 DatabaseStart event...
Database server started at Wed Feb 20 2008 11:13
2008-02-20 11:14:00.468 Running contig on 
   C:\DOCUME~1\bcarter\LOCALS~1\Temp\asat0000.tmp
2008-02-20 11:15:42.328 xp_cmdshell @return_code = 0

Here's the contig_log.txt output from the defragmentation process; note that only 4 fragments existed immediately after the ALTER DBSPACE, rather than the 72 fragments that existed after the file grew during normal usage. This may be good enough for you; i.e., you may decide to only run the ALTER DBSPACE, not the xp_cmdshell - contig.exe step.

Contig v1.54 - Makes files contiguous
Copyright (C) 1998-2007 Mark Russinovich
Sysinternals - www.sysinternals.com

------------------------
Processing C:\DOCUME~1\bcarter\LOCALS~1\Temp\asat0000.tmp:
Scanning file...
Scanning disk...
File is 153617 physical clusters in length.
File is in 4 fragments.

Found a free disk block at 24084622 of length 244001 for entire file.
Moving 153617 clusters at file offset cluster 0 to disk cluster 24084622
File size: 629215232 bytes
Fragments before: 4
Fragments after : 1
------------------------
Summary:
     Number of files processed   : 1
     Number of files defragmented: 1
     Average fragmentation before: 4 frags/file
     Average fragmentation after : 1 frags/file

And what about Vista? I don't know; the situation might be different, but I somehow doubt it. Here is an article that talks a bit about Vista:

The Truth about Defragmentation - Wes Miller, November 2007 issue of the Microsoft TechNet Magazine