TMP/TEMP/USERPROFILE environment variables: the closer they point to, the better

Article
05/29/2008

One recent discovery I wanted to eventually share here. Many more programs than you would expect, store information in temporary files. If you configure your environment so that those temporary files get created in a remote location, you will suffer the painful performance effect.

I had no idea this worked this way behind the scenes.

When we run a query from SSMS and the results are presented in a grid (Query -> Results To -> Results To Grid), every row we receive from the server, we serialize it to a temporary file on disk, and when results have been received completely from the SQL Server instance, then SSMS maps those results to as many grids as resultsets were received. Eventually, you could receive an error like “An error occurred while executing batch. Error message is: There is not enough space on the disk”, if the disk where the file was located runs out of space in the middle of the query execution.

To avoid the problem, either make some room so that the whole resultset fits in the disk pointed by the buffer returned by GetTempPath, or change your environment variable TMP to point to a volume where you have plenty of room. After changing the environment variable, you have to restart SSMS so that the change takes effect. That’s because the PEB (process environment block) of the existing process doesn’t get updated every time you change, delete or add an environment variable.

It's also worth mentioning that this could even cause performance problems if the Temp path is located on a remote share whose response time is slow.

I ran a test to proof that. I ran a select * from a one char(10) column table filled with 110681 rows. I changed my relevant environment variables so that GetTempPath returns \\farawayhost\share\IALONSO a remote path in Texas (I'm located in Madrid). As you can see below, it takes quite a few hops and a reasonable amount of time for my packets to get there (Madrid-London-Redmond-Irving) :-)

Following are the different scenarios and performance results:

1) With Results To Text (no matter what GetTempPath returns), it completes in less than one second.

2) With Results To Grid, with GetTempPath returning \\farawayhost\share\IALONSO, it completes only after 57 seconds.

3) With Results To Grid, with GetTempPath returning C:\Users\IALONS~1.EUR\AppData\Local\Temp, it completes in one second.

Notice the effect on performance can be very important. The longer SSMS takes consuming the rows returned from the server, the longer it takes for the server to release the resources it has locked to satisfy the query.

Following is some data I collected while understanding how this worked, and while running the performance test:

Output from tracert to the host where I had decided to point my TMP variable to:

Tracing route to FARAWAYHOST

over a maximum of 30 hops:

1 2 ms 2 ms 2 ms MADRID-1.network.microsoft.com

2 2 ms 1 ms 1 ms MADRID-2.network.microsoft.com

3 26 ms 26 ms 25 ms LONDON-1.network.microsoft.com

4 228 ms 228 ms 227 ms REDMOND-1.network.microsoft.com

5 311 ms 227 ms 227 ms REDMOND-2.network.microsoft.com

6 230 ms 229 ms 227 ms REDMOND-3.network.microsoft.com

7 294 ms 293 ms 293 ms IRVING-1.network.microsoft.com

8 292 ms 292 ms 291 ms IRVING-2.network.microsoft.com

9 292 ms 291 ms 291 ms IRVING-3.network.microsoft.com

10 292 ms 291 ms 292 ms FARAWAYHOST.northamerica.corp.microsoft.com

Trace complete.

Managed stack of the thread which is creating that temporary spool used by the Results to Grid functionality.

0:003> !clrstack

OS Thread Id: 0x1690 (3)

ESP EIP

08bbefe0 764fcfaa [NDirectMethodFrameSlim: 08bbefe0] Microsoft.Win32.Win32Native.GetTempPath(Int32, System.Text.StringBuilder)

08bbeff0 793b5c0b System.IO.Path.GetTempPath()

08bbf004 793b5b39 System.IO.Path.GetTempFileName()

08bbf018 09465031 Microsoft.SqlServer.Management.UI.Grid.DiskDataStorage.InitStorage(System.Data.IDataReader)

08bbf028 094644fd Microsoft.SqlServer.Management.QueryExecution.QEResultSet.Initialize(Boolean)

08bbf064 09464314 Microsoft.SqlServer.Management.QueryExecution.ResultsToGridBatchConsumer.OnNewResultSet(System.Object, Microsoft.SqlServer.Management.QueryExecution.QESQLBatchNewResultSetEventArgs)

08bbf094 0946415e Microsoft.SqlServer.Management.QueryExecution.QESQLBatch.ProcessResultSet(System.Data.IDataReader)

08bbf0f4 09463ca9 Microsoft.SqlServer.Management.QueryExecution.QESQLBatch.DoBatchExecution(System.Data.IDbConnection, System.String)

08bbf170 0946390f Microsoft.SqlServer.Management.QueryExecution.QESQLBatch.Execute(System.Data.IDbConnection, Microsoft.SqlServer.Management.QueryExecution.QESQLBatchSpecialAction)

08bbf1ac 094636a4 Microsoft.SqlServer.Management.QueryExecution.QEOLESQLExec.DoBatchExecution(Microsoft.SqlServer.Management.QueryExecution.QESQLBatch)

08bbf1f0 09463357 Microsoft.SqlServer.Management.QueryExecution.QESQLExec.ExecuteBatchCommon(System.String, Microsoft.SqlServer.Management.QueryExecution.ITextSpan, Boolean ByRef)

08bbf238 09463154 Microsoft.SqlServer.Management.QueryExecution.QEOLESQLExec.ExecuteBatchHelper(System.String, Int32, Int32)

08bbf268 0946300e Microsoft.SqlServer.Management.QueryExecution.QEOLESQLExec.ProcessBatch(System.String, Int32)

08bbf27c 08536faf <Module>.BatchParser.ThunkCommandExecuter.ProcessBatch(BatchParser.ThunkCommandExecuter*, UInt16*, Int32)

08bbf308 033df418 [PInvokeCalliFrame: 08bbf308]

08bbf320 08536a75 <Module>.BatchParser.ExecutionContext.ProcessWhatsLeftInBatch(BatchParser.ExecutionContext*)

08bbf374 08532b2e <Module>.BatchParser.BatchParserInternal.Parse(BatchParser.BatchParserInternal*, BatchParser.ParserState*, Boolean)

08bbf40c 085329ca ManagedBatchParser.Parser.Parse()

08bbf630 09462e5c Microsoft.SqlServer.Management.QueryExecution.QEOLESQLExec.DoScriptExecution(Microsoft.SqlServer.Management.QueryExecution.ITextSpan)

08bbf65c 09461a1b Microsoft.SqlServer.Management.QueryExecution.QESQLExec.StartExecuting()

08bbf694 793b0d1f System.Threading.ThreadHelper.ThreadStart_Context(System.Object)

08bbf69c 79373ecd System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)

08bbf6b4 793b0c68 System.Threading.ThreadHelper.ThreadStart()

Managed stack of a thread serializing one row to the temporary file.

0:003> g;!clrstack

Breakpoint 4 hit

OS Thread Id: 0x1690 (3)

ESP EIP

08bbeea0 764f24b5 [NDirectMethodFrameStandaloneCleanup: 08bbeea0] Microsoft.Win32.Win32Native.WriteFile(Microsoft.Win32.SafeHandles.SafeFileHandle, Byte*, Int32, Int32 ByRef, IntPtr)

08bbeebc 79398282 System.IO.FileStream.WriteFileNative(Microsoft.Win32.SafeHandles.SafeFileHandle, Byte[], Int32, Int32, System.Threading.NativeOverlapped*, Int32 ByRef)

08bbeef0 793982f3 System.IO.FileStream.WriteCore(Byte[], Int32, Int32)

08bbef10 79398343 System.IO.FileStream.FlushWrite(Boolean)

08bbef1c 793a8058 System.IO.FileStream.Flush()

08bbef24 0946cfc0 Microsoft.SqlServer.Management.UI.Grid.FileStreamWrapper.FlushBuffer()

08bbef30 0946cf6d Microsoft.SqlServer.Management.UI.Grid.FileStreamWriter.FlushBuffer()

08bbef34 0946c6cc Microsoft.SqlServer.Management.UI.Grid.DiskDataStorage.SerializeData()

08bbf018 0946b18a Microsoft.SqlServer.Management.QueryExecution.QEDiskDataStorage.SerializeData()

08bbf04c 0946b138 Microsoft.SqlServer.Management.QueryExecution.QEDiskDataStorage.StartStoringData()

08bbf054 0946ae19 Microsoft.SqlServer.Management.QueryExecution.QEResultSet.StartRetrievingData(Int32, Int32)

08bbf064 09464366 Microsoft.SqlServer.Management.QueryExecution.ResultsToGridBatchConsumer.OnNewResultSet(System.Object, Microsoft.SqlServer.Management.QueryExecution.QESQLBatchNewResultSetEventArgs)