SQL Server Project (3)

In a SQL Server Project, all the SQL Server Objects (SSO), such as stored procedures (SP), must be defined as public static methods. All these methods are marked with Microsoft.SqlServer.Server Attributes, so that they can be deployed as SQL Server Objects.

Let's see how a SSO is called in SQL Sever. For example, when a SP is called first time, the SQL server will load the assembly library from the database, as I mentioned that all the assemblies have to be registered in a DB, into memory. Then the specified static method is called. After the SP finishes its job, however, the assembly stays in the memory forever. I tested this feature with a simple SP and a private static integer counter in its class. The counter increases by 1 for each call. The counter stays in the memory with its last call increment for several days.

This is a quite interesting feature of the deployed SSOs. Actually, if you think it in the context of SQL service process, it is not hard to understand it. The SQL service process loads the assembly into memory. Since static methods are global available, they will stays as long as the SQL service process stays.

However, this posts a problem most developers do not realize. They assume that when a call is finished, all the related resources should be released. If the assembly is not well designed, it may cause memory leaks in a SQL server. For example, if some resources are not cleaned, these resources are left in the memory for each call. You can imagine that if the SSOs were called constantly, it would cause memory leak. Another issue is that if some resources are static and not cleaned, these resources are occupied in memory. As a result, the first call is fine, but the next call may get exception since they cannot access these resources.

Therefore, you have to pay attention to all the cleaning jobs. Make sure that all the resources are freed after the execution, including the case of the execution being interrupted by clients. For example, you have to handle ThreadAbortException exception.

Talking about exception handling, it is not recommended to handle or hide all the exceptions. For example, if you design a Trigger, you may want some exceptions thrown to SQL server so that any related failure would cause the SQL server to roll back transactions. Therefore, if you know how to handle some exceptions, you can handle them, otherwise, leave them alone.

There is a way to clean assembly from memory in SQL server. Run the following command to clean all the unused cache and free up memory:

DBCC FREESYSTEMCACHE ('ALL')

I run this command in a daily job for cleaning memory used during a day.