The threading model used by SQL Server affects how efficiently the processors are used. This includes how many concurrent users it can handle and how quickly each of the users gets processing power.
SQL Server 6.5 uses native Window NT threads. The Windows NT kernel uses its own scheduler to manage the SQL Server thread scheduling as well as synchronization services. Although this process has produced high concurrency on a small number of processors, it does have limitations. At high loads, the thread context switching has some overhead. (Although the implementation of I/O completion ports aided significantly here). Because SQL Server lets Windows NT manage the threads, it has little control over which threads are preempted on and off of the processor and how often. The Windows NT kernel scheduler and dispatch lock periodically sees contention with a large number of threads. Although this contention can exist, SQL Server 6.5 has achieved more than 5,000 concurrent users on a four-way processor in TPC-C testing.
SQL Server 7.0 has improved the threading model.
The most important enhancement is the replacement of the Windows NT kernel schedule with the Microsoft SQL Server scheduler called User Mode Schedulers (UMS). SQL Server has one UMS for every processor. This UMS controls the scheduling of fibers and/or thread requests.
Fibers are a new Windows NT feature exploited in SQL Server 7.0, and are useful on CPU-busy systems with high-context switching. Fibers are lightweight and run on top of native Windows NT threading as shown in the preceding illustration. SQL Server now uses only one Windows NT thread per processor and many lightweight fibers on top of that thread. Fibers automatically assume the identity of the Windows NT thread they are running on and are nonpreemptive with respect to other SQL Server threads running on the other processors. Fibers, called lightweight pooling, should be turned on only if CPU is 100 percent and context switching is high.
The most CPU-intensive database applications benefit from this added thread scheduling flexibility. You now have the ability to move to an even higher number of concurrent users and achieve greater transaction throughput.
Recommendation: