1. If you are using Visual Studio to build and deploy the SQL CLR solution ensure you have below settings

2) First things first, Ensure CLR Integration is enabled at instance level.

EXEC sp_configure 'show advanced options' , '1';
reconfigure;

EXEC sp_configure 'clr enabled' , '1' ;
reconfigure;

EXEC sp_configure 'show advanced options' , '0';
reconfigure;

3) Ensure Appropriate CLR Integration Code Access Security levels are set when creating assembly’s in SQL Server

CREATE ASSEMBLY [SOAPSQLSPCLR]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.WMVDSQL01\MSSQL\Binn\SQLCLRSoapConsumer.dll'
WITH PERMISSION_SET = [EXTERNAL_ACCESS,SAFE,UNSAFE]
GO

• SAFE : Only internal computation and local data access are allowed. SAFE is the most restrictive permission set. Code executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the registry

• EXTERNAL_ACCESS: These assemblies have the same permissions as SAFE assemblies, with the additional ability to access external system resources such as files, networks, environmental variables, and the registry
• UNSAFE:  unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can also call unmanaged code.

Please refer to msdn documentation here https://msdn.microsoft.com/en-us/library/ms345101.aspx

4) Ensure the database is setup correctly

If you are using assemblies with External access and Unsafe the database property TRUSTWORTHY needs to be turned on.

USE database_name
GO
EXEC sp_changedbowner 'sa'
ALTER DATABASE database_name SET TRUSTWORTHY ON 

Refer to MSDN documentation on TRUSTWORTHY https://msdn.microsoft.com/en-us/library/ms187861.aspx

My colleague reported to me that one of our database server is reporting consistent high CPU usage so I looked at it I noticed CPU was at 100% from last one week when I contacted the application owner and I foundthat they implemented a new feature that polls the database for every second to ensure the data collection process is running properly as it was necessary to ensure that we are under compliance in terms of reporting and auditing. So I ran a query to pull the queries with high cpu utilization with execution count. I certainly noticed a query running more often with high cpu usage.

I know that above highlighted query is causing the high cpu usage, next I looked at query stats and noticed this query is running twice every second, so I looked at the plan

Select top 1 col1 from table order by 1

Table is clustered and col1 is not part of clustered index and does not have an index. simple enough SQL server decides to do Clustered index scan and sorts(fully blocking) col1 and selects 1 row with no predicate SQL server doesn’t think its missing an index.

SQL Server Execution Times: with out non clustered index
CPU time = 2296 ms, elapsed time = 658 ms.

So I created a non clustered index on col1 in desc on the table

SQL Server Execution Times: with non clustered index
CPU time = 0 ms, elapsed time = 0 ms.

Cpu Usage dramatically reduced

Bottom line:

Its very important to understand no matter how much physical resources you might have on a  server its very important understand that one bad query can literally bring the server down to it knees.