Microsoft has a way of rolling out a technology in a way that, from a distance, glitters with promise. Get closer, and the reality is often prosaic and underwhelming.
So it is with SQLOS. SQLOS is a user mode operating system that helps SQL Server exploit concurrency, locality, and partitioning to their full degree, and last night Phil Hummel, Technology Architect at Microsoft's Technology Center in Mountain View, gave a talk on it.
Though Phil didn't say so directly, SQLOS seems to have been motivated by poor performance of SQL Server running on Windows. Problems included excessive context switching, inadequate control of memory and cache locality, and no understanding of hyperthreading. In addition, SQLOS helps the SQL Server development team partition its work efficiently. For the vast majority of the team, hardware issues are abstracted into the black box of SQLOS.
The benefits of SQLOS include
- performance (its primary goal)
- enhanced scheduling and processor locality
- a dedicated administrative connection (no more runaway servers that can't be stopped)
- support for hot adding of memory and CPU resources
- a hosting interface for services such as CLR and SQL Server 2008 Reporting Services
The last point needs some elaboration. Reporting Services previously ran in IIS. IIS is not designed to service long-running requests like database reports; it's intended to efficiently service many small requests. Customers were unable to tune IIS to handle large report requests in a reasonable way. Bringing Reporting Services under SQLOS rather than IIS was intended to solve that problem.
Phil spent a lot of time talking about SQLOS scheduling. Windows tries to ensure that all tasks get a share of the CPU by preempting long-running threads to let other threads run. This is especially important in an interactive environment like a GUI. In a database, it is much more efficient to let each scheduled query run to completion before starting the next one. SQLOS manages that with its schedulers: each logical CPU (each core in a multi-core chip) has a non-preemptive scheduler in SQLOS that assigns one thread at a time to the CPU. SQLOS schedulers are hyper-threading aware, and can move a task from a hyper-threaded CPU to a "real" CPU that becomes idle.
SQLOS also recognizes and accommodates NUMA (non-uniform memory access) architectures, something that will become increasingly important as systems scale above 32 cores.
As great as all this sounds, its impact is limited: only Microsoft SQL Server can benefit from SQLOS. It is a tightly coupled, private layer of SQL Server, not a standalone product or module. Even if you create an extended stored procedure (a function in a DLL that SQL Server launches when you call the procedure), it is thrown on the mercy of the Windows scheduler rather than receiving the benefits of SQLOS's non-preemptive scheduler.
So understanding SQLOS is important if you need to tune SQL Server for maximum performance in a robust environment, but plumbing its intricacies has little practical benefit for most of us.
Comments