Database Reference
In-Depth Information
SQL Server consists of five major components including the protocol layer, query processor, storage engine,
utilities, and SQL Server Operating System (SQLOS). SQLOS is the layer between Windows and all other SQL Server
components, and it is responsible for scheduling, resource management, and several other low-level tasks.
SQLOS creates a number of schedulers equal to the number of logical processors in the system. Every scheduler
is responsible for managing a set of workers that perform a job. Every task is assigned to one or more workers for the
duration of the execution.
Tasks stay in one of three major states during execution: RUNNING (currently executing on scheduler),
RUNNABLE (waiting for scheduler to execute), and SUSPENDED (waiting for the resource). SQL Server tracks the
cumulative waiting time for the different types of waits and exposes this information to the users. Wait Statistics
Analysis is a common performance troubleshooting technique that analyzes top system wait types and eliminates the
root-causes of waits.
It is essential that you create a baseline by collecting and monitoring various performance and load metrics in the
system. A baseline helps you to be proactive in detecting and resolving problems in the early stages before they start
affecting the users. It shows how system behavior and load changes over time, which helps in capacity analysis and
prevents the situation where a system outgrows the hardware.
