The CLR is completely hosted by SQL Server. Routines running within SQL Server’s process space make requests to SQL Server for all resources, including memory and processor time. SQL Server is free to either grant or deny these requests, depending on server conditions. SQL Server is also free to completely restart the hosted CLR if a process is taking up too many resources. SQL Server itself is in complete control, and the CLR is unable to compromise the basic integrity that SQL Server offers.

Why Managed Objects Perform Well
SQL Server CLR integration was designed with performance in mind. Compilation of CLR routines for hosting within SQL Server is done using function pointers in order to facilitate high-speed transitions between T-SQL and CLR processes. Type-specific optimizations ensure that routines are just-in-time (JIT) compiled, so no further cost is associated with their invocation.

Another optimization is streaming of result sets from CLR table-valued functions (which will be covered in detail in the next chapter). Unlike some other rowset-based providers that require the client to accept the entire result set before work can be done, table-valued functions are able to stream data a single row at a time. This enables work to be handled in a piecemeal fashion, thereby reducing both memory and processor overhead.

Why CLR Integration Is Stable
SQL Server both hosts and completely controls the CLR routines running within the SQL Server process space. Since SQL Server is in control of all resources, routines are unable to bog down the server or access unavailable resources, as XPs could.

Another important factor is the HostProtection attribute. This attribute allows methods to define their level of cross-process resource interaction, mainly from a threading and locking point of view. For instance, synchronized methods and classes (for example, System.Collections.ArrayList. Synchronized) are decorated with the Synchronization parameter of the attribute. These methods and classes, as well as those that expose a shared provider state or manage external processes, are disallowed from use within the SQL Server–hosted CLR environment, based on permission sets chosen by the DBA at deployment time.

DBAs supporting the CLR features in SQL Server should realize that this is no longer thevworldvof XPs. These objects can be rolled out with a great deal of confidence. And as will be discussed latervin this chapter, the DBA has the final say over what access thevCLR code will have once it is deployedvwithin the server.

Source of Information : Apress Accelerated SQL Server 2008


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner