SQL Server CLR Memory Allocation

sql-server memory encryption assemblies sqlclr
Chris Klepeis picture Chris Klepeis · May 7, 2009 · Viewed 8k times · Source

Currently we have MS SQL Server 2005 (32 bit). We have 1 assembly (and only 1 assembly) which we use for encryption and decryption. Only 512 MB of system memory is allocated to CLR. The assembly runs pretty slow, and I'm trying to rule out if its from memory or not. When I run the SQL code in query analyzer (not in an assembly) it runs quick. We are using symmetric keys and certificates for encryption / decryption.

Is there a recommended amound of memory to allocate to CLR? How can I tell if a lack of memory allocation is slowing down the performance of this assembly?

Answer

John Sansom picture John Sansom · May 7, 2009

How have you determined that 512MB has been assigned?

Take a look at the article I wrote on SQL Server memToLeave. You need to keep in mind that the portion of memory that is used to execute CLR managed code resides outside of the SQL Server Process space.

Let me know how you get on or if you have any further queries.

SQL Server Memory Configuration, Determining MemToLeave Settings

Related questions

How exactly can i troubleshoot MEMORY_ALLOCATION_EXT waits?

We have a server environment in SQL Server 2016. The Server is virtualized meaning it shares its memory and cpu. Lately if heavily under stress i.e. sorts building hashtables etc. we see heavy growth of memory usage that was expectable. …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More