Performace overhead and resolution when using CLR vs TSQL
Integration with .NET framework CLR within SQL Server gives extra flexibility for the developers to use other programming languages such as C# or VB.net by creating functions, stored procedures, triggers, data types, and aggregates are among the kinds of business logic that programmers can write with these languages. But think about performance overhead in using such features on a large scale basis.
IMHO if the same business logic can be implemented using Transact SQL (TSQL) then it is best to go with TSQL, as the general performance will be better. This is before one takes into account the project overhead of managing the source code for stored procedures, triggers, and other database objects. Also I like using advantage of CLR integration to write code that has more complex logic and is more suited for computational tasks than T-SQL can support. I personally haven't developer any such CLR but looked at the ability to logically and physically organize code into assemblies and namespaces is a huge benefit, and will allow you to better find and relate different pieces of code in a large database implementation.
There is an added benefit with managed code as and when it is executed, the CLR performs server checks in order to verify that the code is actually safe to run. For example, the code's memory access is checked to ensure that no memory is being read if it hasn't been written to. CLR-compliant languages are best suited for mathematically intensive functions and procedures that feature complex logic, or when you want to build a solution upon the .NET Framework base class libraries. I would to revisit Adam Machanic's article about the insight in using CLR-Routines within the SQL environment.
In this scenario we have had a situation of performance degradation in using CLR based function, this is mixture of dynamic SQL and UDF. Additionally we have to test the same using a CLR function, which would construct the dynamic query and execute and return the result. Actually the CLR function is running a query in an OLAP database. This is where the performance is affected when calling the CLR functions, when commented the results execution is faster. The reason for selecting CLR here is the procedure involves both significant data access and computation, where we have seperated this compilation procedural code into a CLR portion that calls into a T-SQL procedure to perform data access, or a T-SQL procedure that calls into the CLR to perform computation. As there are aggregation calculations against that OLAP database, I have looked into the SImon'sBlog on the UDFs performance.
So to wrapup if you have had such performance degradation issue then I would suggest to take help of SYSMON to capture counters as the memory is allocated by SQL Server. All the CLR allocations are delegated to SQL server for the thread pool: all the resources are handled by the CLR through SQL. Also if you can perform them with a single set-based TSQL statement that is absolutely the way to go. Iterating over a set from CLR-based code is interesting for scenarios where you need to do per-row computation that are not easily expressible with a UDF or a UDAggregate (or some other single-statement relational operation). Whether you iterate from TSQL using a cursor or from CLR using a SqlDataReader will depend on what exactly is that you're doing on a per-row basis.
Both T-SQL and CLR-hosted code run inside the database engine on the server. Having .NET Framework functionality and the database close together allows you to take advantage of the processing power of a server machine.
