CLR Performance

We had a CLR function that was running in seconds under the April CTP - now it takes over a minute in the new Sep CTP. We've determined that the bottleneck is in the following line:

Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")

Is there a new recommended way to open a connection?

That looks right to me, and I'd be very surprised if it was simply the SqlConnection constructor that was causing this (otherwise everyone would be hitting it).

Can you provide any more information?

Thanks,
-Isaac|||

My fault - it's not the constructor that's the overhead, it's the open command. My test function looks like the following:

<SqlFunction(dataaccess:=DataAccessKind.Read)> _
Public Shared Function PerfTest() As Integer
Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")
SqlConn.Open()
SqlConn.Close()
Return 0
End Function

If I call this function 15,000 times using Apr CTP, it runs in about 5 seconds. Using the latest, Sept CTP, it takes a minute or longer.

|||There was a bug introduced during this time (and fixed after the Sept. CTP) that might have some bearing. Check the memory allocations occuring in the CLR (SELECT single_pages_kb FROM sys.memory_clerks WHERE type = 'MEMORYCLERK_SQLCLR'). We had a problem where Open + Close of the context connection leaked memory, and required additional work to set up again. The equivalent to the connection pool not re-using connections, but with a bit less overhead to re-establish the connection.

One other question: are you seeing this perf difference for functions specificially, or does it also show up for procedures?

~Alazel

Developer
Microsoft Sql Server

This posting is provided "AS IS" with no warranties, and confers no rights.|||- Is there anyway I can obtain the fix to try out?
- I do not see the view "sys.memory_clerks" that you mention in your mail.
- The problem is apparent in the function since I call it over and over in a SELECT statement. I'm not sure about procedures.
|||- Sept CTP is the last SQL Server 2005 CTP. We would RTM soon and the fix would be available in it.
- Its actually sys.dm_os_memory_clerks
- Could you try creating a Stored Procedure with the same code and execute it same number of times as suggested by Alazel?

Thanks,
-Vineet.|||- The single_pages_kb increased from 1088 to 1096 when running the function.
- I created a stored procedure as you requested and I see the same performance problem - it runs in about the same time as the function.

I'm just curious if you have tried to run the PerfTest function (listed earlier in the posting) with the new SQL Server version to make sure the fix you mentioned has truly fixed the problem.
|||I tested running your function 15,000 times on a post-CTP16 build and it executed quickly (2 seconds, actually) as both a UD Function and a UD Stored Procedure.
Here is how I executed it to make sure we're doing an equivalent test:
declare @.i int, @.null int
set @.i = 1
while @.i < 15000
begin
select @.null = dbo.PerfTest()
set @.i = @.i + 1
end|||Yes, you're sample is pretty much what I did to test the UD stored procedure. I just think it would be in Microsoft's interest to send me a patch to test the fix in my environment. Let me know.