Share assemblies from a single database (simulate GAC) in SQL Server

Question

SQL Server version: 2005

.NET version: 3.5

I'm trying to work with a CLR UDF using the most efficient methods in regards to memory and performance. So it seems that SQL Server comes with pre-loaded .NET assemblies from the GAC. However, SQL server prohibits you from loading custom assemblies from the GAC to your SQL database. Trying to find an alternative approach to this, I experimented with SQL CLR by loading assemblies on a per database basis and ran SQL queries utilizing my CLR UDF to find that each database is assigned its own appdomain. In addition, each appdomain loads a copy of the assemblies (my main assembly with its dependencies). Makes sense.

OK, I then experimented with creating the assemblies into a single database and then referencing the CLR UDF via dbname.dbo.clrudfname in other databases to find something interesting. The only appdomain that loaded belongs to the database that hosts the assemblies. In addition, the only assemblies loaded are those that are hosted in that appdomain. In addition, performance was pretty much the same referencing another database's assembly opposed to the database hosting its own copy.

Does anyone see an issue with this approach in managing CLR memory more efficiently?

brian · Accepted Answer · 2012-12-20 21:30:57Z

4

There's no reason to have duplicate assemblies on the same database server. That's just extra copies to maintain.

Create one database, called Utilities (or CLRs or whatever) and put the CLRs there. Then you can reference them with a 3 part name (db.schema.object) from any database on the server.

Since you're using CLRs, I'd recommend you review David Wiseman's IO Utility solution here: http://www.wisesoft.co.uk/articles/sql_server_clr_io_utility.aspx

It's perfect for integrating the file system with T-SQL. It's also great for beginners learning how to build CLRs.

edited Dec 20, 2012 at 21:30

answered Dec 20, 2012 at 20:57

brian

3,72518 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Ritchson Over a year ago

I 100% agree with the maintainability concern you expressed. I was thinking that using a single database to service the CLRs without causing performance to suffer (ex. lock contention) is too good to be true.

Michael Ritchson Over a year ago

I ran a load test just recently by adding the assembly and CLR UDF to 4 databases and performing a SELECT statement calling the CLR UDF on each row to output a value as a column. I did about 150 iterations using a different database per iteration performing the SELECT call and recorded the highest and lowest MS it took for the statement to finish. I did this twice, one using the assembly on the current database and the other using the shared database. I found using the shared database recorded the highest time 1/3 higher than using the assembly per database.

Oleksandr Over a year ago

Note, that in case of having all CLR UDFs in one database, it would make it not possible to use such CLR UDFs in definitions of persisted computed columns in other databases.

Collectives™ on Stack Overflow

Share assemblies from a single database (simulate GAC) in SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related