I finally got around to trying out SQL Server 2005 (a.k.a. Yukon) and <strong>reading</strong> up a little on how it operates under the covers. I had earlier <strong>discussed</strong> SQL Server Express. This is my first of series of posts where I will be highlighting some of the new things a developer can do in SQL Server 2005 from a .NET perspective, since there are many DBA’s who live and breathe SQL Server I will leave all the database and T-SQL specific stuff to them.

What is a runtime host? Basically any process that loads the .NET runtime and runs code in that managed environment is a runtime host. The most common host is the Windows shell - when you double-click on a .net application, the host (i.e. the Win shell in this case), loads the runtime in memory and then loads the requested assembly. The host loads the runtime by calling a shim DLL called mscoree.dll whose only purpose in life is to load the runtime. This exposes two API’s called ICorRuntimeHost or CorBindToRuntimeEx. Till Whidbey the only parameters that can be passed to either of the API’s are:

  • Server or workstation behaviour
  • CLR Version
  • GC Behaviour
  • Enable sharing of JIT code across AppDomains

Till SQL Server 2005 there were two runtime hosts for the .NET namely ASP.NET worker process and IE each with their own priorities of the framework. When adding the CLR, to SQL Server the three main goals in ascending order were:

  1. Security
  2. Reliability
  3. Performance

Its not surprising to see Security and Reliability a higher priority than performance. You don’t want your mission critical application to run in a non-secure environment that can allow for malicious code and allow hackers to get in. Also critical applications such as databases are quite often needed to sustain the five 9’s of requirement (99.999 % uptime) especially in the Enterprise environment. Lastly performance is important (hence it is one of the goals) as you don’t want to wait forever to get your result back, but that performance gain is not at the cost of a secure of reliable system.

SQL Server is a specialised host and not a “simple bootstrap mechanism”. To ensure the SQL Server goals some changes had to be made to the CLR which are incorporated in ver 2.0 (Whidbey). To allow the “new” hosts such as SQL Server to have hooks into the CLR’s resource allocation and management the .NET 2.0 hosts can use ICLRRuntimeHost instead of ICorRuntimeHost. The host then can call the SetHostControl() method which in turn delegates to GetHostManager() for things such as thread management, etc. Here is how it would look like:

<p> <img src="images/Hosting%20the%20CLR.png" alt=""/> </p>
Hosting the CLR

Resource Management - SQL Server lazy loads the clr, so if you never use it then it is never loaded. Unlike the “regular” CLR hosts where the CLR itself manages the resources, SQL Server manages its own thread scheduling, synchronisation, locking and memory allocation. This is done by layering the clr’s mechanisms in top of SQL Server’s mechanisms. SQL Server uses its own memory allocation scheme, managing “real memory” rather than virtual memory. This allows it to optimise memory, balancing between data and index buffers, query caches, etc. SQL Server can do a better job if is manages all of the memory in its process.

SQL Server also uses its own thread management putting threads to sleep when not running. The is also called <strong>user mode scheduler</strong> . SQL Server internally uses cooperative thread scheduling to minimise thread context switches, as opposed to preemptive thread scheduling used by the CLR. This means that in SQL Server a thread has to voluntarily give up control as opposed to the CLR where the processes takes over control after a certain time-slice.

The hosting API’s in .NET 2.0 allow a runtime host to either control of “have a say in” resource allocation. The APIs manage units of work (called Tasks), that can be assigned to a thread (or fibre). The SQL scheduler manages blocking points, and hooks PInvoke and interop calls out of the runtime to control switching the scheduling mode. This allows SQL Server to supply a host memory allocator, to be notified of low memory conditions at the OS level and to fail memory allocations if needed. SQL Server can also use the hosting API to control I/O completion ports.

I’ll be discussing Exception Management, Code Loading and Security in this new host in later posts. Till then, if you want to learn in more details the either get the book mentioned earlier in this post or keep a lookout here. Also if you plan on installing the newly released Beta 2 then check out <strong>Bob&rsquo;s post</strong> on some observations.