Chapter 8: Data Storage Design and SQL Server

This chapter will explore the most common storage technologies available to you. It will also present some benefits and concerns about different storage designs so that you can more easily choose what suits your solution. We will finish the chapter with a presentation of SQL Server-examining its architecture and how it fits into your storage policy.

Threads in SQL Server

Now we have come to the final part of the SQL Server architecture-threads. SQL Server 2000 uses Windows threads and fibers to execute tasks. We have covered threads earlier (in the section "Threads" in Chapter 4), but the fiber concept is new. Before we take a look at fibers, you will need to understand the concept of context switching and why this occurs. When SQL Server does not use fibers, it uses threads. These are distributed evenly across the available CPUs on the system. If you want, you can specify which CPUs SQL Server should use, but in most cases SQL Server handles this better than you can. When one thread is moved offa CPU and another is moved on, a context switch occurs. This is a very performance costly operation, since the switch is between user mode and kernel mode (we covered this switching process earlier in Chapter 4). To get the most out of your system, you should minimize context switching, and fibers is one way of doing this.

A fiber is a subcomponent of a thread, and is something you must enable for your SQL Server to use. Fibers are handled by code running in user mode. This means that switching fibers, and thereby switching tasks, is not as costly as switching threads. The reason for this is that the switch does not occur from user to kernel mode, but instead takes place in only one mode.

As opposed to threads, fibers are handled by SQL Server and not by the operating system. One thread is allocated per CPU available to SQL Server, and one fiber is allocated per concurrent user command. There can be many fibers on one thread, and fibers can also be switched on the thread. During the switch, the thread still remains on the same CPU, so no context switch occurs. By reducing the number of context switches, performance is increased.

NOTE Use fibers only if the server has four or more CPUs, and only use them when the system has more than 5000 context switches per second. You can use SQL Server Enterprise Manager to enable fiber mode (see Figure 8-13), or you can run sp_configure to set the lightweight pooling option to 1.



Figure 8-13.Manually enabling fiber mode from Enterprise Manager

Worker Processes

SQL Server keeps a pool of threads or fibers for all user connections. These threads or fibers are called worker processes. You can limit the number of worker processes by setting a maximum value for this. When this value is reached, SQL Server begins thread pooling. As long as the maximum worker process value has not been reached, SQL Server starts a new thread for the user commands, but as soon as the limit is reached and thread pooling has begun, each new request has to wait for thread to be available. You should not set the maximum value too high (default is 255) because the more worker processes you have, the more resources are consumed. This eventually affects performance. Experiment with it in the lab, and find the best setting for your system.