Simon,
The CLR is hosted by SQL Server and is loaded the first time you invoke a .NET function. You create your .NET assembly and expose its functionality by registering the assembly with SQL Server and defining a function or procedure that can then in turn be invoked.
There will be overhead in not just loading but all that comes with the CLR like JIT, again this is where you must clearly understand when an where to use managed code.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_ovyukonnetprogfeatures.aspHTH
Neil
