Multi thread on SQLCLR

Question

Multi thread on SQLCLR

michel-834898

SSC Rookie

Points: 48
More actions
July 30, 2008 at 4:46 pm

#188825

Hi all,
I need to retrieve some value from 100 different servers trough DBLINK. This connection is trough a VPN on Internet.
Each call take time, and the total off the 100 call take 5 minutes.
I would write a SQLCLR proc and work with a ThreadPool to execute many call on the same time.
It's possible, you have some simple sample or a guide line.
Thank for your help.
Michel

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I don't know about threading and SQLCLR, but I do know that you can do this with Linked Servers and Service Broker.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

While you can do threading in SQLCLR, it is UNSAFE and not recommended. This seems like a job for a SSIS package and a staging table to me. The SSIS package can be wrapped in a SQL Agent job, and called from a stored procedure with sp_start_job, or through service broker. I would strongly advise against doing this in SQLCLR for a number of reasons.

First SQLCLR runs in the MemToLeave memory area of the VAS. This is a very limited space, and you are likely to overflow it and have a memory abort of your CLR process which will unload the app domain.

Second, if you have a runaway process it could cause an unyielding scheduler which may cause a stack dump in SQL, and again unloading of the CLR process and your assembly.

CLR has its place in SQL, and what can and can't be done is highly limited, and for a number of really good reasons. There are much better tools for solving this problem than CLR.