Processing multiple results from CLR stored procedure in T-SQL

Question

I have a complex algorithm written in C # as a CLR stored procedure. The procedure is not deterministic (it depends on the current time). The result of the procedure are two tables. I did not find a solution how to handle multi-results from stored procedures in T-SQL. The execution of this procedure is key (the procedure is called every ~ 2 seconds).

I found the fastest way to update tables:

UPDATE [db-table] SET ... SELECT * FROM [clr-func] 

This is much faster than updating the db table from the CLR procedure through ADO.NET.

I used a static field to store the results and query after executing the clr stored procedure.

Call stack:

 T-SQL proc -> CLR proc (MyStoredProcedure) -> T-SQL proc (UpdateDataFromMyStoredProcedure) -> CLR func (GetFirstResultOfMyStoredProcedure) -> CLR func (GetSecondResultOfMyStoredProcedure) 

The problem is that sometimes CLR functions have a null value in the static field result , but in the CLR procedure, result not null. I found that sometimes the CLR functions are called in a different AppDomain procedure than the CLR. However, the CLR procedure still works and can perform the following operations, and no exception is thrown.

Is there any way how to make the CLR functions invoked in the same AppDomain as the "parent" CLR procedure?

Or is there some other way to achieve my intention?

PS: Initially, a complex algorithm was written in T-SQL, but the performance was poor (~ 100 times slower than the algorithm in C #).

Thanks!

Simplified code:

 // T-SQL CREATE PROC [dbo].[UpdateDataFromMyStoredProcedure] AS BEGIN UPDATE [dbo].[tblObject] SET ... SELECT * FROM [dbo].[GetFirstResultOfMyStoredProcedure]() UPDATE [dbo].[tblObjectAction] SET ... SELECT * FROM [dbo].[GetSecondResultOfMyStoredProcedure]() END // ... somewhere else EXEC [dbo].[MyStoredProcedure] 

-

 // C# public class StoredProcedures { // store for result of "MyStoredProcedure ()" private static MyStoredProcedureResult result; [SqlProcedure] public static int MyStoredProcedure() { result = null; result = ComputeComplexAlgorithm(); UpdateDataFromMyStoredProcedure(); result = null; } [SqlFunction(...)] public static IEnumerable GetFirstResultOfMyStoredProcedure() { return result.First; } [SqlFunction(...)] public static IEnumerable GetSecondResultOfMyStoredProcedure() { return result.Second; } private static void UpdateDataFromMyStoredProcedure() { using(var cnn = new SqlConnection("context connection=true")) { using(var cmd = cnn.CreateCommand()) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "[dbo].[UpdateDataFromMyStoredProcedure]"; cmd.ExecuteNonQuery(); } } } } 

Answer 1

According to Bob Beauchemin, “SQLCLR creates one application for each assembly owner, not one application for each database.” Do both SQLCLR collectors have the same owner?

Answer 2

There are two possibilities:

• A more likely scenario is that application domains become unloaded due to memory pressure. Generally speaking, there is only one application domain for a particular assembly (and therefore for the code in it), since application domains are intended for each database, for each owner. Thus, your code is not called in two application domains, at least not conceptually.

  However, there is a certain sequence of events regarding how SQL Server handles the upload of the application domains that you are experiencing. What happens is that your system is under pressure and notes that the application domain must be unloaded. This can be seen in the SQL Server logs, as it will tell you the exact domain name of the application that is being uploaded.

  AppDomain 61 ({database_name}. {Owner_name} [runtime] .60) is marked for unloading due to memory pressure.

  When the application domain is marked for unloading, it can continue to work until the entire current running process is complete. Currently it has a "state" E_APPDOMAIN_DOOMED instead of the usual E_APPDOMAIN_SHARED . If another process starts, even from a doomed application domain, a new application domain is created. And that is what causes the behavior that you experience. The sequence of events is as follows (and yes, I reproduced this behavior):

  • MyStoredProcedure : Application 1 domain is created if it does not already exist. Domain domain 1 "state" - E_APPDOMAIN_SHARED . result set to null .
    • result fills as expected
    • MyStoredProcedure Executes GetFirstResultOfMyStoredProcedure : The state of domain 1 of the domain is "still" E_APPDOMAIN_SHARED . result is retrieved as expected.
    • Application domain 1 is marked for upload: application domain 1 "state" is changed to E_APPDOMAIN_DOOMED
    • MyStoredProcedure Executes GetSecondResultOfMyStoredProcedure : The state of domain 1 of the domain is still E_APPDOMAIN_DOOMED and therefore cannot be used. An application domain 2 is created. Application state 2 "state" of the domain E_APPDOMAIN_SHARED . result set to null . That's why you sometimes get nothing: this process is in application domain 2 (although it was initiated from application domain 1), without access to application domain 1.
  • MyStoredProcedure completes: application domain 1 is being unloaded.

  
  And there is another possibility of how this sequence of events can happen: Domain 1 domain can be marked for unloading before GetFirstResultOfMyStoredProcedure executed. In this case, App Domain 2 is created when GetFirstResultOfMyStoredProcedure is GetFirstResultOfMyStoredProcedure , and it and GetSecondResultOfMyStoredProcedure start in the application domain 2 and return nothing.

  Therefore, if you want / need an error that should be selected under these conditions, then your Get*ResultOfMyStoredProcedure should check if result == null before trying to restore, and if it is zero, then make an error. OR, if you can recalculate the value of what is stored in a static variable, then if it is null just repeat it (for example, calling ComputeComplexAlgorithm again).

• It is less likely that since the application domain is shared by all sessions / callers into this code, it is possible if you did not otherwise ensure that only one execution of this process was performed at a time, that someone else or an SQL agent job or something it also performed MyStoredProcedure , which will ignore the static variable as it starts.

  Since you already accepted the use of the UNSAFE assembly to get an updated static variable, you can also add a locking mechanism to ensure that MyStoredProcedure is single-threaded.

---

Beyond these areas that could be considered, this process is likely to be carried out even faster and in less confusing ways. You can use Table-Valued Parameters (TVPs) to transfer data back to SQL Server, as well as from application code. Simply create one or two user-defined table types (UDTTs) that match the structures of the two result sets returned by TVF ( GetFirstResultOfMyStoredProcedure and GetSecondResultOfMyStoredProcedure ). Please see my answer here regarding the correct flow of results. Using this model, you can:

• make updates on the line MyStoredProcedure CLR proc
• get rid of a static variable
• Perhaps no longer needed for UNSAFE (if it was used only for a static variable). You might still need EXTERNAL_ACCESS if you cannot pass the results through the Context Connection, in which case you will use a regular connection (for example, the connection string uses either "Server = (local)" or does not indicate "Server" ").
• get rid of the UpdateDataFromMyStoredProcedure method
• get rid of UpdateDataFromMyStoredProcedure T-SQL proc
• get rid of the GetFirstResultOfMyStoredProcedure CLR function
• get rid of the GetSecondResultOfMyStoredProcedure CLR function
• free up all the memory that this static variable is currently using to store two result sets!

This approach is not only simplified, but most likely faster, it also does not allow the new application domain with the uninitialized static variable that you encounter :-).