Measuring SQLCR Performance using SQL Server Profiler

BasharKokash — Mon, 09 Jun 2008 08:38:00 GMT

Recently, I've been doing a lot of invoking .NET Assemblies within SQL Server 2005, I bloged an overview of SQLCLR here, also I posted about how to implement aggregation function in C# here.

Actually invoking .NET code has many advantages but at the same time we should be aware of the performance, that's why I tested my CountNulls Aggregation function - created in a previous post - with the SQL Server Profiler tool.

I tested the function on the Person.Contact table that is in the AdventureWorks Database which contains 19972 records.

SELECT dbo.CountNulls(MiddleName) from Person.Contact

The duration time was ranging between 93 and 154 milliseconds

While the following T-SQL statement gives the same results but with duration time ranges from 1 to 3 milliseconds maximum.

Select count(*) as from Person.Contact where MiddleName is null

As a result we should not move to use SQLCLR unless there is a clear sign, that means to implement a function in .NET we have to give T-SQL a chance and only when T-SQL fails we then use .NET Assemblies.

Bashar Kokash' Blog : C#, T-SQL

Measuring SQLCR Performance using SQL Server Profiler