SQL Server - Wait Statistics : 1 - SQL Server Operating System (SQLOS) Basics

Few basic concepts around SQLOS which is a core component in SQL Server architecture, are critical for understanding SQL Server behaviour. If one wants to be good at troubleshooting performance issues in SQL Server, these concepts would help in creating that solid foundation. This blog is intended to explain the details around SQLOS and wait statistics in a simplified form. The entire topic will be covered in a series of posts. 

Let's go through these terms before we dive deep.

• SQLOS can be visualized as a mini operating system built for SQL Server that works on the top of Windows Operating system. This Operating system works on the top of Windows OS to help SQL Server perform some critical functions. This means that SQLOS in many cases can also be dependent on Windows OS. SQLOS was first introduced in SQL Server 2005. In general, SQLOS performs two major functions of scheduling and memory management.
• Physical CPU can be a multicore or single-core CPU on the SQL Server box. Each core acts as an individual processor. For example: a quad core cpu means on a single die, there are 4 CPUs.
• Logical CPU is CPU visible to Operating System (OS) irrespective of number of physical CPUs for its processing needs. There could be a quad core physical CPU (4 physical CPUs); but with the help of technologies like hyperthreading double the number of physical CPUs (i.e. 8 CPUs) are exposed to OS.
• Scheduler is a logical CPU on SQL Server side. Each scheduler is mapped to one logical processors exposed by operating system. However, one logical processor can have multiple schedulers assigned to it. Schedulers can be visible or hidden. A visible scheduler is visible to SQL Server user processes, whereas, the hidden scheduler is visible only to SQL Server internal processes. One scheduler is dedicated to DAC (Dedicated admin connection). This DAC is a back entry to SQL Server for the DBA if the server hangs.
• Worker is something that executes a task. Scheduler is also called worker thread. Worker threads in SQL Sever are mapped to windows threads or fibers by scheduler.
• Task is a unit of work assigned to SQL Server. Generally a session submits a batch. This batch is broken into multiple tasks and these are processed by multiple threads. This leads to parallellism.
• Session or SPID is a connection over which requests are sent to SQL Server.