2

Is it correct to assume that a static variable which shares state between SQLCLR stored procedures will not be nullified between stored procedures execution, since the stored procedures are defined in the same assembly and are executed in a batch - which enables the query optimizer to recognise it ?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Lorin_F
  • 149
  • 1
  • 7

2 Answers2

2

Static variables do retain their value between executions of any methods within the Assembly (this, of course, requires that the Assembly be marked as UNSAFE). However, this has nothing to do with the Query Optimizer recognizing anything since the Query Optimizer has no insight into what is inside of the Assembly. Static variables retain their values due to the App Domain persisting until: service restart, forced unload due to memory pressure, security change made to the database containing the App Domain, security change to an Assembly in the App Domain, etc. The fact that static variables share their values between SQLCLR objects (not just stored procedures) is due to SQLCLR sharing the App Domain across all sessions.

Please note that while static variables can share values between executions, that:

  • This has nothing to do with batches. The values can be shared across query batches even.

  • It is not guaranteed that any value will always persist between all executions due to SQL Server being allowed to unload the App Domain even while a SQLCLR object is currently executing. So if two SQLCLR objects — Stored Procedures, perhaps — were executed one after the other, even in the same batch, it is possible for the App Domain to be unloaded ** prior to the execution of the 2nd Stored Procedure (i.e. between queries), and hence the App Domain would be started again to process the 2nd Stored Procedure, but with no indication of any prior calls, hence no prior state in any static variables.

    Meaning, if the value in the static variable being cleared will cause a subsequent execution of a SQLCLR object to do something unexpected, then do not use static variables for that purpose. They are fine for caching, but can be unreliable for many uses. IN FACT, there is another question, here on S.O., where someone was doing exactly this – caching some values to improve performance between executions of UPDATE statements – and ran into this problem. In that linked question, the O.P. has a main SQLCLR stored procedure that stores values in a static variable and executes other SQLCLR stored procedures that read from that static variable. The main SQLCLR stored procedure runs until its sub-calls complete successfully, but during its execution, the App Domain is marked for unload. At that point, all new SQLCLR executions happen in a new App Domain, even though the App Domain with the main SQLCLR stored procedure is still running. But, the App Domain for the main SQLCLR stored procedure has a state of "DOOMED" and cannot be accessed by any new SQLCLR calls (since any new calls happen in the new App Domain).

IF you need to persist state between executions within the same Session that is NOT shared with other Sessions, you can:

Each of those needs to be created / set in a SQLCLR stored procedure using "Context Connection = true;" as the Connection String. But they should be readable in a Scalar UDF, also using "Context Connection = true;" as the Connection String.

** An App Domain being marked for unload due to memory pressure, or due to a security change to the Database or Assembly, will not kill a currently running SQLCLR process. The App Domain remains running but is unloaded immediately upon the process completing.

HOWEVER, if the App Domain is unloaded due to .NET Integration / "clr enabled" being turned off via sp_configure, then the SQLCLR process will be immediately killed.

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • Well, maybe the Query Optimizer could recognize that the same assembly is needed again and again and therefore not execute forced unloads under memory pressures ? – Lorin_F Jul 10 '17 at 20:20
  • Nope. SQL Server will unload an App Domain whenever it feels it _needs_ to. This has nothing to do with any future calls, even in the same batch. I updated my answer with some clarifications while you were entering your comment, so please review the updates to my answer. – Solomon Rutzky Jul 10 '17 at 20:21
  • Even during execution? – Lorin_F Jul 10 '17 at 20:56
  • As far as I know, NO, the App Domain will persist _during_ execution, even if marked for unload during execution. I do know that you can drop an Assembly during execution since the image is copied into memory. – Solomon Rutzky Jul 10 '17 at 21:03
  • That brings up an interesting question, though. I will have to try running a long-running SQLCLR object and during its execution, disable .NET integration / "clr enabled" and see what happens. – Solomon Rutzky Jul 10 '17 at 21:05
  • Just a crazy idea of mine - what if I create another thread, and then block it (with some timeout) untill all is performed, just to keep the process alive? I try to avoid using tables to elevate performance. And thank you Mr. Srutzky for your valuable effort. – Lorin_F Jul 12 '17 at 09:10
  • @Lorin_F That won't work. I found that related question yesterday and came up with a test case for it. The test case shows that it is not possible. I was in the process of updating that related question with this info and then was going to update this one with a link to it. – Solomon Rutzky Jul 12 '17 at 19:21
  • Thank you. You put so much effort into it. – Lorin_F Jul 12 '17 at 21:32
  • @Lorin_F No problem. I feel like I "need" to know the answers to this stuff so that it can at least be documented somewhere. I just thought of another variation of the test so I will finish that other update later tonight. I will post a message here once I am done with that update. – Solomon Rutzky Jul 12 '17 at 22:23
  • @Lorin_F I have updated that other answer again, and then updated my answer here with the link and some additional explanation. Enjoy.. – Solomon Rutzky Jul 13 '17 at 19:24
  • I believe that in case of nullified static variables, one shouldn't recalculate the missing value - for given the circumstances the problem may occur again - but instead follow an alternative plan, and perhaps logging the problem for further action as required - perhaps adding memory to the system or using less resources. – Lorin_F Jul 14 '17 at 13:05
  • @Lorin_F As I said in that other answer, recalculate only if it makes sense to do so. Though it is probably a good idea to _also_ log the event so that you can get a sense of how frequently it happens. But if the calculated value won't be different, no sense in losing time by failing the operation if it can be handled appropriately. – Solomon Rutzky Jul 14 '17 at 15:28
0

Writing to static variables is not supported in SQL CLR. Your AppDomains can be torn down and reloaded at any time, and so you can't rely on static variable state to be preserved between calls.

The correct place to store data is in a table, or in SESSION_CONTEXT

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Then how can one safely use SQL CLR at all ? – Lorin_F Jul 10 '17 at 20:22
  • @Lorin_F: By not relying on static state? The `AppDomain` isn't going to be torn down in the middle of a query, if that's what you're thinking -- at least not without aborting the query to maintain consistency, which is all the safety SQL Server guarantees. That you get no hard guarantees doesn't mean the feature is unusable. – Jeroen Mostert Jul 10 '17 at 20:29