Designing a CLR Executable

Before you rush off to create your first CLR executable, it makes sense to pause and think a moment about how they work and what's involved behind the scenes to support their execution in SQL Server. One way to think about CLR executables is to handle them as you would handle remote COM (DCOM) components. Anyone who has worked with a remote component learns that it's expensive to make the trip to the component executable code, so it makes sense to do as much work as possible in the remote component once you arrive. That is, while remote components can expose public properties, it doesn't usually make sense to implement them because it costs too much to call the remote component just to set one static variable. This also assumes the component is designed to maintain state between property setting calls. This is also problematic for any system that hosts these objects, as they have to manage multiple instances of the objecteach with its own state.

On the other hand, what developers usually do when working with remote objects is execute methods on the remote objectspassing in "property" settings as method arguments. This reduces the number of round-trips, simplifies the design, and permits the invoked code (and the host) to create and managed stateless objects. This yields far more scalability. When you start calling CLR executables, keep this concept in mind. Design your components to support rich methods and few to no "initializers" or "destructors."

When the CLR executable is first executed, SQL Server must load a set of DLLs into memory so your first encounter with a CLR executable is expensive. Once in place, these DLLs remain in memory (competing for space) until RAM is needed by other competing processes. The first time your executable is invoked, the just in time (JIT) CLR compiler creates a processor-specific block of code to execute itagain impacting first-run performance. Again, this machine executable code is left in memory until all instances are done and the space is needed for other operations.