Use of Thread Local Storage in an extended stored procedure

Thread Local Storage (TLS) can be a very tricky subject. When working in a thread pooling environment, it is very important that you understand the ramifications of TLS and the thread interactions.

It is highly suggested you avoid TLS in a thread pooling environment such as Microsoft SQL Server. However, if you must use TLS, please carefully read this document and consult the online documentation in the Win32 SDK for the TLS and DLL main functions.

Also, the compiler directive __declspec(thread) is not supported in an extended stored procedure. When LoadLibarary takes place, the __declspec(thread) definition is not properly initialized. See "Advanced Windows" by Jeffrey Ricter for more details.



NOTE: Any extension to SQL Server, including extended stored procedures, may never make a call to DisableThreadLibraryCalls().

As documented, the TlsAlloc function returns an index value that is used in function calls to TlsSetValue and TlsGetValue. The documentation suggests that you call the TlsAlloc function in your DLLMain function when ul_reason_for_call = DLL_PROCESS_ATTACH. By default, DLL_PROCESS_ATTACH is called when the DLL is initially loaded.

In DLL_PROCESS_ATTACH and DLL_THREAD_ATTACH, you should allocate memory and call TlsSetValue. This is specifically where the thread pooling starts to cause some problems in the scenario.

Below is an example scenario that applies to any application that does thread pooling. This example uses the Microsoft SQL Server application.

The following are a few basics for the example that you must understand. These are all documented in more detail in the Win32 SDK documentation under the DLLMain function.

• When a DLL is loaded, it calls DLL_PROCESS_ATTACH.
• All subsequent threads (spawned after the DLL is loaded) call DLL_THREAD_ATTACH.
• Currently running threads (spawned before the DLL is loaded) do not call DLL_THREAD_ATTACH.
• All threads call DLL_THREAD_DETACH, even if they never called DLL_THREAD_ATTACH.

For example, suppose the following chart shows the order and usage of the worker thread pool of SQL Server:

Joe starts a long-running select statement. No one has used the xp_test extended stored procedure yet, so there is no way for the DLL_ATTACH process to be called.

While Joe's select is running, Mary runs xp_test. The thread pooling mechanism determines that a new thread should be spawned to service Mary's request. SQL Server then calls the LoadLibrary function to load the Xproc.dll file. In doing so, thread 2 is the first thread to attach to the DLL, so DLL_PROCESS_ATTACH is called. As discussed earlier, TlsAlloc can be called to initialize the extended stored procedure's TLS index value.

While Joe's and Mary's commands are running, Adam submits his own select. Again, a new thread is spawned to handle Adam's request. Because thread 3 is spawned after the LoadLibrary took place, thread 3 calls DLL_THREAD_ATTACH. As documented, this is where you would allocate the memory for thread 3 and call TlsSetValue.

Now, suppose Joe's select has completed, so worker thread 1 is free for use. Lynn submits the xp_test command, and is assigned thread 1. Thread 1 never calls DLL_PROCESS_ATTACH or DLL_THREAD_ATTACH, because the thread was spawned before the LoadLibrary was called.

From this example, you can see that any attempt by thread 1 to access the TLS memory with TlsGetValue results in a NULL pointer being returned. If the extended stored procedure is not properly written to check for this condition, you will encounter an access violation (AV) when you try to write to the NULL address.

Below are several points that need to be mentioned about TLS and thread pooling.

If you are using TLS in a pooling environment, you must always check the TlsGetValue for a NULL return value. When you get a NULL, you must correctly allocate memory and call TlsSetValue to handle those threads that where spawned before the LoadLibrary took place.

Another caveat not directly addressed in the documentation is that you will allocate the TLS memory in the DLLMain function for every thread spawned after the LoadLibarary, even if the thread never uses the functions in the DLL. The example above shows this with thread 3. It only ran a select, but it called DLL_THREAD_ATTACH, which allocated the TLS memory for the extended stored procedure.

If you place this design in a thread pooling environment, you may allocate memory that may never be used.

The following is the best way to optimize the allocation of memory:

1. In DLL_PROCESS_ATTACH, obtain the correct TLS index value from TlsAlloc.
2. Do not allocate memory in DLL_PROCESS_ATTACH or DLL_THREAD_ATTACH.
3. Design a generic function to get the TLS value; if it is NULL, correctly allocate it and call TlsSetValue.
   
   This step restricts the allocation of memory to those threads that actually use it. This may reduce the overhead and startup time of worker threads significantly. It also builds in the redundancy to correctly handle those threads that were spawned before the LoadLibrary took place.
4. Because DLL_PROCESS_DETACH and DLL_THREAD_DETACH are always called, free memory in those processes.

The only other thing that may still be unclear is the call to TlsAlloc. Reading the documentation, it may appear that the TLS index value returned is a global value. It is a global value, but each call to TlaAlloc returns a different index value. This allows two extended stored procedures to have their own TLS index value and correctly handle their own TLS data without affecting the others.

The following is an extended stored procedure that shows the behavior described in the example:

MORE INFORMATION