April 21, 2009 at 7:24 am
Hi there
I've got four stored procs (all done in VB CLR) that I want to start at the same time from a control stored proc.
I've done a lot of Googling but don't seem to be having much luck 🙁 Any ideas?
April 21, 2009 at 7:34 am
Do not play with threading in CLR. I believe it requires UNSAFE and, if you're not careful you could crash the entire SQL instance. If you play with threads in a CLR procedure you're spawning threads that the SQL engine has no control over.
Why do all four procedures have to run simultaneously?
Have you considered service broker?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2009 at 8:41 am
They monitor performance so I need to get an at-the-time snapshot. I already use UNSAFE as they grab perfmon stats so I definitely take your point r. over relying on UNSAFE.
April 21, 2009 at 9:02 am
I agree with Gail. Threading from CLR is definitely on the list of "Things most likely to crash SQL Server". You really ought to take a different approach. Here are two:
1) Service Broker: set up a queue with multiple activation that calls your CLR functions. Queue multiple messages one for each thread.
2) External Activator: CLR is "Internal Activation" of .Net code, there is also a technique caled "External Activation". In this approach you write your .Net code as a SLQ Client that runs as a service on the SQL Server box (or any other server, actually).
I prefer (2) for a number of reasons, the big one for you being: no CLR restrictions. You can call ANY DLL or assembly that you want without restriction. As you are already finding the list of SAFE or even EXTERNAL_ACCESS assemblies is quite small compared to what is available in .Net.
The downside is that you have to implement a kind of bass-ackwards synchronization and calling design using either Query/Event notifications or Service Broker WaitFor.. RECEIVEs. The good news is that Microsoft has published an official "External Activator" on codeplex, complete with source code. The bad news is that the example code-listing is officialy part of SQL Sever 2008 only. The good news is that everything that it uses also works and is supported in SQL 2005.
[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]
April 21, 2009 at 9:34 am
Thanks, will have a look. I must admit I've not looked at Service Broker so I have no idea at the moment. :unsure:
April 21, 2009 at 9:48 am
The External Activator is announced and discussed here[/url].
[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]
April 21, 2009 at 1:34 pm
Cowboy DBA (4/21/2009)
They monitor performance so I need to get an at-the-time snapshot. I already use UNSAFE as they grab perfmon stats so I definitely take your point r. over relying on UNSAFE.
This sounds like it should be an external utility, not a SQL stored procedure. That way you can spawn threads and read perfmon and all that without worrying about unsafe, trustworthy and all the CLR restrictions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2009 at 1:07 am
I think you're right, maybe I was just trying to be too clever. Now to also consider how to copy the data back to a central repository.
April 23, 2009 at 4:19 am
GilaMonster (4/21/2009)
Do not play with threading in CLR. I believe it requires UNSAFE and, if you're not careful you could crash the entire SQL instance. If you play with threads in a CLR procedure you're spawning threads that the SQL engine has no control over.
Gail,
There are some very powerful statements there.
I would like to encourage as many people as possible to take a look over this PowerPoint presentation: How We Host The CLR by Christian Kleinerman - Microsoft Program Manager.
The later slides are particularly detailed and informative. The comparison with sp_OA* and XPs is useful too.
Cheers,
Paul
edit: typo
April 23, 2009 at 7:58 am
Paul:
I have read this presentation and I do not see anything in it that contradicts what either Gail or I have said. It does seem very typical of the marketing message that Microsoft was giving at that time (2005), hyping the benefits of CLR and being very reserved about what the actual limitations are.
Typically, there is a lot of hand-waving and equivocation about things like threading and synchronization in the first half of the presentation, but if you look closely you will see that they never actually say anything about explicit multi-threading by the CLR code in all of their SQL Scheduler talk. I infer from this that they are actually talking about the SQL OS's use of multi-threading in calling the CLR and not the converse (though you could certainly get that impression from it, which I do not assume is a coincidence).
In fact the only thing that they do say directly about it is that it is unsafe.
[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]
April 23, 2009 at 3:06 pm
Barry,
RBarryYoung (4/23/2009)
I have read this presentation and I do not see anything in it that contradicts what either Gail or I have said.Gail
If you play with threads in a CLR procedure you're spawning threads that the SQL engine has no control over.
Before .NET 2.0, the hosting APIs did not provide a means to put the CLR on top of a cooperatively scheduled host. With 2.0 and later, SQLOS is able to cooperatively schedule CLR threads through the UMS. The hosting APIs give SQLOS very fine control over the hosted code. For example SQLOS is notified if the code exits managed code (and therefore requires pre-emptive scheduling), if it calls back from unmanaged code to managed code, and so on. See Bart De Smet's blog entries for details.
As an example of SQLOS control, consider how CLR functions perform better if they voluntarily yield, as all good UMS tasks should do. Failing to yield results in a forced yield and 'punishment' - being rescheduled to the back of the runnable queue. See this article from the CLR Integration Team.
RBarryYoung (4/23/2009)
...but if you look closely you will see that they never actually say anything about explicit multi-threading by the CLR code in all of their SQL Scheduler talk. I infer from this that they are actually talking about the SQL OS's use of multi-threading in calling the CLR and not the converse (though you could certainly get that impression from it, which I do not assume is a coincidence).
It says "CLR goes through SQL OS for memory, threads, synchronization".
For an explicit statement see this technical article by Bob Dorr and Sameer Tejani. There is a wealth of information in there including the statement "The CLR engine is integrated with the SQL Server 2005 engine. They share a common thread pool, a memory pool, and synchronization mechanisms."
The ThreadPool object is cooperatively scheduled, whereas any explicitly started threads will be pre-emptively scheduled and performance will suffer due to context switching.
RBarryYoung (4/23/2009)
In fact the only thing that they do say directly about it is that it is unsafe.
I don't think I wrote anything that warranted such a glib reply. There is plenty of documentation that describes what the unsafe permission set really means - and what safety restrictions and controls still exist even when running at the unsafe level.
So long as one stays in managed code, and uses the SQLOS threadpool, SQL Server multi-threaded CLR routines can be a valuable (albeit niche) addition to our toolkit.
Paul
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply