When installing Kiln 2.9 or later, you may have received a message asking you to install custom CLR (Common Language Runtime) functions. This article explains how this can be resolved by installing the SQL extensions manually.
Kiln versions 2.9 and above.
For example, Machine M hosts the Kiln website and it talks to SQL Server S, which hosts the Kiln database.
- Go to
\Program Files\Kiln\website\SqlExtensionson the machine M and copy
SqlExtensions.dllto a location on server S’ filesystem.
- Remember the path to the DLL file on server S.
- Ensure 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 a 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 the 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 to find the best resolution to your problem.