Chris Mullins [MVP - C#]

All the talking about deadlocking the threadpool recently got me thinking.
I'm just throwing this out there to promote some discussion on the topic,
and hopefully see if some good ideas pop up...

When I first encountered the Threadpool deadlock issues, it was due to
calling synchronously calling SQL Server while running on a threadpool
thread. At some point, under load, all 25 (or 50, or whatever the actual #
was) threads were hung waiting for SQL to respond. This effectivly killed
the pool, as a threadpool thread was required by the Framework in order to
complete the request.

I had a long conversation with Jeff Richter one day about this, and he
informed me that doing synchronous I/O while on a threadpool thread is
stupid - I should have been making asynchronous calls to SQL server (using
BeginExecuteQuery), and letting the IOCP infrastructure worry about all the
threading. Afterall, all I/O should be done using Async operations - that's
why IOCPs were built.

After hearing this, I promptly went out and tried to do exactly this. I
consider myself something of an expert in asynchronous programming, and this
couldn't be that difficult. ADO.Net 2.0 had just been released, and the
SQLDB provider has all those nice BeginXXX calls.

... at which point I ran across a show-stopper. The ADO.Net call to
OpenConnection doesn't offer an asynchronous version. That's right - there
is no BeginOpenConnection. This means that no matter how Async I make my SQL
Server infrastructure, I'm going to block when I try to open a connection.

I've looked at options to work around this:
1 - Make the ADO.Net connection pool be the same size as the max # of
threadpool threads. This sucks up alot of resources, but should work. I'm
not really sure a good way to do this though, as much of the detail
regarding the connection pool is hidden from view. Short of poking directly
at connection strings, there doesn't seem a good way to do this.

2 - Write my own connection pool, that does provide this. I've never gone
down this path, as I figure there are many subtle issues. If the ADO.Net
team had this in there in the beta versions of .Net 2, but removed it for
the RTM, I probably don't want to deal with it. Given that Richter has an
IOCP based thread pool available in his Power Threading library, this should
be too hard...

3 - (the option I ended up going with) Stay synchronous in all my database
I/O, but do it on my own threadpool. I have explicit control over how many
threads are in this pool, and what types of load it can see.

I would love to hear what others have done to make a scalable .Net
application that hits SQL Server. The synchronous nature of the calls really
kills performance and scalability, and really provides a drag on everything.
Compared to the Async stuff I do with Files & Sockets, having SQL calls be
Async as well would be a huge win.

I would especially love to hear from anyone who has done this, taking
advantage of some feature of the SQL / CLR Integration that I'm aware of.
Should I host the app in SQL Server, just to take advantage of some magic,
"MakeItScale=True" flag?

I would super-especially love to from anyone who's made this work for a
solution other than just SQL Server.

This has also got to be a big limiting factor in LINQ to SQL. I can't be
executing LINK to SQL from a ThreadPool thread, as it's all going to be done
Synchronously, which could cause issues..

--
Chris Mullins