Database Reference
In-Depth Information
The Storage Engine consists of components related to data access and data management in SQL Server. It works
with the data on disk, handles transactions and concurrency, manages the transaction log, and performs several
other functions.
SQL Server includes a set of Utilities , which are responsible for backup and restore operations, bulk loading of
data, full-text index management, and several other actions.
Finally, the vital component of SQL Server is the SQL Server Operating System (SQLOS) . SQLOS is the layer
between SQL Server and Windows, and it is responsible for scheduling and resource management, synchronization,
exception handling, deadlock detection, CLR hosting, and more. For example, when any SQL Server component
needs to allocate memory, it does not call the Windows API function directly, but rather it requests memory from
SQLOS, which in turn uses the memory allocator component to fulfill the request.
the enteprise edition of SQl Server 2014 includes another major component called, “in-memory oltp engine.”
We will discuss this component in more detail in part 7, “in-memory oltp engine (hekaton).”
Note
SQLOS was initially introduced in SQL Server 7.0 to improve the efficiency of scheduling in SQL Server and to
minimize context and kernel mode switching. The major difference between Windows and SQLOS is the scheduling
model. Windows is a general-purpose operating system that uses preemptive scheduling. It controls what processes
are currently running, suspending, and resuming them as needed. Alternatively, with the exception of CLR code,
SQLOS uses cooperative scheduling when processes yield voluntarily on a regular basis.
SQLOS creates a set of schedulers when it starts. The number of schedulers is equal to the number of logical CPUs
in the system. For example, if a server has two quad-core CPUs with Hyper-Threading enabled, SQL Server creates
16 schedulers. Each scheduler can be in either an ONLINE or OFFLINE stage based on the process affinity settings
and core-based licensing model.
Even though the number of schedulers matches the number of CPUs in the system, there is no strict one-to-one
relationship between them unless the process affinity settings are enabled. In some cases, and under heavy load, it
is possible to have more than one scheduler running on the same CPU. Alternatively, when process affinity is set,
schedulers are bound to CPUs in a strict one-to-one relationship.
Each scheduler is responsible for managing working threads called workers . The maximum number of workers
in a system is specified by the Max Worker Thread configuration option. Each time there is a task to execute; it is
assigned to a worker in an idle state. When there are no idle workers, the scheduler creates the new one. It also
destroys idle workers after 15 minutes of inactivity or in case of memory pressure.
Workers do not move between schedulers. Moreover, a task is never moved between workers. SQLOS, however,
can create child tasks and assign them to different workers, for example in the case of parallel execution plans.
Each task can be in one of six different states:
Pending : Task is waiting for an available worker
Done : Task is completed
Running : Task is currently executing on the scheduler
Runnable : Task is waiting for the scheduler to be executed
Suspended : Task is waiting for external event or resource
Spinloop : Task is processing a spinlock
 
 
Search WWH ::




Custom Search