![]() ![]() A thread enters the “Waiting” queue when it voluntarily gives up CPU time (waiting for a event to signal, or going to sleep, etc.). Per CPU, there is a “Waiting” queue and a “Ready” queue (Actually there are 32 “Ready” queues. It may simply be waiting for user-input, but it is still occupying the CPU). (This does not mean that the thread is necessarily doing some processing. If we don’t take hyper-threading and multi-core into account, on a single CPU, there is at any given time a single thread in the “Running” state. To keep things simple, we can imagine that a thread can be in three states: Running, Waiting or Ready. To answer this question, let’s see how the OS handles threads. What is the ideal number of threads to work with and which CPUs do I schedule them on? So now if you are the guy writing SQL Server thread management, you think: Ok, so on the 32-bit platform, I will maybe hit a max limit of 12k threads, and a max of 32 CPUs. (To find out how to do this for SQL, see ) For a stack size of 256 KB, it would be mean 12000 threads with a 3 GB switch. If I reduce the stack size, I can get more threads. With the 3 GB support, it can go up to 3000 threads. ![]() Assuming you do nothing else, that means around 2000 threads. But then, how big a thread-pool should I have? What’s an optimum number? Remember that the default stack size on 32-bit Windows is 1 MB, and address space is 2 GB. It obviously does not make too much sense to go the one thread / request model – that can quickly get out of control. Another model could be using a thread pool. One model could be that I create one thread per request. As SQL Server, I get several requests to process. The first question is, why does SQL Server have its own scheduler and that too non-preemptive? But before I launch into this discussion, a caveat: Whatever I say here is my personal view and most of this is construction from what I have read / heard over the years. Thinking about why they exist and how they work gives some fascinating insights into the working of SQL Server. However, this question does lead to some interesting discussions around scheduling, fiber-mode and CLR hosting. So SQLCLR also does not have Fiber mode support. ![]() Enjoy!ĪFAIK, CLR 2.0 was supposed to have support for Fiber mode, but they did not make it to ship because of time-constraints and Fiber-mode support was dropped from the CLR. So I am posting it here (after some small changes). Srini thought it was an interesting discussion and that I should share it with a larger group. How does SQL 2005 Handle this Limitation?” Answering this question led to a lot of deep-dive and I finally managed to answer this in about 2500 words over the weekend. On a recent mailing list discussion, Veer Wangoo asked: “DOT NET CLR only uses thread-based scheduling and does not support Fiber-mode scheduling however, SQL Server can use Fiber-mode scheduling as well. ![]()
0 Comments
Leave a Reply. |