Stack subject in Windows OS is fascinating. There are so many interesting technical problems surrounding it. The moment you think you fully understand everything about stack you suddenly discover yet another mystery. And so it goes.
In the last couple of weeks I have been approached by our testers and support engineers asking about for how many threads they need to configure SQL Server on IA64 platform with 4GB of RAM. For example they asked is it ok to set number of threads to be 1024?
You probably know that SQL Server commits its stacks upfront. The reason we do it is to avoid unexplained server death – if there is no physical memory and machine is out of swap file, a process can disappear without leaving any traces. This will happen when a process will try to grow its stack. As any respectful server product SQL Server can’t afford such behavior.
SQL Server on IA64 is configured to use 2MB of stack size. You can verify it by looking at SQL Server image header. Does it really consume that much of committed memory per thread? The answer is no. On IA64 there are two stacks – regular stack and backing store. Backing store is a spill space for cpu register engine. It is used by cpu to spill registers to memory when it no longer has registers available.
When a process specifies committed stack size to be 2MB, Windows commits 2MB for regular stack and 2MB for backing store. Consequently each thread will commit 4MB of RAM (If you only specify reservation Windows will reserve that much but not commit). Fig1 shows what the thread stack really looks like on IA64.
| / \
| Backing store
| \ /
I would like to emphasize couple of interesting points. First Windows reserves a contiguous VAS region of size of 4MB for both stack and backing store. Second it divides this region in two so that the stack and the backing store grow in different directions.
Now we are in the position to answer the original question of how much physical memory 1024 threads will consume. The answer is 4MB*1024= 4GB. Considering that max server memory in SQL Server only reflects size of Buffer Pool and usually it is configured to be close to amount of physical memory on the box, this configuration might cause SQL server to crawl. This comes as a real surprise to many people. Max amount of threads SQL needs to be configured for does depend on the type of application running on top of server but in this case it definitely needs to be below 1024. Even 512 number of threads could be too much.
This post will be incomplete if I didn’t somehow mention stack overflow. Backing store makes IA64 stack handling to be different from other platforms. Stack overflow is not an exception in this case – OS needs to handle stack overflow both for regular stack and backing store. Surprise! For you it means that you have to be really careful when recovering from stack overflow especially when you diced to restore guard page by hand. But this is a whole different story on its own.
I hope you learned something from this post. Comments are welcomed!
Have a great weekend!