SQL CLR Assembly

  • We use a SQL assembly to make web calls. We have 1 assembly with a few dozen functions, all use web calls.

    Recently we ran into an issue where one web call was locked up and the entire assembly was locked up and we couldn’t make any function calls. I’m having trouble finding the answer to my question online. My question is, does SQL run these functions in parallel, or can only one function run at a time in the assembly?

    Please help. Is there some sort of setting or limitation that would allow / prevent the same function from being called multiple times at once?

    For an example, I have an assembly with multiple functions. User A calls Function 1. Function 1 (lets say) takes 3 seconds. If User B calls Function 1, does user B’s function not fire until User A’s is finished? Note, we have no issues if User B calls Function 2 while User A is firing Function 1.

  • I have several SQL CLR Table Valued Functions and I'm able to call the same function multiple times with no issues at all. However, my functions don't make web calls.

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • They are handled asynchronously as far as I know AND have seen. However, I have seen in some cases a SQLCLR making web calls "lock up" and in none of those cases were multiple calls going on at the same time. We went back and cleaned it up a bit and made sure it was doing a good job disposing of objects when it was done with them. This helped IMMENSELY. We went from having at LEAST weekly problems to 1-2 times a month. Still not great but not too bad. Also, unless those calls NEED to occur real-time or near real-time I would suggest batching them up and running them from something like SSIS natively, this would be less likely to have issues.

    CEWII

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply