SQL CLR very slow on Amazon EC2 Instance (SQL Server 2014 CU6)

Question

I am running a SQL Server 2014 Standard Edition (Cumulative Updates 6) instance on an r3.large Amazon EC2 box. I noticed that the CPU usage of SQL Server skyrockets to 100% while executing pretty much no queries at all.

Looking at the SQL Performance Tool, I can see that most of the time is lost in SQL CLR functions.

Here's a screenshot of one of such operations: https://dl.dropboxusercontent.com/u/3098924/Schermata%202015-03-04%20alle%20164617.png

Most of those CLR functions are doing basic things. For example the IsNullOrEmpty function is returning the result of String.IsNullOrEmpty() but even those are taking up between 1500 to 7000 ms to execute.

If I take the same job and run it on a physical box that we use for development, those calls can't almost be seen as they're way too quick.

Is there anyone who faced similar issues? I suppose it's due to EC2 or VMs but I can't really tell why. Thanks!

• Solomon Rutzky: Why are you passing a string into SQLCLR just to do the .IsNullOrEmpty method, when it is probably 100x faster to just do (ISNULL(variable, '') = '') ?
• Valerio Santinelli: @srutzky to make a long story short it's because the customer is writing queries and requested a CLR function to do exactly what you are saying. I know it makes no sense. On the other hand, that's not the only CLR function we provide of course. :)

Answer 1

It turns out that the CLR was not enabled and for some reason the exception wasn't being trapped and SQL Server took about 15 seconds just to say that the CLR was not enabled but during that time the instance is being kept at 100% CPU and hogged down.

In case anyone happens to have the same problem, you can turn the CLR on by running this:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO