When installing Kiln 2.9 or later, you may have received a message asking you to install custom CLR functions. Here’s how:
Say machine M hosts the Kiln website and it talks to SQL Server S, which hosts the Kiln database.
\Program Files\Kiln\website\SqlExtensions on the machine M and copy
SqlExtensions.dll to some location on server S’ filesystem. Remember the path to the DLL file on server S. You’ll need the path below. Also make sure that if your kiln database is not called “kiln”, update the line
use kiln; to point to the right one.
Once on that server, run the following SQL script:
EXEC sp_configure 'clr enabled', 1; RECONFIGURE
CREATE ASSEMBLY KilnSqlExtensions FROM N'\path\to\SqlExtensions.dll' WITH permission_set = safe
CREATE FUNCTION KilnSplitBigints(@list nvarchar(MAX)) RETURNS table(ix bigint) AS EXTERNAL NAME KilnSqlExtensions.SqlExtensions.KilnSplitBigints
CREATE FUNCTION KilnSplitBinaries(@list nvarchar(MAX)) RETURNS table(ix binary(20)) AS EXTERNAL NAME KilnSqlExtensions.SqlExtensions.KilnSplitBinaries
Why does Kiln need to enable CLR and create these two functions?
As part of ongoing performance work in Kiln, we consolidated many “n + 1” SQL queries into a single query. Unfortunately, these consolidated queries often contain large lists of integers and binary data that interfere with the database query planner’s ability to cache query plans. Query plan caching is a big win and notably increases performance of frequently issued queries; losing it was a huge blow for us. As a result, these two functions are used in new queries so that they remain cache-friendly.
Does Kiln require CLR to be enabled?
Yes. At this time Kiln will not work without enabling CLR. If you are unable to enable CLR, please contact our friendly support staff and we’ll try to resolve the problem.