1

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%2016.46.17.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!

  • 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, '') = '') ? – Solomon Rutzky Sep 30 '15 at 4:41
  • @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. :) – Valerio Santinelli Sep 30 '15 at 11:00
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.