Multi thread on SQLCLR

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Multi thread on SQLCLR

Multi thread on SQLCLR

Rate Topic

Display Mode

Topic Options

Author

Message

michel-834898

Posted Wednesday, July 30, 2008 4:46 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2008 1:49 AM
Points: 2, Visits: 3

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

Post #543963

RBarryYoung

Posted Wednesday, July 30, 2008 5:33 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #543978

Jonathan Kehayias

Posted Wednesday, July 30, 2008 9:09 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs

Post #544035

« Prev Topic | Next Topic »

Permissions